[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
17-Dec-2007 Kevin Cheng Update the logic for inclusive tax calculation
15-Jan-2008 Kevin Cheng Update for Retroactive Price Enhancement
22-Jan-2008 Eric Ma Update pv_called_from,lv_tax_type,lv_third_party_flag
23-Jan-2008 rchandan for bug#6766561 . File Version 120.31
Issue : INCORRECT TAX CALCULATION ON BLANKET PO RELEASES
Fix : When VAT assessable value is not defined then line amount should be used
as VAt assessable during tax calculation for VAT type of taxes.
This was not happening. So added the code for this.
This change is made to merge the fix made in version 120.29 for bug#6685406
22-Jan-2008 Eric Ma Update cursor of get_rcv_line_for_retro to fix a bug
15-Feb-2008 Kevin Cheng Modify code for bug 6816062.
reset non rate tax amount for ad hoc tax in the third calculation loop.
26-Feb-2008 Kevin Cheng Modify code for bug 6838743.
Change variable v_tax_amt and vamt definition.
Remove precision restriction for these temp
variable, so the final result precision will
not be affected by them.
29-Feb-2008 Kevin Cheng Modify code for bug 6849952.
reset non rate tax amount for ad hoc tax in the third calculation loop.
10-Nov-2008 Bug 7436368 File version 120.36.12010000.3
Issue : Taxes are not defaulted from catalog quotation to PO even after running
the concurrent "India - Concurrent request for defaulting taxes in PO when linked
with Quotation".
Reason: In procedure copy_quot_taxes, the where clause of cursor tax_cur was
changed from "nvl(a.line_location_id,-999)=v_quot_line_loc_id" to
"a.line_location_id=v_quot_line_loc_id" for removing SQL literals. But in
case of quotations with no price breaks, the line_location_id would be null
and this condtion would fail. Because of this taxes will not get copied.
Fix : The condition was modified as follows, to handle the case:
((a.line_location_id IS NULL AND v_quot_line_loc_id=-999) OR (a.line_location_id = v_quot_line_loc_id))
6-FEB-2009 Changes by nprashar for bug 7694945, changes in procedure Copy Agreement Taxes.
--------------------------------------------------------------------------------------*/
PROCEDURE calculate_tax(
p_type IN VARCHAR2,
p_header_id NUMBER,
P_line_id NUMBER,
p_line_loc_id IN NUMBER,
p_line_quantity IN NUMBER,
p_price IN NUMBER,
p_line_uom_code IN VARCHAR2,
p_tax_amount IN OUT NOCOPY NUMBER,
p_assessable_value IN NUMBER DEFAULT NULL,
p_vat_assess_value IN NUMBER, -- Ravi for VAT
p_item_id IN NUMBER DEFAULT NULL,
p_conv_rate IN NUMBER DEFAULT NULL
,pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/10
,pv_called_from IN VARCHAR2 DEFAULT NULL--Added by Eric Ma for Retroactive Price 2008/01/11
) IS
-- Line Location_Id is passed instead of p_header_id; Src_Ship_Id is passed in p_line_loc_id
SELECT
tax_type
, third_party_flag
FROM
jai_rcv_line_taxes
WHERE shipment_line_id = p_line_id -- shipment line id
AND tax_id = pn_tax_id ; --added by erif on Feb 1,2008
SELECT Item_Id
FROM Po_Lines_All
WHERE Po_Line_Id = p_line_id;
SELECT Uom_Code
FROM po_lines_all plines, mtl_units_of_measure units
WHERE plines.Po_Line_Id = p_line_id
AND units.Unit_Of_Measure = plines.unit_meas_lookup_code;
SELECT rt.currency_code
FROM rcv_transactions rt,
rcv_shipment_lines rcl
WHERE rcl.shipment_header_id = p_header_id
AND rcl.shipment_line_id = p_line_id
AND rt.shipment_header_id = rcl.shipment_header_id
AND rt.shipment_line_id = rcl.shipment_line_id
AND rt.transaction_type = 'RECEIVE' ;
select A.tax_amount from JAI_PO_REQ_LINE_TAXES A, JAI_CMN_TAXES_ALL B
where A.tax_id = taxid
AND requisition_line_id in(select requisition_line_id
from po_req_distributions_all
where distribution_id in (select req_distribution_id
from po_distributions_all
where po_header_id = p_header_id
and po_line_id = p_line_id))
and A.Tax_id = B.Tax_id
and B.adhoc_flag = 'Y';
SELECT A.uom_class
FROM mtl_units_of_measure A, mtl_units_of_measure B
WHERE A.uom_code = p_line_uom_code
AND B.uom_code = p_tax_line_uom_code
AND A.uom_class = B.uom_class;
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_PO_TAXES
WHERE Line_Location_Id = linelocid -- For Blanket Rel Line Loc Id is passed in place of header id.
AND Tax_Type <> jai_constants.tax_type_tds ; --'TDS'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
10. 01/15/2008 Kevin Cheng Add a branch to deal with taxes recalculate for retroactive price update.
11. 02/29/2008 Kevin Cheng Modify code for bug 6849952.
reset non rate tax amount for ad hoc tax in the third calculation loop.
===============================================================================
Dependencies:
Version Author Dependency Comments
115.1 LGOPALSA IN60106 + Implemented Cess tax code
4146708
115.2 RCHANDAN 4245089 VAT implementation
-------------------------------------------------------------------------------------------- */
--Added by Kevin Cheng for Retroactive Price 2008/01/10
--=====================================================
IF pv_retroprice_changed = 'N'
THEN
--=====================================================
debug := 'Y';
SELECT A.Tax_Line_No LNo,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
A.Precedence_7 P_7,
A.Precedence_8 P_8,
A.Precedence_9 P_9,
A.precedence_10 P_10,
A.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
--Comment out by Kevin Cheng for inclusive tax Dec 18, 2007
/*jai_constants.tax_type_exc_edu_cess, 1,
jai_constants.tax_type_sh_exc_edu_cess, 1,--Added by kunkumar for bugno5989740*/
--Add by Kevin Cheng for inclusive tax Dec 18, 2007
---------------------------------------------------
jai_constants.tax_type_exc_edu_cess, 6,
jai_constants.tax_type_cvd_edu_cess , 6,
jai_constants.tax_type_sh_exc_edu_cess, 6,
jai_constants.tax_type_sh_cvd_edu_cess, 6,
---------------------------------------------------
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amount, A.currency curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
, B.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 18, 2007
FROM JAI_PO_TAXES A, JAI_CMN_TAXES_ALL B,jai_regime_tax_types_v aa
WHERE Po_Line_Id = p_line_id
AND Line_Location_Id = p_line_loc_id
--AND NVL( Line_Location_Id, -999 ) = p_line_loc_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
SELECT A.Tax_Line_No LNo,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
A.Precedence_7 P_7,
A.Precedence_8 P_8,
A.Precedence_9 P_9,
A.precedence_10 P_10,
A.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
--Comment out by Kevin Cheng for inclusive tax Dec 18, 2007
/*jai_constants.tax_type_exc_edu_cess, 1,
jai_constants.tax_type_sh_exc_edu_cess, 1,--Added by kunkumar for bugno 5989740*/
--Add by Kevin Cheng for inclusive tax Dec 18, 2007
---------------------------------------------------
jai_constants.tax_type_exc_edu_cess, 6,
jai_constants.tax_type_cvd_edu_cess , 6,
jai_constants.tax_type_sh_exc_edu_cess, 6,
jai_constants.tax_type_sh_cvd_edu_cess, 6,
---------------------------------------------------
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amount, A.currency curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
, B.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 18, 2007
FROM JAI_PO_REQ_LINE_TAXES A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa
WHERE requisition_line_Id = p_line_id
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
-- START ILIPROCUREMENT FORWARD PORTING - KVAIDYAN
ORDER BY 1;
SELECT A.Tax_Line_No LNo, 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.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
--Comment out by Kevin Cheng for inclusive tax Dec 18, 2007
/*'Excise_Education_cess', 1,*/
--Add by Kevin Cheng for inclusive tax Dec 18, 2007
---------------------------------------------------
jai_constants.tax_type_exc_edu_cess, 6,
jai_constants.tax_type_cvd_edu_cess , 6,
jai_constants.tax_type_sh_exc_edu_cess, 6,
jai_constants.tax_type_sh_cvd_edu_cess, 6,
---------------------------------------------------
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amount, A.currency curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
, B.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 18, 2007
FROM jai_rcv_line_taxes A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa
WHERE shipment_line_id = p_line_id /* shipment line id */
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
SELECT A.Tax_Line_No LNo, 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.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
--Comment out by Kevin Cheng for inclusive tax Dec 18, 2007
/*'Excise_Education_cess', 1,*/
--Add by Kevin Cheng for inclusive tax Dec 18, 2007
---------------------------------------------------
jai_constants.tax_type_exc_edu_cess, 6,
jai_constants.tax_type_cvd_edu_cess , 6,
jai_constants.tax_type_sh_exc_edu_cess, 6,
jai_constants.tax_type_sh_cvd_edu_cess, 6,
---------------------------------------------------
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amt tax_amount, A.currency_code curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
, B.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 18, 2007
FROM Jai_cmn_document_Taxes A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa
WHERE source_doc_line_id = p_line_id /* source doc line id */
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
this loop typically would have an insert /update in the tax tables to insert/update tax amounts
*/
FOR i in 1 .. row_count --Compute Tax Amount
LOOP
tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
SELECT A.Tax_Line_No LNo,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
A.Precedence_7 P_7,
A.Precedence_8 P_8,
A.Precedence_9 P_9,
A.precedence_10 P_10,
A.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
jai_constants.tax_type_exc_edu_cess, 1,
jai_constants.tax_type_sh_exc_edu_cess, 1,--Added by kunkumar for bugno5989740
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amount, A.currency curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
, pha.vendor_id hdr_vendor_id, A.Vendor_Id tax_vendor_id --Added by Kevin Cheng
FROM JAI_PO_TAXES A, JAI_CMN_TAXES_ALL B,jai_regime_tax_types_v aa
, po_headers_all pha --Added by Kevin Cheng
WHERE Po_Line_Id = p_line_id
AND Line_Location_Id = p_line_loc_id
--AND NVL( Line_Location_Id, -999 ) = p_line_loc_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND A.Tax_Id = B.Tax_Id
AND pha.po_header_id = A.Po_Header_Id --Added by Kevin Cheng
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
SELECT A.Tax_Line_No LNo,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
A.Precedence_7 P_7,
A.Precedence_8 P_8,
A.Precedence_9 P_9,
A.precedence_10 P_10,
A.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
jai_constants.tax_type_exc_edu_cess, 1,
jai_constants.tax_type_sh_exc_edu_cess, 1,--Added by kunkumar for bugno 5989740
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amount, A.currency curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
FROM JAI_PO_REQ_LINE_TAXES A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa
WHERE requisition_line_Id = p_line_id
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
-- START ILIPROCUREMENT FORWARD PORTING - KVAIDYAN
ORDER BY 1;
SELECT A.Tax_Line_No LNo, 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.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'Excise_Education_cess', 1,
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amount, A.currency curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
, rsh.vendor_id hdr_vendor_id, A.Vendor_Id tax_vendor_id --Added by Kevin Cheng
FROM jai_rcv_line_taxes A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa,
RCV_SHIPMENT_HEADERS rsh --Added by Kevin Cheng
WHERE shipment_line_id = p_line_id /* shipment line id */
AND A.Tax_Id = B.Tax_Id
AND rsh.shipment_header_id = A.SHIPMENT_HEADER_ID --Added by Kevin Cheng
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
SELECT A.Tax_Line_No LNo, 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.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'Excise_Education_cess', 1,
'TDS', 2, 0)) tax_type_val,
A.Tax_Rate tax_rate, A.Qty_Rate Qty_Rate, A.uom uom_code,
A.Tax_Amt tax_amount, A.currency_code curr, B.End_Date Valid_Date,
B.rounding_factor rnd_factor, B.adhoc_flag adhoc_flag
FROM Jai_cmn_document_Taxes A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa
WHERE source_doc_line_id = p_line_id \* source doc line id *\
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
SELECT
original_tax_amount
INTO
tax_amt_tab(rec_retro.lno)
FROM
Jai_Retro_Tax_Changes jrtc
WHERE jrtc.tax_id = rec_retro.tax_id
AND jrtc.line_change_id = (SELECT
jrlc.line_change_id
FROM
Jai_Retro_Line_Changes jrlc
WHERE jrlc.doc_header_id = p_header_id
AND jrlc.doc_line_id = P_line_id
AND jrlc.doc_type = 'RECEIPT'
AND jrlc.doc_version_number = (SELECT
MAX(jrlc1.doc_version_number)
FROM
Jai_Retro_Line_Changes jrlc1
WHERE jrlc1.doc_header_id = p_header_id
AND jrlc1.doc_line_id = P_line_id
AND jrlc1.doc_type = 'RECEIPT'
)
);
SELECT
original_tax_amount
INTO
tax_amt_tab(rec_retro.lno)
FROM
Jai_Retro_Tax_Changes jrtc
WHERE jrtc.tax_id = rec_retro.tax_id
AND jrtc.line_change_id = (SELECT
line_change_id
FROM
Jai_Retro_Line_Changes jrlc
WHERE jrlc.line_location_id = p_line_loc_id
AND jrlc.doc_line_id = p_line_id
AND jrlc.doc_type IN ( 'RELEASE'
, 'STANDARD PO'
)
AND jrlc.doc_version_number = (SELECT
MAX(jrlc1.doc_version_number)
FROM
Jai_Retro_Line_Changes jrlc1
WHERE jrlc1.line_location_id = p_line_loc_id
AND jrlc1.doc_line_id = p_line_id
AND jrlc1.doc_type IN ( 'RELEASE'
, 'STANDARD PO'
)
)
);
UPDATE JAI_PO_REQ_LINE_TAXES
SET Tax_Amount = nvl(tax_amt_tab(I),0),
Tax_Target_Amount = NVL( tax_target_tab(I), 0 ) * ( 1/p_conv_rate )
WHERE Requisition_Header_Id = p_header_id
AND Requisition_Line_Id = p_line_id
AND Tax_Line_No = I;
UPDATE jai_rcv_line_taxes
SET Tax_Amount = nvl(tax_amt_tab(I),0),
Tax_Target_Amount = NVL(tax_target_tab(I), 0 ) * ( 1/p_conv_rate )
WHERE shipment_Header_Id = p_header_id
AND shipment_Line_Id = p_line_id
AND Tax_Line_No = I;
UPDATE Jai_cmn_document_Taxes
SET Tax_Amt = nvl(tax_amt_tab(I),0)
WHERE source_doc_id = p_header_id
AND source_doc_Line_Id = p_line_id
AND Tax_Line_No = I;
UPDATE JAI_PO_REQ_LINE_TAXES
SET Tax_Amount = nvl(tax_amt_tab(I),0),
Tax_Target_Amount = NVL( tax_target_tab(I), 0 ) * ( 1/p_conv_rate )
WHERE Requisition_Line_Id = p_header_id
AND Tax_Line_No = I;
UPDATE JAI_PO_TAXES
SET Tax_Amount = NVL( tax_amt_tab(I), 0 ),
Tax_Target_Amount = NVL( tax_target_tab(I), 0 ) * ( 1/p_conv_rate )
WHERE Line_Location_Id = p_header_id
AND Po_Line_Id = p_line_id
AND Tax_Line_No = I;
JAI_RETRO_PRC_PKG.Update_Price_Changes( pn_tax_amt => NVL( tax_amt_tab(I), 0 )
, pn_line_no => I
, pn_line_loc_id => p_header_id
, pv_process_flag => lv_process_flag
, pv_process_message => lv_process_message
);
UPDATE JAI_PO_TAXES
SET Tax_Amount = NVL( tax_amt_tab(I), 0 ),
Tax_Target_Amount = NVL( tax_target_tab(I), 0 ) * ( 1/p_conv_rate )
WHERE Line_Location_Id = p_line_loc_id
AND Po_Line_Id = p_line_id
AND Tax_Line_No = I;
JAI_RETRO_PRC_PKG.Update_Price_Changes( pn_tax_amt => NVL( tax_amt_tab(I), 0 )
, pn_line_no => I
, pn_line_loc_id => p_line_loc_id
, pv_process_flag => lv_process_flag
, pv_process_message => lv_process_message
);
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = NVL( v_tax_amt, 0 ),
Total_Amount = v_tax_amt + p_price
WHERE Line_Location_Id = p_header_id
AND Po_Line_Id = p_line_id;
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = NVL( v_tax_amt, 0 ),
Total_Amount = v_tax_amt + p_price
WHERE Line_Location_Id = p_line_loc_id
AND Po_Line_Id = p_line_id;
SELECT quantity
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id;
SELECT enable_trace
FROM fnd_concurrent_programs a, fnd_application b
WHERE b.application_short_name = lv_app_name --'PO'
AND b.application_id = a.application_id
AND a.concurrent_program_name = lv_conc_name; --'JAINPOTD';/* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
SELECT a.sid, a.serial#, b.spid FROM v$session a,v$process b
WHERE audsid = userenv('SESSIONID')
AND a.paddr = b.addr;
SELECT name FROM v$database;
trigger ja_in_po_tax_insert_trg on PO_LINE_LOCATIONS_ALL. The main concepts
used here are,
- if an error occurs while processing a particular record, the program will skip processing
all other shipment lines of the error PO(Purchase Order).
- the program will continue to process other PO's.
- the program will update the temporary table for the error record, with error flag time and error
- if a particular line has error flag set to 'Y', this program will not pick up that record and
also all other lines pertaining to such an error PO.
The concurrent which is referring this procedure needs to be trace enabled( useful when there is some problem
with the concurrent). In order to get the trace the DBA should query for JAINPOTD concurrent and check the
'Trace Enabled' flag which is used in this concurrent to create level 4 trace.
------------------------------------------------------------------------------------------------------------------------ */
v_total := 0;
FND_FILE.put_line(FND_FILE.LOG, 'Start procedure - Ja_In_Bulk_PO_Tax_Insert');
SELECT
ROWID,
po_header_id,
po_line_id,
line_location_id,
from_header_id,
from_line_id,
price_override,
uom_code,
assessable_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM JAI_PO_QUOT_LINES_T
WHERE nvl(error_flag, 'N') <> 'Y'
ORDER BY po_header_id, po_line_id
)
LOOP
v_line_location_id := c_rec.line_location_id;
p_last_upd_dt => nvl(c_rec.last_update_date, SYSDATE),
p_last_upd_by => nvl(c_rec.last_updated_by, FND_GLOBAL.USER_ID),
p_last_upd_login=> nvl(c_rec.last_update_login, FND_GLOBAL.LOGIN_ID)
);
DELETE FROM JAI_PO_QUOT_LINES_T
WHERE rowid = v_rowid;
UPDATE JAI_PO_QUOT_LINES_T
SET error_flag = 'Y',
processing_time = SYSDATE,
error_message = v_error_mesg
WHERE ROWID = v_rowid;
p_Last_Update_Date date,
p_Last_Updated_By number,
p_Last_Update_Login number
/* Brathod, For Bug# 4242351 */
,p_rate PO_HEADERS_ALL.RATE%TYPE DEFAULT NULL
,p_rate_type PO_HEADERS_ALL.RATE_TYPE%TYPE DEFAULT NULL
,p_rate_date PO_HEADERS_ALL.RATE_DATE%TYPE DEFAULT NULL
,p_currency_code PO_HEADERS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
/* End of Bug# 4242351 */
)
IS
v_vendor_id NUMBER; --File.Sql.35 Cbabu := NVL( p_Vendor_Id, 0 );
v_last_upd_dt DATE ; --File.Sql.35 Cbabu := p_Last_Update_Date;
v_last_upd_by NUMBER; --File.Sql.35 Cbabu := p_Last_Updated_By;
v_last_upd_login NUMBER; --File.Sql.35 Cbabu := p_Last_Update_Login;
CURSOR Fetch_Org_Id_Cur IS SELECT Inventory_Organization_id
FROM Hr_Locations
WHERE Location_Id = v_ship_loc_id;
CURSOR Fetch_Focus_Id IS SELECT JAI_PO_LINE_LOCATIONS_S.NEXTVAL
FROM Dual;
CURSOR Lines_Cur IS SELECT DISTINCT Po_Line_Id
FROM Po_Line_Locations_all
WHERE Po_Header_Id = v_po_hdr_id;
CURSOR Fetch_Item_Cur( Lineid IN NUMBER ) IS SELECT Item_Id
FROM Po_Lines_All
WHERE Po_Line_Id = Lineid;
CURSOR Line_Loc_Cur( lineid IN NUMBER ) IS SELECT Line_Location_Id
FROM po_line_locations_all
WHERE Po_Line_Id = lineid;
CURSOR Fetch_Dtls_Cur( lineid IN NUMBER ) IS SELECT Quantity, Unit_Price, Unit_Meas_Lookup_Code
FROM Po_Lines_All
WHERE Po_Line_Id = lineid;
CURSOR Fetch_Dtls1_Cur( lineid IN NUMBER, linelocid IN NUMBER ) IS SELECT Quantity, Price_Override,
Unit_Meas_Lookup_Code
FROM Po_Line_Locations_All
WHERE Po_Line_Id = lineid
AND Line_Location_Id = linelocid;
CURSOR Fetch_UOMCode_Cur IS SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom;
Issue:- Cursor get_po_hdr having a select statement on table po_headers_all
was raising mutating error.
Solution:- Four new parameters are passed to the procedure
to avoid select statement.
4. Kunkumar Added v_service_type_code and its manipulations for forward porting to R12.
===============================================================================
Dependencies
Version Author Dependencies Comments
115.2 rallamse IN60106+4245089 Changes for VAT implementation
--------------------------------------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
v_vendor_id := NVL( p_Vendor_Id, 0 );
v_last_upd_dt := p_Last_Update_Date;
v_last_upd_by := p_Last_Updated_By;
v_last_upd_login := p_Last_Update_Login;
INSERT INTO JAI_PO_LINE_LOCATIONS( Line_Focus_Id, Line_Location_Id, Po_Line_Id, Po_Header_Id,
Tax_Modified_Flag, Tax_Amount, Total_Amount,
Creation_Date, Created_By, Last_Update_Date, Last_Updated_By,
Last_Update_Login,Service_type_code )
VALUES
( v_next_val, v_Line_Location_Id, v_po_line_id1, v_po_hdr_id,
'N', 0, 0,
v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by,
v_last_upd_login,v_service_type_code );
SELECT currency_code, org_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT org_id
FROM po_requisition_headers_all
WHERE requisition_header_id = p_reqn_header_id;
SELECT item_id
FROM po_lines_all
WHERE po_line_id = p_line_id;
SELECT a.tax_line_no lno,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
a.precedence_7 p_7,
a.precedence_8 p_8,
a.precedence_9 p_9,
a.precedence_10 p_10,
a.tax_id,
a.tax_rate,
nvl(a.tax_amount, 0) tax_amt,
b.end_date valid_date,
b.rounding_factor rnd_factor,
a.qty_rate,
a.uom uom_code,
a.currency curr,
b.adhoc_flag,
b.inclusive_tax_flag, -- Add by Kevin Cheng for inclusive tax Dec 17, 2007
DECODE( aa.regime_code, 'VAT', 4 ,
DECODE( UPPER( A.Tax_Type ),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, --'CVD', 1,
--'ADDITIONAL_CVD',1, -- Added by Girish , w.r.t BUG#5143906
--commented the CVD, ADDITIONAL_CVD and cvd_edu_cess for Bug#5219225 by Sanjikum
/* added by ssawant for bug 5989740 */
--Comment out by Kevin Cheng for inclusive tax Dec 17, 2007
/*JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,1,jai_constants.tax_type_exc_edu_cess, 1,*/ --,jai_constants.tax_type_cvd_edu_cess, 1,
--Add by Kevin Cheng for inclusive tax Dec 17, 2007
---------------------------------------------------
jai_constants.tax_type_exc_edu_cess, 6,
jai_constants.tax_type_cvd_edu_cess , 6,
jai_constants.tax_type_sh_exc_edu_cess, 6,
jai_constants.tax_type_sh_cvd_edu_cess, 6,
---------------------------------------------------
'TDS', 2, 0
)
) tax_type_val
FROM JAI_PO_REQ_LINE_TAXES a, JAI_CMN_TAXES_ALL b, jai_regime_tax_types_v aa
WHERE requisition_line_id = p_line_id
AND a.Tax_Id = b.Tax_Id
AND aa.tax_type(+) = b.tax_type
order by 1/*2*/;--Modified by Kevin Cheng for inclusive tax Dec 17, 2007
SELECT A.Tax_Line_No LNo,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
A.Precedence_7 P_7,
A.Precedence_8 P_8,
A.Precedence_9 P_9,
A.precedence_10 P_10,
A.Tax_Id,
DECODE( aa.regime_code, 'VAT', 4 ,
DECODE( UPPER( A.Tax_Type ),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, --'CVD', 1,
--'ADDITIONAL_CVD',1, -- Added by Girish , w.r.t BUG#5143906
--commented the CVD, ADDITIONAL_CVD and cvd_edu_cess for Bug#5219225 by Sanjikum
/* added by ssawant for bug 5989740 */
--Comment out by Kevin Cheng for inclusive tax Dec 17, 2007
/*JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,1,jai_constants.tax_type_exc_edu_cess, 1,*/ --jai_constants.tax_type_cvd_edu_cess, 1,
--Add by Kevin Cheng for inclusive tax Dec 17, 2007
---------------------------------------------------
jai_constants.tax_type_exc_edu_cess, 6,
jai_constants.tax_type_cvd_edu_cess , 6,
jai_constants.tax_type_sh_exc_edu_cess, 6,
jai_constants.tax_type_sh_cvd_edu_cess, 6,
---------------------------------------------------
'TDS', 2, 0
)
) tax_type_val,
A.Tax_Rate tax_rate,
A.Qty_Rate Qty_Rate,
A.uom uom_code,
A.Tax_Amount,
A.currency curr,
B.End_Date Valid_Date,
B.rounding_factor rnd_factor,
B.adhoc_flag adhoc_flag
,b.inclusive_tax_flag --Add by Kevin for inclusive tax Dec 17, 2007
FROM JAI_PO_TAXES A, JAI_CMN_TAXES_ALL B, jai_regime_tax_types_v aa
WHERE Po_Line_Id = p_po_line_id
--AND nvl(line_location_id,-999) = p_line_location_id
AND line_location_id = p_line_location_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
order by 1/*2*/;--Modified by Kevin Cheng for inclusive tax Dec 17, 2007
SELECT a.uom_class
FROM mtl_units_of_measure a, mtl_units_of_measure b
WHERE a.uom_code = p_line_uom_code
AND b.uom_code = p_tax_line_uom_code
AND a.uom_class = b.uom_class;
SELECT SUM( NVL( tax_amount, 0 ) )
FROM JAI_PO_TAXES
WHERE line_location_id = p_line_location_id -- For Blanket Rel Line Loc Id is passed in place of header id.
AND tax_type <> jai_constants.tax_type_tds; --'TDS'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
SELECT uom_code
FROM po_lines_all plines, mtl_units_of_measure units
WHERE plines.po_line_id = p_line_id
AND units.unit_of_measure = plines.unit_meas_lookup_code;
SELECT A.Tax_Line_No LNo,
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.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
--Comment out by Kevin Cheng for inclusive tax Dec 17, 2007
/*'Excise_Education_cess', 1,
jai_constants.tax_type_sh_exc_edu_cess,1,*/
--Add by Kevin Cheng for inclusive tax Dec 17, 2007
---------------------------------------------------
jai_constants.tax_type_exc_edu_cess, 6,
jai_constants.tax_type_cvd_edu_cess , 6,
jai_constants.tax_type_sh_exc_edu_cess, 6,
jai_constants.tax_type_sh_cvd_edu_cess, 6,
----------------------------------------------------
'TDS', 2,
0)) tax_type_val,
A.Tax_Rate tax_rate,
A.Qty_Rate Qty_Rate,
A.uom uom_code,
A.Tax_Amt tax_amount,
A.currency_code curr,
B.End_Date Valid_Date,
B.rounding_factor rnd_factor,
B.adhoc_flag adhoc_flag
, b.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 17, 2007
FROM Jai_cmn_document_Taxes A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa
WHERE source_doc_line_id = cp_source_doc_line_id
AND source_doc_id = cp_source_doc_id --pramasub added this condn for bug #6137011
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
SELECT a.tax_line_no lno,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
a.precedence_7 p_7,
a.precedence_8 p_8,
a.precedence_9 p_9,
a.precedence_10 p_10,
a.tax_id,
a.tax_rate,
nvl(a.tax_amount, 0) tax_amt,
b.end_date valid_date,
b.rounding_factor rnd_factor,
a.qty_rate,
a.uom uom_code,
a.currency curr,
b.adhoc_flag,
DECODE( aa.regime_code, 'VAT', 4 ,
DECODE( UPPER( A.Tax_Type ),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, --'CVD', 1,
--'ADDITIONAL_CVD',1, -- Added by Girish , w.r.t BUG#5143906
--commented the CVD, ADDITIONAL_CVD and cvd_edu_cess for Bug#5219225 by Sanjikum
/* added by ssawant for bug 5989740 */
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,1,jai_constants.tax_type_exc_edu_cess, 1, --,jai_constants.tax_type_cvd_edu_cess, 1,
'TDS', 2, 0
)
) tax_type_val
FROM JAI_PO_REQ_LINE_TAXES a, JAI_CMN_TAXES_ALL b, jai_regime_tax_types_v aa
WHERE requisition_line_id = p_line_id
AND a.Tax_Id = b.Tax_Id
AND aa.tax_type(+) = b.tax_type
order by 2;
SELECT A.Tax_Line_No LNo,
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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
A.Precedence_7 P_7,
A.Precedence_8 P_8,
A.Precedence_9 P_9,
A.precedence_10 P_10,
A.Tax_Id,
DECODE( aa.regime_code, 'VAT', 4 ,
DECODE( UPPER( A.Tax_Type ),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, --'CVD', 1,
--'ADDITIONAL_CVD',1, -- Added by Girish , w.r.t BUG#5143906
--commented the CVD, ADDITIONAL_CVD and cvd_edu_cess for Bug#5219225 by Sanjikum
/* added by ssawant for bug 5989740 */
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,1,jai_constants.tax_type_exc_edu_cess, 1, --jai_constants.tax_type_cvd_edu_cess, 1,
'TDS', 2, 0
)
) tax_type_val,
A.Tax_Rate tax_rate,
A.Qty_Rate Qty_Rate,
A.uom uom_code,
A.Tax_Amount,
A.currency curr,
B.End_Date Valid_Date,
B.rounding_factor rnd_factor,
B.adhoc_flag adhoc_flag
, pha.vendor_id hdr_vendor_id, a.vendor_id tax_vendor_id --Added by Kevin Cheng
FROM JAI_PO_TAXES A, JAI_CMN_TAXES_ALL B, jai_regime_tax_types_v aa
, po_headers_all pha --Added by Kevin Cheng
WHERE Po_Line_Id = p_po_line_id
--AND nvl(line_location_id,-999) = p_line_location_id
AND line_location_id = p_line_location_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND A.Tax_Id = B.Tax_Id
AND pha.po_header_id = A.Po_Header_Id --Added by Kevin Cheng
AND aa.tax_type(+) = b.tax_type
order by 2;
SELECT A.Tax_Line_No LNo,
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.Tax_Id,
DECODE(aa.regime_code, 'VAT', 4, DECODE( UPPER( A.Tax_Type ),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'Excise_Education_cess', 1,
jai_constants.tax_type_sh_exc_edu_cess,1,
'TDS', 2,
0)) tax_type_val,
A.Tax_Rate tax_rate,
A.Qty_Rate Qty_Rate,
A.uom uom_code,
A.Tax_Amt tax_amount,
A.currency_code curr,
B.End_Date Valid_Date,
B.rounding_factor rnd_factor,
B.adhoc_flag adhoc_flag
FROM Jai_cmn_document_Taxes A,
JAI_CMN_TAXES_ALL B,
jai_regime_tax_types_v aa
WHERE source_doc_line_id = cp_source_doc_line_id
AND source_doc_id = cp_source_doc_id --pramasub added this condn for bug #6137011
AND A.Tax_Id = B.Tax_Id
AND aa.tax_type(+) = b.tax_type
ORDER BY 1;
Added standard who columns in all update statements.
7. 12/17/2007 Kevin Cheng Update the logic for inclusive tax calculation
8. 01/15/2008 Kevin Cheng Add a branch to deal with taxes recalculate for retroactive price update
9. 02/15/2008 Kevin Cheng Modify code for bug 6816062.
reset non rate tax amount for ad hoc tax in the third calculation loop.
10. 02/26/2008 Kevin Cheng Modify code for bug 6838743.
Change variable v_tax_amt and vamt definition.
Remove precision restriction for these temp
variable, so the final result precision will
not be affected by them.
===============================================================================
Dependencies
Version Author Dependencies Comments
115.1 LGOPALSA IN60106 + Added Cess tax dependency
4146708
115.2 rallamse IN60106 + Changes for VAT implementation
4146708 +
4245089
--------------------------------------------------------------------------------------------------------------------------*/
--Added by Kevin Cheng for Retroactive price 2008/01/10
-------------------------------------------------------
IF pv_retroprice_changed = 'N'
THEN
-------------------------------------------------------
--File.Sql.35 Cbabu
bsln_amt := p_tax_amount;
SELECT
original_tax_amount
INTO
tax_amt_tab(rec.lno)
FROM
Jai_Retro_Tax_Changes jrtc
WHERE jrtc.tax_id = rec.tax_id
AND jrtc.line_change_id = (SELECT
line_change_id
FROM
Jai_Retro_Line_Changes jrlc
WHERE jrlc.line_location_id = p_line_location_id
AND jrlc.doc_type IN ( 'RELEASE'
, 'RECEIPT'
, 'STANDARD PO'
)
AND jrlc.doc_version_number = (SELECT
MAX(jrlc1.doc_version_number)
FROM
Jai_Retro_Line_Changes jrlc1
WHERE jrlc1.line_location_id = p_line_location_id
AND jrlc1.doc_type IN ( 'RELEASE'
, 'RECEIPT'
, 'STANDARD PO'
)
)
);
UPDATE JAI_PO_REQ_LINE_TAXES
SET Tax_Amount = round( nvl(tax_amt_tab(I),0), nvl(rnd_factor(I),0) ),/*4281841*/
Tax_Target_Amount = NVL( tax_target_tab(I), 0 ),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE Requisition_Header_Id = p_header_id
AND Requisition_Line_Id = p_line_id
AND Tax_Line_No = I;
UPDATE JAI_PO_REQ_LINES
SET Tax_Amount = v_tax_amt,
total_amount = p_base_value + v_tax_amt,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE requisition_header_id = p_header_id
AND requisition_line_id = p_line_id;
UPDATE JAI_PO_REQ_LINE_TAXES
SET Tax_Amount = round( nvl(tax_amt_tab(I),0), rnd_factor(I) ),
Tax_Target_Amount = NVL( tax_target_tab(I), 0 ),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE Requisition_Line_Id = p_requisition_line_id/*5877782..replaced p_line_id with p_requisition_line_id */
AND Tax_Line_No = I;
UPDATE JAI_PO_TAXES
SET tax_amount = round( nvl( tax_amt_tab(i), 0 ), nvl(rnd_factor(I),0) ),
tax_target_amount = nvl( tax_target_tab(i), 0 ),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE line_location_id = p_line_location_id
AND po_line_id = p_line_id
AND tax_line_no = i;
JAI_RETRO_PRC_PKG.Update_Price_Changes( pn_tax_amt => round( nvl( tax_amt_tab(i), 0 ), nvl(rnd_factor(I),0) )
, pn_line_no => i
, pn_line_loc_id => p_line_location_id
, pv_process_flag => lv_process_flag
, pv_process_message => lv_process_message
);
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = NVL( v_tax_amt, 0 ),
Total_Amount = v_tax_amt + p_base_value ,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE Line_Location_Id = p_line_location_id
AND Po_Line_Id = p_line_id;
UPDATE jai_cmn_document_taxes
SET Tax_Amt = round( nvl( tax_amt_tab(i), 0 ), nvl(rnd_factor(I),0) ),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE source_doc_id = p_header_id
AND source_doc_Line_Id = p_line_id
AND Tax_Line_No = I;
select vendor_id,vendor_site_id
from po_headers_all
where po_header_id=p_po_hdr_id;
SELECT JAI_PO_LINE_LOCATIONS_S.nextval FROM Dual;
SELECT jpll.Line_Location_Id
FROM po_line_locations_all plla, JAI_PO_LINE_LOCATIONS jpll,
po_lines_all pla, po_headers_all pha
WHERE pha.po_header_id = pla.po_header_id
AND jpll.line_location_id = plla.line_location_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.po_header_id = p_from_hdr_id
AND pla.line_num = p_line_num
AND pla.item_id = p_item_id
AND plla.shipment_num = p_ship_num;
SELECT pla.Po_Line_Id
FROM po_lines_all pla, po_headers_all pha
WHERE pha.Po_Header_Id = p_from_hdr_id
AND pla.line_num = p_line_num -- Vijay Shankar for Bug# 3466223
AND pha.po_header_id =pla.po_header_id;
SELECT line_location_id, po_line_id, po_header_id, tax_modified_flag,
tax_amount, total_amount, line_focus_id, creation_date,
created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_LINE_LOCATIONS
WHERE Line_Location_Id = v_ln_loc_id;
SELECT line_location_id,
tax_line_no,
po_line_id,
po_header_id,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id,
currency,
tax_rate,
qty_rate,
uom,
tax_amount,
tax_type,
vendor_id,
modvat_flag,
tax_target_amount,
line_focus_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES
WHERE Line_Location_Id = v_ln_loc_id;
SELECT line_location_id, po_line_id, po_header_id, tax_modified_flag,
tax_amount, total_amount, line_focus_id, creation_date,
created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = v_line_id
AND po_header_id = p_from_hdr_id
AND ( line_location_id IS NULL
OR line_location_id = 0 ); -- cbabu for EnhancementBug# 2427465
SELECT line_location_id,
tax_line_no,
po_line_id,
po_header_id,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id,
currency,
tax_rate,
qty_rate,
uom,
tax_amount,
tax_type,
vendor_id,
modvat_flag,
tax_target_amount,
line_focus_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES
WHERE Po_Line_Id = v_line_id
AND po_header_id = p_from_hdr_id
AND ( line_location_id IS NULL
OR line_location_id = 0 ); -- cbabu for EnhancementBug# 2427465
Update JAI_PO_COPYDOC_T
Set po_header_id = p_po_hdr_id
Where po_line_id = p_po_line_id;
Update JAI_PO_COPYDOC_T
Set po_header_id = p_po_hdr_id
Where line_location_id = p_po_line_loc_id;
INSERT INTO JAI_PO_LINE_LOCATIONS (
line_location_id, po_line_id, po_header_id,
tax_modified_flag, tax_amount, total_amount,
line_focus_id, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
,service_type_code ) VALUES (
p_po_line_loc_id, p_po_line_id, p_po_hdr_id,
'Y', rec1.tax_amount, rec1.total_amount,
v_seq_val, p_cre_dt, p_cre_by,
p_last_upd_dt, p_last_upd_by, p_last_upd_login,
rec1.tax_category_id -- cbabu for EnhancementBug# 2427465
,v_service_type_code);
INSERT INTO JAI_PO_TAXES (
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, vendor_id, modvat_flag,
tax_target_amount, line_focus_id, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
p_po_line_loc_id, rec2.tax_line_no, p_po_line_id, p_po_hdr_id,
rec2.precedence_1, rec2.precedence_2, rec2.precedence_3, rec2.precedence_4,rec2.precedence_5,
rec2.precedence_6, rec2.precedence_7, rec2.precedence_8, rec2.precedence_9, rec2.precedence_10 ,
rec2.tax_id, rec2.currency, rec2.tax_rate, rec2.qty_rate,
rec2.uom, rec2.tax_amount, rec2.tax_type, rec2.vendor_id, rec2.modvat_flag,
rec2.tax_target_amount, v_seq_val, p_cre_dt, p_cre_by,
p_last_upd_dt, p_last_upd_by, p_last_upd_login,
rec2.tax_category_id -- cbabu for EnhancementBug# 2427465
);
INSERT INTO JAI_PO_LINE_LOCATIONS (
line_location_id, po_line_id, po_header_id,
tax_modified_flag, tax_amount, total_amount, line_focus_id,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
tax_category_id,service_type_code -- cbabu for EnhancementBug# 2427465
) VALUES (
p_po_line_loc_id, p_po_line_id, p_po_hdr_id,
'Y', fetch_ja_in_po_ln_loc_rec.tax_amount, fetch_ja_in_po_ln_loc_rec.total_amount,
v_seq_val, p_cre_dt, p_cre_by, p_last_upd_dt, p_last_upd_by, p_last_upd_login,
fetch_ja_in_po_ln_loc_rec.tax_category_id -- cbabu for EnhancementBug# 2427465
,v_service_type_code);
INSERT INTO JAI_PO_TAXES (
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, vendor_id, modvat_flag,
tax_target_amount, line_focus_id, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
p_po_line_loc_id, rec.tax_line_no, p_po_line_id, p_po_hdr_id,
rec.precedence_1, rec.precedence_2, rec.precedence_3, rec.precedence_4, rec.precedence_5,
rec.precedence_6, rec.precedence_7, rec.precedence_8, rec.precedence_9, rec.precedence_10,
rec.tax_id, rec.currency, rec.tax_rate, rec.qty_rate,
rec.uom, rec.tax_amount, rec.tax_type, rec.vendor_id, rec.modvat_flag,
rec.tax_target_amount, v_seq_val, p_cre_dt, p_cre_by,
p_last_upd_dt, p_last_upd_by, p_last_upd_login,
rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
Delete From JAI_PO_COPYDOC_T
Where po_line_id = p_po_line_id
AND line_location_id is null;
Delete From JAI_PO_COPYDOC_T
Where line_location_id = p_po_line_loc_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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
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
/*condition modified for bug 7436368*/
WHERE ((a.line_location_id IS NULL AND v_quot_line_loc_id=-999) OR (a.line_location_id = v_quot_line_loc_id))
--a.line_location_id = v_quot_line_loc_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
--WHERE NVL( a.line_location_id, -999 ) = v_quot_line_loc_id
AND Po_Line_Id = p_frm_line_id
ORDER BY a.tax_line_no;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = p_po_line_id AND
Line_Location_Id = p_line_loc_id;
SELECT tax_category_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_po_line_id
AND line_location_id IS NULL;
SELECT tax_category_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_po_line_id
AND line_location_id = p_line_location_id;
select nvl(adhoc_flag,'N')
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
SELECT vendor_id,vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_po_hdr_id;
SELECT ITEM_ID
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
jai_po_cmn_pkg.insert_line
( 'STANDARD',
p_line_loc_id,
p_po_hdr_id,
p_po_line_id,
p_cre_dt,
p_cre_by,
p_last_upd_dt,
p_last_upd_by,
p_last_upd_login,
'I'
);
INSERT INTO JAI_PO_TAXES(
po_line_id,
po_header_id,
line_location_id,
line_focus_id,
tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
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 (
p_po_line_id, p_po_hdr_id, p_line_loc_id, v_line_focus_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, v_tax_amount, 0, --Previously The Value of TaxAmount is 0 Which is now changed to v_tax_amount for Bug2953445
rec.tax_type, rec.modvat_flag, rec.vendor_id, rec.currency,
p_cre_dt, p_cre_by, p_last_upd_dt, p_last_upd_by, p_last_upd_login,
rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_tax_category_id_holder
WHERE line_focus_id = v_line_focus_id;
/* following block is commented and replaced with the above UPDATE statement
-- Start, cbabu for EnhancementBug# 2427465
BEGIN
IF v_quot_line_loc_id = -999 THEN
SELECT tax_category_id INTO v_tax_category_id_holder
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_frm_line_id
AND (line_location_id IS NULL OR line_location_id = 0);
SELECT tax_category_id INTO v_tax_category_id_holder
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_frm_line_id
AND line_location_id = v_quot_line_loc_id;
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_tax_category_id_holder
WHERE line_focus_id = v_line_focus_id;
SELECT 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,
Vendor_Id, Modvat_Flag, Tax_Target_Amount,
tax_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES
WHERE NVL( Line_Location_Id, -999 ) = llid /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980* -- NVL condition is added by SAcsethi for bug 6134628 */
AND Po_Line_Id = lineid
ORDER BY Tax_Line_No;
SELECT *
FROM JAI_PO_REQUEST_T
WHERE line_focus_id = p_seq_val -- Modified By Sjha
ORDER BY Tax_Line_No;
SELECT Quantity, Price_Override, Unit_Meas_Lookup_Code
FROM Po_Line_Locations_All
WHERE Line_Location_Id = llid;
SELECT SUM( Quantity )
FROM Po_Line_Locations_All PLL
WHERE Po_Line_Id = p_line_id
-- cbabu for Bug# 3102375
AND shipment_type <> cp_shipment_type --'PRICE BREAK'
AND ((pQryOn = 'ALL' AND PLL.SHIP_TO_ORGANIZATION_ID = pShipToOrganizationId AND PLL.SHIP_TO_LOCATION_ID = pShipToLocationId )
OR (pQryOn = 'ORG' AND PLL.SHIP_TO_ORGANIZATION_ID = pShipToOrganizationId AND PLL.SHIP_TO_LOCATION_ID IS NULL )
OR (pQryOn = 'NULL' AND PLL.SHIP_TO_ORGANIZATION_ID IS NULL AND PLL.SHIP_TO_LOCATION_ID IS NULL )
);
SELECT NVL( COUNT( Line_Location_Id ), 0 )
FROM Po_Line_Locations_All PLL
WHERE Po_Line_Id = p_line_id
AND Shipment_Type = cp_shipment_type --'PRICE BREAK'
-- cbabu for Bug# 3102375
AND ((pQryOn = 'ALL' AND PLL.SHIP_TO_ORGANIZATION_ID = pShipToOrganizationId AND PLL.SHIP_TO_LOCATION_ID = pShipToLocationId )
OR (pQryOn = 'ORG' AND PLL.SHIP_TO_ORGANIZATION_ID = pShipToOrganizationId AND PLL.SHIP_TO_LOCATION_ID IS NULL )
OR (pQryOn = 'NULL' AND PLL.SHIP_TO_ORGANIZATION_ID IS NULL AND PLL.SHIP_TO_LOCATION_ID IS NULL )
)
AND SYSDATE between nvl(start_date, SYSDATE) and nvl(end_date, SYSDATE);
SELECT Line_Location_Id, Quantity, Price_Override, Unit_Meas_Lookup_Code
FROM Po_Line_Locations_All PLL
WHERE Po_Header_Id = p_hdr_id
AND Po_Line_Id = p_line_id
AND Shipment_Type = cp_shipment_type --'PRICE BREAK'
-- cbabu for Bug# 3102375
AND ((pQryOn = 'ALL' AND PLL.SHIP_TO_ORGANIZATION_ID = pShipToOrganizationId AND PLL.SHIP_TO_LOCATION_ID = pShipToLocationId )
OR (pQryOn = 'ORG' AND PLL.SHIP_TO_ORGANIZATION_ID = pShipToOrganizationId AND PLL.SHIP_TO_LOCATION_ID IS NULL )
OR (pQryOn = 'NULL' AND PLL.SHIP_TO_ORGANIZATION_ID IS NULL AND PLL.SHIP_TO_LOCATION_ID IS NULL )
)
AND SYSDATE between nvl(start_date, SYSDATE) and nvl(end_date, SYSDATE)
ORDER BY Quantity;
SELECT ship_to_organization_id, ship_to_location_id
FROM po_line_locations_all
WHERE line_location_id = pLineLocationId;
SELECT Price_Override
FROM Po_Line_Locations_All
WHERE po_header_id = p_hdr_id
AND po_line_id = p_line_id
AND line_location_id=p_line_loc_id;
SELECT Po_Line_Id, Unit_Price, Unit_Meas_Lookup_Code, Item_Id
FROM Po_Lines_All
WHERE Po_Line_Id = p_line_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = uom;
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_PO_TAXES
WHERE Line_Location_Id = llid
AND Tax_Type <> jai_constants.tax_type_tds; --'TDS';
SELECT Vendor_Id, Vendor_Site_Id, Currency_Code
FROM Po_Headers_All
WHERE Po_Header_Id = v_po_hdr_id;
SELECT Item_Id
FROM Po_Lines_All
WHERE Po_Line_Id = v_po_line_id;
SELECT tax_category_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_po_line_id
AND line_location_id IS NULL;
SELECT tax_category_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_po_line_id
AND line_location_id = p_line_location_id;
SELECT override_flag
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = c_vendor_id
AND vendor_site_id = nvl(c_vendor_site_id,0) ;
select vendor_id,vendor_site_id , currency_code , rate ,RATE_TYPE , RATE_DATE ,ship_to_location_id
from po_headers_all
where po_header_id=p_po_hdr_id;
SELECT Price_Override ,Unit_Meas_Lookup_Code
FROM Po_Line_Locations_All
WHERE Po_Line_Id = lineid
AND Line_Location_Id = linelocid;
SELECT Inventory_Organization_id
FROM Hr_Locations
WHERE Location_Id = lv_ship_to_location_id ;
9. 01/15/2008 Kevin Cheng Add a parameter to distinguish retroactive price update process
-------------------------------------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
v_qty := p_qty;
SELECT COUNT(1) INTO v_tax_count FROM
JAI_PO_TAXES
WHERE Line_Location_Id = Tax_Rec.Line_Location_Id AND
TAX_ID = Tax_Rec.Tax_id;
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 (
Tax_Rec.Line_Focus_Id, --p_seq_val, -- Modified By Sjha
Tax_Rec.Line_Location_Id, Tax_Rec.Tax_Line_No,
Tax_Rec.Po_Line_Id, Tax_Rec.Po_Header_Id,
Tax_Rec.Precedence_1,Tax_Rec.Precedence_2, Tax_Rec.Precedence_3, Tax_Rec.Precedence_4, Tax_Rec.Precedence_5,
Tax_Rec.Precedence_6,Tax_Rec.Precedence_7, Tax_Rec.Precedence_8, Tax_Rec.Precedence_9, Tax_Rec.Precedence_10,
Tax_Rec.Tax_Id, Tax_Rec.Currency,
Tax_Rec.Tax_Rate, Tax_Rec.Qty_Rate, Tax_Rec.UOM, v_tax_amt,
Tax_Rec.Tax_Type, Tax_Rec.Modvat_Flag, Tax_Rec.Vendor_Id, Tax_Rec.Tax_Target_Amount,
Tax_Rec.Creation_Date, Tax_Rec.Created_By,
Tax_Rec.Last_Update_Date, Tax_Rec.Last_Updated_By, Tax_Rec.Last_Update_Login,
tax_rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = v_tax_amt1,
Total_Amount = NVL( v_qty * v_temp_price, 0 ) + v_tax_amt1 ,
Last_Update_Date = p_last_cre_dt,
Last_Updated_By = p_last_cre_by,
Last_Update_Login = p_last_login
WHERE Line_Location_Id = v_line_loc_id;
insert all the qty, price into PL/SQL table then do all the checking.
*/
-- FOR Lines_Rec IN Fetch_Locid_Cur LOOP
FOR Lines_Rec IN Fetch_Locid_Cur(vQryOn, vShpDtl.ship_to_organization_id, vShpDtl.ship_to_location_id, 'PRICE BREAK') LOOP /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
Llid_tab( i ) := Lines_Rec.Line_Location_Id;
SELECT COUNT(1) INTO v_tax_count FROM
JAI_PO_TAXES
WHERE Line_Location_Id = p_line_Loc_Id AND
TAX_ID = Tax_Rec.Tax_id;
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 (
p_seq_val, p_line_Loc_Id, Tax_Rec.Tax_Line_No,
p_Line_Id, p_hdr_id,
Tax_Rec.Precedence_1,Tax_Rec.Precedence_2, Tax_Rec.Precedence_3, Tax_Rec.Precedence_4, Tax_Rec.Precedence_5,
Tax_Rec.Precedence_6,Tax_Rec.Precedence_7, Tax_Rec.Precedence_8, Tax_Rec.Precedence_9, Tax_Rec.Precedence_10,
Tax_Rec.Tax_Id, Tax_Rec.Currency, Tax_Rec.Tax_Rate,
Tax_Rec.Qty_Rate, Tax_Rec.UOM, 0, Tax_Rec.Tax_Type,
Tax_Rec.Modvat_Flag, Tax_Rec.Vendor_Id, 0,
p_cre_dt, p_cre_by, p_last_cre_dt, p_last_cre_by, p_last_login,
tax_rec.tax_category_id -- cbabu for EnhancementBug# 2427465
);
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_tax_category_id_holder
WHERE line_focus_id = p_seq_val;
/* following block is commented and replaced with the above UPDATE statement
-- Start, cbabu for EnhancementBug# 2427465
BEGIN
IF v_line_loc_id = -999 THEN
SELECT tax_category_id INTO v_tax_category_id_holder
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = v_po_line_id
AND (line_location_id IS NULL OR line_location_id = 0);
SELECT tax_category_id INTO v_tax_category_id_holder
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = v_po_line_id
AND line_location_id = v_line_loc_id;
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_tax_category_id_holder
WHERE line_focus_id = p_seq_val;
DELETE FROM JAI_PO_REQUEST_T WHERE line_focus_id = p_seq_val;
New parameter is added in JA_IN_PO_INSERT procedure that is internally called from other procedure of this package
3 14/04/2004 Vijay Shankar for Bug# 3466223, FileVersion# 619.1
RAISE_APPLICATION_ERROR is removed, this is stopping tax defaultation if tax_category_id is not found for
the Source Document line/shipment in Localization tables.
For this purpose Cursors c_line_tax_category_id_1 and c_line_tax_category_id_2 are added
4 12/03/2005 Bug 4210102. Added by LGOPALSA
(1) Added check file syntax
(2) Added NOCOPY for OUT Parameters
(3) Added CVD and Customs education cess
5.17-Mar-2005 hjujjuru - bug #4245062 File version 115.2
The Assessable Value is calculated for the transaction. For this, a call is
made to the function ja_in_vat_assessable_value_f.sql with the parameters
relevant for the transaction. This assessable value is again passed to the
procedure that calucates the taxes.
Base bug - #4245089
6. 22-Jun-2007 CSahoo for bug#6144740 File Version 120.15
added a new input parameter p_quantity to the procedure.
===============================================================================
Dependencies
Version Author Dependencies Comments
115.1 LGOPALSA IN60106 + Added cess related tax types
4146708
115.2 hjujjuru 4245089 VAT Implelentation
--------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE Ja_In_Po_Case1(
v_type_lookup_code IN VARCHAR2,
v_quot_class_code IN VARCHAR2,
vendor_id IN NUMBER,
v_vendor_site_id IN NUMBER,
currency IN VARCHAR2,
v_org_id IN NUMBER,
v_item_id IN NUMBER,
v_uom_measure IN VARCHAR2,
v_line_loc_id IN NUMBER,
v_po_hdr_id IN NUMBER,
v_po_line_id IN NUMBER,
v_frm_po_line_id IN NUMBER,
v_frm_line_loc_id IN NUMBER,
v_price IN NUMBER,
v_qty IN NUMBER,
v_cre_dt IN DATE,
v_cre_by IN NUMBER,
v_last_upd_dt IN DATE,
v_last_upd_by IN NUMBER,
v_last_upd_login IN NUMBER,
flag IN VARCHAR2,
success IN OUT NOCOPY NUMBER, -- If success doesnt return 0, then Use Ja_In_Po_Case2
p_quantity IN PO_LINE_LOCATIONS_ALL.quantity%TYPE DEFAULT NULL --added by csahoo for bug#6144740
)
IS
v_seq_val NUMBER;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom_measure;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id IS NULL;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;
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_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES
WHERE Po_Line_Id = v_frm_po_line_id
AND Line_Location_Id IS NULL
ORDER BY Tax_Line_No;
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_category_id -- cbabu for EnhancementBug# 2427465
FROM JAI_PO_TAXES
WHERE Po_Line_Id = v_frm_po_line_id
AND Line_Location_Id = v_frm_line_loc_id
ORDER BY Tax_Line_No;
SELECT Tax_Type, Mod_Cr_Percentage, Vendor_Id,adhoc_flag --kundan kumar for forward porting to R12
FROM JAI_CMN_TAXES_ALL
WHERE Tax_Id = taxid;
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_PO_TAXES
WHERE Line_Focus_Id = lfid;
SELECT tax_category_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_po_line_id
AND line_location_id IS NULL;
SELECT tax_category_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = p_po_line_id
AND line_location_id = p_line_location_id;
SELECT quantity
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id
AND line_location_id IS NULL ;
SELECT quantity
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id
AND line_location_id = p_po_line_location_id ;
jai_po_tax_pkg.Ja_In_Po_Insert( v_type_lookup_code, v_quot_class_code,
v_seq_val, 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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
v_taxid, v_price, v_qty, v_curr,
v_tax_rate, v_qty_rate, v_uom,
v_tax_amt, v_tax_type, v_mod_flag,--NULL WAS REPLACED BY KUNDAN KUMAR FOR FORWARD PORTING TO R12
v_vendor_id, NULL,
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
);
jai_po_tax_pkg.Ja_In_Po_Insert( v_type_lookup_code, v_quot_class_code,
v_seq_val, 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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
v_taxid, v_price, v_qty, v_curr,
v_tax_rate, v_qty_rate, v_uom,
v_tax_amt, v_tax_type, v_mod_flag, --Added by kundan kumar for forward porting to R12
v_vendor_id, NULL,
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_category_id = v_tax_category_id_dflt
WHERE line_focus_id = v_seq_val;
SELECT tax_category_id INTO v_tax_category_id_dflt
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = v_frm_line_loc_id;
SELECT tax_category_id INTO v_tax_category_id_dflt
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = v_frm_po_line_id
AND (line_location_id IS NULL OR line_location_id = 0);
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_tax_category_id_dflt
WHERE line_focus_id = v_seq_val;
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = NVL( v_tax_amt, 0 ),
Total_Amount = NVL( ( v_qty * v_price ), 0 ) + Tax_Amount,
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login
WHERE Line_Focus_id = v_seq_val;
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = NULL,
Total_Amount = NULL,
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login
WHERE Line_Focus_Id = v_seq_val;
SELECT Item_Class
FROM JAI_INV_ITM_SETUPS
WHERE Inventory_Item_Id = itemid
AND Organization_id = v_org_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom_measure;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = v_po_line_id
AND Po_Header_Id = v_po_hdr_id
AND Line_Location_Id IS NULL;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = v_po_line_id
AND Po_Header_Id = v_po_hdr_id
AND Line_Location_Id = v_line_loc_id;
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'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
DELETE Jai_Po_Taxes
WHERE Po_Line_Id = v_po_line_id
AND NVL( Line_Location_Id, 0 ) = NVL( v_line_loc_id, 0 ); /* Replaced -999 with 0 - Bug 6012541 */
UPDATE Jai_Po_Line_Locations
SET Tax_Amount = NULL,
Total_Amount = NULL,
Last_Updated_By = fnd_global.user_id,
Last_Update_Date = sysdate,
Last_Update_Login = fnd_global.login_id
WHERE Po_Line_Id = v_po_line_id
AND NVL( Line_Location_Id, 0 ) = NVL( v_line_loc_id, 0 ); /* Replaced -999 with 0 - Bug 6012541 */
operation := '$I'; -- Insert
UPDATE JAI_PO_TAXES
SET Tax_Amount = NULL,
Tax_Target_Amount = NULL,
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = NULL,
Total_Amount = NULL,
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_id = v_line_loc_id;
UPDATE JAI_PO_TAXES
SET Tax_Amount = NULL,
Tax_Target_Amount = NULL,
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id IS NULL;
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = NULL,
Total_Amount = NULL,
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_id IS NULL;
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = nvl(v_tax_amt,0),
Total_Amount = NVL( ( v_qty * v_price ), 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
WHERE Line_Location_id = v_line_loc_id
AND Po_Line_Id = v_po_line_id;
PROCEDURE ja_in_po_insert(
v_type_lookup_code IN VARCHAR2,
v_quot_class_code IN VARCHAR2,
v_seq_val IN NUMBER,
v_line_loc_id IN NUMBER,
v_tax_line_no IN NUMBER,
v_po_line_id IN NUMBER,
v_po_hdr_id IN NUMBER,
v_prec1 IN NUMBER,
v_prec2 IN NUMBER,
v_prec3 IN NUMBER,
v_prec4 IN NUMBER,
v_prec5 IN NUMBER,
v_prec6 IN NUMBER, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
v_prec7 IN NUMBER,
v_prec8 IN NUMBER,
v_prec9 IN NUMBER,
v_prec10 IN NUMBER,
v_taxid IN NUMBER,
v_price IN NUMBER,
v_qty IN NUMBER,
v_curr IN VARCHAR2,
v_tax_rate IN NUMBER,
v_qty_rate IN NUMBER,
v_uom IN VARCHAR2,
v_tax_amt IN NUMBER ,
v_tax_type VARCHAR2,
v_mod_flag IN VARCHAR2,
v_vendor_id IN NUMBER,
v_tax_target_amt IN NUMBER,
v_cre_dt IN DATE,
v_cre_by IN NUMBER,
v_last_upd_dt IN DATE,
v_last_upd_by IN NUMBER,
v_last_upd_login IN NUMBER,
v_tax_category_id IN NUMBER -- cbabu for EnhancementBug# 2427465
) IS
v_tax_amt1 NUMBER; --File.Sql.35 Cbabu := 0;
SELECT adhoc_flag
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = cp_tax_id;
lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_tax_pkg.ja_in_po_insert';
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_seq_val, 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, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
v_taxid, v_curr, v_tax_rate, v_qty_rate,
v_uom, v_tax, v_tax_type, v_mod_flag,
v_vendor_id, v_tax_target,
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
);
END Ja_In_Po_Insert;