[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: update_line()
Preetam B.(OPM-GML) 21-feb-2000 Bug# 1056597 added 5 columns to update line.
Preetam B.(OPM-GML) 21-nov-2003 Bug# 3274039 derive sec qty for shipment.
===========================================================================*/
PROCEDURE update_line(X_Rowid VARCHAR2,
X_Po_Line_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Po_Header_Id NUMBER,
X_Line_Type_Id NUMBER,
X_Line_Num NUMBER,
X_Last_Update_Login NUMBER,
X_Item_Id NUMBER,
X_Item_Revision VARCHAR2,
X_Category_Id NUMBER,
X_Item_Description VARCHAR2,
X_Unit_Meas_Lookup_Code VARCHAR2,
X_Quantity_Committed NUMBER,
X_Committed_Amount NUMBER,
X_Allow_Price_Override_Flag VARCHAR2,
X_Not_To_Exceed_Price NUMBER,
X_List_Price_Per_Unit NUMBER,
X_Unit_Price NUMBER,
X_Quantity NUMBER,
X_Un_Number_Id NUMBER,
X_Hazard_Class_Id NUMBER,
X_Note_To_Vendor VARCHAR2,
X_From_Header_Id NUMBER,
X_From_Line_Id NUMBER,
X_From_Line_Location_Id NUMBER, --
X_Min_Order_Quantity NUMBER,
X_Max_Order_Quantity NUMBER,
X_Qty_Rcv_Tolerance NUMBER,
X_Over_Tolerance_Error_Flag VARCHAR2,
X_Market_Price NUMBER,
X_Unordered_Flag VARCHAR2,
X_Closed_Flag VARCHAR2,
X_User_Hold_Flag VARCHAR2,
X_Cancel_Flag VARCHAR2,
X_Cancelled_By NUMBER,
X_Cancel_Date DATE,
X_Cancel_Reason VARCHAR2,
X_Firm_Status_Lookup_Code VARCHAR2,
X_Firm_Date DATE,
X_Vendor_Product_Num VARCHAR2,
X_Contract_Num VARCHAR2,
X_Taxable_Flag VARCHAR2,
X_Tax_Code_Id NUMBER,
X_Type_1099 VARCHAR2,
X_Capital_Expense_Flag VARCHAR2,
X_Negotiated_By_Preparer_Flag VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Reference_Num VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Min_Release_Amount NUMBER,
X_Price_Type_Lookup_Code VARCHAR2,
X_Closed_Code VARCHAR2,
X_Price_Break_Lookup_Code VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Government_Context VARCHAR2,
X_Closed_Date DATE,
X_Closed_Reason VARCHAR2,
X_Closed_By NUMBER,
X_Transaction_Reason_Code VARCHAR2,
X_unapprove_doc IN OUT NOCOPY BOOLEAN,
X_authorization_status IN OUT NOCOPY VARCHAR2,
X_approved_flag IN OUT NOCOPY VARCHAR2,
--< NBD TZ/Timestamp FPJ Start >
--X_combined_param IN VARCHAR2,
-- The following 5 parameters were being combined
-- into one due to the historic reasons. That is not
-- required now.
p_ship_window_open IN VARCHAR2,
p_type_lookup_code IN VARCHAR2,
p_change_date IN VARCHAR2,
p_promised_date IN DATE,
p_need_by_date IN DATE,
--< NBD TZ/Timestamp FPJ End >
p_shipment_block_status IN VARCHAR2, -- bug 4042434
X_orig_unit_price IN NUMBER,
X_orig_quantity IN NUMBER,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2,
X_Expiration_Date DATE,
--Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
--Bug# 1056597
X_Base_Uom VARCHAR2,
X_Base_Qty NUMBER,
X_Secondary_Uom VARCHAR2,
X_Secondary_Qty NUMBER,
X_Qc_Grade VARCHAR2,
--togeorge 10/03/2000
--added oke columns
X_oke_contract_header_id NUMBER default null,
X_oke_contract_version_id NUMBER default null,
-- 1548597.. added 3 fields for process item..
X_Secondary_Unit_of_measure VARCHAR2 default null,
X_Secondary_Quantity NUMBER default null,
X_preferred_Grade VARCHAR2 default null,
p_contract_id IN NUMBER DEFAULT NULL, --
X_job_id NUMBER default null, --
X_contractor_first_name VARCHAR2 default null, --
X_contractor_last_name VARCHAR2 default null, --
X_assignment_start_date DATE default null, --
X_amount_db NUMBER default null, --
--
X_Base_Unit_Price NUMBER DEFAULT NULL,
--
p_manual_price_change_flag VARCHAR2 DEFAULT NULL, --
p_planned_item_flag VARCHAR2 DEFAULT NULL --bug 5533267
) IS
X_progress VARCHAR2(3) := NULL;
SELECT line_location_id,
quantity,
ship_to_organization_id
FROM po_line_locations
WHERE po_line_id = X_po_line_id
AND nvl(cancel_flag,'N') = 'N'
AND unit_meas_lookup_code <> X_unit_meas_lookup_code
AND shipment_type in ('STANDARD','PLANNED')
AND secondary_unit_of_measure is NOT NULL;
select order_type_lookup_code,purchase_basis,amount,category_id, ip_category_id
into l_line_type,l_purchase_basis ,l_orig_amount,l_orig_category_id,l_ip_category_id
from po_lines_all
where po_line_id = X_po_line_id;
/* If the Unit Price on the line has changed, update every shipment of SHIPMENT/PLANNED
** shipment type and that is not cancelled, with this price.
** DEBUG : Move this to POXPOSHB.pls
*/
if X_orig_unit_price <> X_unit_price then
X_progress := '030';
canceled shipments can also be updated. Pl. refer the bug for further
info */
UPDATE po_line_locations
SET price_override = X_unit_price,
calculate_tax_flag = 'Y',
approved_flag = decode(approved_flag, NULL, 'N', 'N','N','R'),
last_update_date = sysdate,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login
WHERE po_line_id = X_po_line_id
AND shipment_type in ('STANDARD','PLANNED') ;
SELECT oi.item_id , oi.item_um2, oi.dualum_ind
INTO l_opm_item_id, l_item_um2, l_dualum_ind
FROM ic_item_mst oi,
mtl_system_items ai
WHERE ai.organization_id = x_ship_org_id
AND ai.inventory_item_id = x_item_id
AND ai.segment1 = oi.item_no;
/*Bug4906693 who columns like last_updated_by and last_update_login
also needs to be updated.*/
UPDATE po_line_locations
SET secondary_quantity = l_shipment_sec_quantity,
last_update_date = X_last_update_date,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login
WHERE po_line_id = X_po_line_id
AND line_location_id = l_line_location_id;
/*Bug4906693 who columns like last_updated_by and last_update_login
also needs to be updated.*/
UPDATE po_line_locations
SET unit_meas_lookup_code = X_unit_meas_lookup_code,
last_update_date = sysdate,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login
WHERE po_line_id = X_po_line_id
AND nvl(cancel_flag,'N') = 'N'
AND unit_meas_lookup_code <> X_unit_meas_lookup_code
AND shipment_type in ('STANDARD','PLANNED') ;
** update the shipment. SImilarly, if there is only one distribution, we need to
** update that too.
** DEBUG Move this to the appropriate packages ( POXPOSHB for shipments ..)
*/
-- Bug 3262883
---Bug 13067295, Moved the below query to the begining of this procedure.
/* select order_type_lookup_code,purchase_basis,amount
into l_line_type,l_purchase_basis ,l_orig_amount
from po_lines_all
where po_line_id = X_po_line_id;*/
SELECT count(pll.po_line_id)
INTO X_num_of_shipments
FROM po_line_locations pll
WHERE pll.po_line_id = X_po_line_id
AND NOT EXISTS (SELECT 'there are encumbered or cancelled or drop shipments'
FROM po_line_locations pll2
WHERE pll2.po_line_id = X_po_line_id
AND pll2.shipment_type IN ('STANDARD','PLANNED')
AND ( nvl(pll2.encumbered_flag, 'N') <> 'N'
OR nvl(pll2.cancel_flag,'N') <> 'N'
OR nvl(pll2.closed_code,'OPEN') = 'FINALLY CLOSED' --bug 5856760
OR nvl(pll2.drop_ship_flag,'N') <> 'N') --bug 3359011
);
SELECT secondary_quantity,
ship_to_organization_id
INTO X_secondary_quantity_ship,
X_ship_org_id
FROM po_line_locations pll
WHERE pll.po_line_id = X_po_line_id
AND pll.shipment_type IN ('STANDARD','PLANNED')
AND nvl(pll.cancel_flag,'N') <> 'Y';
SELECT oi.item_id , oi.item_um2, oi.dualum_ind
INTO l_opm_item_id, l_item_um2, l_dualum_ind
FROM ic_item_mst oi,
mtl_system_items ai
WHERE ai.organization_id = X_ship_org_id
AND ai.inventory_item_id = X_item_id
AND ai.segment1 = oi.item_no;
/* Bug - 1101939 - Need to update the calculate_tax_flag to 'Y' so
that the tax is recalculated when the shipment quantity is changed
automatically */
IF l_line_type in ('RATE','FIXED PRICE') THEN -- Bug 3262883
-- bug 4042434: Add check for p_shipment_block_status. In
-- prior versions, this update would erroneously occur if
-- the line amount was changed before the contents of
-- the shipment block were analyzed when saving a PO line.
IF ((p_ship_window_open = 'N') and (p_shipment_block_status <> 'C')) or
((p_ship_window_open = 'Y') and (l_purchase_basis = 'TEMP LABOR')) THEN
/*Bug4906693 who columns like last_updated_by and last_update_login
also needs to be updated.*/
UPDATE po_line_locations
SET amount = X_amount_db,
calculate_tax_flag = 'Y',
last_update_date = sysdate,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login,
approved_flag = decode(approved_flag, NULL, 'N', 'N','N', 'R'),
-- Bug 5227695. Recalculate tax if tax attributes on
-- shipment are being updated
tax_attribute_update_code = nvl(tax_attribute_update_code, 'UPDATE')
WHERE po_line_id = X_po_line_id
AND nvl(cancel_flag,'N') <> 'Y'
AND shipment_type = 'STANDARD';
/*Bug4906693 who columns like last_updated_by and last_update_login
also needs to be updated.*/
UPDATE po_line_locations
SET quantity = X_quantity,
-- start of 1548597 --PB Bug# 3274039 changed the variable to X_secondary_quantity_ship_new
secondary_quantity = decode(secondary_quantity,null,null,X_secondary_quantity_ship_new),
-- end of 1548597
calculate_tax_flag = 'Y',
last_update_date = sysdate,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login,
approved_flag = decode(approved_flag, NULL, 'N', 'N','N', 'R'),
-- Bug 5227695. Recalculate tax if tax attributes on
-- shipment are being updated
tax_attribute_update_code = nvl(tax_attribute_update_code, 'UPDATE')
WHERE po_line_id = X_po_line_id
AND nvl(cancel_flag,'N') <> 'Y'
AND shipment_type IN ('STANDARD','PLANNED');
SELECT count(po_distribution_id)
INTO X_num_of_distributions
FROM po_distributions pd
WHERE pd.po_line_id = X_po_line_id
AND NOT EXISTS (SELECT 'there are encumbered distributions'
FROM po_distributions pd2
WHERE pd2.po_line_id = X_po_line_id
AND nvl(pd2.encumbered_flag, 'N') <> 'N');
/*Bug4906693 who columns like last_updated_by and last_update_login
also needs to be updated.*/
UPDATE po_distributions
SET amount_ordered = X_amount_db,
last_update_date = sysdate,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login
WHERE po_line_id = X_po_line_id;
/*Bug4906693 who columns like last_updated_by and last_update_login
also needs to be updated.*/
UPDATE po_distributions
SET quantity_ordered = X_quantity,
last_update_date = sysdate,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login
WHERE po_line_id = X_po_line_id;
SELECT count(pll.po_line_id), max(days_late_receipt_allowed)
INTO X_num_of_shipments,
X_days_late_receipt_allowed
FROM po_line_locations pll
WHERE pll.po_line_id = X_po_line_id
AND nvl(pll.cancel_flag,'N') <> 'Y'
AND pll.shipment_type IN ('STANDARD','PLANNED');
/*Bug4906693 who columns like last_updated_by and last_update_login
also needs to be updated.*/
UPDATE po_line_locations
SET promised_date = p_promised_date,
need_by_date = p_need_by_date,
last_accept_date = decode(p_promised_date,NULL,NULL,
p_promised_date+nvl(X_days_late_receipt_allowed,0)),
last_update_date = sysdate,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login,
approved_flag = decode(approved_flag, NULL, 'N', 'N','N', 'R'),
-- Bug 5227695. Recalculate tax if tax attributes on
-- shipment are being updated
tax_attribute_update_code = nvl(tax_attribute_update_code, 'UPDATE')
WHERE po_line_id = X_po_line_id
AND nvl(cancel_flag,'N') <> 'Y'
AND shipment_type IN ('STANDARD','PLANNED');
/* Update the PO LINE itself */
po_lines_pkg_sud.update_row(
X_Rowid ,
X_Po_Line_Id ,
X_Last_Update_Date ,
X_Last_Updated_By ,
X_Po_Header_Id ,
X_Line_Type_Id ,
X_Line_Num ,
X_Last_Update_Login ,
X_Item_Id ,
X_Item_Revision ,
X_Category_Id ,
X_Item_Description ,
X_Unit_Meas_Lookup_Code ,
X_Quantity_Committed ,
X_Committed_Amount ,
X_Allow_Price_Override_Flag ,
X_Not_To_Exceed_Price ,
X_List_Price_Per_Unit ,
--
-- Bug 3417479
X_Base_Unit_Price,
--
X_Unit_Price ,
X_Quantity ,
X_Un_Number_Id ,
X_Hazard_Class_Id ,
X_Note_To_Vendor ,
X_From_Header_Id ,
X_From_Line_Id ,
X_From_Line_Location_Id , --
X_Min_Order_Quantity ,
X_Max_Order_Quantity ,
X_Qty_Rcv_Tolerance ,
X_Over_Tolerance_Error_Flag ,
X_Market_Price ,
X_Unordered_Flag ,
X_Closed_Flag ,
X_User_Hold_Flag ,
X_Cancel_Flag ,
X_Cancelled_By ,
X_Cancel_Date ,
X_Cancel_Reason ,
X_Firm_Status_Lookup_Code ,
X_Firm_Date ,
X_Vendor_Product_Num ,
X_Contract_Num ,
X_Taxable_Flag ,
X_Tax_Code_Id ,
X_Type_1099 ,
X_Capital_Expense_Flag ,
X_Negotiated_By_Preparer_Flag ,
X_Attribute_Category ,
X_Attribute1 ,
X_Attribute2 ,
X_Attribute3 ,
X_Attribute4 ,
X_Attribute5 ,
X_Attribute6 ,
X_Attribute7 ,
X_Attribute8 ,
X_Attribute9 ,
X_Attribute10 ,
X_Reference_Num ,
X_Attribute11 ,
X_Attribute12 ,
X_Attribute13 ,
X_Attribute14 ,
X_Attribute15 ,
X_Min_Release_Amount ,
X_Price_Type_Lookup_Code ,
X_Closed_Code ,
X_Price_Break_Lookup_Code ,
NULL , --
X_Government_Context ,
X_Closed_Date ,
X_Closed_Reason ,
X_Closed_By ,
X_Transaction_Reason_Code ,
X_Global_Attribute_Category ,
X_Global_Attribute1 ,
X_Global_Attribute2 ,
X_Global_Attribute3 ,
X_Global_Attribute4 ,
X_Global_Attribute5 ,
X_Global_Attribute6 ,
X_Global_Attribute7 ,
X_Global_Attribute8 ,
X_Global_Attribute9 ,
X_Global_Attribute10 ,
X_Global_Attribute11 ,
X_Global_Attribute12 ,
X_Global_Attribute13 ,
X_Global_Attribute14 ,
X_Global_Attribute15 ,
X_Global_Attribute16 ,
X_Global_Attribute17 ,
X_Global_Attribute18 ,
X_Global_Attribute19 ,
X_Global_Attribute20 ,
X_Expiration_Date,
--Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
--Bug# 1056597
X_Base_Uom ,
X_Base_Qty ,
X_Secondary_Uom ,
X_Secondary_Qty ,
X_Qc_Grade ,
--togeorge 10/03/2000
--added oke columns
X_oke_contract_header_id ,
X_oke_contract_version_id,
-- start of 1548597.add 3 process fields..
X_secondary_unit_of_measure,
X_secondary_quantity,
X_preferred_grade,
-- end of 1548597
p_contract_id, --
X_job_id, --
X_contractor_first_name, --
X_contractor_last_name, --
X_assignment_start_date, --
X_amount_db, --
p_manual_price_change_flag, --
l_ip_category_id -- Bug 7577670
);
PO_ATTRIBUTE_VALUES_PVT.update_attributes(
p_doc_type => p_type_lookup_code,
p_po_line_id => x_po_line_id,
p_req_template_name => NULL,
p_req_template_line_num => NULL,
p_org_id => PO_MOAC_UTILS_PVT.get_current_org_id,
p_ip_category_id => l_ip_category_id,
p_item_description => x_item_description,
p_language => userenv('LANG')
);
po_message_s.sql_error('update_line', x_progress, sqlcode);
END update_line;