[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1)
from JAI_RCV_LINE_TAXES
where shipment_header_id = p_shipment_header_id
and shipment_line_id = p_shipment_line_id;
select
count(1)
from
JAI_CMN_INVENTORY_ORGS
where
organization_id = p_organization_id AND
location_id = p_location_id;
select destination_type_code from
rcv_shipment_lines
where shipment_line_id = p_shipment_line_id;
select location_id
from JAI_INV_SUBINV_DTLS
where organization_id = p_organization_id
and sub_inventory_name = cp_subinventory;
SELECT rowid, receipt_source_code, receipt_num, shipment_num, shipped_date, organization_id,
vendor_id, vendor_site_id, customer_id, customer_site_id
FROM rcv_shipment_headers
WHERE shipment_header_id = pr_new.shipment_header_id;
select 1
from jai_rcv_headers
where shipment_header_id = cp_shipment_header_id;
SELECT tax_modified_flag
FROM JAI_RCV_LINES
WHERE shipment_line_id = cp_shipment_line_id;
SELECT NVL(process_enabled_flag, jai_constants.no) process_enabled_flag
FROM mtl_parameters_view
WHERE Organization_id = cp_organization_id;
SELECT transaction_type
from JAI_RCV_TRANSACTIONS
where transaction_id = pr_new.parent_transaction_id;
SELECT po_unit_price
FROM rcv_transactions
WHERE shipment_line_id = p_shipment_line_id
AND transaction_type = cp_transaction_type ; /* 'DELIVER'; Ramananda for removal of SQL LITERALs */
Change history for ja_in_receipt_tax_insert_trg.sql
S.No Date Author and Details
-------------------------------------------------------------------------------------------------
1. 31-aug-01 Subbu Modified code for DFF issue.
2. 07-07-02 Nagaraj.s for Bug2449826.
Incorporated an IF condition
wherein if the comments is OPM Receipt
then the trigger should not be processed
further.
3. 18/08/2002 Aparajita, revamp of process at receipt. bug #2514979.
Added the call to procedure Ja_In_Set_Rcv_Process_Flags
to set the process flag for various processes.
4. 23/08/2002 Nagaraj.s for Bug2525910
Incorporated an parameter in the call to ja_in_receipts_p.sql
pr_new.ROUTING_HEADER_ID
5. 24/10/2002 ashish for bug # 2613817
changes done for express receipt functionality.
this functionality enables a user to perform the express receipt.
this functionality was lost and is reintroduced.
6. 04/03/2003 Nagaraj.s for Bug#2692052 Version:615.4
High Dependency with this Patch
Added 3 arguments for the call to JA_NI_SET_RCV_PROCESS_FLAGS.
The Arguments are :
pr_new.ATTRIBUTE3
NVL(pr_new.ROUTING_HEADER_ID,0)
'TRIGGER'
7. 2003/04/01 Sriram - Bug # 2881674
Attribute5 was not getting copied for 'India RMA Receipt' attribute category. This has
been fixed in this bug.
8. 08/07/2003 Nagaraj.s for Bug#3036825. Version : 616.1
A new parameter attribute_category is passed to
ja_in_set_rcv_process_flags through this procedure.
10. 15/10/2003 Nagaraj.s for Bug#3162928. Version : 616.2
One more Condition is added in the Trigger to allow
"To handle Deliver RTR RTV" to fire in case of an
RMA Receipt/standard Delivery.
11. 08/01/2004 Nagaraj.s for Bug#3354415. Version : 618.1
The call to ja_in_set_rcv_process_flags is now having one more parameter
p_attribute5. Hence this would result into an Dependency. This is being
passed to the procedure
12. 13/03/2004 Nagaraj.s for Bug#3456636. Version: 619.1
The call to ja_in_set_rcv_process_flags is made only in case of Transaction
Type=RECEIVE so that the program flow may not enter ja_in_set_rcv_process_flags
in case of other transactions.
This Patch has an alter statement and is hence a high dependency.
13. 16/06/2004 SSUMAITH - bug# 3683666 File version 115.1
if the attribute_category is a null value and attribute2 is a not null value
it is being set to NULL and passed on to the jai_rcv_tax_pkg.default_taxes_onto_line procedure.
If the value is not one of the India Localization standard ones, then we entering the
values in a JAI_CMN_ERRORS_T and are returing the control.
The 'INR' check which was commented is now un-commented so that code returns in cases where
in Non-INR set of books , no processing occurs..
Dependency due to this bug - None
14. 16/07/2004 Vijay Shankar for Enhancement Bug# 3496408, Version: 115.2
trigger enabled to Support CORRECT transactions for Localization Processing
Also DELIVER and RTR transactions are delinked from Old Code and linked to New code with this enhancement.
New Concurrent program JAINRVCTP is called incase of DELIVER, RTR and CORRECT transactions
HIGH DEPENDENCY
15. 20/10/2004 Vijay Shankar for Bug#3927371 (3836490), Version: 115.3
Concurrent request for JAINRVCTP should not be fired for Direct Delivery case, as it is handled in
ja_in_rel_close_loc Also the issue of RTV passing Localization Accounting for UNORDERED Receipt
even if it is not matched is resolved by returning back the execution if PO_HEADER_ID
link is not found for transaction
16. 03/11/2004 Vijay Shankar for Bug#3959765, Version: 115.4
Modified the code added for Bug#3683666, so that the trigger fires even if NEW.attribute_category has customer DFF values.
commented the code that is checking for localization DFFs and doesnt allow customer DFFs for localization processing.
17. 30/11/2005 Aparajita for bug#4036241. Version#115.5
Introduced the call to centralized packaged procedure,
jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
18 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.6
Following are the changes done as part of RECEIPTS DEPLUG
- Submits a request for "India - Receiving Transaction Processor" for DELIVER, RTR, RTV, Any CORRECTs, RECEIVE
Transations that are not Created without Navigating from Localization Receipts Screen
- Makes a call to jai_rcv_tax_pkg.default_taxes_onto_line incase of 'RECEIVE', 'UNORDERED', 'MATCH', 'RETURN TO VENDOR' transactions only
- a new parameter v_chk_form is added in call to jai_rcv_tax_pkg.default_taxes_onto_line based on which request for JAINRVCTP is submitted
incase of RECEIVE transaction
- Commented the call to ja_in_set_rvc_process_flags as it is redundant with RECEIPTS DEPLUG from Old Code
- Updates flags of JAI_RCV_LINES with X value. updates transaction_id to MATCH transaction_id in case of
UNORDERED transaction
19 03/02/2005 Vijay Shankar for Bug# 4159557, Version:115.7
Modified the code, so that users will be able to modify taxes of Receipt by Querying it in Localization Screen with the
help of localization Receipts Hook for Open Interface/WMS Receipts.
v_chk_form chk is modified to look only for ASBN Receipts and submit request for "India - Receiving Transaction Processor"
* This is a dependancy for Future Versions of the trigger *
20 22/02/2005 Vijay Shankar for Bug# 4199929, Version:115.8
Revoked the call to jai_cmn_hook_pkg as it is replaced with Orgn. Addl. info setup usage in jai_rcv_tax_pkg.default_taxes_onto_line call
* This is a dependancy for Future Versions of the trigger *
21 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.9
modified an if condition to assign a proper value to lv_called_from variable. if a wrong value is assigned, then
India RTP may not process the transaction
22 25/03/2005 Vijay Shankar for Bug#4250171. Version:115.11
Code is modified to populate JAI_RCV_TRANSACTIONS even if the transaction is created through an
OPM Receipt/Return. This modification is done, so that VAT Processing of OPM Receipt happens through Discrete code
23 07/04/2005 Harshita for Bug #4285064 Version : 115.12
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.
24 15/04/2005 Harshita for Bug #4285064 Version : 115.13
Debug messages that have been added for testing were not removed in the previous chech in.
Removed the debug messages.
25 15/04/2005 Sanjikum for Bug #4105721, File Version 116.0(115.14)
Problem
-------
In case of RTR and RTV, PO_UNIT_PRICE is not updated with the proper costing effect.
Previously base was updating the PO_UNIT_PRICE, same as the PO_UNIT_PRICE of the
Deliver Transaction. Now base has changed the logic, and in 11.5.10, it is not
Populated correctly
Fix
---
In case of RTR and RTV, PO_UNIT_PRICE is updated same as the PO_UNIT_PRICE of the Deliver Transaction.
Following changes are done for the same -
1) Created a new inline function get_deliver_unit_price
2) Added a new IF Condition, before the <> LABEL
IF pr_new.transaction_type IN ('RETURN TO RECEIVING', 'RETURN TO VENDOR') THEN
pr_new.po_unit_price := get_deliver_unit_price(pr_new.shipment_line_id);
/* following insert is moved from bottom to here to take care of OPM Functionality also */
IF pr_new.transaction_type in ( 'RECEIVE', 'DELIVER', 'RETURN TO RECEIVING',
'RETURN TO VENDOR', 'CORRECT', 'MATCH')
THEN
jai_rcv_transactions_pkg.insert_row(
P_SHIPMENT_HEADER_ID => pr_new.shipment_header_id,
P_SHIPMENT_LINE_ID => pr_new.shipment_line_id,
P_TRANSACTION_ID => pr_new.transaction_id,
P_TRANSACTION_DATE => pr_new.transaction_date,
P_TRANSACTION_TYPE => pr_new.transaction_type,
P_QUANTITY => pr_new.quantity,
P_UOM_CODE => nvl(pr_new.uom_code, jai_general_pkg.get_uom_code(pr_new.unit_of_measure)),
P_PARENT_TRANSACTION_ID => pr_new.parent_transaction_id,
P_PARENT_TRANSACTION_TYPE => NULL,
P_destination_type_code => pr_new.destination_type_code,
P_RECEIPT_NUM => NULL,
P_ORGANIZATION_ID => pr_new.organization_id,
P_LOCATION_ID => NULL,
P_INVENTORY_ITEM_ID => NULL,
p_excise_invoice_no => null,
p_excise_invoice_date => null,
p_tax_amount => null,
P_assessable_value => NULL,
P_currency_conversion_rate => pr_new.currency_conversion_rate,
P_ITEM_CLASS => NULL,
P_ITEM_cenvatABLE => NULL,
P_ITEM_EXCISABLE => NULL,
P_ITEM_TRADING_FLAG => NULL,
P_INV_ITEM_FLAG => NULL,
P_INV_ASSET_FLAG => NULL,
P_LOC_SUBINV_TYPE => NULL,
P_BASE_SUBINV_ASSET_FLAG => NULL,
P_ORGANIZATION_TYPE => NULL,
P_EXCISE_IN_TRADING => NULL,
P_COSTING_METHOD => NULL,
P_BOE_APPLIED_FLAG => NULL,
P_THIRD_PARTY_FLAG => NULL,
P_ATTRIBUTE_CATEGORY => lv_comments,
P_ATTRIBUTE1 => NULL,
P_ATTRIBUTE2 => NULL,
P_ATTRIBUTE3 => NULL,
P_ATTRIBUTE4 => NULL,
P_ATTRIBUTE5 => NULL,
P_ATTRIBUTE6 => NULL,
P_ATTRIBUTE7 => NULL,
P_ATTRIBUTE8 => NULL,
P_ATTRIBUTE9 => NULL,
P_ATTRIBUTE10 => NULL,
p_tax_transaction_id => NULL
);
INSERT INTO jai_rcv_headers(
SHIPMENT_HEADER_ID
,RECEIPT_SOURCE_CODE
,RECEIPT_NUM
,SHIPMENT_NUM
,SHIPPED_DATE
,ORGANIZATION_ID
,VENDOR_ID
,VENDOR_SITE_ID
,CUSTOMER_ID
,CUSTOMER_SITE_ID,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
pr_new.shipment_header_id,
r_rcv_hdr.receipt_source_code,
r_rcv_hdr.receipt_num,
r_rcv_hdr.shipment_num,
r_rcv_hdr.shipped_date,
r_rcv_hdr.organization_id,
r_rcv_hdr.vendor_id,
r_rcv_hdr.vendor_site_id,
r_rcv_hdr.customer_id,
r_rcv_hdr.customer_site_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
For head_rec IN (SELECT attribute1,
attribute2,
attribute3,
attribute4,
attribute5, --ashish for bug # 2613817
receipt_source_code,
attribute_category
FROM rcv_shipment_headers
WHERE shipment_header_id = v_shipment_header_id)
LOOP
IF head_rec.attribute_category = 'India Receipt' THEN
pr_new.attribute_category := nvl(pr_new.attribute_category, head_rec.attribute_category);
pr_new.last_update_date,
pr_new.last_updated_by,
pr_new.last_update_login,
pr_new.unit_of_measure,
pr_new.po_distribution_id,
pr_new.oe_order_header_id,
pr_new.oe_order_line_id,
pr_new.routing_header_id
-- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
-- , v_chk_form -- commented by ssumaith - R12-PADDR
-- Vijay Shankar for Bug#4159557
, pr_new.interface_source_code
, pr_new.interface_transaction_id
, lv_allow_tax_change_hook
);
UPDATE JAI_RCV_LINES
SET
process_receiving = 'X', --DECODE(process_receiving, 'Y', 'Y', v_process_receiving),
process_delivery = 'X', --DECODE(process_delivery, 'Y', 'Y', v_process_delivery),
process_third_party_inv = 'X', --DECODE(process_third_party_inv, 'Y', 'Y', v_process_third_party_inv),
process_modvat = 'X', --DECODE(process_modvat, 'Y', 'Y', v_process_modvat),
process_rg = 'X', --DECODE(process_rg, 'Y', 'Y', v_process_rg),
process_populate_cenvat = 'X', --DECODE(process_populate_cenvat,'Y','Y',v_process_populate_cenvat) --Changed by Nagaraj.s for Bug3036825
process_rtr = 'X',
process_rtv = 'X'
-- this update is to take care that the RECEIPT line is of MATCH and not of UNORDERED
, transaction_id = decode(pr_new.transaction_type,'MATCH', pr_new.transaction_id, transaction_id)
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
WHERE shipment_line_id = pr_new.shipment_line_id;
UPDATE JAI_RCV_LINE_TAXES
SET transaction_id = pr_new.transaction_id
, last_update_date = sysdate
, last_updated_by = fnd_global.user_id
WHERE shipment_line_id = pr_new.shipment_line_id
and (transaction_id is null or transaction_id <> pr_new.transaction_id);
lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
UPDATE JAI_CMN_LOCATORS_T
SET row_id = v_rowid
WHERE FORM_NAME = 'JAINRTVN';