[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_unit_of_measure VARCHAR2,
p_po_distribution_id NUMBER,
p_oe_order_header_id NUMBER,
p_oe_order_line_id NUMBER,
p_routing_header_id NUMBER,
-- Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG
/* R12-PADDR p_chk_form OUT NOCOPY VARCHAR2, */
-- Vijay Shankar for Bug#4159557
p_interface_source_code VARCHAR2,
p_interface_transaction_id VARCHAR2,
p_allow_tax_change_hook VARCHAR2
--Reverted the chnage in R12 p_group_id IN NUMBER DEFAULT NULL /*added by nprashar for bug 8566481*/
) IS
/* Added by Ramananda for bug# exc_objects */
lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_tax_pkg.default_taxes_onto_line';
select NVL(inclusive_tax_flag,'N'),
end_date,
NVL(adhoc_flag,'N')--Added by qiong.liu for bug12717416 2011.07.06
from jai_cmn_taxes_all
where tax_id=pn_tax_id;
SELECT 1 FROM jai_rgm_parties a, JAI_RGM_DEFINITIONS b
WHERE a.regime_id = b.regime_id
AND b.regime_code=cp_regime_code
AND a.organization_type = cp_organization_type
AND a.organization_id = cp_organization_id
AND (cp_location_id is null or a.location_id=cp_location_id);
SELECT nvl(process_status_flag, jai_constants.no) process_status_flag,
regime_item_class,
invoice_no
FROM jai_rcv_rgm_lines
WHERE shipment_line_id = cp_shipment_line_id
AND regime_code = cp_regime_code;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = c_uom;
CURSOR get_paddr IS SELECT paddr
FROM v$session vs, v$mystat vm
WHERE vs.sid = vm.sid
AND ROWNUM = 1;
SELECT -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. attribute5,
shipment_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id;
SELECT delivery_id
FROM wsh_new_deliveries
WHERE name = p_delivery_name ;
SELECT nvl(unclaim_cenvat_flag,'N'),
nvl(cenvat_claimed_ptg,0),
nvl(non_bonded_delivery_flag,'N'), --3655330
nvl(cenvat_amount,0) -- Bug 4516678. Added by Lakshmi Gopalsami
FROM JAI_RCV_CENVAT_CLAIMS
WHERE shipment_line_id = p_shipment_line_id;
select order_header_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = p_shipment_num;
select qty_received
from JAI_RCV_LINES
where shipment_header_id = p_shipment_header_id
and shipment_line_id = p_shipment_line_id;
select sum(quantity)
from JAI_RCV_TRANSACTIONS
where shipment_header_id = p_shipment_header_id
and shipment_line_id = p_shipment_line_id
and transaction_type = p_transaction_type;
select interface_transaction_id, excise_invoice_no, excise_invoice_date, online_claim_flag
from jai_rcv_interface_trxs
where interface_transaction_id = cp_interface_trx_id;
SELECT item_id
FROM rcv_shipment_lines
WHERE shipment_line_id = cp_shipment_line_id;
SELECT apps_source_code
FROM po_requisition_headers_all WHERE requisition_header_id IN
(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE line_location_id = p_line_location_id
);
SELECT '1' , shipment_num
from rcv_shipment_headers
where shipment_header_id = p_shipment_header_id
And asn_type = 'ASBN';
select mmt_transaction_id
from rcv_shipment_lines
where shipment_line_id = cp_ship_line_id;
select abs(transaction_quantity) , transaction_uom , original_transaction_temp_id , prior_cost
from mtl_material_transactions
where transaction_id = cp_transaction_id ;
select *
from jai_mtl_trxs
where transaction_temp_id = cp_trx_temp_id;
SELECT *
FROM jai_cmn_lines
WHERE po_line_location_id = p_line_location_id
AND shipment_number = cp_shipment_num;
SELECT
(
SELECT
EXCISE_INV_NUMBER
FROM
JAI_CMN_LINES
WHERE
HEADER_INTERFACE_ID = RHI.HEADER_INTERFACE_ID
AND rownum = 1
)
EXCISE_INV_NUM,
(
SELECT
EXCISE_INV_DATE
FROM
JAI_CMN_LINES
WHERE
HEADER_INTERFACE_ID = RHI.HEADER_INTERFACE_ID
AND rownum = 1
)
EXCISE_INV_DATE
FROM
RCV_HEADERS_INTERFACE RHI
WHERE
RHI.receipt_header_id = p_shipment_header_id;
SELECT
'1'
FROM
rcv_shipment_headers RSH
WHERE
RSH.shipment_header_id = p_shipment_header_id
AND asn_type = 'ASN';
SELECT adhoc_flag
FROM jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
PROCEDURE insert_receipt_line IS
lv_mfg_trading JAI_RCV_LINES.mfg_trading%type ;
SELECT COUNT(*) INTO v_chk_receipt_lines
FROM JAI_RCV_LINES
WHERE shipment_line_id = p_shipment_line_id AND
shipment_header_id = p_shipment_header_id;
/*DELETE FROM JAI_RCV_LINES
WHERE shipment_line_id = p_shipment_line_id AND
shipment_header_id = p_shipment_header_id;*/
INSERT INTO JAI_RCV_LINES
(shipment_line_id,
shipment_header_id,
receipt_num,
qty_received,
boe_number,
excise_invoice_no,
excise_invoice_date,
online_claim_flag, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
tax_modified_flag,
line_location_id,
tax_amount,
MFG_TRADING,
transaction_id, --Added by Nagaraj.s for Bug#2692052.
organization_id,--Added by Nagaraj.s for Bug#2692052.
inventory_item_id, -- added by Aparajita for Bug#2813244
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (p_shipment_line_id,
p_shipment_header_id,
v_receipt_num,
p_qty_received,
NULL,
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_attribute1,
-- v_attribute2,
lv_excise_invoice_no,
lv_excise_invoice_date,
lv_online_claim_flag,
'Y',-- v_receipt_modify_flag,added 'Y' for bug#9045278
p_line_location_id,
NULL,
lv_mfg_trading, --NVL(v_manufacturing, 'N')||NVL(v_trading, 'N'), /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
p_transaction_id, --Added by Nagaraj.s for Bug#2692052.
p_organization_id, --Added by Nagaraj.s for Bug#2692052. This is made as per RCV_TRANSACTIONS.ORGANIZATION_ID so that Joins can be avoided with RCV_TRANSACTIONS
v_item_id,-- added by Aparajita for Bug#2813244
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login);
END insert_receipt_line;
PROCEDURE update_receipt_line IS
BEGIN
IF v_tax_total <> 0
THEN
UPDATE JAI_RCV_LINES
SET tax_amount = v_tax_total
WHERE shipment_line_id = p_shipment_line_id;
END update_receipt_line;
FOR date_rec IN (SELECT creation_date,
vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id)
LOOP
v_po_header_date := date_rec.creation_date;
PROCEDURE duplicate_shipment_update IS
BEGIN
IF p_transaction_type = 'RECEIVE'
THEN
FOR conf_rec IN (SELECT shipment_line_id,
qty_received
FROM JAI_RCV_LINES
WHERE shipment_line_id = p_shipment_line_id)
LOOP
v_conf := conf_rec.shipment_line_id;
FOR lines_rec IN (SELECT rtl.tax_amount,
rtl.tax_type,
rtl.tax_line_no,
jtc.rounding_factor
FROM JAI_RCV_LINE_TAXES rtl,
JAI_CMN_TAXES_ALL jtc
WHERE rtl.shipment_line_id = p_shipment_line_id
AND jtc.tax_id = rtl.tax_id)
LOOP
v_current_tax := ROUND((lines_rec.tax_amount * v_cor_quantity),
NVL(lines_rec.rounding_factor, 0));
UPDATE JAI_RCV_LINE_TAXES
SET tax_amount = v_current_tax
WHERE shipment_line_id = p_shipment_line_id
AND tax_line_no = lines_rec.tax_line_no;
UPDATE JAI_RCV_LINES
SET qty_received = NVL(qty_received, 0) + p_qty_received,
tax_amount = v_tax_total
WHERE shipment_line_id = p_shipment_line_id;
END duplicate_shipment_update;
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_qty_received,
p_transaction_id,
p_transaction_type,
p_transaction_date,
p_po_line_id,
p_line_location_id,
v_attribute1,
v_attribute2,
p_uom_code,
p_primary_quantity,
v_rg_type);
/*cursor query changed for bug 8486273 - on successful import, rows will be deleted
* from rcv_transactions_interface. We should check rcv_headers_interface table instead,
* where data would be retained until being purged. It should be noted that the group_id
* link between rcv_transactions and rcv_headers_interface is not one-one, but it is enough
* to establish that the receipt is imported through open interface.
bug 8594501 - earlier query caused mutating table error, as it hit the rcv_transactions
* table for the same row which triggered this procedure (not committed yet). For this,
* group_id is being passed as a procedure parameter from the trigger.*/
/* Code added from the above bugs needs to be Reverted as they are not supported in R12*/
--SELECT nvl(validation_flag, 'N') validation_flag commented by Vijay Shankar for bug#4240265
SELECT decode(header_interface_id, null, 'N', 'Y') imported_receipt_flag
FROM rcv_transactions_interface
WHERE interface_transaction_id = cp_interface_transaction_id;
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM RCV_HEADERS_INTERFACE RHI
WHERE RHI.group_id = p_group_id);*/
SELECT fnd_profile.value('JA_ACCESS_IPROC_TAX')
FROM DUAL;
FOR rec_upd_rec IN (SELECT nvl(receipt_modify_flag, 'N') receipt_modify_flag,
nvl(trading, 'N') trading,
nvl(manufacturing, 'N') manufacturing
/* following added by Vijay Shankar for Bug#4199929 */
, nvl(tax_change_on_open_int_recpts, 'N') tax_change_on_open_int_recpts
, nvl(tax_change_on_wms_recpts, 'N') tax_change_on_wms_recpts
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = v_rg_location_id)
LOOP
v_receipt_modify_flag := rec_upd_rec.receipt_modify_flag;
lv_dynamic_sql := 'select nvl(mobile_txn, ''N'') mobile_txn FROM rcv_transactions_interface WHERE interface_transaction_id = :1';
For reg_rec IN (SELECT item_class
FROM JAI_INV_ITM_SETUPS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id)
LOOP
v_item_class := reg_rec.item_class;
which in turn makes a call to ja_in_receipt_rg_pkg which had a select statement
from RCV_Transactions, which resulted in Mutation Error. Also, according to the URM
no RG Entries should happen at the point of Unordered Receipts and hence the coding
which was previously calling rg23_part_i_entry is commented and this has resulted in
Mutation Trigger being avoided. And also the same coding has been written in
the case where Transaction Type is Match. And also previously Tax Lines were inserted
for source_document_code='PO' but this can also go into an error as in case of UNORDERED
receipt, source_document_code will be PO, but Transaction Type will be Unordered and hence
this condition is also incorporated.
9. 21/11/2002 Changed by Nagaraj.s for Bug # 2659628 Version - 615.8
Receipt Taxes are recalculated irrespective of whether
there is a currency change or not so that rounding does
not happen on the higher side in both PO and Receipt.
10. 04/03/2003 By Nagaraj.s Version - 615.9
1. For Bug # 2692052
Commented the calls to ja_in_temp_receipt
Changed the calls to "TO HANDLE DELIVER RTR RTV"
Changed the Insert of JAI_RCV_LINES with 2 columns ( organization_id,transaction_id).
2. For Bug # 2808110
Added an Order by Clause in the Fetching
of Tax related Information from JAI_PO_TAXES
as this is very critical in the Precedence Logic Incorporated.
3. For Bug # 2798999 - Generic Fix for the One Off Patch.
Added the call to jai_rcv_utils_pkg.get_organization_id
to ensure that Organization Id and Item Id is picked up as this
is critical in INV_CONVERT.INV_UM_CONVERSION to calculate the UOM
Rate.
11. 10/03/2003 Aparajita for bug # 2813244. Version 615.11
For 'INTERNAL SALES ORDER', if the requisition has lines in uom other than the primary uom, then after the shipment when receipt is made in the destination organization, the taxes are not getting calculated properly.
The reason was that the uom conversion was as follows,
- uom from requisition and quantity from delivery
- uom from receipt and quanitity from receipt.
The quantity in delivery is always in primary uom where as the requisition is not in primary uom. The apportionment was hence going wrong.
Version 615.10 is obsoleted as wrong file was checked in.
12. 22/03/2003 Nagaraj.s for Bug # 2915829. Version : 615.12
The Tax Modified Flag is set through set_receipt_flag Procedure.
This is changed to populate the value as 'X' in case it is 'N'.
Hence Now the Tax Modified Flag will have 3 possible Values.
1. 'Y' - Which Indicates that Taxes can be changed at Receipt Level.
This Value will Invoke For Passing Accounting Entries Program
2. 'N' - Which Indicates that Taxes can no longer be changed at Receipt Level.
This Value will ensure that For Passing Accounting Entries will not be Invoked.
3. 'X' - Which Indicates that Taxes cannot be changed at Receipt Level,
But still this Ensures that For Passing Accounting Entries Program is Invoked
on Closing the Receipts Localized screen.
Hence, For Passing Accounting Entries Concurrent gets Invoked as Long as this Flag
has an value of 'X' or 'Y'. And For Passing Procedure Updates this to 'N' after
Processing. This Prevents Invoking of For Passing Accounting Entries Concurrent
Multiple Times.
13. 02/05/2003 Aparajita for bug # 2929171. Version#615.13
Taxes were not getting calculated properly when,
- uom is changed at receipt
- taxes have non zero precedences, that is tax on tax.
The problem was because of uom conversion being applied to non zero precedence taxes.
The tax lines are always calculated in the order of tax line number. When uom is changed,
only the tax precedence 0 undergoes the change. This conversion should not be applied to
precedence 1 as, when tax line 1 was calculated, that is already taken care of.
14. 08/05/2003 Nagaraj.s for Bug#2915783. Version#615.14
The Initialization of v_item_modvat_flag and v_item_trading_flag was not done at the
proper place as a result of which Modvat_flag of JAI_RCV_LINE_TAXES
was not populated properly. This has been moved to a location which is proper,
so that Proper comparison happens.
15. 13/05/2003 Vijay Shankar for bug # 2943558. Version#615.15
When a Receipt is made against a SCHEDULED Release (partial quantity) of
PLANNED PO, then taxes are calculated for whole of the PLANNED PO line quantity
instead of SCHEDULED Release quantity.
The issue is occuring because, precedence_0 is calculated as unit_price * quantity
of PO_LINES_ALL instead of PO_LINE_LOCATIONS_ALL. Code is modified to calculate
precedence_0 as PRICE_OVERRIDE * QUANTITY of PO_LINE_LOCATIONS_ALL instead of using
PO_LINES_ALL
16. 06/06/2003 Nagaraj.s for Bug #2991872. Version : 616.1
Code is added to ensure that if Assessable price is defined for Excise Taxes, then
the Assessable Price is picked up instead of the unit price from po_line_locations_all
table.
17. 21/07/2003 Vijay Shankar for Bug# 3028040, Version : 616.2
Code is added to check for EXPRESS transaction and then stop 'For Passing' to fire for every RECEIVE transaction
of EXPRESS Receipt. This change is made as the PADDR concept is removed in RCV_SHIPMENT_HEADERS DFF.
18. 29/07/2003 Nagaraj.s for Bug #2993865 . Version#:616.3
The two queries which were written earlier to fetch line_id,transaction_curr_code,
delivery_detail_id have been merged to form one query.
The join is now changed to
order. source document id = requisition.requisition header id
order line.source document line id = requisition line. requisition line id.
19. 30/07/2003 Nagaraj.s for Bug#3037075. Version#:616.4
The Vendor site id is now fetched in the lines_rec cursor and
the same is populated into JAI_RCV_LINE_TAXES table.
This change is applicable for only PO type of Transaction. Huge Dependency
20. 22/08/2003 Nagaraj.s for Bug#3057752. Version#:616.5
The changes are as below:
An Raise Application Error is written to ensure that RTR Transactions should not happen
if neither claim nor unclaim is done.
21. 31/10/2003 Nagaraj.s for Bug # 3123778, File Version : 616.6
The check for CGIN,CGEX items for 100% claim is removed as per
the Functional Requirement.
22. 03/11/2003 Nagaraj.s for Bug # 3202319 File Version : 616.7
In case of FGIN,FGEX Item Classes, the Claim percentage should not hold good.
Hence the cursor mod_rec is added with Item class FGIN,FGEX and the same
is incorporated in the IF Condition, which validates for 100% cenvat claim.
23. 05/11/2003 Nagaraj.s for Bug3237536 File Version : 616.8 (IN60105D2)
Added the condition v_tax_vendor_site_id := null as this was leading to Vendor
site id being populated in all cases irrespective of the type of Tax.
24. 02/06/2004 ssumaith - bug# 3657662 - File Version 115.1
There was a performance problem with a query . The issue was it was taking time because
of a full table scan on the oe_order_headers_all table.
Issue was resolved by retreiving the order_header_id from the JAI_OM_WSH_LINES_ALL
based on delivery id and adding a where clause to qualify the order_header_id.
25. 04/06/2004 Nagaraj.s - Bug # 3655330 - File Version : 115.1
In case of RTR and RTV scenarios, check has been made for either
claim or unclaim but in this case neither claim or unclaim happens and hence,
added one more condition :v_non_bonded_delivery_flag = 'N'
so that RTR and RTV will be done without any problems.
26. 18/06/2004 ssumaith - bug# 3683666 - File Version 115.2
When a RMA receipt is done without navigating from Receipts-localised form and not entering
the values in the DFF of RCV_SHIPMENT_HEADERS or RCV_TRANSACTIONS, it was observed that
taxes are not defaulted into localization tables (JAI_RCV_LINE_TAXES).
The reason this was happening is because the if condition which was checking this to be a
RMA receipt was also checking the chk_form to be not null with an "AND" to other required
conditions. Hence the problem
This issue has been fixed by doing making the v_chk_form check with 'OR' rather than 'AND'
Dependecny due to this bug - None
27. 22/07/2004 ssumaith - bug# 3772135 file version 115.3
When two internal internal orders created out of two internal requisitions are
merged into a single delivery ,and a receipt created for the delivery, it was
causing the taxes only for the first line to be populated.
28. 28/08/2004 Nagaraj.s for Bug#3858917 File Version : 115.4
In valid number error was occuring as the shipment number was entered as
alpha numeric and the condition was entering into the ISO route the comparison
for the shipment num with the delivery id in the cursor c_order_cur
was going into this error.
Ideally the condition :
========================================================
v_receipt_source_code = 'INTERNAL ORDER' OR -- AND
v_chk_form IS NOT NULL AND
========================================================
is wrong as in case of PO and RMA also this enters the code
and as the shipment num can be entered as any value the comparison is wrong.
In any case, this code should get executed for Internal Order
and hence the condition of v_chk_form is not needed.
Hence this is commented. This will be fixed as part of the generic
fix for the bug3848133 as this object is being changed.
29. 24/08/2004 Nagaraj.s - Bug# 3848133 (BaseBug# 3496408). File version : 115.4
The code now fetches the Precedences from the following points for the following sources:
----------------------------------------------------------------------------------------
source type Source Table
RMA JAI_OM_OE_RMA_TAXES
PO JAI_PO_TAXES
ISO JAI_OM_WSH_LINE_TAXES
----------------------------------------------------------------------------------------
In these 3 cases a insert into JAI_RCV_LINE_TAXES is present and this is,
changed to incorporate precedences as well as transaction id.
30. 05/11/2004 Vijay Shankar - Bugs#3949408. File version : 115.5
Commented the redundant code for MATCH processing
31. 14/10/2004 ssumaith - Bug# 3878439 File Version - 115.6
When a delivery which consists of multiple sales orders is split into multiple delivery
details at the shipment level only and not at sales order level, for each delivery detail
a shipment line is being created at the receipt level.
As there is no link available between the receipt line and delivery detail at the delivery
detail level, we are unable to exactly apportion the taxes.
devised approach to get the delivery detail based on the shipment line id also ini addition
to the other clauses.
This change has been done in the cursor which fetches the delivery details based on the order and
receipt details.
32 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.7
Modified the code to execute only for RECEIVE, UNORDERED, MATCH and RETURN TO VENDOR transactions.
Functionality for all the other transactions will be handled through ja_in_receipt_transaction_pkg, which is
coded for Receipts Corrections functionality and from now on will be used for all Localization Receipts
functionality as a gateway.
- added a new parameter p_chk_form (OUT Variable) to return back a value, if the receipt transaction that is
begin processed is created after navigating through Localization form
- Instead of submitting the concurrents JAINRVCTP is submitted for processing
- JAI_RCV_LINES.tax_modified_flag will be set now from above mentioned package when submitted from JAINPORE
- Added Validation at the end of Procedure to error out if this is an RTV and Cenvat is not yet Claimed
** Please refer to Old Version of the Object incase commented code needs to be looked at **
33 09/02/2005 Vijay Shankar for Bug# 4159557, Version:115.8
Modified the code to assign proper value to JAI_RCV_LINES.tax_modified_flag column based on Receipts
tax modification Value returned by Localization Hook given to customers. the hook is called in ja_in_receipt_tax_insert_trg
trigger and returned value is passed as parameter to this procedure which is used for tax_modified_flag value
determination
* This is a dependancy for Future Versions of the procedure *
34 22/02/2005 Vijay Shankar for Bug# 4199929, Version:115.9
Changes made in the previous version are modified to use new setup at Organization Addl Information instead of
value returned by Hook
Changes are made in internal procedure set_receipt_flag
* This is a dependancy for Future Versions of the procedure *
35 12/03/2005 Bug 4210102. resolved by LGOPALSA Version: 115.10
(1) Added CVD, Excise and customs education cess
36 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.12
.Implemented VAT Tax Calculation based on VAT Assessable value by making a call to jai_general_pkg.ja_in_vat_assessable_value
.JAI_RCV_LINE_TAXES.modvat_flag is set to proper value for VAT Taxes based on RECOVERABLE item attribute
of item. If item setup is not done, then Default value is taken as "Y" (meaning, tax is recoverable)
.modified the main SQL's to fetch taxes from various sources to use jai_regime_tax_types_v to fetch regime_code
against each tax so that the information can be used for MODVAT_FLAG setting of tax
.RTV will raise an exception incase recoverable VAT exists in the receipt line and it is neither Claimed or
Unclaimed
37 07/04/2005 Harshita for Bug #4285064 Version : 116.0(115.13)
When a user creates a new receipt against a purchase order, he needs to enter the following information
through a DFF : invoice no, invoice_date, Claim Cenvat On Receipt etc.
This DFF is provided at two places, header and line.
Information from the header DFF is captured into the rcv_shipment_headers table.
Information from the lines DFF is captured into the rcv_transactions table.
This information is retrieved into our base tables JAI_RCV_TRANSACTIONS and JAI_RCV_LINES.
At this time, a facility has been provided for the user to default the information
given at the header level DFF to all the lines only if these columns are null at the
line level. Else the information in the line level DFF is sustained.
For this NVL conditions have been added where this information gets defaulted.
38. 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 *
39. 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.
40 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
Removal of SQL LITERALs is done
41 27-Jul-2005 Bug 4516678. Added by Lakshmi Gopalsami, File Version 120.2
Issue :
a.Whenever a user creates a receipt for a CGIN or
CGEX item, 50% cenvat is claimed. If he/she intends to
return the entire quantity in the receipt, he/she must
claim the remaining 50% cenvat first and then do the
RTV. Else, the system should throw an error.
b.After creating a receipt for a CGIN or CGEX item,
if the user does a partial RTV on that receipt,
the system should allow it although the remaining
50% CENVAT has not been claimed.
Fix :
a. Added code to check this in Package jai_rcv_tax_pkg
(1) Created new procedure pick_register_type to get the
register_type depending on the item_class
(2)Created two new cursors c_fetch_receive_quantity
and c_fetch_transaction_Quantity to get the
quantity received for the receipt and RTV transactions
(3) Added nvl(cenvat_amount,0) in
cursor c_fetch_unclaim_cenvat
b. The cenvat receivable accounts were not getting passed
in case of a CGIN/CGEX item.
Fixed this by passing these values.
Commented the generic assignment for cenvat
accounting entries and added the condition for
CGIN and CGEX item class in procedure
accounting_entries.
42 01-Aug-2005 Ramananda for bug#4519697, File Version 120.3
Changed the value being assigned to variable - v_receipt_modify_flag from X to N at 2 places
As a part of this bug, the minus sign which got introducted during 'Removal of SQL Literals' is removed
Dependency due to this Bug
--------------------------
jai_rcv_trx_prc.plb (120.3)
jai_rcv_rt_t1.sql (120.2)
43 01-Aug-2005 Ramananda for bug#4530112. File Version 120.4
Problem
-------
In case of RTV, if VAT Claim is not done, system is giving error
Fix
---
1) Commented the Condition -
"IF lv_vat_recoverable_for_item = jai_constants.yes
AND NVL(ln_chk_vat, 0) <> 0
AND r_rcv_rgm_dtl.process_status_flag <> 'U' --Not Unclaimed
AND r_rcv_rgm_dtl.invoice_no IS NULL"
Dependency Due to this Bug -
File jai_rcv_rgm_clm.plb (120.2) is changed as part of this Bug,
so this object is dependent on current Bug and object jai_rcv_rgm_clm.plb (120.2)
44 05-Aug-2005 Ramananda for Bug#4533114, File Version 120.5
1) Added a new cursor - c_hdr_attribute5_1
2) Added a new begin end part and fetched the values from cursor - c_hdr_attribute5_1
3) In the cursor - line_rec, changed the condition -
"and spl.delivery_id = rsh.shipment_num"
to
"and spl.delivery_id = decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,
rsh.shipment_num,(select delivery_id from wsh_new_deliveries where name=rsh.shipment_num))"
44 19-Aug-2005 Ramananda for Bug#4562844, File Version 120.6
Problem
-------
System is creating receiving accounting entry and generating tax invoice against disable taxes
Fix
---
1) In the cursor for selecting the taxes from JAI_PO_TAXES,
changed the columns tax_rate, qty_rate and tax_amount
Dependency due to this Bug-
None
45. 24-Aug-2005 Bug4568090. Added by Lakshmi Gopalsami Version 120.7
Added check for trading items to set the modvat_flag
For trading items the modvat flag should be set to 'Y'
on tax lines if the item_trading_flag at item level is 'Y'
and modvat percentage is specified in the taxes
46. 02/11/2006 For Bug 5228046, File version 120.9
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
47. 21/02/2007 CSahoo for Bug#5344225, File Version 120.10
Forward Porting of 11i Bug#5343848.
Issue :
India - Receiving Transaction Processor Concurrent Program was called
for each transaction on a shipment line.
Fix :
In the procedure jai_rcv_tax_pkg,
Added code to set the Tax Modified Flag based on whether the Call was
made from India - Receipts Localized Form or not.
48. 13/04/2007 bduvarag for the Bug#5989740, file version 120.11
Forward porting the changes done in 11i bug#5907436
49. 13/04/2007 bduvarag for the Bug#4644524, file version 120.11
Forward porting the changes done in 11i bug#4593273
50. 27/06/2007 CSahoo for bug#6154234, File Version 120.12
commmented the line v_receipt_modify_flag = 'N'
Dependency Due to this Bug : Yes.
51. 16-07-2007 iSuppleir forward porting
Changed shipment_num to shipment_number
excise_inv_num to excise_inv_number in jai_cmn_lines table
52. 01-08-2007 rchandan for bug#6030615 , Version 120.17
Issue : Inter org Forward porting
53. 09/10/2007 CSahoo for bug#6209911, File Version 120.20
Added a IF block in the set_receipt_flag procedure.
54. 15/10/2007 bgowrava for Bug#6459894, File Version 120.22
Uncommented statements which were wrongly commented.
55. 16/10/2007 rchandan for bug#6504410, File Version 120.23
Issue : R12RUP04.I/ORG.QA.ST1:RTP GOING INTO ERROR FOR MFG-MFG INTRANSIT RCPT WITH VAT
Fix : Few utl_file debugs were added in previous version without checking for the v_debug_flag
Added the check now.
56. 16/10/2007 bgowrava for Bug#6459894, File Version 120.24
removed the lv_object_name from the JAI_PROCESS_MESSAGE of JAI_EXCEPTION
to avoid truncation of the error message text.
57. 01/01/2007 Walton for Inclusive Tax Computation
58. 05/03/2008 Kevin Cheng for bug 6853787
Add a condition to prevent receipt type like RMA from trapping into the tax calculation loop.
59. 08/04/2008 JMEENA for Bug#6917520, File Version 120.27
Assigned the error message to variable 'errormsg' and printed the message in exception section.
60. 19/08/2009 JMEENA for bug#8302581
Changed the v_tax_modvat_flag from No to Yes for RMA order where VAT recoverable flag is No for the Item
As VAT entries need to reverse for the RMA order.
61. 16/09/2009 Jia for bug#8904043
Issue: If Error definition's price list is assigned to Supplier, Advanced Pricing Error message is
not shown in log during receipt creation process .
Fix: Added Item-UOM validation logic before get AV, and catched validation error message handle.
62. 16-10-2009 vkaranam for bug#8880760
Issue:
TST1212.XB1.QA VAT TAX IS NOT RIGHT WHEN RECEIVE PART OF INTER-ORG TRANSACTION
Fix:
Chnages are done in default_taxes_onto_line procedure.
Interorg vat assessable value has been proportioned with the receipt qty.
62. 23/09/2009 Jia for bug#8932471
Issue: If Error definition's price list is assigned to Supplier null site level, Advanced Pricing
Error message is not shown in log during receipt creation process .
Fix: Added Item-UOM validation logic for Supplier null site level before get AV, and catched
validation error message handle.
63. 29/09/2009 CSahoo for bug#8920186, File Version 120.27.12010000.8
Issue: TST1212.XB1.QA DEFAULT TAX IS NOT RIGHT WHEN UPDATE UOM DURING RECEIPT PROCESS
FIX: Added a IF condition to calculate the assessable value correctly if the receipt UOM and
PO UOM are different.
64. 25/02/2010 vkaranam for bug#9045278
Issue:
Not able to account the ISO receipt.
Fix:
Jai_rcv_lines.tax_modified_flag has been populated as 'Y' .
65. 15/sep/2010 vkaranam for bug#10086567
Issue:
FOR RMA RECEIPT TAX AMOUNTS ARE COMING AS ZERO
Reason:
for rma orders UOM will be the UOM_CODE
for receipt UOM will be the UOM_description.
for getting the UOM_rate we need to pass the UOM_codes.
for rma the UOM_code has been again converted to another UOM_code ,due to which conversion
rate has been arrived as 0 and further based on the UOM rate the taxes has been arrived as 0.
Fix:
for RMA receipts UOM code retrieved in the RMA order has been passed directly to the UOM conversion
function.
UOM shall be converted to UOM code only if the source document code is not equal to 'RMA'.
Modified code is
--start additions for bug#10086567,added the if condition
if p_source_document_code='RMA'
then
v_po_uom_code := v_po_uom;
Issue :RG23D register is not getting updated for ISO receipt
Cause :
jai_rcv_line_taxes.modvat_flag is populated as 'N' even if the tax has the cr percentage >0.
fix:
for trading organization as per the userguide/PM
the below setup has to be there for the item:
item excisable : "Y"
item tradable : "Y"
item modvatable : "N".
item_trading_flag has been added for setting the jai_rcv_line_taxes.modvat_flag
70. 13-Aug-2012 Bug 14010660
Description: Excise Invoice Number and Date provided in ASN is not reflected in Receipts
Localized form.
Fix: Fetched Excise Invoice Number and Date from ASN and populated the same to Localized Receipt Tables
71. 13-Sep-2012 by nkodakan for the bug 14590356
Description: AT RECEIPT LEVEL INDIA LOCAL TAX AMOUNT IS CALCULATING WRONG AMOUNT FOR FOREIGN
Fix: Commented the line which is multiplying assessable value with currency conversion rate if the
assessable amount is defined. And multiplying with p_currency_conversion_rate if the assessable
amount is not defined.
Added variable 'v_assessable_val_defined' and 'ln_vat_assess_val_defined' to hold the assessable value.
Create a PO with foreign currency..Attach taxes.. Then the taxes are calculating below case.
Case 1: if the assessable value is define
For e.g if assessable value is 350, Quantity=100 and price=100 then Calculation is happening as
(Quantity * Assessable INR price/currency rate)*Excise 16% i.e (100*350/50) * 16% = 112 (currency USD)
Case 2: if the assessable valued is not defined
If the assessable value is not defined then
the calculation happening is (Quantity * Price)*Excise 16%
i.e (100*100)*16%=1600.
72. 03-Oct-2012 amandali for bug 14696186
Description:For receipts having non Adhoc UOm based taxes, the tax amount is calculated as zero
Fix:Added a condition for handling non adhoc UOM based taxes along with code for Adhoc UOM based taxes
===============================================================================================================
Bug Number Dependency
3037075 JAI_RCV_LINE_TAXES has been altered.
3057752 Tables : JAI_RCV_CENVAT_CLAIMS, ja_in_temp_mod_params, ja_in_batch_claim_modvat have been
altered.
3123778 JAI_RCV_CENVAT_CLAIMS has been altered to add the column partial_cenvat_claim
4210102 IN60106 + 4239736 + 4245089
==============================================================================================================
Dependencies For Future Bugs
-------------------------------------
IN60104d + 3037075
IN60104d + 3037075 + 3057752
IN60104d + 3037075 + 3057752 + 3123778
IN60105D2 + 3655330 + 3848133
IN60106 + 3940588 + 4239736 + 4245089 + 4346453
----------------------------------------------------------------------------------------------------------------------*/
--Added by Nagaraj.s for Bug#2499017
/*OPEN get_receipt_routing;
SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
Value,SUBSTR (value,1,INSTR(value,',') -1))
INTO v_utl_location
FROM v$parameter
WHERE name = 'utl_file_dir';
FOR po_rec IN (SELECT currency_code,
rate
FROM po_headers_all
WHERE po_header_id = p_po_header_id)
LOOP
v_currency_code := po_rec.currency_code;
FOR row_rec IN (SELECT ROWID,
organization_id
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id)
LOOP
-- v_rowid := row_rec.ROWID;
FOR loc_rec IN (SELECT form_id_drop
FROM JAI_CMN_LOCATORS_T
WHERE form_id_drop = v_form_id
AND paddr = v_paddr)
LOOP
v_chk_form := loc_rec.form_id_drop;
UPDATE JAI_CMN_LOCATORS_T
SET row_id = v_rowid
WHERE FORM_NAME = v_form_id
AND paddr = v_paddr;
FOR header_rec IN (SELECT receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id)
LOOP
v_receipt_num := header_rec.receipt_num;
FOR head_rec IN (SELECT receipt_source_code
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id)
LOOP
v_receipt_source_code := head_rec.receipt_source_code;
(SELECT rsl.shipment_header_id
FROM rcv_shipment_lines rsl
Where rsl.to_organization_id = p_organization_id
and exists -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. rsl.po_header_id in
(select pha.po_header_id
from po_headers_all pha
Where pha.vendor_site_id=p_vendor_site_id
and po_header_id = rsl.po_header_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
)
and rsl.shipment_line_id in
(select jrl.shipment_line_id
from JAI_RCV_LINES jrl
-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE jrl.excise_invoice_no = p_attribute1
-- AND jrl.excise_invoice_date = p_attribute2
WHERE jrl.excise_invoice_no = lv_excise_invoice_no
AND jrl.excise_invoice_date = lv_excise_invoice_date
)
)
LOOP
IF p_shipment_header_id <> excise_rec.shipment_header_id THEN
IF v_debug_flag = 'Y' THEN
UTL_FILE.PUT_LINE(v_myfilehandle,'error 1 dup exc inv ' );
FOR ll_rec IN (SELECT plla.unit_meas_lookup_code ll_uom,
pla.unit_meas_lookup_code l_uom
FROM po_line_locations_all plla,
po_lines_all pla
WHERE plla.line_location_id = p_line_location_id
AND pla.po_header_id = plla.po_header_id
AND pla.po_line_id = p_po_line_id)
LOOP
IF ll_rec.ll_uom IS NOT NULL THEN
v_po_uom := ll_rec.ll_uom;
FOR req_rec IN (SELECT unit_meas_lookup_code r_uom
FROM po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id)
LOOP
v_po_uom := req_rec.r_uom;
FOR rma_rec IN (SELECT order_quantity_uom rma_uom
FROM oe_order_lines_all
WHERE HEADER_ID = p_oe_order_header_id)
LOOP
v_po_uom := rma_rec.rma_uom;
FOR mod_rec IN (SELECT
modvat_flag,
item_trading_flag,
item_class --Added by Nagaraj.s for Bug3202319
FROM JAI_INV_ITM_SETUPS
WHERE organization_id = v_organization_id
AND inventory_item_id = v_item_id)
LOOP
v_item_modvat_flag := NVL(mod_rec.modvat_flag, 'N');
Duplicate_shipment_update;
UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_receipt_source_code ->'||v_receipt_source_code);
UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, p_transaction_type ->'||p_transaction_type);
UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_duplicate_ship ->'||v_duplicate_ship);
UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_chk_form ->'||v_chk_form);
FOR qty_rec IN (SELECT quantity
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id)
LOOP
v_loc_quantity := qty_rec.quantity;
insert_receipt_line;
FOR lines_rec IN (SELECT
rtl.tax_line_no,
rtl.tax_id,
rtl.tax_rate,
rtl.qty_rate,
rtl.uom,
rtl.tax_amount,
jtc.tax_type,
jtc.tax_name,
jtc.vendor_id,
NVL(jtc.mod_cr_percentage, 0) modcp,
NVL(jtc.rounding_factor, 0) rounding_factor,
jtc.duty_drawback_percentage duty,
--3848133
rtl.precedence_1,
rtl.precedence_2,
rtl.precedence_3,
rtl.precedence_4,
rtl.precedence_5,
rtl.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
rtl.precedence_7,
rtl.precedence_8,
rtl.precedence_9,
rtl.precedence_10,
tax_types.regime_code regime_code
--3848133
FROM JAI_OM_OE_RMA_TAXES rtl,
JAI_CMN_TAXES_ALL jtc
, jai_regime_tax_types_v tax_types
WHERE rtl.rma_line_id = p_oe_order_line_id
AND jtc.tax_id = rtl.tax_id
AND tax_types.tax_type(+) = jtc.tax_type
)
LOOP
-- Start of addition by Srihari on 30-NOV-99
--Gsr
/* Added by LGOPALSa. Bug 4210102.
* ADded Excise and CVD education cess */
/* Bug 4568090. Added by LGOPALSA
Added check for trading flag to ensure that
recoverable flag is properly set for trading items*/
IF v_item_modvat_flag = 'N' AND
v_item_trading_flag = 'N' AND
upper(lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*Bug 5989740 bduvarag*/
THEN
v_tax_modvat_flag := 'N';
FOR pick_rec IN (SELECT quantity
FROM JAI_OM_OE_RMA_LINES rel
WHERE rel.rma_line_id = p_oe_order_line_id)
LOOP
v_loc_quantity := pick_rec.quantity;
SELECT COUNT(*)
INTO v_chk_receipt_tax_lines
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = p_shipment_line_id
AND shipment_header_id = p_shipment_header_id
AND tax_id = lines_rec.tax_id;
DELETE FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = p_shipment_line_id AND
shipment_header_id = p_shipment_header_id AND
tax_id = lines_rec.tax_id;*/
INSERT INTO JAI_RCV_LINE_TAXES
(
shipment_line_id,
tax_line_no,
shipment_header_id,
tax_id,
tax_name,
currency,
tax_rate,
qty_rate,
uom,
tax_amount,
tax_type,
modvat_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
vendor_id,
claimable_amount,
--3848133
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10,
transaction_id
--3848133
)
VALUES
(
p_shipment_line_id,
lines_rec.tax_line_no,
p_shipment_header_id,
lines_rec.tax_id,
lines_rec.tax_name,
p_currency_code,
lines_rec.tax_rate,
lines_rec.qty_rate,
lines_rec.uom,
v_cor_amount,
lines_rec.tax_type,
v_tax_modvat_flag, --NVL(v_tax_modvat_flag,'N'),
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
lines_rec.vendor_id,
v_claimable_amount,
--3848133
lines_rec.precedence_1,
lines_rec.precedence_2,
lines_rec.precedence_3,
lines_rec.precedence_4,
lines_rec.precedence_5,
lines_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
lines_rec.precedence_7,
lines_rec.precedence_8,
lines_rec.precedence_9,
lines_rec.precedence_10,
p_transaction_id
--3848133
);
SELECT currency_code,
rate
INTO v_po_currency,
v_po_rate
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT tax_line_no,
llt.tax_id,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
uom,
--DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amount) tax_amount,
DECODE(SIGN(jtc.end_date - SYSDATE),
-1,
0,
DECODE(llt.currency,
v_func_currency,
llt.tax_amount,
llt.tax_amount *
nvl(v_currency_conversion_rate, 1))) tax_amount, --changed by pezheng for bug # 10630487
llt.tax_type,
jtc.tax_name,
llt.modvat_flag,
llt.vendor_id,
jtc.vendor_id tax_vendor_id,
jtc.vendor_site_id,
llt.currency,
jtc.rounding_factor,
jtc.duty_drawback_percentage duty,
llt.precedence_1,
llt.precedence_2,
llt.precedence_3,
llt.precedence_4,
llt.precedence_5
, tax_types.regime_code regime_code,
llt.precedence_6,
llt.precedence_7,
llt.precedence_8,
llt.precedence_9,
llt.precedence_10
FROM JAI_PO_TAXES llt,
JAI_CMN_TAXES_ALL jtc
, jai_regime_tax_types_v tax_types
WHERE line_location_id = p_line_location_id
AND jtc.tax_id = llt.tax_id
AND jtc.tax_type = tax_types.tax_type (+)
order by tax_line_no;
update JAI_RCV_LINES
set excise_invoice_no = r_jai_cmn_lines.excise_inv_number,
excise_invoice_date=r_jai_cmn_lines.excise_inv_Date
where shipment_line_id = p_shipment_line_id;
SELECT tax_line_no,
llt.tax_id,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
llt.uom,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amt) tax_amount,
llt.tax_type,
jtc.tax_name,
llt.modvat_flag,
nvl(jtc.vendor_id,p_vendor_id) vendor_id, /*rchandan for bug#6030615*/
jtc.vendor_id tax_vendor_id,
jtc.vendor_site_id,
llt.currency_code currency ,
jtc.rounding_factor,
jtc.duty_drawback_percentage duty,
llt.precedence_1,
llt.precedence_2,
llt.precedence_3,
llt.precedence_4,
llt.precedence_5
, tax_types.regime_code regime_code,
llt.precedence_6,
llt.precedence_7,
llt.precedence_8,
llt.precedence_9,
llt.precedence_10
FROM jai_cmn_document_taxes llt,
jai_cmn_lines cml,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v tax_types
WHERE cml.po_line_location_id = p_line_location_id
AND cml.cmn_line_id = llt.source_doc_line_id
AND cml.shipment_number = lv_shipment_num
AND llt.source_doc_type = 'ASBN'
AND jtc.tax_id = llt.tax_id
AND jtc.tax_type = tax_types.tax_type (+)
order by tax_line_no;
update jai_rcv_lines
set excise_invoice_no = r_jai_mtl_trxs.excise_invoice_no ,
excise_invoice_Date = r_jai_mtl_trxs.creation_Date
where shipment_line_id = p_shipment_line_id;
SELECT tax_line_no,
llt.tax_id,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
llt.uom,
DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amt) tax_amount,
llt.tax_type,
jtc.tax_name,
llt.modvat_flag,
NVL(jtc.vendor_id,r_jai_mtl_Trxs.from_organization) vendor_id, /* 6030615*/
jtc.vendor_id tax_vendor_id,
jtc.vendor_site_id,
llt.currency_code currency ,
jtc.rounding_factor,
jtc.duty_drawback_percentage duty,
llt.precedence_1,
llt.precedence_2,
llt.precedence_3,
llt.precedence_4,
llt.precedence_5,
tax_types.regime_code regime_code,
llt.precedence_6,
llt.precedence_7,
llt.precedence_8,
llt.precedence_9,
llt.precedence_10
FROM jai_cmn_document_taxes llt,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v tax_types,
mtl_material_transactions mtl
WHERE llt.source_doc_line_id = mtl.original_transaction_temp_id
AND llt.source_doc_type = 'INTERORG_XFER'
AND jtc.tax_id = llt.tax_id
AND mtl.transaction_id = r_rec_ship_txn.MMT_TRANSACTION_ID
AND jtc.tax_type = tax_types.tax_type (+)
order by tax_line_no;
SELECT price_override , quantity
INTO v_price_override,v_po_quantity
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id;
SELECT COUNT(*)
INTO v_chk_receipt_tax_lines
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = p_shipment_line_id
AND shipment_header_id = p_shipment_header_id
AND tax_id = po_lines_rec.tax_id;
/*DELETE FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = p_shipment_line_id AND
shipment_header_id = p_shipment_header_id AND
tax_id = tax_rec.tax_id;*/
INSERT INTO JAI_RCV_LINE_TAXES
(
shipment_line_id,
tax_line_no,
shipment_header_id,
tax_id,
tax_name,
currency,
tax_rate,
qty_rate,
uom,
tax_amount,
tax_type,
modvat_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
vendor_id,
claimable_amount,
vendor_site_id, --Added by Nagaraj.s for Bug3037075
third_party_flag,
--3848133
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10,
transaction_id
--3848133
)
VALUES
(
p_shipment_line_id,
po_lines_rec.tax_line_no,
p_shipment_header_id,
po_lines_rec.tax_id,
po_lines_rec.tax_name,
po_lines_rec.currency,
po_lines_rec.tax_rate,
po_lines_rec.qty_rate,
po_lines_rec.uom,
tax_amt_tab(i),
po_lines_rec.tax_type,
lt_tax_modvat_flag(i),
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
po_lines_rec.vendor_id,
v_claimable_amount,
v_tax_vendor_site_id(i) , /*Added by nprshar for bug # 10193326 */ --Added by Nagaraj.s for Bug3037075
lt_third_party_flag(i),
--3848133
po_lines_rec.precedence_1,
po_lines_rec.precedence_2,
po_lines_rec.precedence_3,
po_lines_rec.precedence_4,
po_lines_rec.precedence_5,
po_lines_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
po_lines_rec.precedence_7,
po_lines_rec.precedence_8,
po_lines_rec.precedence_9,
po_lines_rec.precedence_10,
p_transaction_id
--3848133
);
update_receipt_line;
UPDATE JAI_RCV_LINES
SET -- tax_modified_flag = 'N', /* Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG*/
line_location_id = p_line_location_id,
tax_amount = NVL(tax_amount, 0) + v_tax_total,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE shipment_line_id = p_shipment_line_id;
insert_receipt_line;
SELECT
line_id,
sha.transactional_curr_code,
spl.delivery_detail_id,
spl.quantity,
spl.unit_code
FROM
oe_order_lines_all sla,
oe_order_headers_all sha,
po_requisition_headers_all prha,
po_requisition_lines_all prla,
JAI_OM_WSH_LINES_ALL spl,
rcv_shipment_headers rsh
WHERE
prha.requisition_header_id = prla.requisition_header_id
and sha.source_document_id = prha.requisition_header_id
AND sla.header_id = sha.header_id
AND sha.header_id in /* following subquery added by ssumaith - 3772135*/
(
select order_header_id
from JAI_OM_WSH_LINES_ALL
where delivery_id = v_shipment_num
)
/* = v_order_header_id -- ssumaith - bug# 3657662*/
and sla.source_document_line_id = prla.requisition_line_id
/*and to_char(spl.delivery_id) = rsh.shipment_num*/
--and spl.delivery_id = rsh.shipment_num
--commented the above and added the below by Ramananda for Bug#4533114
and spl.delivery_id = decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,rsh.shipment_num,
(select delivery_id from wsh_new_deliveries where name=rsh.shipment_num))
and spl.order_line_id = sla.line_id
and prla.requisition_line_id = p_requisition_line_id
AND rsh.shipment_header_id = p_shipment_header_id
and rownum <= (select line_num from rcv_shipment_lines where shipment_line_id = p_shipment_line_id) -- bug#3878439
)
LOOP
v_line_id := line_rec.line_id;
SELECT tax_line_no,
stl.tax_id,
stl.tax_rate,
stl.qty_rate,
uom,
stl.tax_amount,
base_tax_amount,
func_tax_amount,
jtc.tax_name,
jtc.tax_type,
jtc.vendor_id,
jtc.mod_cr_percentage,
jtc.rounding_factor,
jtc.duty_drawback_percentage duty,
--3848133
stl.precedence_1,
stl.precedence_2,
stl.precedence_3,
stl.precedence_4,
stl.precedence_5,
stl.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
stl.precedence_7,
stl.precedence_8,
stl.precedence_9,
stl.precedence_10,
tax_types.regime_code regime_code
--3848133
FROM JAI_OM_WSH_LINE_TAXES stl,
JAI_CMN_TAXES_ALL jtc,
jai_regime_tax_types_v tax_types
WHERE delivery_detail_id = v_picking_line_id --added by GSri and Jagdish on 5-may-01
AND jtc.tax_id = stl.tax_id
AND tax_types.tax_type(+) = jtc.tax_type
)
LOOP
IF NVL(v_loc_quantity, 0) <> 0 THEN
v_cor_amount := ROUND(( P_qty_received * tax_rec.tax_amount * v_uom_rate / v_loc_quantity),
NVL(tax_rec.rounding_factor, 0));
SELECT COUNT(*)
INTO v_chk_receipt_tax_lines
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = p_shipment_line_id
AND shipment_header_id = p_shipment_header_id
AND tax_id = tax_rec.tax_id;
INSERT INTO JAI_RCV_LINE_TAXES
(
shipment_line_id,
tax_line_no,
shipment_header_id,
tax_id,
tax_name,
currency,
tax_rate,
qty_rate,
uom,
tax_amount,
tax_type,
modvat_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
vendor_id,
--3848133
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10,
transaction_id
--3848133
)
VALUES
(
p_shipment_line_id,
tax_rec.tax_line_no,
p_shipment_header_id,
tax_rec.tax_id,
tax_rec.tax_name,
p_currency_code,
tax_rec.tax_rate,
tax_rec.qty_rate,
tax_rec.uom,
v_cor_amount,
tax_rec.tax_type,
v_tax_modvat_flag,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
v_internal_vendor,
--3848133
tax_rec.precedence_1,
tax_rec.precedence_2,
tax_rec.precedence_3,
tax_rec.precedence_4,
tax_rec.precedence_5,
tax_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
tax_rec.precedence_7,
tax_rec.precedence_8,
tax_rec.precedence_9,
tax_rec.precedence_10,
p_transaction_id
--3848133
);
update_receipt_line;
UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert Receipt Line Procedure');
insert_receipt_line;
UTL_FILE.PUT_LINE(v_myfilehandle,'After Insert Receipt Line Procedure');
FOR exc_rec IN (SELECT jrtl.tax_amount, jrtl.tax_type tax_type, nvl(mod_cr_percentage, 0) mod_cr_percentage
, nvl(tax_types.regime_code, 'XXXX') regime_code
FROM JAI_RCV_LINE_TAXES jrtl, JAI_CMN_TAXES_ALL jtc, jai_regime_tax_types_v tax_types
WHERE shipment_line_id = p_shipment_line_id
AND NVL(modvat_flag, 'N') = 'Y'
AND jtc.tax_type = tax_types.tax_type(+)
AND jrtl.tax_id = jtc.tax_id)
LOOP
if upper(exc_rec.tax_type) IN ('EXCISE', 'CVD', 'ADDL. EXCISE', 'OTHER EXCISE',
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess, JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*Bug5989740 bduvarag*/
then
v_chk_excise := NVL(v_chk_excise, 0) + (exc_rec.tax_amount * exc_rec.mod_cr_percentage / 100);
FOR i IN (select manufacturing from JAI_CMN_INVENTORY_ORGS where organization_id=p_organization_id) loop
v_manufacturing := i.manufacturing;