[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure process_req_line_inserting
(
--pn_requisition_line_id NUMBER
pr_requisition_line PO_REQUISITION_LINES_ALL%ROWTYPE
);
SELECT line_focus_id
FROM jai_po_line_locations
WHERE po_line_id = v_po_line_id AND
nvl(line_location_id,0) = nvl(v_line_loc_id,0);
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,
tax_amount, tax_type, modvat_flag, vendor_id, tax_category_id
FROM jai_po_taxes
WHERE po_line_id = v_frm_po_line_id AND
nvl(line_location_id,0) = nvl(v_line_loc_id,0)
ORDER BY tax_line_no;
SELECT tax_type, mod_cr_percentage, vendor_id, adhoc_flag
FROM jai_cmn_taxes_all
WHERE tax_id = taxid;
SELECT tax_category_id
FROM jai_po_line_locations
WHERE po_line_id = p_po_line_id AND
nvl(line_location_id,0) = nvl(p_line_location_id,0);
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_PO_TAXES
WHERE Po_Line_Id = v_po_line_id
AND nvl(Line_Location_Id,0) = nvl(v_line_loc_id,0)
AND Tax_Type <> jai_constants.tax_type_tds;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = c_po_line_id
AND nvl(line_location_id,0)= nvl(c_line_loc_id,0);
jai_po_proc_pkg.insert_po_taxes(
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
,v_prec6
,v_prec7
,v_prec8
,v_prec9
,v_prec10
,v_taxid
,v_price
,v_qty
,lv_currency
,v_tax_rate
,v_qty_rate
,v_uom_code
,v_tax_amt
,v_tax_type
,v_mod_flag
,lv_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
);
UPDATE jai_po_line_locations
SET tax_category_id = v_tax_category_id_dflt
WHERE line_focus_id = v_seq_val;
DELETE Jai_Po_Taxes WHERE Po_Line_Id = v_po_line_id
AND NVL( Line_Location_Id, 0 ) = NVL( v_line_loc_id, 0 );
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 );
operation := '$I'; -- Insert
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 nvl(Line_Location_Id,0) = nvl(v_line_loc_id,0);
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 nvl(Line_Location_Id,0) = nvl(v_line_loc_id,0);
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 Po_Line_Id = v_po_line_id
AND nvl(Line_Location_Id,0) = nvl(v_line_loc_id,0);
REM Purpose : process tax related logic when the event of Create and Update for PO Documents.
REM The main logic is insert/update data into JAI_PO_LINE_LOCATIONS table
REM and JAI_PO_TAXES table.
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ --------------------- ---------- ------------------------------------
REM prec_po_line IN PO_LINES_ALL%ROWTYPE N PO Line info.
REM prec_po_location IN PO_LINE_LOCATIONS_ALL%ROWTYPE N PO Shipment line.
REM pv_retro_price_flag IN Varchar2 N retroactive price changed flag.
REM CALLED BY
REM process_po, process_po_line, process_po_shipment_line
REM +================================================================================================+
*/
PROCEDURE process_po_tax_wrapper (prec_po_header IN po_headers_all%ROWTYPE DEFAULT NULL--Added by Wenqiong for bug16288154
,prec_po_line IN PO_LINES_ALL%ROWTYPE DEFAULT NULL
,prec_po_location IN PO_LINE_LOCATIONS_ALL%ROWTYPE DEFAULT NULL
,pv_retro_price_flag IN VARCHAR2 DEFAULT 'N'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'process_po_tax_wrapper';
v_last_upd_dt PO_LINES_ALL.Last_Update_Date%type;
v_last_upd_by PO_LINES_ALL.Last_Updated_By%type;
v_last_upd_login PO_LINES_ALL.Last_Update_Login%type;
SELECT Type_Lookup_Code, Quotation_Class_Code,vendor_id, vendor_site_id
,Currency_Code, Ship_To_Location_Id,rate_type, rate_date, rate
FROM Po_Headers_All
WHERE Po_Header_Id = v_po_hdr_id;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = c_po_line_id
AND line_location_id IS NULL;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = c_line_loc_id;
SELECT item_id
FROM PO_LINES_ALL
WHERE po_line_id = cn_po_line_id;
SELECT quantity
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = c_line_loc_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom_measure;
SELECT Inventory_Organization_Id
FROM Hr_Locations
WHERE Location_Id = v_ship_loc_id;
SELECT set_of_books_id
FROM org_organization_definitions
WHERE operating_unit = v_org_id;
SELECT
sob.currency_code
FROM
financials_system_params_all FSP, gl_sets_of_books SOB
WHERE
FSP.set_of_books_id = SOB.set_of_books_id
AND FSP.org_id = v_org_id ;
v_last_upd_dt := prec_po_line.Last_Update_Date ;
v_last_upd_by := prec_po_line.Last_Updated_By;
v_last_upd_login:= prec_po_line.Last_Update_Login;
v_last_upd_dt := prec_po_location.Last_Update_Date ;
v_last_upd_by := prec_po_location.Last_Updated_By;
v_last_upd_login:= prec_po_location.Last_Update_Login;
UPDATE JAI_PO_TAXES a
SET tax_amount = (tax_amount * v_qty/ v_old_qty ),
tax_target_amount = (tax_target_amount * v_qty/ v_old_qty)
WHERE line_location_id = v_line_loc_id
AND EXISTS (select 1 from JAI_CMN_TAXES_ALL b where b.tax_id = a.tax_id and b.adhoc_flag = 'Y');
insert_jai_line_locs(
v_line_loc_id => v_line_loc_id
,v_po_hdr_id => v_po_hdr_id
,v_po_line_id => v_po_line_id
,v_cre_dt => v_cre_dt
,v_cre_by => v_cre_by
,v_last_upd_dt => v_last_upd_dt
,v_last_upd_by => v_last_upd_by
,v_last_upd_login => v_last_upd_login
,flag => 'U'
,v_item_id => v_item_id
,v_vendor_id => v_vendor_id
,v_vendor_site_id => v_vendor_site_id
,v_qty => v_qty
,v_price_override => v_price
,v_currency => v_currency
,v_line_focus_id => v_line_focus_id
);
delete_po_taxes(pn_po_line_id =>v_po_line_id, pn_line_loc_id => v_line_loc_id);
delete_jai_po_lines(pn_line_loc_id => v_line_loc_id);
,pv_action => JAI_CONSTANTS.inserting
,pn_conv_rate => v_currency_conv_rate
,pn_assessable_value => v_assessable_value
,pn_vat_assess_value => v_vat_assess_value
);
delete_po_taxes(pn_po_line_id =>v_po_line_id, pn_line_loc_id => v_line_loc_id);
delete_jai_po_lines(pn_line_loc_id => v_line_loc_id);
delete_po_taxes(pn_po_line_id =>v_po_line_id);
delete_jai_po_lines(pn_line_id =>v_po_line_id);
insert_jai_line_locs(
v_line_loc_id => v_line_loc_id
,v_po_hdr_id => v_po_hdr_id
,v_po_line_id => v_po_line_id
,v_cre_dt => v_cre_dt
,v_cre_by => v_cre_by
,v_last_upd_dt => v_last_upd_dt
,v_last_upd_by => v_last_upd_by
,v_last_upd_login => v_last_upd_login
,flag => 'I'
,v_item_id => v_item_id
,v_vendor_id => v_vendor_id
,v_vendor_site_id => v_vendor_site_id
,v_qty => v_qty
,v_price_override => v_price
,v_currency => v_currency
,v_line_focus_id => v_line_focus_id
);
REM p_last_upd_dt IN DATE Y last update date
REM p_last_upd_by IN NUMBER Y last update by
REM p_last_upd_login IN NUMBER Y last update login
REM CALLED BY
REM process_po_line, process_po_shipment_line
REM +================================================================================================+
*/
PROCEDURE copy_source_taxes (
p_type VARCHAR2,
p_po_hdr_id NUMBER,
p_po_line_id NUMBER,
p_po_line_loc_id NUMBER,
p_line_num NUMBER,
p_ship_num NUMBER,
p_item_id NUMBER,
p_from_hdr_id NUMBER,
p_from_type_lookup_code VARCHAR2,
p_source_line_id NUMBER,
p_source_loc_id NUMBER,
p_cre_dt DATE,
p_cre_by NUMBER,
p_last_upd_dt DATE,
p_last_upd_by NUMBER,
p_last_upd_login NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'copy_source_taxes';
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
AND pha.po_header_id =pla.po_header_id;*/
SELECT line_location_id, po_line_id, po_header_id, item_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 ,quantity,PRICE_OVERRIDE, currency -- 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 );
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,
line_focus_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
tax_category_id
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 );
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,item_id, quantity,PRICE_OVERRIDE,currency
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,
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
FROM JAI_PO_TAXES
WHERE Line_Location_Id = v_ln_loc_id;
select vendor_id,vendor_site_id
from po_headers_all
where po_header_id=p_po_hdr_id;
insert_jai_line_locs(
v_line_loc_id => p_po_line_loc_id
,v_po_hdr_id => p_po_hdr_id
,v_po_line_id => p_po_line_id
,v_cre_dt => p_cre_dt
,v_cre_by => p_cre_by
,v_last_upd_dt => p_last_upd_dt
,v_last_upd_by => p_last_upd_by
,v_last_upd_login => p_last_upd_login
,flag => 'I'
,v_item_id => rec1.item_id
,v_vendor_id => v_vendor_id
,v_vendor_site_id => v_vendor_site_id
,v_price_override => rec1.PRICE_OVERRIDE
,v_qty => rec1.quantity
,v_tax_modified_flag => 'Y'
,v_tax_category_id => rec1.tax_category_id
,v_tax_amount => rec1.tax_amount
,v_total_amount => rec1.total_amount
,v_currency => rec1.currency
,v_line_focus_id => v_seq_val
);
insert_po_taxes
( p_from_type_lookup_code, NULL,
v_seq_val, 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, NULL, 0, rec2.currency,
rec2.tax_rate, rec2.qty_rate, rec2.uom,
rec2.tax_amount , rec2.tax_type, rec2.modvat_flag,
rec2.vendor_id, rec2.tax_target_amount ,
p_cre_dt, p_cre_by, p_last_upd_dt,
p_last_upd_by, p_last_upd_login,
rec2.tax_category_id
);
insert_jai_line_locs(
v_line_loc_id => p_po_line_loc_id
,v_po_hdr_id => p_po_hdr_id
,v_po_line_id => p_po_line_id
,v_cre_dt => p_cre_dt
,v_cre_by => p_cre_by
,v_last_upd_dt => p_last_upd_dt
,v_last_upd_by => p_last_upd_by
,v_last_upd_login => p_last_upd_login
,flag => 'I'
,v_item_id => fetch_ja_in_po_ln_loc_rec.item_id
,v_vendor_id => v_vendor_id
,v_vendor_site_id => v_vendor_site_id
,v_price_override => fetch_ja_in_po_ln_loc_rec.PRICE_OVERRIDE
,v_qty => fetch_ja_in_po_ln_loc_rec.quantity
,v_tax_modified_flag => 'Y'
,v_tax_category_id => fetch_ja_in_po_ln_loc_rec.tax_category_id
,v_tax_amount => fetch_ja_in_po_ln_loc_rec.tax_amount
,v_total_amount => fetch_ja_in_po_ln_loc_rec.total_amount
,v_currency => fetch_ja_in_po_ln_loc_rec.currency
,v_line_focus_id => v_seq_val
);
insert_po_taxes
( p_from_type_lookup_code, NULL,
v_seq_val, 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, NULL, 0, rec.currency,
rec.tax_rate, rec.qty_rate, rec.uom,
rec.tax_amount , rec.tax_type, rec.modvat_flag,
rec.vendor_id, rec.tax_target_amount ,
p_cre_dt, p_cre_by, p_last_upd_dt,
p_last_upd_by, p_last_upd_login,
rec.tax_category_id
);
REM p_last_upd_dt IN DATE Y last update date
REM p_last_upd_by IN NUMBER Y last update by
REM p_last_upd_login IN NUMBER Y last update login
REM CALLED BY
REM process_po_line, process_po_shipment_line
REM +================================================================================================+
*/
PROCEDURE copy_quot_taxes
(
p_line_loc_id IN NUMBER,
p_po_hdr_id IN NUMBER,
p_po_line_id IN NUMBER,
p_item_id IN NUMBER,--Added by Wenqiong for avoid trigger mutating
p_qty IN NUMBER,
p_frm_hdr_id IN NUMBER,
p_frm_line_id IN NUMBER,
p_price IN NUMBER,
p_unit_code IN VARCHAR2,
p_cre_dt IN DATE,
p_cre_by IN NUMBER,
p_last_upd_dt IN DATE,
p_last_upd_by IN NUMBER,
p_last_upd_login IN NUMBER,
p_call_from IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'copy_quot_taxes';
SELECT a.Po_Line_Id, a.tax_line_no, a.tax_id,
a.precedence_1,
a.precedence_2,
a.precedence_3,
a.precedence_4,
a.precedence_5,
a.precedence_6,
a.precedence_7,
a.precedence_8,
a.precedence_9,
a.precedence_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 JAI_PO_TAXES a
WHERE ((a.line_location_id IS NULL AND v_quot_line_loc_id=-999) OR (a.line_location_id = v_quot_line_loc_id))
AND Po_Line_Id = p_frm_line_id
ORDER BY a.tax_line_no;
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,currency_code
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_proc_pkg.delete_jai_po_lines(pn_line_id=>p_po_line_id);
jai_po_proc_pkg.delete_po_taxes(pn_po_line_id=>p_po_line_id);
insert_jai_line_locs(
v_line_loc_id => p_line_loc_id
,v_po_hdr_id => p_po_hdr_id
,v_po_line_id => p_po_line_id
,v_cre_dt => p_cre_dt
,v_cre_by => p_cre_by
,v_last_upd_dt => p_last_upd_dt
,v_last_upd_by => p_last_upd_by
,v_last_upd_login => p_last_upd_login
,flag => 'I'
,v_item_id => v_item_id
,v_vendor_id => v_vendor_id
,v_vendor_site_id => v_vendor_site_id
,v_price_override => p_price
,v_qty => p_qty
,v_currency => v_currency
,v_line_focus_id => v_line_focus_id
);
insert_po_taxes
( 'STANDARD', NULL,
v_line_focus_id, p_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, NULL, 0, rec.currency,
rec.tax_rate, rec.qty_rate, rec.uom,
rec.tax_amount , rec.tax_type, rec.modvat_flag,
rec.vendor_id, 0 ,
p_cre_dt, p_cre_by, p_last_upd_dt,
p_last_upd_by, p_last_upd_login,
rec.tax_category_id
);
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_tax_category_id_holder
WHERE line_focus_id = v_line_focus_id;
REM pv_action IN VARCHAR2 Y Indiate action is inserting/updating.
REM CALLED BY
REM JAI_TAX_PROCESSING_PKG.calculate_tax
REM +================================================================================================+
*/
PROCEDURE process_po (pn_header_id IN Number, pv_action IN VARCHAR2) IS
CURSOR c_po_line IS
SELECT * FROM PO_LINES_ALL
WHERE Po_Header_Id = pn_header_id;
SELECT * FROM PO_HEADERS_ALL
WHERE po_header_id = pn_header_id;
SELECT DISTINCT currency,vendor_id, vendor_site_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_header_id = pn_header_id;
SELECT 1
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = line_id;
v_last_upd_dt := r_po_header.Last_Update_Date ;
v_last_upd_by := r_po_header.Last_Updated_By;
v_last_upd_login := r_po_header.Last_Update_Login;
IF pv_action = JAI_CONSTANTS.inserting THEN
FOR r_po_line IN c_po_line
LOOP
process_po_line(r_po_line.po_line_id,r_po_header,pv_action);
jai_po_proc_pkg.update_currency(
pn_po_header_id => pn_header_id
,pv_old_currency => v_old_currency
,pv_currency => r_po_header.Currency_Code
,pd_last_upd_dt => v_last_upd_dt
,pn_last_upd_by => v_last_upd_by
,pn_last_upd_login => v_last_upd_login
);
\*For vendor merge case, need update vendor in receipts,
todo, seemed to be removed since vendor merge case will not fire through ZX.
*\
IF v_vendor_id <> v_old_vendor_id then
for c_receipt_rec in
(
select distinct shipment_header_id, shipment_line_id
from rcv_transactions
where po_header_id = v_vendor_id
)
loop
update JAI_RCV_LINE_TAXES
set vendor_id = v_vendor_id
where shipment_header_id = c_receipt_rec.shipment_header_id
and shipment_line_id = c_receipt_rec.shipment_line_id
and vendor_id = v_old_vendor_id;
delete_po_taxes(pn_po_hdr_id => pn_header_id);
jai_po_proc_pkg.update_vendor(
pn_po_header_id => pn_header_id
,pn_old_vendor_id => v_old_vendor_id
,pn_vendor_id => v_vendor_id
,pn_vendor_site_id => v_vendor_site_id
,pd_last_upd_dt => v_last_upd_dt
,pn_last_upd_by => v_last_upd_by
,pn_last_upd_login => v_last_upd_login
);
lv_action := JAI_CONSTANTS.inserting;
REM pv_action IN VARCHAR2 Y Indiate action is inserting/updating.
REM CALLED BY
REM jai_po_proc_pkg.process_po
REM +=========================================================================================================+
*/
PROCEDURE process_po_line (pn_line_id IN NUMBER,pr_po_header IN PO_HEADERS_ALL%ROWTYPE DEFAULT NULL, pv_action IN VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'process_po_line';
SELECT * FROM PO_LINES_ALL
WHERE po_line_id = pn_line_id;
SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE po_line_id = pn_line_id
AND shipment_type NOT IN ( 'SCHEDULED', 'BLANKET' );
SELECT Line_location_id, Price_Override, Quantity
FROM Po_Line_Locations_All
WHERE Po_Line_Id = pn_line_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom;
SELECT DISTINCT item_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = pn_line_id;
SELECT 1
FROM JAI_PO_LINE_LOCATIONS
WHERE Line_Location_Id = cn_line_loc_id;
SELECT *
FROM PO_HEADERS_ALL
WHERE po_header_id = cn_po_hdr_id;
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
AND pha.po_header_id =pla.po_header_id;
r_po_line.PROGRAM_UPDATE_DATE ,
r_po_line.CLOSED_DATE ,
r_po_line.CLOSED_REASON ,
r_po_line.CLOSED_BY ,
r_po_line.TRANSACTION_REASON_CODE ,
r_po_line.ORG_ID ,
r_po_line.QC_GRADE ,
r_po_line.BASE_UOM ,
r_po_line.BASE_QTY ,
r_po_line.SECONDARY_UOM ,
r_po_line.SECONDARY_QTY ,
r_po_line.LINE_REFERENCE_NUM ,
r_po_line.PROJECT_ID ,
r_po_line.TASK_ID ,
r_po_line.EXPIRATION_DATE ,
r_po_line.TAX_CODE_ID
);
IF pv_action = JAI_CONSTANTS.inserting THEN
IF v_type_lookup_code = 'BLANKET' OR v_quot_class_code = 'CATALOG' THEN
IF v_type_lookup_code = 'BLANKET' AND v_frm_hdr_id IS NOT NULL THEN
OPEN Fetch_Line_Id_Cur( v_frm_hdr_id,r_po_line.line_num);
,p_last_upd_dt => r_po_line.Last_Update_Date
,p_last_upd_by => r_po_line.Last_Updated_By
,p_last_upd_login => r_po_line.Last_Update_Login
);
pv_action => JAI_CONSTANTS.inserting);
pv_action =>JAI_CONSTANTS.inserting);
REM pv_action IN VARCHAR2 Y Indiate action is inserting/updating.
REM CALLED BY
REM jai_po_proc_pkg.process_po
REM +=========================================================================================================+
*/
PROCEDURE process_po_shipment_line (pn_line_loc_id IN NUMBER,pr_po_header IN PO_HEADERS_ALL%ROWTYPE DEFAULT NULL,pr_po_line IN PO_LINES_ALL%ROWTYPE DEFAULT NULL, pv_action IN VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'process_po_shipment_line';
SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE line_location_id = pn_line_loc_id;
SELECT *
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = p_line_location_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = v_uom_measure;
SELECT COUNT(Line_Location_Id)
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Header_Id = cn_po_hdr_id ;
SELECT *
FROM PO_HEADERS_ALL
WHERE po_header_id = cn_po_hdr_id;
SELECT *
FROM PO_LINES_ALL
WHERE po_line_id = cn_po_line_id;
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;
po_line_loc_rec.PROGRAM_UPDATE_DATE ,
po_line_loc_rec.USSGL_TRANSACTION_CODE ,
po_line_loc_rec.GOVERNMENT_CONTEXT ,
po_line_loc_rec.RECEIVING_ROUTING_ID ,
po_line_loc_rec.ACCRUE_ON_RECEIPT_FLAG ,
po_line_loc_rec.CLOSED_REASON ,
po_line_loc_rec.CLOSED_DATE ,
po_line_loc_rec.CLOSED_BY ,
po_line_loc_rec.ORG_ID ,
po_line_loc_rec.QUANTITY_SHIPPED ,
po_line_loc_rec.COUNTRY_OF_ORIGIN_CODE ,
po_line_loc_rec.TAX_USER_OVERRIDE_FLAG ,
po_line_loc_rec.MATCH_OPTION ,
po_line_loc_rec.TAX_CODE_ID ,
po_line_loc_rec.CALCULATE_TAX_FLAG ,
po_line_loc_rec.CHANGE_PROMISED_DATE_REASON
);
v_last_upd_dt := po_line_loc_rec.Last_Update_Date ;
v_last_upd_by := po_line_loc_rec.Last_Updated_By;
v_last_upd_login := po_line_loc_rec.Last_Update_Login;
IF pv_action = JAI_CONSTANTS.inserting THEN
IF is_split_line(v_orig_ship_id, v_po_hdr_id, v_po_line_id) = TRUE THEN
-- process for split line;
JAI_RETRO_PRC_PKG.Insert_Price_Changes( pr_old => old_po_line_loce_rec
, pr_new => po_line_loc_rec
, pv_process_flag => lv_process_flag
, pv_process_message => lv_process_message
);
REM SubProgram Name : update_currency
REM Type : table handler API
REM Purpose : Update currency in JAI_PO_LINE_LOCATIONS and JAI_PO_TAXES
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------ ---------- ------------------------------------
REM pn_po_header_id IN NUMBER Y PO header id
REM pv_old_currency IN VARCHAR2 Y old currency before change.
REM pv_currency IN VARCHAR2 Y new currency after change.
REM pd_last_upd_dt IN DATE Y last update date
REM pn_last_upd_by IN NUMBER Y last update by
REM pn_last_upd_login IN NUMBER Y last update login
REM CALLED BY
REM process_po
REM +================================================================================================+
*/
PROCEDURE update_currency(pn_po_header_id NUMBER,pv_old_currency VARCHAR2, pv_currency VARCHAR2,pd_last_upd_dt IN DATE,
pn_last_upd_by IN NUMBER,
pn_last_upd_login IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_currency';
UPDATE JAI_PO_TAXES
SET Currency = pv_currency,
Last_Update_Date = pd_last_upd_dt,
Last_Updated_By = pn_last_upd_by,
Last_Update_Login = pn_last_upd_login
WHERE Po_Header_Id = pn_po_header_id
AND Currency = pv_old_currency;
UPDATE JAI_PO_LINE_LOCATIONS
SET Currency = pv_currency,
Last_Update_Date = pd_last_upd_dt,
Last_Updated_By = pn_last_upd_by,
Last_Update_Login = pn_last_upd_login
WHERE Po_Header_Id = pn_po_header_id;
END update_currency;
REM SubProgram Name : update_vendor
REM Type : table handler API
REM Purpose : Update currency in JAI_PO_LINE_LOCATIONS and JAI_PO_TAXES
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------ ---------- ------------------------------------
REM pn_po_header_id IN NUMBER Y PO header id
REM pn_old_vendor_id IN NUMBER Y old vendor id before change.
REM pn_vendor_id IN NUMBER Y new vendor id after change.
REM pn_vendor_site_id IN NUMBER Y new vendor id after change.
REM pd_last_upd_dt IN DATE Y last update date
REM pn_last_upd_by IN NUMBER Y last update by
REM pn_last_upd_login IN NUMBER Y last update login
REM CALLED BY
REM process_po
REM +================================================================================================+
*/
PROCEDURE update_vendor
(
pn_po_header_id NUMBER
,pn_old_vendor_id IN NUMBER
,pn_vendor_id IN NUMBER
,pn_vendor_site_id IN NUMBER
,pd_last_upd_dt IN DATE
,pn_last_upd_by IN NUMBER
,pn_last_upd_login IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_vendor';
UPDATE jai_po_taxes
SET vendor_id = pn_vendor_id, last_update_date = pd_last_upd_dt,
last_updated_by = pn_last_upd_by, last_update_login = pn_last_upd_login
WHERE po_header_id = pn_po_header_id
AND vendor_id = pn_old_vendor_id;
UPDATE JAI_PO_LINE_LOCATIONS
SET vendor_id = pn_vendor_id,
vendor_site_id = pn_vendor_site_id,
Last_Update_Date = pd_last_upd_dt,
Last_Updated_By = pn_last_upd_by,
Last_Update_Login = pn_last_upd_login
WHERE Po_Header_Id = pn_po_header_id;
END update_vendor;
REM SubProgram Name : delete_jai_po_lines
REM Type : table handler API
REM Purpose : delete JAI_PO_LINE_LOCATIONS
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------ ---------- ------------------------------------
REM pn_line_id IN NUMBER N PO line id
REM pn_line_loc_id IN NUMBER N PO line location id
REM CALLED BY
REM process_po_tax_wrapper
REM +================================================================================================+
*/
PROCEDURE delete_jai_po_lines(pn_line_id IN NUMBER DEFAULT NULL,pn_line_loc_id IN NUMBER DEFAULT NULL)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_jai_po_lines';
DELETE FROM JAI_PO_LINE_LOCATIONS WHERE Line_Location_Id = pn_line_loc_id;
DELETE FROM JAI_PO_LINE_LOCATIONS WHERE po_line_id = pn_line_id;
END delete_jai_po_lines;
REM SubProgram Name : delete_po_taxes
REM Type : table handler API
REM Purpose : delete data from JAI_PO_TAXES
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------ ---------- ------------------------------------
REM pn_po_hdr_id IN NUMBER N PO header id
REM pn_line_id IN NUMBER N PO line id
REM pn_line_loc_id IN NUMBER N PO line location id
REM CALLED BY
REM process_po_tax_wrapper
REM +================================================================================================+
*/
PROCEDURE delete_po_taxes(pn_po_hdr_id IN NUMBER DEFAULT NULL,pn_po_line_id IN NUMBER DEFAULT NULL ,pn_line_loc_id IN NUMBER DEFAULT NULL)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_po_taxes';
DELETE FROM JAI_PO_TAXES
WHERE po_header_id = pn_po_hdr_id;
DELETE FROM JAI_PO_TAXES
WHERE Po_Line_Id = pn_po_line_id
AND NVL( Line_Location_Id, -999 ) = NVL( pn_line_loc_id, -999 );
END delete_po_taxes;
REM SubProgram Name : delete_po_taxes
REM Type : table handler API
REM Purpose : insert data in JAI_PO_LINE_LOCATIONS
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------ ---------- ------------------------------------
REM v_line_loc_id IN NUMBER N PO shipment line id
REM v_po_hdr_id IN NUMBER Y PO header id
REM v_po_line_id IN NUMBER Y PO line id
REM v_vendor_id IN NUMBER N PO vendor id
REM v_vendor_site_id IN NUMBER N PO vendor site id
REM v_item_id IN NUMBER Y Item id
REM v_qty IN NUMBER Y Quantity of item
REM v_price_override IN NUMBER N price of item
REM v_tax_category_id IN NUMBER N tax category id of the line
REM v_tax_amount IN NUMBER N tax amount of the line
REM v_total_amount IN NUMBER N total amount of the line
REM v_currency IN VARCHAR2 N currency of the PO
REM v_tax_modified_flag IN VARCHAR2 N tax modified flag
REM flag IN VARCHAR2 Y flag to indicate insert or udate
REM v_cre_dt IN DATE Y creation date
REM v_cre_by IN NUMBER Y created by
REM v_last_upd_dt IN DATE Y last update date
REM v_last_upd_by IN NUMBER Y last update by
REM v_last_upd_login IN NUMBER Y last update login
REM v_line_focus_id OUT NUMBER Y Return the line focus id after insert.
REM
REM Call by process_po_tax_wrapper
REM +================================================================================================+
*/
PROCEDURE insert_jai_line_locs
(
v_line_loc_id IN NUMBER,
v_po_hdr_id IN NUMBER,
v_po_line_id 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,
v_item_id IN NUMBER ,
v_vendor_id IN NUMBER ,
v_vendor_site_id IN NUMBER,
v_qty IN NUMBER DEFAULT NULL,
v_price_override IN NUMBER DEFAULT NULL,
v_tax_category_id IN NUMBER DEFAULT NULL,
v_tax_amount IN NUMBER DEFAULT NULL,
v_total_amount IN NUMBER DEFAULT NULL,
v_currency IN VARCHAR2,
v_tax_modified_flag IN VARCHAR2 DEFAULT 'N',
v_line_focus_id OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_jai_line_locs';
SELECT JAI_PO_LINE_LOCATIONS_S.NEXTVAL
FROM Dual;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Line_Location_Id = v_line_Loc_id AND
Po_Line_Id = v_po_line_id AND
Po_Header_Id = v_po_hdr_id;
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,currency,
Creation_Date, Created_By, Last_Update_Date, Last_Updated_By,tax_category_id,Last_Update_Login,Service_type_code
,item_id,vendor_id, vendor_site_id ,quantity
,PRICE_OVERRIDE
)
VALUES
( v_seq_val, v_line_loc_id , v_po_line_id, v_po_hdr_id,
v_tax_modified_flag, v_tax_amount, v_total_amount,v_currency,
v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by,v_tax_category_id,
v_last_upd_login,v_service_type_code
, v_item_id,
v_vendor_id,v_vendor_site_id, v_qty,
v_price_override
);
UPDATE JAI_PO_LINE_LOCATIONS
SET
Last_Update_Date = v_last_upd_dt,
Last_Updated_By = v_last_upd_by,
Last_Update_Login = v_last_upd_login
,item_id = v_item_id
,price_override = v_price_override
,vendor_id = v_vendor_id
,vendor_site_id = v_vendor_site_id
,quantity = v_qty
,currency = v_currency
WHERE Line_Location_Id = v_line_Loc_id AND
Po_Line_Id = v_po_line_id AND
Po_Header_Id = v_po_hdr_id;
END insert_jai_line_locs;
REM pv_action IN VARCHAR2 N Indicate action is insert or update
REM pn_conv_rate IN NUMBER N convert rate for currency to functional currency
REM pn_assessable_value OUT NUMBER Y Return the assessable value calculated.
REM pn_vat_assess_value OUT NUMBER Y Return the VAT assessable value calculated.
REM
REM Call by process_po_tax_wrapper
REM +================================================================================================+
*/
PROCEDURE get_assessable_value(
pn_po_hdr_id IN NUMBER,
pn_vendor_id IN NUMBER,
pn_vendor_site_id IN NUMBER,
pn_item_id IN NUMBER,
pn_price IN NUMBER,
pn_qty IN NUMBER,
pv_uom_code IN VARCHAR2,
pv_currency IN VARCHAR2,
pv_action IN VARCHAR2 DEFAULT NULL,
pn_conv_rate IN NUMBER DEFAULT 1,
pn_assessable_value OUT NOCOPY NUMBER,
pn_vat_assess_value OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'get_assessable_value';
IF pv_action = JAI_CONSTANTS.inserting THEN
ln_default_price := 0;
SELECT po_header_id, po_line_id
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = p_line_location_id;
REM Purpose : Update original shipment line in JAI tables and create new data in JAI for split line.
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------ ---------- ------------------------------------
REM pn_orig_ship_id IN NUMBER Y Original Shipment ID.
REM pn_po_hdr_id IN NUMBER Y PO header id
REM pn_po_line_id IN NUMBER Y PO Line id
REM
REM Call by process_po_tax_wrapper
REM +================================================================================================+
*/
PROCEDURE process_split_line
(
pn_orig_ship_id IN NUMBER
,pn_line_loc_id IN NUMBER
,pn_quantity IN NUMBER
,pn_price IN NUMBER
,pv_type_lookup_code IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'process_split_line';
select quantity
from po_line_locations_all
where line_location_id = pn_orig_ship_id;
SELECT *
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = pn_orig_ship_id;
SELECT *
FROM JAI_PO_TAXES
WHERE line_location_id = p_line_location_id;
/*Insert into JAI_PO_LINE_LOCATIONS*/
insert_jai_line_locs(
v_line_loc_id => pn_line_loc_id
,v_po_hdr_id => rec_jai_line.po_header_id
,v_po_line_id => rec_jai_line.po_line_id
,v_cre_dt => rec_jai_line.creation_date
,v_cre_by => rec_jai_line.created_by
,v_last_upd_dt => rec_jai_line.last_update_date
,v_last_upd_by => rec_jai_line.last_updated_by
,v_last_upd_login => rec_jai_line.LAST_UPDATE_LOGIN
,flag => 'I'
,v_item_id => rec_jai_line.item_id
,v_vendor_id => rec_jai_line.vendor_id
,v_vendor_site_id => rec_jai_line.vendor_site_id
,v_tax_category_id => rec_jai_line.tax_category_id
,v_price_override => pn_price
,v_qty => pn_QUANTITY
,v_currency => rec_jai_line.currency
,v_line_focus_id => l_line_focus_id
);
/*Insert Taxes of the parent line in to the split line after adjusting the Tax Amount and Tax Target Amount*/
for r_get_po_taxes in c_get_po_taxes(pn_orig_ship_id) loop
insert_po_taxes
( pv_type_lookup_code, NULL,
l_line_focus_id, pn_line_loc_id,
r_get_po_taxes.tax_line_no, r_get_po_taxes.po_line_id, r_get_po_taxes.po_header_id,
r_get_po_taxes.precedence_1, r_get_po_taxes.precedence_2, r_get_po_taxes.precedence_3,
r_get_po_taxes.precedence_4, r_get_po_taxes.precedence_5,
r_get_po_taxes.precedence_6, r_get_po_taxes.precedence_7, r_get_po_taxes.precedence_8,
r_get_po_taxes.precedence_9, r_get_po_taxes.precedence_10,
r_get_po_taxes.tax_id, NULL, 0, r_get_po_taxes.currency,
r_get_po_taxes.tax_rate, r_get_po_taxes.qty_rate, r_get_po_taxes.uom,
(r_get_po_taxes.tax_amount * pn_QUANTITY/l_prev_quantity), r_get_po_taxes.tax_type, r_get_po_taxes.modvat_flag,
r_get_po_taxes.vendor_id, (r_get_po_taxes.tax_target_amount * pn_QUANTITY/l_prev_quantity),
r_get_po_taxes.creation_date, r_get_po_taxes.created_by, r_get_po_taxes.last_update_date,
r_get_po_taxes.last_updated_by, r_get_po_taxes.last_update_login,
r_get_po_taxes.tax_category_id
);
select sum(tax_amount) into l_tax_amount
from jai_po_taxes
where line_location_id = pn_line_loc_id;
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_modified_flag = 'N',
tax_amount = l_tax_amount,
total_amount = l_ship_line_amount + l_tax_amount
WHERE line_location_id = pn_line_loc_id;
v_last_upd_dt PO_DISTRIBUTIONS_ALL.Last_Update_Date%TYPE ;
v_last_upd_by PO_DISTRIBUTIONS_ALL.Last_Updated_By%TYPE;
v_last_upd_login PO_DISTRIBUTIONS_ALL.Last_Update_Login%TYPE;
SELECT *
FROM PO_DISTRIBUTIONS_ALL
WHERE PO_HEADER_ID = prec_po_location.po_header_id
AND PO_LINE_ID = prec_po_location.po_line_id
AND LINE_LOCATION_ID = prec_po_location.line_location_id;
SELECT type_lookup_code,Quotation_Class_Code,Ship_To_Location_Id,
rate, rate_type, rate_date,currency_code,
nvl(Vendor_id,0), nvl(vendor_Site_Id,0),style_id
FROM po_headers_all
WHERE po_header_id = v_po_hdr_id;
SELECT override_flag
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = c_supplier_id
AND vendor_site_id = c_supp_site_id;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = v_line_location_id;
SELECT count(line_location_id)
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id=v_line_location_id;
v_last_upd_dt := rec_dist.Last_Update_Date ;
v_last_upd_by := rec_dist.Last_Updated_By;
v_last_upd_login := rec_dist.Last_Update_Login;
delete_po_taxes(pn_po_line_id =>v_po_line_id, pn_line_loc_id => v_line_location_id);
delete_jai_po_lines(pn_line_loc_id =>v_line_location_id);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'po_release_id is null, delete jai data before return.');
SELECT REQUISITION_LINE_ID
FROM PO_REQUISITION_LINES_ALL
WHERE REQUISITION_HEADER_ID = pn_requisition_hdr_id;
SELECT 1
FROM JAI_PO_REQ_LINES
WHERE Requisition_Line_Id = cn_req_line_id
AND Requisition_Header_Id = pn_requisition_hdr_id;
lv_action := JAI_CONSTANTS.inserting;
REM pv_action IN VARCHAR2 Y Indicate action is insert/update.
REM
REM CALLED BY
REM process_requisition
REM +================================================================================================+
*/
procedure process_po_req_line
(
pn_requisition_line_id NUMBER,
pr_requisition_line PO_REQUISITION_LINES_ALL%ROWTYPE DEFAULT NULL,--added by Wenqiong for bug16288154 .
pv_action VARCHAR2
) IS
CURSOR get_requesition_info IS
SELECT *
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id = pn_requisition_line_id;
r_requisition_line.LAST_UPDATE_DATE := pr_requisition_line.LAST_UPDATE_DATE ;
r_requisition_line.LAST_UPDATED_BY := pr_requisition_line.LAST_UPDATED_BY ;
r_requisition_line.LAST_UPDATE_LOGIN := pr_requisition_line.LAST_UPDATE_LOGIN ;
r_requisition_line.PROGRAM_UPDATE_DATE := pr_requisition_line.PROGRAM_UPDATE_DATE ;
r_requisition_line.TAX_ATTRIBUTE_UPDATE_CODE := pr_requisition_line.TAX_ATTRIBUTE_UPDATE_CODE ;
If pv_action = JAI_CONSTANTS.inserting then
process_req_line_inserting(r_requisition_line);
REM SubProgram Name : process_req_line_inserting
REM Type : Process API
REM Purpose : Process Requistion Line's inserting
REM
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------------- ---------- ------------------------------------
REM pn_requisition_line_id IN NUMBER Y PO Requisition Line Id.
REM
REM CALLED BY
REM process_po_req_line
REM +================================================================================================+
*/
procedure process_req_line_inserting
(
--pn_requisition_line_id NUMBER
pr_requisition_line PO_REQUISITION_LINES_ALL%ROWTYPE
) IS
v_type_lookup_code VARCHAR2(25);
SELECT *
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id = pn_requisition_line_id;*/
SELECT NVL(Operating_Unit,0)
FROM Org_Organization_Definitions
WHERE Organization_Id = p_Destination_Organization_Id;
SELECT A.Segment1, A.Type_Lookup_Code,apps_source_code
FROM Po_Requisition_Headers_All A
WHERE A.Requisition_Header_Id = p_requisition_Header_Id;
l_api_name CONSTANT VARCHAR2(200) := 'process_inserting';
,message => 'Before insert into JAI_PO_REQ_LINES');
INSERT INTO JAI_PO_REQ_LINES (
requisition_line_id, requisition_header_id, tax_modified_flag,
tax_amount, total_amount,SUGGESTED_VENDOR_NAME,SUGGESTED_VENDOR_LOCATION,currency_code,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
) VALUES (
pr_requisition_line.requisition_line_id, pr_requisition_line.requisition_header_id, 'N',
NULL, NULL,pr_requisition_line.SUGGESTED_VENDOR_NAME,pr_requisition_line.SUGGESTED_VENDOR_LOCATION,pr_requisition_line.currency_code,
pr_requisition_line.creation_date, pr_requisition_line.created_by, pr_requisition_line.last_update_date,
pr_requisition_line.last_updated_by, pr_requisition_line.last_update_login
);
,message => 'After insert into JAI_PO_REQ_LINES');
SELECT SUM(jr.TAX_AMOUNT)
INTO v_exclu_tax_amount
FROM JAI_PO_REQ_LINE_TAXES jr,
JAI_CMN_TAXES_ALL jt
WHERE jr.Requisition_Header_Id = pr_requisition_line.requisition_header_id
AND jr.Requisition_Line_Id = pr_requisition_line.Requisition_Line_Id
AND jr.tax_id = jt.tax_id
AND jr.Tax_Type <> jai_constants.tax_type_tds
AND NVL(jt.INCLUSIVE_TAX_FLAG,'N') <> 'Y';
SELECT SUM(jr.TAX_AMOUNT)
INTO v_tax_amount
FROM JAI_PO_REQ_LINE_TAXES jr
WHERE jr.Requisition_Header_Id = pr_requisition_line.requisition_header_id
AND jr.Requisition_Line_Id = pr_requisition_line.Requisition_Line_Id
AND jr.Tax_Type <> jai_constants.tax_type_tds ;
UPDATE JAI_PO_REQ_LINES
SET tax_amount = v_tax_amount,
total_amount = v_total_amount,
tax_category_id=v_tax_category_id
WHERE requisition_line_id = pr_requisition_line.Requisition_Line_Id;
END process_req_line_inserting;
v_last_update_date po_requisition_lines_all.Last_Update_Date%TYPE;
v_last_updated_by po_requisition_lines_all.Last_Updated_By%TYPE;
v_last_update_login po_requisition_lines_all.Last_Update_Login%TYPE;
SELECT *
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id = pn_requisition_line_id;
SELECT *
FROM JAI_PO_REQ_LINES
WHERE requisition_line_id = pr_requisition_line.requisition_line_id;
SELECT *
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_Header_Id = p_requisition_Header_Id
AND requisition_line_id = pr_requisition_line.requisition_line_id;--pn_requisition_line_id;
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
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));
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_requisition_line.Last_Update_Date;
v_last_updated_by := pr_requisition_line.Last_Updated_By;
v_last_update_login := pr_requisition_line.Last_Update_Login;
DELETE from JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = pr_requisition_line.requisition_line_id ;
DELETE from JAI_PO_REQ_LINES
WHERE requisition_line_id = pr_requisition_line.requisition_line_id;
Delete From JAI_PO_REQ_LINE_TAXES
Where Requisition_Line_Id = v_line_id;
UPDATE JAI_PO_REQ_LINES
SET suggested_vendor_name = pr_requisition_line.suggested_vendor_name,
suggested_vendor_location = pr_requisition_line.suggested_vendor_location,
currency_code = pr_requisition_line.currency_code
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 nvl(Tax_Rate,0) <> 0 and nvl(qty_rate,0) <> 0;
SELECT SUM(jr.TAX_AMOUNT)
INTO v_exclu_tax_amount
FROM JAI_PO_REQ_LINE_TAXES jr,
JAI_CMN_TAXES_ALL jt
WHERE jr.Requisition_Header_Id = pr_requisition_line.requisition_header_id
AND jr.Requisition_Line_Id = pr_requisition_line.Requisition_Line_Id
AND jr.tax_id = jt.tax_id
AND jr.Tax_Type <> jai_constants.tax_type_tds
AND NVL(jt.INCLUSIVE_TAX_FLAG,'N') <> 'Y';
SELECT SUM(jr.TAX_AMOUNT)
INTO v_line_tax_amount
FROM JAI_PO_REQ_LINE_TAXES jr
WHERE jr.Requisition_Header_Id = pr_requisition_line.requisition_header_id
AND jr.Requisition_Line_Id = pr_requisition_line.Requisition_Line_Id
AND jr.Tax_Type <> jai_constants.tax_type_tds ;
UPDATE JAI_PO_REQ_LINES
SET tax_amount = v_line_tax_amount,
total_amount = v_total_amount,
tax_category_id=v_tax_category_id,
currency_code = pr_requisition_line.currency_code
WHERE requisition_line_id = pr_requisition_line.Requisition_Line_Id;
REM process_req_line_inserting , process_req_line_updating.
REM +================================================================================================+
*/
procedure po_req_tax_default
(
pr_requisition_line PO_REQUISITION_LINES_ALL%ROWTYPE,
pn_tax_category_id OUT NOCOPY NUMBER,
pn_line_amount OUT NOCOPY NUMBER
) IS
v_blanket_hdr NUMBER;
SELECT *
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id = pn_requisition_line_id;*/
SELECT NVL(Operating_Unit,0)
FROM Org_Organization_Definitions
WHERE Organization_Id = p_Destination_Organization_Id;
SELECT A.Segment1, A.Type_Lookup_Code,apps_source_code
FROM Po_Requisition_Headers_All A
WHERE A.Requisition_Header_Id = p_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)) ;
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
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 NVL( Currency_Code, '$' )
FROM Po_Requisition_Headers_V
WHERE Requisition_Header_Id = r_req_line.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 set_of_books_id
from hr_operating_units
Where organization_id = cp_org_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 (
pr_requisition_line.Requisition_Line_Id, pr_requisition_line.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,
pr_requisition_line.creation_date, pr_requisition_line.created_by, pr_requisition_line.last_update_date, pr_requisition_line.last_updated_by, pr_requisition_line.last_update_login,
rec.tax_category_id );
pr_requisition_line.creation_date, pr_requisition_line.created_by, pr_requisition_line.last_update_date,
pr_requisition_line.last_updated_by, pr_requisition_line.last_update_login,p_vat_assessable_value => ln_vat_assess_value,
p_modified_by_agent_flag => v_modified_by_agent_flag,
p_parent_req_line_id => v_parent_req_line_id );
pr_requisition_line.creation_date, pr_requisition_line.created_by, pr_requisition_line.last_update_date,
pr_requisition_line.last_updated_by, pr_requisition_line.last_update_login, -1*pr_requisition_line.Source_Organization_Id,p_vat_assessable_value => ln_vat_assess_value,
p_modified_by_agent_flag => v_modified_by_agent_flag,
p_parent_req_line_id => v_parent_req_line_id);
REM process_req_line_inserting , process_req_line_updating.
REM +================================================================================================+
*/
PROCEDURE copy_req_to_po(prec_po_location IN Po_Line_Locations_All%ROWTYPE) IS
v_requisition_line_id NUMBER;
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,
style_id
FROM Po_Headers_All
WHERE Po_Header_Id = hdr_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_Target_Amount,
tax_category_id
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
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';*/
SELECT COUNT(REQUISITION_LINE_ID)
FROM JAI_PO_REQ_LINES
WHERE REQUISITION_LINE_ID = c_req_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;
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_LINE_LOCATIONS
where line_location_id = v_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 = prec_po_location.line_location_id ;
SELECT *
FROM PO_REQUISITION_LINES_ALL
WHERE line_location_id = prec_po_location.line_location_id;
v_last_upd_dt := rec_req_line.Last_Update_Date ;
v_last_upd_by := rec_req_line.Last_Updated_By;
v_last_upd_login := rec_req_line.Last_Update_Login;
delete_jai_po_lines(pn_line_loc_id => v_line_loc_id);
insert_jai_line_locs(
v_line_loc_id => v_line_loc_id
,v_po_hdr_id => v_po_hdr_id
,v_po_line_id => v_po_line_id
,v_cre_dt => v_cre_dt
,v_cre_by => v_cre_by
,v_last_upd_dt => v_last_upd_dt
,v_last_upd_by => v_last_upd_by
,v_last_upd_login => v_last_upd_login
,flag => 'I'
,v_item_id => v_item_id
,v_vendor_id => v_po_vendor_id
,v_vendor_site_id => v_po_vendor_site_id
,v_qty => v_quantity
,v_price_override => v_price_override
,v_currency => v_po_curr
,v_line_focus_id => v_line_focus_id
);
,pv_action => JAI_CONSTANTS.inserting
,pn_conv_rate => v_curr_conv_factor
,pn_assessable_value => v_assessable_value
,pn_vat_assess_value => v_vat_assess_value
);
||Insert the line locations from requisition into po using the procedure below
*/
insert_jai_line_locs(
v_line_loc_id => v_line_loc_id
,v_po_hdr_id => v_po_hdr_id
,v_po_line_id => v_po_line_id
,v_cre_dt => v_cre_dt
,v_cre_by => v_cre_by
,v_last_upd_dt => v_last_upd_dt
,v_last_upd_by => v_last_upd_by
,v_last_upd_login => v_last_upd_login
,flag => 'I'
,v_item_id => v_item_id
,v_vendor_id => v_po_vendor_id
,v_vendor_site_id => v_po_vendor_site_id
,v_qty => v_quantity
,v_price_override => v_price_override
,v_currency => v_po_curr
,v_line_focus_id => v_line_focus_id
);
SELECT COUNT(1) INTO tax_cnt FROM JAI_PO_TAXES WHERE
Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'before insert taxes, tax_cnt is '||tax_cnt);
insert_po_taxes
( 'STANDARD', NULL,
v_line_focus_id, v_line_loc_id,
rec_tax.Tax_Line_no, v_po_line_id, v_po_hdr_id,
rec_tax.precedence_1, rec_tax.precedence_2, rec_tax.precedence_3,
rec_tax.precedence_4, rec_tax.precedence_5,
rec_tax.precedence_6, rec_tax.precedence_7, rec_tax.precedence_8,
rec_tax.precedence_9, rec_tax.precedence_10,
rec_tax.tax_id, NULL, 0, /*rec_tax.currency*/v_po_curr,--Modified by Junjian for bug#14729941 on 10-Oct-2012
rec_tax.tax_rate, rec_tax.qty_rate, rec_tax.uom,
v_tax_amount , rec_tax.tax_type, rec_tax.Modvat_Flag,
rec_tax.vendor_id, rec_tax.Tax_Target_Amount ,
v_cre_dt, v_cre_by, v_last_upd_dt,
v_last_upd_by, v_last_upd_login,
rec_tax.tax_category_id
);
SELECT COUNT(1) INTO tax_cnt FROM JAI_PO_TAXES WHERE
Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'after insert taxes, tax_cnt is '||tax_cnt);
,pv_action => JAI_CONSTANTS.inserting
,pn_conv_rate => v_curr_conv_factor
,pn_assessable_value => v_assessable_value
,pn_vat_assess_value => v_vat_assess_value
);
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
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_Loc_id;
REM process_req_line_inserting , process_req_line_updating.
REM +================================================================================================+
*/
PROCEDURE cancel_po(pn_po_hdr_id IN NUMBER) IS
rec_line_loc PO_LINE_LOCATIONS_ALL%ROWTYPE;
SELECT *
FROM PO_LINE_LOCATIONS_ALL
WHERE PO_HEADER_ID = pn_po_hdr_id;
REM pv_action IN VARCHAR2 Y To indicate insert/udate.
REM
REM CALLED BY
REM JAI_TAX_PROCESSING_PKG.calculate_tax
REM +=====================================================================================================+
*/
PROCEDURE process_release(pn_po_release_id IN NUMBER, pv_action IN VARCHAR2) IS
CURSOR c_release_line_locs IS
SELECT *
FROM PO_LINE_LOCATIONS_ALL
WHERE po_release_id = pn_po_release_id;
SELECT 1
FROM JAI_PO_LINE_LOCATIONS jai
WHERE jai.PO_LINE_ID = cn_line_id
AND jai.line_location_id IS NULL;
REM p_last_upd_dt IN DATE Y last update date
REM p_last_upd_by IN NUMBER Y last update by
REM p_last_upd_login IN NUMBER Y last update login
REM
REM CALLED BY
REM JAI_PO_PROC_PKG.process_po_tax_wrapper
REM +=====================================================================================================+
*/
PROCEDURE copy_agreement_taxes
(
p_src_ship_id IN NUMBER,
p_qty IN NUMBER,
p_hdr_id IN NUMBER,
p_line_id IN NUMBER,
p_line_loc_id IN NUMBER,
p_ship_type IN VARCHAR2,
p_cre_dt IN DATE,
p_cre_by IN NUMBER,
p_last_upd_dt IN DATE,
p_last_upd_by IN NUMBER,
p_last_upd_login IN NUMBER
, pv_retroprice_changed IN VARCHAR2 DEFAULT 'N'
) IS
v_qty NUMBER;
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
FROM JAI_PO_TAXES
WHERE NVL( Line_Location_Id, -999 ) = llid
AND Po_Line_Id = lineid
ORDER BY Tax_Line_No;
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
FROM JAI_PO_TAXES
WHERE line_location_id = p_src_ship_id
AND po_line_id = v_po_line_id;
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
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'
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'
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 Price_Break_Lookup_Code
FROM Po_Lines_All
WHERE Po_Line_Id = line_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 ;
insert_jai_line_locs(
v_line_loc_id => p_line_loc_id
,v_po_hdr_id => p_hdr_id
,v_po_line_id => p_line_id
,v_cre_dt => p_cre_dt
,v_cre_by => p_cre_by
,v_last_upd_dt => p_last_upd_dt
,v_last_upd_by => p_last_upd_by
,v_last_upd_login => p_last_upd_login
,flag => 'I'
,v_item_id => v_item_id
,v_vendor_id => v_vendor_id
,v_vendor_site_id => v_vendor_site_id
,v_qty => v_old_qty
,v_price_override => v_temp_price
,v_tax_modified_flag => 'N'
,v_tax_category_id => v_tax_category_id_holder
,v_tax_amount => 0
,v_total_amount => 0
,v_currency => lv_curr
,v_line_focus_id => v_seq_val
);
,pv_action => JAI_CONSTANTS.inserting
,pn_conv_rate => v_curr_conv_factor
,pn_assessable_value => v_assessable_value
,pn_vat_assess_value => ln_vat_assess_value
);
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_po_taxes
(lv_type_lookup_code, NULL,
v_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, NULL, 0, Tax_Rec.currency,
Tax_Rec.tax_rate, Tax_Rec.qty_rate, Tax_Rec.uom,
Tax_Rec.tax_amount , Tax_Rec.tax_type, Tax_Rec.modvat_flag,
Tax_Rec.vendor_id, Tax_Rec.tax_target_amount ,
p_cre_dt, p_cre_by, p_last_upd_dt, p_last_upd_by, p_last_upd_login,
Tax_Rec.tax_category_id
);
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_upd_dt,
Last_Updated_By = p_last_upd_by,
Last_Update_Login = p_last_upd_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(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_po_taxes
(lv_type_lookup_code, NULL,
v_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, NULL, 0, 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_upd_dt, p_last_upd_by, p_last_upd_login,
Tax_Rec.tax_category_id
);
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_tax_category_id_holder
WHERE line_focus_id = v_seq_val;
REM SubProgram Name : insert_po_taxes
REM Type : Table Handler API
REM Purpose : Insert data into JAI_PO_TAXES.
REM
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------------ ---------- ------------------------------------
REM
REM
REM CALLED BY
REM JAI_PO_PROC_PKG.process_po_taxes copy_agreement_taxes, copy_req_to_po
REM +=====================================================================================================+
*/
PROCEDURE insert_po_taxes
(
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,
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
) IS
v_tax_amt1 NUMBER;
SELECT adhoc_flag
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = cp_tax_id;
l_api_name CONSTANT VARCHAR2(30) := 'insert_po_taxes';
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
) 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,
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
);
END insert_po_taxes;
SELECT * FROM
PO_HEADERS_ALL
WHERE po_header_id = c_po_hdr_id;
SELECT * FROM
PO_LINES_ALL
WHERE po_line_id = c_po_line_id;
SELECT *
FROM PO_HEADERS_ALL
WHERE po_header_id = cn_po_hdr_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_uom;
SELECT Line_location_id, Price_Override, Quantity
FROM Po_Line_Locations_All
WHERE Po_Line_Id = v_po_line_id;
SELECT DISTINCT item_id
FROM JAI_PO_LINE_LOCATIONS
WHERE po_line_id = v_po_line_id;
SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE po_line_id = v_po_line_id
AND shipment_type NOT IN ( 'SCHEDULED', 'BLANKET' );
r_po_line.PROGRAM_UPDATE_DATE ,
r_po_line.CLOSED_DATE ,
r_po_line.CLOSED_REASON ,
r_po_line.CLOSED_BY ,
r_po_line.TRANSACTION_REASON_CODE ,
r_po_line.ORG_ID ,
r_po_line.QC_GRADE ,
r_po_line.BASE_UOM ,
r_po_line.BASE_QTY ,
r_po_line.SECONDARY_UOM ,
r_po_line.SECONDARY_QTY ,
r_po_line.LINE_REFERENCE_NUM ,
r_po_line.PROJECT_ID ,
r_po_line.TASK_ID ,
r_po_line.EXPIRATION_DATE ,
r_po_line.TAX_CODE_ID
);
IF pv_action = JAI_CONSTANTS.inserting THEN
IF v_type_lookup_code = 'BLANKET' OR v_quot_class_code = 'CATALOG' THEN
IF v_type_lookup_code = 'BLANKET' AND v_frm_hdr_id IS NOT NULL THEN
v_src_loc_id := get_line_loc_id(v_frm_hdr_id ,r_po_line.line_num ,r_po_line.item_id , NULL);
,p_last_upd_dt => r_po_line.Last_Update_Date
,p_last_upd_by => r_po_line.Last_Updated_By
,p_last_upd_login => r_po_line.Last_Update_Login
);
/*jai_po_proc_pkg.delete_jai_po_lines(pn_line_id=>v_po_line_id);
jai_po_proc_pkg.delete_po_taxes(pn_po_line_id=>v_po_line_id);*/
SELECT *
FROM PO_HEADERS_ALL
WHERE po_header_id = cn_po_hdr_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = v_uom_measure;
SELECT *
FROM PO_LINES_ALL
WHERE po_line_id = cn_po_line_id;
SELECT COUNT(Line_Location_Id)
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Header_Id = cn_po_hdr_id ;
po_line_loc_rec.PROGRAM_UPDATE_DATE ,
po_line_loc_rec.USSGL_TRANSACTION_CODE ,
po_line_loc_rec.GOVERNMENT_CONTEXT ,
po_line_loc_rec.RECEIVING_ROUTING_ID ,
po_line_loc_rec.ACCRUE_ON_RECEIPT_FLAG ,
po_line_loc_rec.CLOSED_REASON ,
po_line_loc_rec.CLOSED_DATE ,
po_line_loc_rec.CLOSED_BY ,
po_line_loc_rec.ORG_ID ,
po_line_loc_rec.QUANTITY_SHIPPED ,
po_line_loc_rec.COUNTRY_OF_ORIGIN_CODE ,
po_line_loc_rec.TAX_USER_OVERRIDE_FLAG ,
po_line_loc_rec.MATCH_OPTION ,
po_line_loc_rec.TAX_CODE_ID ,
po_line_loc_rec.CALCULATE_TAX_FLAG ,
po_line_loc_rec.CHANGE_PROMISED_DATE_REASON
);
v_last_upd_dt := po_line_loc_rec.Last_Update_Date ;
v_last_upd_by := po_line_loc_rec.Last_Updated_By;
v_last_upd_login := po_line_loc_rec.Last_Update_Login;
IF pv_action = JAI_CONSTANTS.inserting THEN
IF is_split_line(v_orig_ship_id, v_po_hdr_id, v_po_line_id) = TRUE THEN
-- process for split line;
SELECT DISTINCT Po_Line_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Header_Id = prec_po_header.po_header_id;
SELECT Line_Location_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = lineid;
SELECT * FROM
PO_LINE_LOCATIONS_ALL
WHERE Line_Location_Id = cn_line_loc_id;
SELECT * FROM PO_LINES_ALL
WHERE PO_LINE_ID = cn_po_line_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = c_uom;
SELECT override_flag
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = c_supplier_id
AND vendor_site_id = c_supp_site_id;
v_last_upd_dt prec_po_header.Last_Update_Date%TYPE:= prec_po_header.Last_Update_Date ;
v_last_upd_by prec_po_header.Last_Updated_By%TYPE:= prec_po_header.Last_Updated_By;
v_last_upd_login prec_po_header.Last_Update_Login%TYPE:= prec_po_header.Last_Update_Login;
/*For vendor merge case, need update vendor in receipts,
todo, seemed to be removed since vendor merge case will not fire through ZX.
*/
IF ln_vendor_id <> nvl(pn_old_vendor_id,-999) then
for c_receipt_rec in
(
select distinct shipment_header_id, shipment_line_id
from rcv_transactions
where po_header_id = ln_po_header_id
)
loop
update JAI_RCV_LINE_TAXES
set vendor_id = ln_vendor_id
where shipment_header_id = c_receipt_rec.shipment_header_id
and shipment_line_id = c_receipt_rec.shipment_line_id
and vendor_id = pn_old_vendor_id;
delete_po_taxes(pn_po_hdr_id => ln_po_header_id);
jai_po_proc_pkg.update_vendor(
pn_po_header_id => ln_po_header_id
,pn_old_vendor_id => pn_old_vendor_id
,pn_vendor_id => ln_vendor_id
,pn_vendor_site_id => v_vendor_site_id
,pd_last_upd_dt => v_last_upd_dt
,pn_last_upd_by => v_last_upd_by
,pn_last_upd_login => v_last_upd_login
);
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
AND pha.po_header_id =pla.po_header_id;
PROCEDURE delete_po_req_line_taxes
(
pn_requisition_line_id IN NUMBER
) IS
BEGIN
DELETE from JAI_PO_REQ_LINE_TAXES --ja_in_reqn_tax_lines
WHERE requisition_line_id = pn_requisition_line_id ;
DELETE from JAI_PO_REQ_LINES --ja_in_reqn_lines
WHERE requisition_line_id = pn_requisition_line_id;
END delete_po_req_line_taxes;
SELECT Quantity, Line_Location_Id
FROM Po_Line_Locations_All
WHERE Po_Header_Id = p_header_id
AND Po_Line_Id = v_po_line_id
and SYSDATE between nvl(start_date, SYSDATE) and nvl(end_date, SYSDATE) -- cbabu for Bug# 2740918
ORDER BY Quantity;
SELECT Po_Line_Id, NVL( Price_Break_Lookup_Code, 'NC' ) Price_Break_Lookup_Code
FROM Po_Lines_All
WHERE Po_Header_Id = p_header_id
AND Line_Num = p_line_num;
SELECT NVL( Price_Break_Lookup_Code, 'NC' ) Price_Break_Lookup_Code
FROM Po_Lines_All
WHERE Po_Line_Id = p_line_id;
SELECT SUM( Quantity )
FROM Po_Line_Locations_All
WHERE Po_Line_Id = v_po_line_id;
SELECT COUNT( * )
FROM Po_Line_Locations_All
WHERE Po_Line_Id = v_po_line_id;
SELECT
nvl(a.tax_amount,0) tax_amount,
nvl(b.adhoc_flag, 'N') adhoc_flag
FROM
JAI_PO_TAXES a ,
JAI_CMN_TAXES_ALL b
WHERE
a.tax_id = b.tax_id AND
a.po_line_id = v_po_line_id AND
a.po_header_id = v_po_hdr_id AND
a.line_location_id = v_line_loc_id FOR UPDATE OF a.tax_amount;
SELECT sum(tax_amount )
FROM JAI_PO_TAXES
WHERE
po_line_id = v_po_line_id AND
po_header_id = v_po_hdr_id AND
line_location_id = v_line_loc_id AND
tax_type <> jai_constants.tax_type_tds ;
then delete the line and associated taxes from JAI_PO_LINE_LOCATIONS
and JAI_PO_TAXES and return .
*/
IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||': entire line been cancelled.');
delete_po_taxes(pn_po_line_id=>v_po_line_id,pn_line_loc_id => v_line_loc_id);
delete_jai_po_lines(pn_line_loc_id => v_line_loc_id);
Now in this scenario update the line and the associated apportioned taxes in the table JAI_PO_LINE_LOCATIONS
and JAI_PO_TAXES
*/
/*
Update the JAI_PO_TAXES with the apportioned tax_amount
The tax amounts are apportioned in a ratio of the (quantity_received\quantity)
Only the taxes which have a adhoc flag set to 'N' can be apportioned.
Taxes which have adhoc flag set to 'Y' would remain unaffected .
*/
FOR cur_rec_get_tax_amount IN rec_get_tax_amount
LOOP
UPDATE
JAI_PO_TAXES
SET
tax_amount = (nvl(v_quantity_received,0) / nvl(v_qty,1)) * nvl(cur_rec_get_tax_amount.tax_amount,0)
WHERE
CURRENT OF rec_get_tax_amount;
The record in ja_in_po_line_location has to be updated with the total of all taxes from JAI_PO_TAXES
excluding the TDS type of taxes. The total_amount should be calculated as (quantity_received * price_override) + total of tax amount
*/
OPEN rec_calc_total_tax ;
UPDATE
JAI_PO_LINE_LOCATIONS
SET
tax_amount = l_total_tax_amount ,
total_amount = nvl(v_quantity_received * v_price, 0) + nvl(l_total_tax_amount,0)
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 type_lookup_code,Quotation_Class_Code,Ship_To_Location_Id,
rate, rate_type, rate_date,currency_code,
nvl(Vendor_id,0), nvl(vendor_Site_Id,0),style_id
FROM po_headers_all
WHERE po_header_id = c_po_header_id;
SELECT override_flag
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = c_supplier_id
AND vendor_site_id = c_supp_site_id;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = c_line_location_id;
SELECT count(line_location_id)
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id=c_line_location_id;
v_last_upd_dt PO_DISTRIBUTIONS_ALL.Last_Update_Date%TYPE ;
v_last_upd_by PO_DISTRIBUTIONS_ALL.Last_Updated_By%TYPE;
v_last_upd_login PO_DISTRIBUTIONS_ALL.Last_Update_Login%TYPE;
v_last_upd_dt := pr_dist.Last_Update_Date ;
v_last_upd_by := pr_dist.Last_Updated_By;
v_last_upd_login := pr_dist.Last_Update_Login;
delete_po_taxes(pn_po_line_id =>v_po_line_id, pn_line_loc_id => v_line_location_id);
delete_jai_po_lines(pn_line_loc_id =>v_line_location_id);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'po_release_id is null, delete jai data before return.');
SELECT *
FROM PO_HEADERS_ALL
WHERE po_header_id = cn_po_hdr_id;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = v_uom_measure;
SELECT *
FROM PO_LINES_ALL
WHERE po_line_id = cn_po_line_id;
SELECT COUNT(Line_Location_Id)
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Header_Id = cn_po_hdr_id ;
SELECT *
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = p_line_location_id;
po_line_loc_rec.PROGRAM_UPDATE_DATE ,
po_line_loc_rec.USSGL_TRANSACTION_CODE ,
po_line_loc_rec.GOVERNMENT_CONTEXT ,
po_line_loc_rec.RECEIVING_ROUTING_ID ,
po_line_loc_rec.ACCRUE_ON_RECEIPT_FLAG ,
po_line_loc_rec.CLOSED_REASON ,
po_line_loc_rec.CLOSED_DATE ,
po_line_loc_rec.CLOSED_BY ,
po_line_loc_rec.ORG_ID ,
po_line_loc_rec.QUANTITY_SHIPPED ,
po_line_loc_rec.COUNTRY_OF_ORIGIN_CODE ,
po_line_loc_rec.TAX_USER_OVERRIDE_FLAG ,
po_line_loc_rec.MATCH_OPTION ,
po_line_loc_rec.TAX_CODE_ID ,
po_line_loc_rec.CALCULATE_TAX_FLAG ,
po_line_loc_rec.CHANGE_PROMISED_DATE_REASON
);
v_last_upd_dt := po_line_loc_rec.Last_Update_Date ;
v_last_upd_by := po_line_loc_rec.Last_Updated_By;
v_last_upd_login := po_line_loc_rec.Last_Update_Login;
IF pv_action = JAI_CONSTANTS.inserting THEN
IF is_split_line(v_orig_ship_id, v_po_hdr_id, v_po_line_id) = TRUE THEN
-- process for split line;
JAI_RETRO_PRC_PKG.Insert_Price_Changes( pr_old => old_line_loc_rec
, pr_new => po_line_loc_rec
, pv_process_flag => lv_process_flag
, pv_process_message => lv_process_message
);
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,
style_id
FROM Po_Headers_All
WHERE Po_Header_Id = hdr_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_Target_Amount,
tax_category_id
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
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';*/
SELECT COUNT(REQUISITION_LINE_ID)
FROM JAI_PO_REQ_LINES
WHERE REQUISITION_LINE_ID = c_req_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;
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_LINE_LOCATIONS
where line_location_id = v_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 = prec_po_req_line.REQUISITION_HEADER_ID;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = prec_po_req_line.line_location_id ;
SELECT * FROM
Po_Line_Locations_All
WHERE LINE_LOCATION_ID = prec_po_req_line.Line_Location_Id;
v_last_upd_dt := prec_po_req_line.Last_Update_Date ;
v_last_upd_by := prec_po_req_line.Last_Updated_By;
v_last_upd_login := prec_po_req_line.Last_Update_Login;
delete_jai_po_lines(pn_line_loc_id => v_line_loc_id);
insert_jai_line_locs(
v_line_loc_id => v_line_loc_id
,v_po_hdr_id => v_po_hdr_id
,v_po_line_id => v_po_line_id
,v_cre_dt => v_cre_dt
,v_cre_by => v_cre_by
,v_last_upd_dt => v_last_upd_dt
,v_last_upd_by => v_last_upd_by
,v_last_upd_login => v_last_upd_login
,flag => 'I'
,v_item_id => v_item_id
,v_vendor_id => v_po_vendor_id
,v_vendor_site_id => v_po_vendor_site_id
,v_qty => v_quantity
,v_price_override => v_price_override
,v_currency => v_po_curr
,v_line_focus_id => v_line_focus_id
);
,pv_action => JAI_CONSTANTS.inserting
,pn_conv_rate => v_curr_conv_factor
,pn_assessable_value => v_assessable_value
,pn_vat_assess_value => v_vat_assess_value
);
||Insert the line locations from requisition into po using the procedure below
*/
insert_jai_line_locs(
v_line_loc_id => v_line_loc_id
,v_po_hdr_id => v_po_hdr_id
,v_po_line_id => v_po_line_id
,v_cre_dt => v_cre_dt
,v_cre_by => v_cre_by
,v_last_upd_dt => v_last_upd_dt
,v_last_upd_by => v_last_upd_by
,v_last_upd_login => v_last_upd_login
,flag => 'I'
,v_item_id => v_item_id
,v_vendor_id => v_po_vendor_id
,v_vendor_site_id => v_po_vendor_site_id
,v_qty => v_quantity
,v_price_override => v_price_override
,v_currency => v_po_curr
,v_line_focus_id => v_line_focus_id
);
SELECT COUNT(1) INTO tax_cnt FROM JAI_PO_TAXES WHERE
Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'before insert taxes, tax_cnt is '||tax_cnt);
insert_po_taxes
( 'STANDARD', NULL,
v_line_focus_id, v_line_loc_id,
rec_tax.Tax_Line_no, v_po_line_id, v_po_hdr_id,
rec_tax.precedence_1, rec_tax.precedence_2, rec_tax.precedence_3,
rec_tax.precedence_4, rec_tax.precedence_5,
rec_tax.precedence_6, rec_tax.precedence_7, rec_tax.precedence_8,
rec_tax.precedence_9, rec_tax.precedence_10,
rec_tax.tax_id, NULL, 0, /*rec_tax.currency*/v_po_curr,--Modified by Junjian for bug#14729941 on 10-Oct-2012
rec_tax.tax_rate, rec_tax.qty_rate, rec_tax.uom,
v_tax_amount , rec_tax.tax_type, rec_tax.Modvat_Flag,
rec_tax.vendor_id, rec_tax.Tax_Target_Amount ,
v_cre_dt, v_cre_by, v_last_upd_dt,
v_last_upd_by, v_last_upd_login,
rec_tax.tax_category_id
);
SELECT COUNT(1) INTO tax_cnt FROM JAI_PO_TAXES WHERE
Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'after insert taxes, tax_cnt is '||tax_cnt);
,pv_action => JAI_CONSTANTS.inserting
,pn_conv_rate => v_curr_conv_factor
,pn_assessable_value => v_assessable_value
,pn_vat_assess_value => v_vat_assess_value
);
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
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_Loc_id;