[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_last_update_date DATE ;--File.Sql.35 Cbabu := pr_new.Last_Update_Date;
v_last_updated_by NUMBER;--File.Sql.35 Cbabu := pr_new.Last_Updated_By;
v_last_update_login NUMBER ;--File.Sql.35 Cbabu := pr_new.Last_Update_Login;
SELECT NVL(Operating_Unit,0)
FROM Org_Organization_Definitions
WHERE Organization_Id = v_dest_org_id;
SELECT A.Segment1, A.Type_Lookup_Code,apps_source_code--added apps_source_code by rchandan for bug#4627239
FROM Po_Requisition_Headers_All A
WHERE A.Requisition_Header_Id = v_requisition_header_id;
SELECT Vendor_Id
FROM Po_Vendors
WHERE Vendor_Name = p_sugg_vendor_name;
SELECT Vendor_Site_Id
FROM Po_Vendor_Sites_All A
WHERE A.Vendor_Site_Code = p_sugg_vendor_loc
AND A.Vendor_Id = v_vendor_id
AND (A.Org_Id = v_org_id
OR
(A.Org_Id is NULL AND v_org_id is NULL)) ; /* Modified by Ramananda for removal of SQL LITERALs */
SELECT Line_Location_Id, Quantity, Price_Override*/
/*4281841 start*/
CURSOR cur_bpa_unit_measure(p_blanket_hdr IN NUMBER, p_blanket_line NUMBER) IS
SELECT unit_meas_lookup_code
FROM Po_Line_Locations_All
WHERE Po_Line_Id IN (SELECT Po_Line_Id
FROM Po_Lines_All
WHERE Po_Header_Id = p_blanket_hdr
AND Line_Num = p_blanket_line);
SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
a.precedence_1 p_1, a.precedence_2 p_2, a.precedence_3 p_3,a.precedence_4 p_4, a.precedence_5 p_5,
a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8,a.precedence_9 p_9, a.precedence_10 p_10,
a.currency, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.tax_type,
a.vendor_id, a.modvat_flag,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES a
WHERE a.line_location_id = p_line_location_id
ORDER BY a.tax_line_no;
SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
a.precedence_1 p_1, a.precedence_2 p_2, a.precedence_3 p_3, a.precedence_4 p_4, a.precedence_5 p_5,
a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8, a.precedence_9 p_9, a.precedence_10 p_10,
a.currency, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.tax_type,
a.vendor_id, a.modvat_flag,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES a
WHERE a.po_line_id = v_po_line_id
AND Line_Location_Id IS NULL
ORDER BY a.tax_line_no;*/
SELECT a.Po_Line_Id,
a.tax_line_no lno ,
a.tax_id ,
a.precedence_1 p_1,
a.precedence_2 p_2,
a.precedence_3 p_3,
a.precedence_4 p_4,
a.precedence_5 p_5,
a.precedence_6 p_6,
a.precedence_7 p_7,
a.precedence_8 p_8,
a.precedence_9 p_9,
a.precedence_10 p_10,
a.currency ,
a.tax_rate ,
a.qty_rate ,
a.uom ,
a.tax_amount ,
a.tax_type ,
a.vendor_id ,
a.modvat_flag ,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES a --Ja_In_Po_Line_Location_Taxes a
WHERE po_line_id = p_po_line_id
AND nvl(line_location_id,-999) = p_line_location_id
ORDER BY a.tax_line_no; --new cursor added by pramasub for FP
SELECT NVL( Currency_Code, '$' )
FROM Po_Requisition_Headers_V
WHERE Requisition_Header_Id = v_requisition_header_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = cp_unit_of_meas;
SELECT 1
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = p_reqn_line_id;
SELECT concurrent_program_id
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_name ='REQIMPORT'
AND application_id = 201;
select set_of_books_id
from hr_operating_units
Where organization_id = cp_org_id;
CHANGE HISTORY: FILENAME: ja_in_reqn_tax_insert_trg.sql
S.No Date Author and Details
-------------------------------------------------------------------------------------------------------------------------
1 06/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
tax_category_id column is populated into PO and SO localization tables, which will be used to
identify from which tax_category the taxes are defaulted. Also the tax_category_id populated into
the tax table will be useful to identify whether the tax is a defaulted or a manual tax.
2. 26/05/2003 sriram - Bug # 2977200
A check has been added to ensure that the insert into JAI_PO_REQ_LINE_TAXES
should happen only when the requisition line id
does not exist in the JAI_PO_REQ_LINE_TAXES table.
This check has been added at 2 places , before insert into JAI_PO_REQ_LINE_TAXES
3 21/10/2003 Vijay Shankar for Bug# 3207886, FileVersion# 616.2
Taxes are not getting defaulted from ITEM_CLASS and TAX_CATEGORY setup if Supplier information is not provided
requisition line. The issue occured because jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes accepts the Inventory
organization id as first argument, but we are passing Operating Unit Id as first parameter. Fixed the issue
by passing Destination Organization as parameter.
4. 30/11/2005 Aparajita for bug#4036241. Version#115.1
Introduced the call to centralized packaged procedure,
jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
5. 17/mar-2005 Rchandan for bug#4245365 Version#115.3
Changes made to calculate VAT assessable value . This vat assessable is passed
to the procedure that calculates the VAT related taxes
6. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
DB as required for CASE COMPLAINCE. Version 116.1
7. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
8 06-Jul-2005 rallamse for bug#4479131 PADDR Elimination
1. Replaced call to jai_po_cmn_pkg.query_locator_for_line with
jai_cmn_hook_pkg.Po_Requisition_Lines_All
9. 03/11/2006 SACSETHI for Bug 5228046, File version 120.4
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement, Tax precedence, BOE).
10. 17-APR-2007 Bgowrava for forward porting bug#5989740, 11i Bug#5907436 , File version 120.5
Changes added for Handling secondary and Higher Secondary Education Cess
This bug has datamodel and spec changes.
11 15-JUN-2007 ssawant for bug 6134111
The Taxes are defaulted from the Req to PO but the the Tax Category name
it not visible. Hence tax_category_id = v_tax_category_id_holder clause was added
in update table query.
12. 07-Nov-2008 JMEENA for bug#5394234
Increased the length of variables v_sugg_vendor_name and v_sugg_vendor_loc from 80 to 360
13 31-JUL-2009 Bug 8711805
Uom_Code was fetched from mtl_units_of_measure using a Query instead of cursor
Hence when unit_of_measure is NULL in case of 'Fixed Price Services' Line Type
(Set in Profile option 'POR : Amount Based Services Line Type') NO_DATA_FOUND
error is thrown.
14 10-SEP-2010 Jia for GST Bug#10091373.
Dependency:
----------
Sl No. Bug Dependent on
Bug/Patch set Details
-------------------------------------------------------------------------------------------------
1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
4033992.
ja_in_util_pkg_s.sql 115.0
ja_in_util_pkg_b.sql 115.0
2. 4245365 4245089 VAT implementation
--------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
--v_rowid := pr_new.ROWID;
v_last_update_date := pr_new.Last_Update_Date;
v_last_updated_by := pr_new.Last_Updated_By;
v_last_update_login := pr_new.Last_Update_Login;
pr_new.LAST_UPDATE_DATE,
pr_new.LAST_UPDATED_BY,
pr_new.SOURCE_TYPE_CODE,
pr_new.LAST_UPDATE_LOGIN,
pr_new.CREATION_DATE,
pr_new.CREATED_BY,
pr_new.ITEM_ID,
pr_new.ITEM_REVISION,
pr_new.QUANTITY_DELIVERED,
pr_new.SUGGESTED_BUYER_ID,
pr_new.ENCUMBERED_FLAG,
pr_new.RFQ_REQUIRED_FLAG,
pr_new.NEED_BY_DATE,
pr_new.LINE_LOCATION_ID,
pr_new.MODIFIED_BY_AGENT_FLAG,
pr_new.PARENT_REQ_LINE_ID,
pr_new.JUSTIFICATION,
pr_new.NOTE_TO_AGENT,
pr_new.NOTE_TO_RECEIVER,
pr_new.PURCHASING_AGENT_ID,
pr_new.DOCUMENT_TYPE_CODE,
pr_new.BLANKET_PO_HEADER_ID,
pr_new.BLANKET_PO_LINE_NUM,
pr_new.CURRENCY_CODE,
pr_new.RATE_TYPE,
pr_new.RATE_DATE,
pr_new.RATE,
pr_new.CURRENCY_UNIT_PRICE,
pr_new.SUGGESTED_VENDOR_NAME,
pr_new.SUGGESTED_VENDOR_LOCATION,
pr_new.SUGGESTED_VENDOR_CONTACT,
pr_new.SUGGESTED_VENDOR_PHONE,
pr_new.SUGGESTED_VENDOR_PRODUCT_CODE,
pr_new.UN_NUMBER_ID,
pr_new.HAZARD_CLASS_ID,
pr_new.MUST_USE_SUGG_VENDOR_FLAG,
pr_new.REFERENCE_NUM,
pr_new.ON_RFQ_FLAG,
pr_new.URGENT_FLAG,
pr_new.CANCEL_FLAG,
pr_new.SOURCE_ORGANIZATION_ID,
pr_new.SOURCE_SUBINVENTORY,
pr_new.DESTINATION_TYPE_CODE,
pr_new.DESTINATION_ORGANIZATION_ID,
pr_new.DESTINATION_SUBINVENTORY,
pr_new.QUANTITY_CANCELLED,
pr_new.CANCEL_DATE,
pr_new.CANCEL_REASON,
pr_new.CLOSED_CODE,
pr_new.AGENT_RETURN_NOTE,
pr_new.CHANGED_AFTER_RESEARCH_FLAG,
pr_new.VENDOR_ID,
pr_new.VENDOR_SITE_ID,
pr_new.VENDOR_CONTACT_ID,
pr_new.RESEARCH_AGENT_ID,
pr_new.ON_LINE_FLAG,
pr_new.WIP_ENTITY_ID,
pr_new.WIP_LINE_ID,
pr_new.WIP_REPETITIVE_SCHEDULE_ID,
pr_new.WIP_OPERATION_SEQ_NUM,
pr_new.WIP_RESOURCE_SEQ_NUM,
pr_new.ATTRIBUTE_CATEGORY,
pr_new.DESTINATION_CONTEXT,
pr_new.INVENTORY_SOURCE_CONTEXT,
pr_new.VENDOR_SOURCE_CONTEXT,
pr_new.ATTRIBUTE1,
pr_new.ATTRIBUTE2,
pr_new.ATTRIBUTE3,
pr_new.ATTRIBUTE4,
pr_new.ATTRIBUTE5,
pr_new.ATTRIBUTE6,
pr_new.ATTRIBUTE7,
pr_new.ATTRIBUTE8,
pr_new.ATTRIBUTE9,
pr_new.ATTRIBUTE10,
pr_new.ATTRIBUTE11,
pr_new.ATTRIBUTE12,
pr_new.ATTRIBUTE13,
pr_new.ATTRIBUTE14,
pr_new.ATTRIBUTE15,
pr_new.BOM_RESOURCE_ID,
pr_new.CLOSED_REASON,
pr_new.CLOSED_DATE,
pr_new.TRANSACTION_REASON_CODE,
pr_new.QUANTITY_RECEIVED,
pr_new.SOURCE_REQ_LINE_ID,
pr_new.ORG_ID,
pr_new.KANBAN_CARD_ID,
pr_new.CATALOG_TYPE,
pr_new.CATALOG_SOURCE,
pr_new.MANUFACTURER_ID,
pr_new.MANUFACTURER_NAME,
pr_new.MANUFACTURER_PART_NUMBER,
pr_new.REQUESTER_EMAIL,
pr_new.REQUESTER_FAX,
pr_new.REQUESTER_PHONE,
pr_new.UNSPSC_CODE,
pr_new.OTHER_CATEGORY_CODE,
pr_new.SUPPLIER_DUNS,
pr_new.TAX_STATUS_INDICATOR,
pr_new.PCARD_FLAG,
pr_new.NEW_SUPPLIER_FLAG,
pr_new.AUTO_RECEIVE_FLAG,
pr_new.TAX_USER_OVERRIDE_FLAG,
pr_new.TAX_CODE_ID,
pr_new.NOTE_TO_VENDOR,
pr_new.OKE_CONTRACT_VERSION_ID,
pr_new.OKE_CONTRACT_HEADER_ID,
pr_new.ITEM_SOURCE_ID,
pr_new.SUPPLIER_REF_NUMBER,
pr_new.SECONDARY_UNIT_OF_MEASURE,
pr_new.SECONDARY_QUANTITY,
pr_new.PREFERRED_GRADE,
pr_new.SECONDARY_QUANTITY_RECEIVED,
pr_new.SECONDARY_QUANTITY_CANCELLED,
pr_new.VMI_FLAG,
pr_new.AUCTION_HEADER_ID,
pr_new.AUCTION_DISPLAY_NUMBER,
pr_new.AUCTION_LINE_NUMBER,
pr_new.REQS_IN_POOL_FLAG,
pr_new.BID_NUMBER,
pr_new.BID_LINE_NUMBER,
pr_new.NONCAT_TEMPLATE_ID,
pr_new.SUGGESTED_VENDOR_CONTACT_FAX,
pr_new.SUGGESTED_VENDOR_CONTACT_EMAIL,
pr_new.AMOUNT,
pr_new.CURRENCY_AMOUNT,
pr_new.LABOR_REQ_LINE_ID,
pr_new.JOB_ID,
pr_new.JOB_LONG_DESCRIPTION,
pr_new.CONTRACTOR_STATUS,
pr_new.CONTACT_INFORMATION,
pr_new.SUGGESTED_SUPPLIER_FLAG,
pr_new.CANDIDATE_SCREENING_REQD_FLAG,
pr_new.CANDIDATE_FIRST_NAME,
pr_new.CANDIDATE_LAST_NAME,
pr_new.ASSIGNMENT_END_DATE,
pr_new.OVERTIME_ALLOWED_FLAG,
pr_new.CONTRACTOR_REQUISITION_FLAG,
pr_new.DROP_SHIP_FLAG,
pr_new.ASSIGNMENT_START_DATE,
pr_new.ORDER_TYPE_LOOKUP_CODE,
pr_new.PURCHASE_BASIS,
pr_new.MATCHING_BASIS,
pr_new.NEGOTIATED_BY_PREPARER_FLAG,
pr_new.SHIP_METHOD,
pr_new.ESTIMATED_PICKUP_DATE,
pr_new.SUPPLIER_NOTIFIED_FOR_CANCEL,
pr_new.BASE_UNIT_PRICE,
pr_new.AT_SOURCING_FLAG,
/* Bug 4535701. Added by Lakshmi Gopalsami
* Passing event_id and line_number as null
* for build issue */
/* Bug4540709. Added by Lakshmig Gopalsami
* Reverting the fix for bug 4535701 */
/* pr_new.EVENT_ID,
pr_new.LINE_NUMBER*/ -- the above two lines commented by ssumaith - bug#4616729
NULL, /* the following two nulls are added by ssumaith because these two columns are not present in the table po_requisition_lines_all at this time */
NULL
) ;
INSERT INTO JAI_PO_REQ_LINES (
requisition_line_id, requisition_header_id, tax_modified_flag,
tax_amount, total_amount,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
) VALUES (
v_requisition_line_id, v_requisition_header_id, 'N',
NULL, NULL,
v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login
);
SELECT Uom_Code
INTO v_uom_code
FROM Mtl_Units_of_Measure
WHERE Unit_Of_Measure = pr_new.Unit_Meas_Lookup_Code;
IF NVL(v_currency,'$$') = NVL(v_hdr_currency,'$$') THEN -- inserted by pramasub on FP
conv_rate := 1;
INSERT INTO JAI_PO_REQ_LINE_TAXES(
requisition_line_id, requisition_header_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
tax_id, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
tax_type, modvat_flag, vendor_id, currency,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_requisition_line_id, v_requisition_header_id, rec.lno,
rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, rec.tax_rate, rec.qty_rate, rec.uom, rec.tax_amount, rec.tax_amount + v_line_amount,
rec.tax_type, rec.modvat_flag, rec.vendor_id, rec.currency,
v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
INSERT INTO JAI_PO_REQ_LINE_TAXES(
requisition_line_id, requisition_header_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
tax_id, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
tax_type, modvat_flag, vendor_id, currency,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_requisition_line_id, v_requisition_header_id, rec.lno,
rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, rec.tax_rate, rec.qty_rate, rec.uom, rec.tax_amount, rec.tax_amount + v_line_amount,
rec.tax_type, rec.modvat_flag, rec.vendor_id, rec.currency,
v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login,p_vat_assessable_value => ln_vat_assess_value, -- Ravi for VAT
p_modified_by_agent_flag => v_modified_by_agent_flag, /*Added for Bug 8241905*/
p_parent_req_line_id => v_parent_req_line_id /*Added for Bug 8241905*/
-- , pn_gst_assessable_value => ln_gst_assessable_value
); -- Added by Jia for GST Bug#10091373 on 2010/09/10
v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login, -1*v_src_org_id,p_vat_assessable_value => ln_vat_assess_value ,
p_modified_by_agent_flag => v_modified_by_agent_flag, /*Added for Bug 8241905*/
p_parent_req_line_id => v_parent_req_line_id /*Added for Bug 8241905*/
--, pn_gst_assessable_value => ln_gst_assessable_value
); -- Added by Jia for GST Bug#10091373 on 2010/09/10
SELECT SUM(TAX_AMOUNT)
INTO v_tax_amount
FROM JAI_PO_REQ_LINE_TAXES
WHERE Requisition_Header_Id = v_requisition_header_id
AND Requisition_Line_Id = v_requisition_line_id
AND Tax_Type <> jai_constants.tax_type_tds ; /* 'TDS' ;Ramananda for removal of SQL LITERALs */
UPDATE JAI_PO_REQ_LINES
SET tax_amount = v_tax_amount,
total_amount = v_total_amount
WHERE requisition_line_id = v_requisition_line_id;
v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
SELECT Po_Header_Id, Po_Line_Id, ( Price_Override * Quantity ) Total,
Shipment_Type, Po_Release_Id, Source_Shipment_Id
, quantity -- cbabu for Bug# 3051278
, ship_to_organization_id, ship_to_location_id, price_override, Unit_Meas_Lookup_Code -- Vijay Shankar for Bug# 3193592
FROM Po_Line_Locations_All
WHERE Line_Location_Id = v_line_loc_id;
SELECT Inventory_Organization_Id
FROM Hr_Locations
WHERE Location_Id = p_ship_to_location_id;
SELECT Unit_Meas_Lookup_Code
FROM Po_Lines_All
WHERE Po_Line_Id = v_po_line_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = p_uom;
SELECT Vendor_Id, Vendor_SIte_Id, Currency_Code, Rate_Date, Rate_Type, Rate
, type_lookup_code, quotation_class_code, -- Vijay Shankar for Bug# 3193592
style_id --Added by Sanjikum for Bug#4483042
FROM Po_Headers_All
WHERE Po_Header_Id = hdr_id;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = line_id
-- AND Line_Location_Id IS NULL
AND ( Line_Location_Id IS NULL OR line_location_id = 0 ); -- cbabu for EnhancementBug# 2427465
SELECT Tax_Line_no,
Precedence_1, Precedence_2, Precedence_3, Precedence_4, Precedence_5,
Precedence_6, Precedence_7, Precedence_8, Precedence_9, Precedence_10,
Tax_Id, Currency, Tax_Rate, Qty_Rate, UOM, Tax_Amount, Tax_Type, Modvat_Flag,
Vendor_Id, Tax_Target_Amount,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = rqlineid
ORDER BY Tax_Line_No;
SELECT Tax_Type, Mod_Cr_Percentage, Vendor_Id
, adhoc_flag -- Vijay Shankar for Bug# 2782356
FROM JAI_CMN_TAXES_ALL
WHERE Tax_Id = taxid;
SELECT Vendor_Id
FROM Po_Vendors
WHERE Vendor_Name = v_sugg_vendor_name;
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_PO_TAXES
WHERE line_location_id = v_line_loc_id
AND Tax_Type <> jai_constants.tax_type_tds ; /* 'TDS'; Ramananda for removal of SQL LITERALs */
SELECT COUNT(REQUISITION_LINE_ID)
FROM JAI_PO_REQ_LINES
WHERE REQUISITION_LINE_ID = pr_new.REQUISITION_LINE_ID;
SELECT override_flag
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = c_vendor_id
AND vendor_site_id = c_vendor_site_id;
SELECT tax_category_id
FROM JAI_PO_REQ_LINES
WHERE requisition_line_id = p_requisition_line_id;
This is used when we are inserting taxes from
requistion and ja_in_po_line_locations already exists */
CURSOR Fetch_Focus_Id_Cur_for_req( line_id IN NUMBER , line_loc_id in Number ) IS
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = line_id
AND Line_Location_Id = line_loc_id;
select count(1)
from JAI_PO_TAXES
where line_location_id = cp_line_loc_id;
SELECT NVL( Currency_Code, '$' )
FROM Po_Requisition_Headers_V
WHERE Requisition_Header_Id = v_requisition_header_id;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = pr_new.line_location_id ;
A cursor is modified to fetch tax_category_id. UPDATE statement of JAI_PO_LINE_LOCATIONS is modified to update
tax_category_id column with defaulting tax category
3 04/02/2003 cbabu for Bug# 2782356, FileVersion# 615.3, Bug logged for IN60104
Adhoc tax amounts are not defaulted from requisition taxes to STANDARD PO autocreated from requisition
4 22/07/2003 cbabu for Bug# 3051278, FileVersion# 616.1
This fix is done to default the taxes onto PO Shipment line which got created through AutoCreate of
Blanket Release from Purchase Requisition. If there is any change in UOM between
requisition line and BPO Line, then PO Shipment quantity has to be given as input to jai_po_cmn_pkg.process_release_shipment
procedure. But requisition quantity is going as input and this is causing the problem.
Code is modified to pick PO Shipment quantity and give it as input to jai_po_cmn_pkg.process_release_shipment procedure
5 18/11/2003 Vijay Shankar for Bug# 3193592, FileVersion# 617.1
when multiple requisitions are merged to form a single PO Shipment during Autocreation process, then this trigger
is erroring out as taxes from multiple requisition lines are getting populated into the same line_location_id.
This is resolved by defaulting taxes from setup with an API call to jai_po_tax_pkg.ja_in_po_case2 procedure
instead of carrying taxes from requisition lines to PO Shipment
6.02/08/2004 ssumaith - bug# 3729015 file version 115.1
commented call to jai_po_cmn_pkg.process_release_shipment because in the ja_in_po_tax_insert_trg
a hook has been implemented which defaults the taxes from a requisition to the release.
and JAINPOCR concurrent is anyway called from ja_in_po_tax_insert_trg , hence the presense
of the call is superfluous.
Also ported the changes done as part of the one-off bug 3599268 into generic code path at two places
in the code.
7.12/Mar/2005 Bug 4210102 - Added by LGOPALSA Version 115.3
(1) Added Customs and CVD education cess
(2) Added checkfile in dbdrv
8. 14/03/2005 bug#4250072 rallamse Version# 115.4
VAT implementation
9. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
DB as required for CASE COMPLAINCE. Version 116.1
10. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
11. 08-Jul-2005 Sanjikum for Bug#4483042
1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
IL functionality should work or not.
12. 08-Jul-2005 Sanjikum for Bug#4483042, File version 117.2
1) Added a new column in cursor - Fetch_Po_Vendor_Id_Cur
13. 17-Aug-2005 Ramananda for bug#4513549 during R12 sanity testing. jai_mfg_t.sql File Version 120.2
Re-done the jai_po_rla_t1.sql 120.2 changes.
Problem :
---------
Existing taxes on PO were getting deleted and then getting
inserted from the setup always whenever merge happens.
Fix:
----
Checked whether tax already exists for PO for the current line location id
If there exists atleast one line we will not copy the taxes from the PO
Else we will copy the taxes from the current requisition which is getting
merged.
Dependency Due To the current fix:-
None
14. 07-Nov-2008 JMEENA for bug#5394234
Increased the length of variables v_sugg_vendor_name and v_sugg_vendor_locations from 80 to 360
15. 26-Aug-2009 Bgowrava for Bug#8766851 , File Version
Issue: CAN'T AUTOCREATE PO FOR INDIA OPERATING ORG
Fix: Introduced the v_reqn_tax variable to hold the value of number of taxes on the purchase requisition and if this is greater than zero then
the code for calculating tax and updating JaI_Po_Line_Locations is done.
16. 10-Sep-2010 Jia for GST Bug#10091373.
17. 19-Apr-2011 Xiao for bug#12344603
Issue: Exclusive tax amount is incorrect in autocreate po flow,
In procedure ARU_T1, when jai_po_tax_pkg.calculate_tax is invoked, Assessable value
is passed as line amount to calculate taxes.
Fixed: In fact, line amount should be passed to calculate tax as basis.
===============================================================================
Dependencies
Version Author Dependencies Comments
115.3 LGOPALSA IN60106 + Added CVD and Customs education cess
4146708
115.4 rallamse IN60106 + For VAT implementation
4146708 +
4245089
120.2 RPOKKULA jai_po_da_t1.sql 120.2 (Functional)
------------------------------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
v_requisition_line_id := pr_new.Requisition_Line_Id;
v_last_upd_dt := pr_new.Last_Update_Date ;
v_last_upd_by := pr_new.Last_Updated_By;
v_last_upd_login := pr_new.Last_Update_Login;
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';
because in the ja_in_po_tax_insert_trg, because of a hook ,
the taxes JAINPOCR concurrent is anyway called from ja_in_po_tax_insert_trg
jai_po_cmn_pkg.process_release_shipment ( 'BLANKET',
v_src_ship_id,
v_line_loc_id,
v_po_line_id,
v_po_hdr_id,
-- v_qty, -- commented by cbabu for Bug# 3051278
v_quantity, -- cbabu for Bug# 3051278
v_po_rel_id,
v_cre_dt,
v_cre_by,
v_last_upd_dt,
v_last_upd_by,
v_last_upd_login );
select count(1) into v_line_loc_cnt from JAI_PO_LINE_LOCATIONS
where line_location_id = pr_new.line_location_id;
DELETE FROM JAI_PO_TAXES
WHERE line_location_id = pr_new.line_location_id;
UTL_FILE.PUT_LINE(v_myfilehandle, 'Deleted Taxes');
||Insert the line locations from requisition into po using the procedure below
*/
jai_po_cmn_pkg.insert_line( 'STANDARD',
v_line_loc_id, -- Bug 4513549
v_po_hdr_id,
v_po_line_id,
v_cre_dt,
v_cre_by,
v_last_upd_dt,
v_last_upd_by,
v_last_upd_login,
'I' );
INSERT INTO JAI_PO_TAXES(
line_focus_id, line_location_id, tax_line_no, po_line_id, po_header_id,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
tax_id, currency, tax_rate, qty_rate, uom,
tax_amount, tax_type, modvat_flag, vendor_id, tax_target_amount,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_line_focus_id, v_line_loc_id, v_tax_line_no, v_po_line_id, v_po_hdr_id,
v_prec1, v_prec2, v_prec3, v_prec4, v_prec5,
v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
v_taxid, NVL( v_po_curr, NVL( v_curr, v_hdr_curr ) ), v_tax_rate, v_qty_rate, v_uom,
-- 0, v_tax_type, v_mod_flag, v_vendor_id, 0,
v_tax_amount, v_tax_type, v_mod_flag, v_vendor_id, 0, -- Vijay Shankar for Bug# 2782356
-- v_taxid, v_tax_currency, v_tax_rate, v_qty_rate, v_uom,
v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login,
v_tax_category_id -- cbabu for EnhancementBug# 2427465
);
UPDATE JaI_Po_Line_Locations
SET Tax_Amount = NVL( v_tax_amt, 0 ),
Total_Amount = NVL( Line_Tot, 0 ) + NVL( v_tax_amt, 0 ),
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login,
tax_category_id = v_tax_category_id_holder /*added by ssawant for bug 6134111*/
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_Loc_id;
v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date;
v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
CURSOR Fetch_Org_Id_Cur IS SELECT NVL(Operating_Unit,0)
FROM Org_Organization_Definitions
WHERE Organization_Id = v_dest_org_id;
SELECT Segment1, Type_Lookup_Code, apps_source_code -- pramasub FP
FROM Po_Requisition_Headers_All
WHERE Requisition_Header_Id = v_header_id;
SELECT vendor_id
FROM po_vendors
WHERE vendor_name = p_sugg_vendor_name;
SELECT Vendor_Site_Id
FROM Po_Vendor_Sites_All A
WHERE A.Vendor_Site_Code = p_sugg_vendor_loc
AND A.Vendor_Id = v_vendor_id
AND (A.Org_Id = v_org_id
OR
(A.Org_Id is NULL AND v_org_id is NULL));
SELECT Tax_Rate, Tax_Amount, Uom_Code, Tax_Type
FROM JAI_CMN_TAXES_ALL
WHERE Tax_Id = p_tax_id;
CURSOR Fetch_Hdr_Curr_Cur IS SELECT NVL( Currency_Code, '$' )
FROM Po_Requisition_Headers_V
WHERE Requisition_Header_Id = v_header_id;
CURSOR Fetch_Uom_Code_Cur IS SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom;
CURSOR Fetch_Mod_Flag_Cur IS Select Tax_modified_Flag
From JAI_PO_REQ_LINES
Where Requisition_Line_Id = v_line_id;
SELECT a.Po_Line_Id ,
a.tax_line_no lno ,
a.tax_id ,
a.precedence_1 p_1 ,
a.precedence_2 p_2 ,
a.precedence_3 p_3 ,
a.precedence_4 p_4 ,
a.precedence_5 p_5 ,
a.precedence_6 p_6 ,
a.precedence_7 p_7 ,
a.precedence_8 p_8 ,
a.precedence_9 p_9 ,
a.precedence_10 p_10 ,
a.currency ,
a.tax_rate ,
a.qty_rate ,
a.uom ,
a.tax_amount ,
a.tax_type ,
a.vendor_id ,
a.modvat_flag ,
tax_category_id
--FROM Ja_In_Po_Line_Location_Taxes a
FROM JAI_PO_TAXES a
WHERE po_line_id = p_po_line_id
AND nvl(line_location_id,-999) = p_line_location_id
ORDER BY a.tax_line_no;
SELECT 1
--FROM ja_in_reqn_tax_lines
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = p_reqn_line_id;
CHANGE HISTORY: FILENAME: Ja_In_Reqn_Tax_Update_Trg.sql
S.No Date Author and Details
------------------------------------------------------------------------------------------
1 16/08/2002 Nagaraj.s for Bug#2508790
Previously the Coding was
OPEN Fetch_Book_Id_Curr ;
v_last_update_date := pr_new.Last_Update_Date;
v_last_updated_by := pr_new.Last_Updated_By;
v_last_update_login := pr_new.Last_Update_Login;
pr_new.LAST_UPDATE_DATE,
pr_new.LAST_UPDATED_BY,
pr_new.SOURCE_TYPE_CODE,
pr_new.LAST_UPDATE_LOGIN,
pr_new.CREATION_DATE,
pr_new.CREATED_BY,
pr_new.ITEM_ID,
pr_new.ITEM_REVISION,
pr_new.QUANTITY_DELIVERED,
pr_new.SUGGESTED_BUYER_ID,
pr_new.ENCUMBERED_FLAG,
pr_new.RFQ_REQUIRED_FLAG,
pr_new.NEED_BY_DATE,
pr_new.LINE_LOCATION_ID,
pr_new.MODIFIED_BY_AGENT_FLAG,
pr_new.PARENT_REQ_LINE_ID,
pr_new.JUSTIFICATION,
pr_new.NOTE_TO_AGENT,
pr_new.NOTE_TO_RECEIVER,
pr_new.PURCHASING_AGENT_ID,
pr_new.DOCUMENT_TYPE_CODE,
pr_new.BLANKET_PO_HEADER_ID,
pr_new.BLANKET_PO_LINE_NUM,
pr_new.CURRENCY_CODE,
pr_new.RATE_TYPE,
pr_new.RATE_DATE,
pr_new.RATE,
pr_new.CURRENCY_UNIT_PRICE,
pr_new.SUGGESTED_VENDOR_NAME,
pr_new.SUGGESTED_VENDOR_LOCATION,
pr_new.SUGGESTED_VENDOR_CONTACT,
pr_new.SUGGESTED_VENDOR_PHONE,
pr_new.SUGGESTED_VENDOR_PRODUCT_CODE,
pr_new.UN_NUMBER_ID,
pr_new.HAZARD_CLASS_ID,
pr_new.MUST_USE_SUGG_VENDOR_FLAG,
pr_new.REFERENCE_NUM,
pr_new.ON_RFQ_FLAG,
pr_new.URGENT_FLAG,
pr_new.CANCEL_FLAG,
pr_new.SOURCE_ORGANIZATION_ID,
pr_new.SOURCE_SUBINVENTORY,
pr_new.DESTINATION_TYPE_CODE,
pr_new.DESTINATION_ORGANIZATION_ID,
pr_new.DESTINATION_SUBINVENTORY,
pr_new.QUANTITY_CANCELLED,
pr_new.CANCEL_DATE,
pr_new.CANCEL_REASON,
pr_new.CLOSED_CODE,
pr_new.AGENT_RETURN_NOTE,
pr_new.CHANGED_AFTER_RESEARCH_FLAG,
pr_new.VENDOR_ID,
pr_new.VENDOR_SITE_ID,
pr_new.VENDOR_CONTACT_ID,
pr_new.RESEARCH_AGENT_ID,
pr_new.ON_LINE_FLAG,
pr_new.WIP_ENTITY_ID,
pr_new.WIP_LINE_ID,
pr_new.WIP_REPETITIVE_SCHEDULE_ID,
pr_new.WIP_OPERATION_SEQ_NUM,
pr_new.WIP_RESOURCE_SEQ_NUM,
pr_new.ATTRIBUTE_CATEGORY,
pr_new.DESTINATION_CONTEXT,
pr_new.INVENTORY_SOURCE_CONTEXT,
pr_new.VENDOR_SOURCE_CONTEXT,
pr_new.ATTRIBUTE1,
pr_new.ATTRIBUTE2,
pr_new.ATTRIBUTE3,
pr_new.ATTRIBUTE4,
pr_new.ATTRIBUTE5,
pr_new.ATTRIBUTE6,
pr_new.ATTRIBUTE7,
pr_new.ATTRIBUTE8,
pr_new.ATTRIBUTE9,
pr_new.ATTRIBUTE10,
pr_new.ATTRIBUTE11,
pr_new.ATTRIBUTE12,
pr_new.ATTRIBUTE13,
pr_new.ATTRIBUTE14,
pr_new.ATTRIBUTE15,
pr_new.BOM_RESOURCE_ID,
pr_new.CLOSED_REASON,
pr_new.CLOSED_DATE,
pr_new.TRANSACTION_REASON_CODE,
pr_new.QUANTITY_RECEIVED,
pr_new.SOURCE_REQ_LINE_ID,
pr_new.ORG_ID,
pr_new.KANBAN_CARD_ID,
pr_new.CATALOG_TYPE,
pr_new.CATALOG_SOURCE,
pr_new.MANUFACTURER_ID,
pr_new.MANUFACTURER_NAME,
pr_new.MANUFACTURER_PART_NUMBER,
pr_new.REQUESTER_EMAIL,
pr_new.REQUESTER_FAX,
pr_new.REQUESTER_PHONE,
pr_new.UNSPSC_CODE,
pr_new.OTHER_CATEGORY_CODE,
pr_new.SUPPLIER_DUNS,
pr_new.TAX_STATUS_INDICATOR,
pr_new.PCARD_FLAG,
pr_new.NEW_SUPPLIER_FLAG,
pr_new.AUTO_RECEIVE_FLAG,
pr_new.TAX_USER_OVERRIDE_FLAG,
pr_new.TAX_CODE_ID,
pr_new.NOTE_TO_VENDOR,
pr_new.OKE_CONTRACT_VERSION_ID,
pr_new.OKE_CONTRACT_HEADER_ID,
pr_new.ITEM_SOURCE_ID,
pr_new.SUPPLIER_REF_NUMBER,
pr_new.SECONDARY_UNIT_OF_MEASURE,
pr_new.SECONDARY_QUANTITY,
pr_new.PREFERRED_GRADE,
pr_new.SECONDARY_QUANTITY_RECEIVED,
pr_new.SECONDARY_QUANTITY_CANCELLED,
pr_new.VMI_FLAG,
pr_new.AUCTION_HEADER_ID,
pr_new.AUCTION_DISPLAY_NUMBER,
pr_new.AUCTION_LINE_NUMBER,
pr_new.REQS_IN_POOL_FLAG,
pr_new.BID_NUMBER,
pr_new.BID_LINE_NUMBER,
pr_new.NONCAT_TEMPLATE_ID,
pr_new.SUGGESTED_VENDOR_CONTACT_FAX,
pr_new.SUGGESTED_VENDOR_CONTACT_EMAIL,
pr_new.AMOUNT,
pr_new.CURRENCY_AMOUNT,
pr_new.LABOR_REQ_LINE_ID,
pr_new.JOB_ID,
pr_new.JOB_LONG_DESCRIPTION,
pr_new.CONTRACTOR_STATUS,
pr_new.CONTACT_INFORMATION,
pr_new.SUGGESTED_SUPPLIER_FLAG,
pr_new.CANDIDATE_SCREENING_REQD_FLAG,
pr_new.CANDIDATE_FIRST_NAME,
pr_new.CANDIDATE_LAST_NAME,
pr_new.ASSIGNMENT_END_DATE,
pr_new.OVERTIME_ALLOWED_FLAG,
pr_new.CONTRACTOR_REQUISITION_FLAG,
pr_new.DROP_SHIP_FLAG,
pr_new.ASSIGNMENT_START_DATE,
pr_new.ORDER_TYPE_LOOKUP_CODE,
pr_new.PURCHASE_BASIS,
pr_new.MATCHING_BASIS,
pr_new.NEGOTIATED_BY_PREPARER_FLAG,
pr_new.SHIP_METHOD,
pr_new.ESTIMATED_PICKUP_DATE,
pr_new.SUPPLIER_NOTIFIED_FOR_CANCEL,
pr_new.BASE_UNIT_PRICE,
pr_new.AT_SOURCING_FLAG,
/*
|| Commented the columns for P1 bug# 4535701.
|| These will be uncommented once PO teams po_requisition_lines_all table has these columns
*/
/* Bug4540709. Added by Lakshmig Gopalsami
* Reverting the fix for bug 4535701 */
/* pr_new.EVENT_ID, /*following 2 cols commented by ssumaith - bug# 4616729 and added the two null towards the end /*
pr_new.LINE_NUMBER*/
NULL,
NULL
) ;
DELETE from JAI_PO_REQ_LINE_TAXES --ja_in_reqn_tax_lines
WHERE requisition_line_id = pr_old.requisition_line_id ;
DELETE from JAI_PO_REQ_LINES --ja_in_reqn_lines
WHERE requisition_line_id = pr_old.requisition_line_id;
DELETE from JAI_PO_REQ_LINE_TAXES --ja_in_reqn_tax_lines
WHERE requisition_line_id = pr_old.requisition_line_id ;
DELETE from JAI_PO_REQ_LINES --ja_in_reqn_lines
WHERE requisition_line_id = pr_old.requisition_line_id;
Delete From JAI_PO_REQ_LINE_TAXES
Where Requisition_Line_Id = v_line_id;
conv_rate, v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login, v_src_org_id,p_vat_assessable_value => ln_vat_assess_value -- Ravi for VAT
--, pn_gst_assessable_value => ln_gst_assessable_value
); -- Added by Jia for GST Bug#10091373 on 2010/09/10
/*UPDATE JAI_PO_REQ_LINES
SET Last_Update_Date = pr_new.last_update_date,
Last_Updated_By = pr_new.last_updated_by,
Last_Update_Login = pr_new.last_update_login
WHERE Requisition_Line_Id = v_line_id
AND Requisition_Header_Id = v_header_id;*/ --pramasub commented for FP
Delete From JAI_PO_REQ_LINE_TAXES --Ja_In_Reqn_Tax_Lines pramasub FP
Where Requisition_Line_Id = v_line_id;
INSERT INTO JAI_PO_REQ_LINE_TAXES(requisition_line_id, requisition_header_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
tax_id, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
tax_type, modvat_flag, vendor_id, currency,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id)
VALUES (
v_line_id, v_header_id, rec.lno,rec.p_1, rec.p_2, rec.p_3, rec.p_4,
rec.p_5,rec.p_6 , rec.p_7, rec.p_8 , rec.p_9 , rec.p_10 ,
rec.tax_id, rec.tax_rate, rec.qty_rate, rec.uom, rec.tax_amount,
rec.tax_amount + v_line_amount,rec.tax_type, rec.modvat_flag,
rec.vendor_id, rec.currency,v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login,rec.tax_category_id);
v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login,p_vat_assessable_value => ln_vat_assess_value
-- , pn_gst_assessable_value => ln_gst_assessable_value
); -- Added by Jia for GST Bug#10091373 on 2010/09/10
v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login,
-1*v_src_org_id,p_vat_assessable_value => ln_vat_assess_value
-- , pn_gst_assessable_value => ln_gst_assessable_value
); -- Added by Jia for GST Bug#10091373 on 2010/09/10
Update JAI_PO_REQ_LINE_TAXES
Set Tax_Amount = 0
WHERE Requisition_Header_Id = v_header_id
AND Requisition_Line_Id = v_line_id
--AND Tax_Rate is Not Null;
UPDATE JAI_PO_REQ_LINE_TAXES --ja_in_reqn_tax_lines
SET currency = nvl(pr_new.CURRENCY_CODE,'INR')
WHERE requisition_header_id = v_header_id
AND requisition_line_id = v_line_id;
UPDATE JAI_PO_REQ_LINE_TAXES jrtl --ja_in_reqn_tax_lines jrtl
SET tax_amount = tax_amount * DECODE(nvl(pr_new.CURRENCY_CODE,'INR'),
'INR',jai_cmn_utils_pkg.currency_conversion( v_gl_set_of_bks_id, pr_old.CURRENCY_CODE,
pr_old.rate_date, pr_old.rate_type, pr_old.rate ),conv_rate)
WHERE requisition_header_id = v_header_id
AND requisition_line_id = v_line_id
AND exists ( SELECT 1
FROM JAI_CMN_TAXES_ALL --ja_in_tax_codes
WHERE tax_id = jrtl.tax_id
AND adhoc_flag = 'Y'
);
/*UPDATE JAI_PO_REQ_LINES --pramasub FP commented out 115.10
SET Last_Update_Date = pr_new.last_update_date,
Last_Updated_By = pr_new.last_updated_by,
Last_Update_Login = pr_new.last_update_login
WHERE Requisition_Line_Id = v_line_id
AND Requisition_Header_Id = v_header_id;*/
v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date;
v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
CURSOR Fetch_Org_Id_Cur IS SELECT nvl(Operating_Unit,0)
FROM Org_Organization_Definitions
WHERE Organization_Id = v_dest_org_id;
Cursor bind_cur IS SELECT Segment1, Type_Lookup_Code
FROM Po_Requisition_Headers_All
WHERE Requisition_Header_Id = v_header_id;
Cursor Vend_cur(p_sugg_vendor_name IN VARCHAR2) IS SELECT vendor_id
FROM po_vendors
WHERE vendor_name = p_sugg_vendor_name;
Cursor site_cur(p_sugg_vendor_loc IN VARCHAR2) IS SELECT vendor_Site_Id
FROM Po_vendor_Sites_All A
WHERE A.vendor_Site_Code = p_sugg_vendor_loc
AND A.vendor_Id = v_vendor_id
AND nvl(A.Org_Id,0) = nvl(v_org_id,0);
SELECT Tax_Rate, Tax_Amount, Uom_Code, Tax_Type
FROM JAI_CMN_TAXES_ALL
WHERE Tax_Id = p_tax_id;
CURSOR Fetch_Hdr_Curr_Cur IS SELECT NVL( Currency_Code, '$' )
FROM Po_Requisition_Headers_V
WHERE Requisition_Header_Id = v_header_id;
CURSOR Fetch_Uom_Code_Cur IS SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom;
CURSOR Fetch_Mod_Flag_Cur IS SELECT Tax_modIFied_Flag
From JAI_PO_REQ_LINES
Where Requisition_Line_Id = v_line_id;
v_last_update_date := pr_new.Last_Update_Date;
v_last_updated_by := pr_new.Last_Updated_By;
v_last_update_login := pr_new.Last_Update_Login;
pr_new.LAST_UPDATE_DATE,
pr_new.LAST_UPDATED_BY,
pr_new.SOURCE_TYPE_CODE,
pr_new.LAST_UPDATE_LOGIN,
pr_new.CREATION_DATE,
pr_new.CREATED_BY,
pr_new.ITEM_ID,
pr_new.ITEM_REVISION,
pr_new.QUANTITY_DELIVERED,
pr_new.SUGGESTED_BUYER_ID,
pr_new.ENCUMBERED_FLAG,
pr_new.RFQ_REQUIRED_FLAG,
pr_new.NEED_BY_DATE,
pr_new.LINE_LOCATION_ID,
pr_new.MODIFIED_BY_AGENT_FLAG,
pr_new.PARENT_REQ_LINE_ID,
pr_new.JUSTIFICATION,
pr_new.NOTE_TO_AGENT,
pr_new.NOTE_TO_RECEIVER,
pr_new.PURCHASING_AGENT_ID,
pr_new.DOCUMENT_TYPE_CODE,
pr_new.BLANKET_PO_HEADER_ID,
pr_new.BLANKET_PO_LINE_NUM,
pr_new.CURRENCY_CODE,
pr_new.RATE_TYPE,
pr_new.RATE_DATE,
pr_new.RATE,
pr_new.CURRENCY_UNIT_PRICE,
pr_new.SUGGESTED_VENDOR_NAME,
pr_new.SUGGESTED_VENDOR_LOCATION,
pr_new.SUGGESTED_VENDOR_CONTACT,
pr_new.SUGGESTED_VENDOR_PHONE,
pr_new.SUGGESTED_VENDOR_PRODUCT_CODE,
pr_new.UN_NUMBER_ID,
pr_new.HAZARD_CLASS_ID,
pr_new.MUST_USE_SUGG_VENDOR_FLAG,
pr_new.REFERENCE_NUM,
pr_new.ON_RFQ_FLAG,
pr_new.URGENT_FLAG,
pr_new.CANCEL_FLAG,
pr_new.SOURCE_ORGANIZATION_ID,
pr_new.SOURCE_SUBINVENTORY,
pr_new.DESTINATION_TYPE_CODE,
pr_new.DESTINATION_ORGANIZATION_ID,
pr_new.DESTINATION_SUBINVENTORY,
pr_new.QUANTITY_CANCELLED,
pr_new.CANCEL_DATE,
pr_new.CANCEL_REASON,
pr_new.CLOSED_CODE,
pr_new.AGENT_RETURN_NOTE,
pr_new.CHANGED_AFTER_RESEARCH_FLAG,
pr_new.VENDOR_ID,
pr_new.VENDOR_SITE_ID,
pr_new.VENDOR_CONTACT_ID,
pr_new.RESEARCH_AGENT_ID,
pr_new.ON_LINE_FLAG,
pr_new.WIP_ENTITY_ID,
pr_new.WIP_LINE_ID,
pr_new.WIP_REPETITIVE_SCHEDULE_ID,
pr_new.WIP_OPERATION_SEQ_NUM,
pr_new.WIP_RESOURCE_SEQ_NUM,
pr_new.ATTRIBUTE_CATEGORY,
pr_new.DESTINATION_CONTEXT,
pr_new.INVENTORY_SOURCE_CONTEXT,
pr_new.VENDOR_SOURCE_CONTEXT,
pr_new.ATTRIBUTE1,
pr_new.ATTRIBUTE2,
pr_new.ATTRIBUTE3,
pr_new.ATTRIBUTE4,
pr_new.ATTRIBUTE5,
pr_new.ATTRIBUTE6,
pr_new.ATTRIBUTE7,
pr_new.ATTRIBUTE8,
pr_new.ATTRIBUTE9,
pr_new.ATTRIBUTE10,
pr_new.ATTRIBUTE11,
pr_new.ATTRIBUTE12,
pr_new.ATTRIBUTE13,
pr_new.ATTRIBUTE14,
pr_new.ATTRIBUTE15,
pr_new.BOM_RESOURCE_ID,
pr_new.CLOSED_REASON,
pr_new.CLOSED_DATE,
pr_new.TRANSACTION_REASON_CODE,
pr_new.QUANTITY_RECEIVED,
pr_new.SOURCE_REQ_LINE_ID,
pr_new.ORG_ID,
pr_new.KANBAN_CARD_ID,
pr_new.CATALOG_TYPE,
pr_new.CATALOG_SOURCE,
pr_new.MANUFACTURER_ID,
pr_new.MANUFACTURER_NAME,
pr_new.MANUFACTURER_PART_NUMBER,
pr_new.REQUESTER_EMAIL,
pr_new.REQUESTER_FAX,
pr_new.REQUESTER_PHONE,
pr_new.UNSPSC_CODE,
pr_new.OTHER_CATEGORY_CODE,
pr_new.SUPPLIER_DUNS,
pr_new.TAX_STATUS_INDICATOR,
pr_new.PCARD_FLAG,
pr_new.NEW_SUPPLIER_FLAG,
pr_new.AUTO_RECEIVE_FLAG,
pr_new.TAX_USER_OVERRIDE_FLAG,
pr_new.TAX_CODE_ID,
pr_new.NOTE_TO_VENDOR,
pr_new.OKE_CONTRACT_VERSION_ID,
pr_new.OKE_CONTRACT_HEADER_ID,
pr_new.ITEM_SOURCE_ID,
pr_new.SUPPLIER_REF_NUMBER,
pr_new.SECONDARY_UNIT_OF_MEASURE,
pr_new.SECONDARY_QUANTITY,
pr_new.PREFERRED_GRADE,
pr_new.SECONDARY_QUANTITY_RECEIVED,
pr_new.SECONDARY_QUANTITY_CANCELLED,
pr_new.VMI_FLAG,
pr_new.AUCTION_HEADER_ID,
pr_new.AUCTION_DISPLAY_NUMBER,
pr_new.AUCTION_LINE_NUMBER,
pr_new.REQS_IN_POOL_FLAG,
pr_new.BID_NUMBER,
pr_new.BID_LINE_NUMBER,
pr_new.NONCAT_TEMPLATE_ID,
pr_new.SUGGESTED_VENDOR_CONTACT_FAX,
pr_new.SUGGESTED_VENDOR_CONTACT_EMAIL,
pr_new.AMOUNT,
pr_new.CURRENCY_AMOUNT,
pr_new.LABOR_REQ_LINE_ID,
pr_new.JOB_ID,
pr_new.JOB_LONG_DESCRIPTION,
pr_new.CONTRACTOR_STATUS,
pr_new.CONTACT_INFORMATION,
pr_new.SUGGESTED_SUPPLIER_FLAG,
pr_new.CANDIDATE_SCREENING_REQD_FLAG,
pr_new.CANDIDATE_FIRST_NAME,
pr_new.CANDIDATE_LAST_NAME,
pr_new.ASSIGNMENT_END_DATE,
pr_new.OVERTIME_ALLOWED_FLAG,
pr_new.CONTRACTOR_REQUISITION_FLAG,
pr_new.DROP_SHIP_FLAG,
pr_new.ASSIGNMENT_START_DATE,
pr_new.ORDER_TYPE_LOOKUP_CODE,
pr_new.PURCHASE_BASIS,
pr_new.MATCHING_BASIS,
pr_new.NEGOTIATED_BY_PREPARER_FLAG,
pr_new.SHIP_METHOD,
pr_new.ESTIMATED_PICKUP_DATE,
pr_new.SUPPLIER_NOTIFIED_FOR_CANCEL,
pr_new.BASE_UNIT_PRICE,
pr_new.AT_SOURCING_FLAG,
/*
|| Commented the columns for P1 bug# 4535701.
|| These will be uncommented once PO teams po_requisition_lines_all table has these columns
*/
/* Bug4540709. Added by Lakshmig Gopalsami
* Reverting the fix for bug 4535701 */
/* pr_new.EVENT_ID,/*following 2 cols commented by ssumaith - bug# 4616729 and added the two null towards the end /*
pr_new.LINE_NUMBER*/
null,
null
) ;
Delete From JAI_PO_REQ_LINE_TAXES
Where Requisition_Line_Id = v_line_id;
conv_rate, v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login, v_src_org_id, ln_vat_assess_value
-- , pn_gst_assessable_value => ln_gst_assessable_value
); -- Added by Jia for GST Bug#10091373 on 2010/09/10
UPDATE JAI_PO_REQ_LINES
SET Last_Update_Date = pr_new.last_update_date,
Last_Updated_By = pr_new.last_updated_by,
Last_Update_Login = pr_new.last_update_login
WHERE Requisition_Line_Id = v_line_id
AND Requisition_Header_Id = v_header_id;
Update JAI_PO_REQ_LINE_TAXES
Set Tax_Amount = 0
WHERE Requisition_Header_Id = v_header_id
AND Requisition_Line_Id = v_line_id
AND Currency = NVL( v_currency, v_hdr_curr )
AND Tax_Rate is Not Null;
UPDATE JAI_PO_REQ_LINES
SET Last_Update_Date = pr_new.last_update_date,
Last_Updated_By = pr_new.last_updated_by,
Last_Update_Login = pr_new.last_update_login
WHERE Requisition_Line_Id = v_line_id
AND Requisition_Header_Id = v_header_id;