[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: delete_all_shipments
===========================================================================*/
PROCEDURE delete_all_shipments
(X_delete_id IN NUMBER,
X_entity_level IN VARCHAR2,
X_type_lookup_code IN VARCHAR2) IS
X_progress VARCHAR2(3) := '';
SELECT line_location_id
FROM po_line_locations_all /*Bug6632095: using base table instead of view */
WHERE po_line_id = X_delete_id;
SELECT line_location_id,
po_line_id,
quantity
FROM po_line_locations_all /*Bug6632095: using base table instead of view */
WHERE po_release_id = X_delete_id;
SELECT line_location_id
FROM po_line_locations_all /*Bug6632095: using base table instead of view */
WHERE po_header_id = X_delete_id;
fnd_attached_documents2_pkg.delete_attachments('PO_SHIPMENTS',
x_line_location_id,
'', '', '', '', 'Y');
po_req_lines_sv.remove_req_from_po(X_delete_id,'LINE');
DELETE FROM PO_LINE_LOCATIONS
WHERE po_line_id = X_delete_id
AND shipment_type in ('PLANNED', 'STANDARD', 'PRICE BREAK',
'RFQ', 'QUOTATION')
--
RETURNING
shipment_type,
po_header_id,
po_release_id,
line_location_id,
org_id
BULK COLLECT INTO
l_shipment_type_tbl,
l_po_header_id_tbl,
l_po_release_id_tbl,
l_line_location_id_tbl,
l_line_location_org_id_tbl
;
po_distributions_sv.delete_distributions(X_delete_id,
'LINE');
fnd_attached_documents2_pkg.delete_attachments('PO_SHIPMENTS',
x_line_location_id,
'', '', '', '', 'Y');
IF (PO_CLM_INTG_GRP.IS_CLM_DOCUMENT(p_doc_type => 'PO',p_document_id => X_delete_id) <> 'Y')
THEN
-- Remove the req link
po_req_lines_sv.remove_req_from_po(X_delete_id,'PURCHASE ORDER');
DELETE FROM PO_LINE_LOCATIONS_ALL /*Bug6632095: using base table instead of view */
WHERE po_header_id = X_delete_id
AND shipment_type in ('PLANNED', 'STANDARD', 'PRICE BREAK',
'RFQ', 'QUOTATION')
--
RETURNING
shipment_type,
po_header_id,
po_release_id,
line_location_id,
org_id
BULK COLLECT INTO
l_shipment_type_tbl,
l_po_header_id_tbl,
l_po_release_id_tbl,
l_line_location_id_tbl,
l_line_location_org_id_tbl
;
po_distributions_sv.delete_distributions(X_delete_id,
'HEADER');
fnd_attached_documents2_pkg.delete_attachments('PO_SHIPMENTS',
x_line_location_id,
'', '', '', '', 'Y');
/* Bug 1560839 - Released quantity should be updated only when
the shipment_type is BLANKET */
SELECT shipment_type
INTO x_shipment_type
FROM po_line_locations
WHERE line_location_id = x_line_location_id;
po_lines_sv.update_released_quantity('DELETE',
'BLANKET',
x_po_line_id,
x_original_quantity,
x_quantity);
po_req_lines_sv.remove_req_from_po(X_delete_id,'RELEASE');
When a release is deleted, then the shipments,distributions should also
be deleted.
The deletion of distributions is based on the line_location_id
and prior to the fix we were deleting shipment first and then trying to
delete the distributions based on the line_location_id which did
not delete any records and thereby we ended up having orphan
distribution records.
Moved the po_distributions_sv_delete_distributions before deletion
of shipment lines
*/
-- delete the distributions associated with the release
po_distributions_sv.delete_distributions(X_delete_id,
'RELEASE');
DELETE FROM PO_LINE_LOCATIONS
WHERE po_release_id = X_delete_id
AND shipment_type in ('SCHEDULED', 'BLANKET')
--
RETURNING
shipment_type,
po_header_id,
po_release_id,
line_location_id,
org_id
BULK COLLECT INTO
l_shipment_type_tbl,
l_po_header_id_tbl,
l_po_release_id_tbl,
l_line_location_id_tbl,
l_line_location_org_id_tbl
;
is pased when the tax lines are created. Same should be passed when they are deleted. */
l_transaction_line_rec_type.entity_code := PO_CONSTANTS_SV.PO_ENTITY_CODE ;
po_message_s.sql_error('update_shipment_price', X_progress, sqlcode);
END delete_all_shipments;
PROCEDURE NAME: delete_shipment
===========================================================================*/
PROCEDURE delete_shipment
(X_line_location_id IN NUMBER,
X_row_id IN VARCHAR2,
X_doc_header_id IN NUMBER,
X_shipment_type IN VARCHAR2 ) IS
X_progress VARCHAR2(3) := '';
** Call the cover routine to delete children if the
** shipment type is NOT 'PRICE BREAK'.
*/
IF (X_shipment_type IN ('PRICE BREAK', 'RFQ', 'QUOTATION')) then
null;
po_shipments_sv4.delete_children(X_line_location_id, X_doc_header_id,
X_shipment_type);
** Call the Shipments table handler delete row
*/
po_line_locations_pkg_s2.delete_row(X_row_id);
po_message_s.sql_error('delete_shipment', X_progress, sqlcode);
END delete_shipment;
PROCEDURE NAME: delete_children
===========================================================================*/
PROCEDURE delete_children
(X_line_location_id IN NUMBER,
X_doc_header_id IN NUMBER,
X_shipment_type IN VARCHAR2) IS
X_progress VARCHAR2(3) := '';
po_distributions_sv.delete_distributions(X_line_location_id,
'SHIPMENT');
SELECT quantity,
po_line_id
INTO x_original_quantity,
x_po_line_id
FROM po_line_locations
WHERE line_location_id = X_line_location_id;
po_lines_sv.update_released_quantity('DELETE',
'BLANKET',
x_po_line_id,
x_original_quantity,
x_quantity);
fnd_attached_documents2_pkg.delete_attachments('PO_SHIPMENTS',
X_line_location_id,
'', '', '', '', 'Y');
po_message_s.sql_error('delete_children', X_progress, sqlcode);
END delete_children;
PROCEDURE NAME: update_shipment
===========================================================================*/
PROCEDURE update_shipment
(X_Rowid VARCHAR2,
X_Line_Location_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Po_Header_Id NUMBER,
X_Po_Line_Id NUMBER,
X_Last_Update_Login NUMBER,
X_Quantity NUMBER,
X_Quantity_Received NUMBER,
X_Quantity_Accepted NUMBER,
X_Quantity_Rejected NUMBER,
X_Quantity_Billed NUMBER,
X_Quantity_Cancelled NUMBER,
X_Unit_Meas_Lookup_Code VARCHAR2,
X_Po_Release_Id NUMBER,
X_Ship_To_Location_Id NUMBER,
X_Ship_Via_Lookup_Code VARCHAR2,
X_Need_By_Date DATE,
X_Promised_Date DATE,
X_Last_Accept_Date DATE,
X_Price_Override NUMBER,
X_Encumbered_Flag VARCHAR2,
X_Encumbered_Date DATE,
X_Fob_Lookup_Code VARCHAR2,
X_Freight_Terms_Lookup_Code VARCHAR2,
X_Taxable_Flag VARCHAR2,
X_Tax_Code_Id NUMBER,
X_Tax_User_Override_Flag VARCHAR2,
X_Calculate_Tax_Flag VARCHAR2,
X_From_Header_Id NUMBER,
X_From_Line_Id NUMBER,
X_From_Line_Location_Id NUMBER,
X_Start_Date DATE,
X_End_Date DATE,
X_Lead_Time NUMBER,
X_Lead_Time_Unit VARCHAR2,
X_Price_Discount NUMBER,
X_Terms_Id NUMBER,
X_Approved_Flag IN OUT NOCOPY VARCHAR2,
X_Approved_Date DATE,
X_Closed_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_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_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Inspection_Required_Flag VARCHAR2,
X_Receipt_Required_Flag VARCHAR2,
X_Qty_Rcv_Tolerance NUMBER,
X_Qty_Rcv_Exception_Code VARCHAR2,
X_Enforce_Ship_To_Location VARCHAR2,
X_Allow_Substitute_Receipts VARCHAR2,
X_Days_Early_Receipt_Allowed NUMBER,
X_Days_Late_Receipt_Allowed NUMBER,
X_Receipt_Days_Exception_Code VARCHAR2,
X_Invoice_Close_Tolerance NUMBER,
X_Receive_Close_Tolerance NUMBER,
X_Ship_To_Organization_Id NUMBER,
X_Shipment_Num NUMBER,
X_Source_Shipment_Id NUMBER,
X_Shipment_Type VARCHAR2,
X_Closed_Code VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Government_Context VARCHAR2,
X_Receiving_Routing_Id NUMBER,
X_Accrue_On_Receipt_Flag VARCHAR2,
X_Closed_Reason VARCHAR2,
X_Closed_Date DATE,
X_Closed_By NUMBER,
X_need_to_approve IN OUT NOCOPY NUMBER,
X_increment_revision BOOLEAN,
X_new_rev_num NUMBER,
X_po_rel_Rowid VARCHAR2,
X_dist_window_open VARCHAR2,
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_Country_of_Origin_Code VARCHAR2,
X_Invoice_Match_Option VARCHAR2, --bgu, Dec. 7, 98
--togeorge 10/03/2000
--added note to receiver
X_note_to_receiver VARCHAR2,
-- Mahesh Chandak(GML) Add 7 process related fields.
-- start of Bug# 1548597
X_Secondary_Unit_Of_Measure VARCHAR2,
X_Secondary_Quantity NUMBER,
X_Preferred_Grade VARCHAR2,
X_Secondary_Quantity_Received NUMBER,
X_Secondary_Quantity_Accepted NUMBER,
X_Secondary_Quantity_Rejected NUMBER,
X_Secondary_Quantity_Cancelled NUMBER,
-- end of Bug# 1548597
X_Consigned_Flag VARCHAR2, /* CONSIGNED FPI */
X_amount NUMBER, --
p_transaction_flow_header_id NUMBER, --< Shared Proc FPJ >
p_manual_price_change_flag VARCHAR2 default null --< Manual Price Override FPJ >
) IS
X_progress VARCHAR2(3) := '';
SELECT quantity
INTO x_orig_quantity
FROM po_line_locations
WHERE line_location_id = X_line_location_id;
po_lines_sv.update_released_quantity('UPDATE',
'BLANKET',
x_po_line_id,
x_orig_quantity,
x_quantity);
** Call the update row routine with all parameters.
*/
po_line_locations_pkg_s2.update_row(
X_Rowid,
X_Line_Location_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Po_Header_Id,
X_Po_Line_Id,
X_Last_Update_Login,
X_Quantity,
X_Quantity_Received,
X_Quantity_Accepted,
X_Quantity_Rejected,
X_Quantity_Billed,
X_Quantity_Cancelled,
X_Unit_Meas_Lookup_Code,
X_Po_Release_Id,
X_Ship_To_Location_Id,
X_Ship_Via_Lookup_Code,
X_Need_By_Date,
X_Promised_Date,
X_Last_Accept_Date,
X_Price_Override,
X_Encumbered_Flag,
X_Encumbered_Date,
X_Fob_Lookup_Code,
X_Freight_Terms_Lookup_Code,
X_Taxable_Flag,
X_Tax_Code_Id,
X_Tax_User_Override_Flag,
X_Calculate_Tax_Flag,
X_From_Header_Id,
X_From_Line_Id,
X_From_Line_Location_Id,
X_Start_Date,
X_End_Date,
X_Lead_Time,
X_Lead_Time_Unit,
X_Price_Discount,
X_Terms_Id,
X_Approved_Flag,
X_Approved_Date,
X_Closed_Flag,
X_Cancel_Flag,
X_Cancelled_By,
X_Cancel_Date,
X_Cancel_Reason,
X_Firm_Status_Lookup_Code,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Inspection_Required_Flag,
X_Receipt_Required_Flag,
X_Qty_Rcv_Tolerance,
X_Qty_Rcv_Exception_Code,
X_Enforce_Ship_To_Location,
X_Allow_Substitute_Receipts,
X_Days_Early_Receipt_Allowed,
X_Days_Late_Receipt_Allowed,
X_Receipt_Days_Exception_Code,
X_Invoice_Close_Tolerance,
X_Receive_Close_Tolerance,
X_Ship_To_Organization_Id,
X_Shipment_Num,
X_Source_Shipment_Id,
X_Shipment_Type,
X_Closed_Code,
NULL, --
X_Government_Context,
X_Receiving_Routing_Id,
X_Accrue_On_Receipt_Flag,
X_Closed_Reason,
X_Closed_Date,
X_Closed_By,
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_Country_of_Origin_Code,
X_Invoice_Match_Option, --bgu, Dec. 7, 98
--togeorge 10/03/2000
--added note to receiver
X_note_to_receiver,
--Start of Bug# 1548597.
X_Secondary_Unit_Of_Measure,
X_Secondary_Quantity,
X_Preferred_Grade,
X_Secondary_Quantity_Received,
X_Secondary_Quantity_Accepted,
X_Secondary_Quantity_Rejected,
X_Secondary_Quantity_Cancelled,
-- end of Bug# 1548597
X_Consigned_Flag, /* CONSIGNED FPI */
X_amount, --
p_transaction_flow_header_id, --< Shared Proc FPJ >
p_manual_price_change_flag --< Manual Price Override FPJ >
);
UPDATE po_distributions
SET po_line_id = X_Po_Line_Id
WHERE line_location_id = X_line_location_id;
** update the distributions quantity. The user must
** manually update the quantity. There are to many issues
** with this depending on window coordination.
** Only update the distributions quantity if there is one
** distriubtion and the distribution is not encumbered.
*/
IF (X_Shipment_Type not in ('RFQ', 'QUOTATION', 'PRICE BREAK') AND
X_dist_window_open = 'FALSE') THEN
BEGIN
X_Progress := '080';
SELECT COUNT(po_distribution_id)
INTO X_num_of_distributions
FROM po_distributions pd
WHERE pd.line_location_id = X_line_location_id
AND NOT EXISTS (SELECT 'there are encumbered distributions'
FROM po_distributions pd2
WHERE pd2.line_location_id =
X_line_location_id
AND NVL(pd2.encumbered_flag, 'N') <> 'N') ;
po_message_s.sql_error('update_shipment', X_progress, sqlcode);
UPDATE po_distributions
SET quantity_ordered = X_quantity,
last_update_date = X_last_update_date,
last_updated_by = X_last_updated_by
WHERE line_location_id = X_line_location_id;
The following update is done for standard po and releases.
to have the accrue on receipt flag in distributions in sync. with
shipments accrue on receipt.
*/
/* Bug: 2194604 Added the SCHEDULED also to make aor in sync with dist for release of
planned po
*/
IF (X_Shipment_Type in ('STANDARD','BLANKET','SCHEDULED')) then
X_Progress := '091';
update po_distributions
set accrue_on_receipt_flag = X_accrue_on_receipt_flag
where line_location_id = X_line_location_id;
po_message_s.sql_error('update_shipment', X_progress, sqlcode);
END update_shipment;
PROCEDURE validate_delete_line_loc(p_line_loc_id IN NUMBER
,p_po_line_id IN NUMBER
,p_doc_type IN VARCHAR2
,p_style_disp_name IN VARCHAR2
,x_message_text OUT NOCOPY VARCHAR2) IS
l_some_dists_reserved_flag VARCHAR2(1) := 'N';
l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_line_loc';
d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SHIPMENTS_SV4.validate_delete_line_loc';
SELECT shipment_type,
approved_flag
INTO l_shipment_type,
l_approved_flag
FROM po_line_locations_all
WHERE line_location_id = p_line_loc_id;
x_message_text := PO_CORE_S.get_translated_text('PO_CANT_DELETE_PB_ON_APRVD_PO');
SELECT line_location_id
INTO l_dummy
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id
AND nvl(closed_code, PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN) <> PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
AND nvl(cancel_flag, 'N') <> 'Y';
x_message_text := PO_CORE_S.get_translated_text('PO_CANT_DELETE_ONLY_SCHEDULE');
END validate_delete_line_loc;
PROCEDURE process_delete_line_loc(p_line_loc_id IN NUMBER
,p_line_loc_row_id IN ROWID
,p_po_header_id IN NUMBER
,p_po_line_id IN NUMBER
,p_doc_subtype IN VARCHAR2)
IS
l_vendor_id PO_HEADERS_ALL.vendor_id%TYPE;
l_api_name CONSTANT VARCHAR2(30) := 'process_delete_line_loc';
d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SHIPMENTS_SV4.process_delete_line_loc';
SELECT shipment_type
INTO l_shipment_type
FROM po_line_locations_all
WHERE line_location_id = p_line_loc_id;
po_shipments_sv4.delete_shipment(p_line_loc_id,
p_line_loc_row_id,
p_po_header_id,
l_shipment_type);
SELECT *
INTO l_po_lines_rec
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
SELECT poh.vendor_id,
poh.vendor_site_id,
poh.currency_code
INTO l_vendor_id,
l_vendor_site_id,
l_currency_code
FROM po_headers_all poh
WHERE poh.po_header_id = l_po_lines_rec.po_header_id;
PO_SOURCING2_SV.update_line_price(
p_po_line_id => l_po_lines_rec.po_line_id,
p_price => l_price,
p_base_unit_price => l_base_unit_price,
p_from_line_location_id => l_from_line_location_id);
PO_PRICE_DIFFERENTIALS_PVT.delete_price_differentials(
p_entity_type => 'PO LINE',
p_entity_id => l_po_lines_rec.po_line_id);
UPDATE po_line_locations_all
SET price_override = l_price,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id
WHERE po_line_id = l_po_lines_rec.po_line_id;
END process_delete_line_loc;