[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pkg_name CONSTANT VARCHAR2(30) := 'PO_DOCUMENT_UPDATE_PVT';
g_update_source VARCHAR2(100);
p_update_source IN VARCHAR2
);
p_mass_update_releases IN VARCHAR2 -- Bug 3373453
);
PROCEDURE delete_records (
p_chg IN PO_CHANGES_REC_TYPE
);
PROCEDURE update_document (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_changes IN OUT NOCOPY PO_CHANGES_REC_TYPE,
p_run_submission_checks IN VARCHAR2,
p_launch_approvals_flag IN VARCHAR2,
p_buyer_id IN NUMBER,
p_update_source IN VARCHAR2,
p_override_date IN DATE,
x_api_errors OUT NOCOPY PO_API_ERRORS_REC_TYPE,
p_approval_background_flag IN VARCHAR2,
p_mass_update_releases IN VARCHAR2, -- Bug 3373453
p_req_chg_initiator IN VARCHAR2 DEFAULT NULL --Bug 14549341
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DOCUMENT';
|| ' update source: ' || p_update_source
|| ' override date: ' || p_override_date
|| ' approval background: '||p_approval_background_flag
|| ' mass update releases: '||p_mass_update_releases
|| ' p_req_chg_initiator: '||p_req_chg_initiator --Bug 14549341
|| ' concurrent request ID: '||g_request_id); --bug 7278327
SAVEPOINT PO_DOCUMENT_UPDATE_PVT_SP;
g_sec_qty_grade_only_chge_doc := po_document_update_grp.g_process_param_chge_only;
init_globals (p_changes, p_update_source);
p_mass_update_releases );
p_mass_update_releases => p_mass_update_releases,
p_retroactive_price_change => g_retroactive_price_change
);
ROLLBACK TO PO_DOCUMENT_UPDATE_PVT_SP;
ROLLBACK TO PO_DOCUMENT_UPDATE_PVT_SP;
ROLLBACK TO PO_DOCUMENT_UPDATE_PVT_SP;
END update_document;
p_update_source IN VARCHAR2
) IS
l_proc_name CONSTANT VARCHAR2(30) := 'INIT_GLOBALS';
g_update_source := p_update_source;
SELECT POH.type_lookup_code,
POH.revision_num,
POH.pcard_id,
POH.currency_code,
POH.agent_id,
POH.approved_date
INTO g_document_subtype,
g_revision_num,
g_pcard_id,
l_currency_code,
g_agent_id,
g_approved_date
FROM po_headers POH
WHERE POH.po_header_id = g_po_header_id;
SELECT POH.style_id
INTO g_doc_style_id
FROM po_headers POH
WHERE POH.po_header_id = g_po_header_id;
SELECT POR.release_type,
POR.revision_num,
POR.pcard_id,
POH.currency_code,
POR.agent_id,
POR.approved_date
INTO g_document_subtype,
g_revision_num,
g_pcard_id,
l_currency_code,
g_agent_id,
g_approved_date
FROM po_releases POR, po_headers POH
WHERE POR.po_release_id = g_po_release_id
AND POR.po_header_id = POH.po_header_id; -- JOIN
SELECT CUR.minimum_accountable_unit, CUR.precision
INTO g_min_accountable_unit, g_precision
FROM fnd_currencies CUR
WHERE CUR.currency_code = l_currency_code;
SELECT archive_external_revision_code
INTO g_archive_mode
FROM po_document_types
WHERE document_type_code = g_document_type
AND document_subtype = g_document_subtype;
SELECT 1
INTO l_dummy
FROM po_headers
WHERE po_header_id = g_po_header_id;
SELECT 1
INTO l_dummy
FROM po_releases
WHERE po_release_id = g_po_release_id;
SELECT 1
INTO l_dummy
FROM po_lines
WHERE po_header_id = g_po_header_id
AND po_line_id = p_chg.line_changes.po_line_id(i);
SELECT 1
INTO l_dummy
FROM po_line_locations
WHERE po_header_id = g_po_header_id
AND line_location_id =
NVL(p_chg.shipment_changes.po_line_location_id(i),
p_chg.shipment_changes.parent_line_location_id(i));
SELECT 1
INTO l_dummy
FROM po_line_locations
WHERE po_release_id = g_po_release_id
AND line_location_id =
NVL(p_chg.shipment_changes.po_line_location_id(i),
p_chg.shipment_changes.parent_line_location_id(i));
SELECT 1
INTO l_dummy
FROM po_distributions
WHERE po_header_id = g_po_header_id
AND po_distribution_id =
NVL(p_chg.distribution_changes.po_distribution_id(i),
p_chg.distribution_changes.parent_distribution_id(i));
SELECT 1
INTO l_dummy
FROM po_distributions
WHERE po_release_id = g_po_release_id
AND po_distribution_id =
NVL(p_chg.distribution_changes.po_distribution_id(i),
p_chg.distribution_changes.parent_distribution_id(i));
p_mass_update_releases IN VARCHAR2 -- Bug 3373453
) IS
l_proc_name CONSTANT VARCHAR2(30) := 'PROCESS_INPUTS';
p_parameter_name => 'p_mass_update_releases',
p_parameter_value => p_mass_update_releases );
IF (p_mass_update_releases = G_PARAMETER_YES)
AND (g_document_type <> 'PA') THEN
FND_MESSAGE.set_name('PO', 'PO_INVALID_MASS_UPDATE_REL');
p_mode => 'CHECK_UPDATEABLE',
p_lock_flag => 'Y', -- Lock the document
x_po_status_rec => l_status_rec_type,
x_return_status => l_return_status
);
SELECT POL.quantity,
POL.unit_meas_lookup_code,
POL.item_id,
POL.from_header_id,
POL.from_line_id,
NVL(POHA.global_agreement_flag, 'N'),
NVL(POL.cancel_flag, 'N'),
NVL(POL.closed_code, 'OPEN'),
PLT.order_type_lookup_code, -- value basis
PLT.purchase_basis,
POL.amount,
POL.start_date,
POL.expiration_date,
POL.unit_price,
POL.from_line_location_id,
POL.secondary_quantity,
POL.secondary_unit_of_measure,
MSI.segment1, -- item_number
POL.manual_price_change_flag, --
POL.preferred_grade --INVCONV
INTO p_chg.line_changes.c_quantity(i),
p_chg.line_changes.c_unit_meas_lookup_code(i),
p_chg.line_changes.c_item_id(i),
p_chg.line_changes.c_from_header_id(i),
p_chg.line_changes.c_from_line_id(i),
p_chg.line_changes.c_has_ga_reference(i),
p_chg.line_changes.c_cancel_flag(i),
p_chg.line_changes.c_closed_code(i),
p_chg.line_changes.c_value_basis(i),
p_chg.line_changes.c_purchase_basis(i),
p_chg.line_changes.c_amount(i),
p_chg.line_changes.c_start_date(i),
p_chg.line_changes.c_expiration_date(i),
p_chg.line_changes.c_unit_price(i),
p_chg.line_changes.c_from_line_location_id(i),
p_chg.line_changes.c_secondary_quantity(i),
p_chg.line_changes.c_secondary_uom(i),
p_chg.line_changes.c_item_number(i),
-- :
p_chg.line_changes.t_manual_price_change_flag(i),
p_chg.line_changes.c_preferred_grade(i) --INVCONV
FROM po_lines POL,
po_line_types PLT,
po_headers_all POHA,
mtl_system_items_b MSI,
financials_system_parameters FSP
WHERE POL.po_line_id = p_chg.line_changes.po_line_id(i)
AND PLT.line_type_id = POL.line_type_id -- JOIN
AND POHA.po_header_id(+) = POL.from_header_id -- JOIN
AND MSI.inventory_item_id(+) = POL.item_id -- JOIN
AND NVL(MSI.organization_id, FSP.inventory_organization_id)
= FSP.inventory_organization_id; -- JOIN
SELECT PLL.po_line_id,
-- quantity:
decode (l_parent_line_loc_id, null,
PLL.quantity, -- existing shipment
0), -- split shipment
--POL.unit_meas_lookup_code,
PLL.unit_meas_lookup_code,
-- cancel_flag:
decode (l_parent_line_loc_id, null,
NVL(PLL.cancel_flag, 'N'), -- existing shipment
'N'), -- split shipment
-- closed_code:
decode (l_parent_line_loc_id, null,
NVL(PLL.closed_code, 'OPEN'), -- existing shipment
'OPEN'), -- split shipment
POL.item_id,
PLL.ship_to_organization_id,
NVL(PLL.drop_ship_flag, 'N'),
-- quantity_received:
decode (l_parent_line_loc_id, null,
NVL(PLL.quantity_received, 0), -- existing shipment
0), -- split shipment
-- quantity_billed:
decode (l_parent_line_loc_id, null,
NVL(PLL.quantity_billed, 0), -- existing shipment
0), -- split shipment
-- amount_received: Bug 3524527
decode (l_parent_line_loc_id, null,
NVL(PLL.amount_received, 0), -- existing shipment
0), -- split shipment
-- amount_billed: Bug 3524527
decode (l_parent_line_loc_id, null,
NVL(PLL.amount_billed, 0), -- existing shipment
0), -- split shipment
NVL(PLL.accrue_on_receipt_flag, 'N'),
--PLT.order_type_lookup_code, -- value basis
PLL.value_basis, --
PLT.purchase_basis,
-- amount:
decode (l_parent_line_loc_id, null,
PLL.amount, -- existing shipment
0), -- split shipment
PLL.price_override,
-- parent_quantity:
decode (l_parent_line_loc_id, null,
null, -- existing shipment
PLL.quantity), -- split shipment
-- parent_amount:
decode (l_parent_line_loc_id, null,
null, -- existing shipment
PLL.amount), -- split shipment
-- secondary_quantity:
decode (l_parent_line_loc_id, null,
PLL.secondary_quantity, -- existing shipment
null), -- split shipment
PLL.secondary_unit_of_measure,
MSI.segment1, -- item_number
-- approved_date:
decode (l_parent_line_loc_id, null,
PLL.approved_date, -- existing shipment
null), -- split shipment
-- encumbered_flag:
decode (l_parent_line_loc_id, null,
NVL(PLL.encumbered_flag, 'N'), -- existing shipment
'N'), -- split shipment
PLL.shipment_type,
-- quantity_shipped:
decode (l_parent_line_loc_id, null,
NVL(PLL.quantity_shipped,0), -- existing shipment
0), -- split shipment
PLL.manual_price_change_flag, --
PLL.preferred_grade -- INVCONV sschinch 09/07/04
--PLL.PAYMENT_TYPE -- Progress Payment type
INTO p_chg.shipment_changes.c_po_line_id(i),
p_chg.shipment_changes.c_quantity(i),
p_chg.shipment_changes.c_unit_meas_lookup_code(i),
p_chg.shipment_changes.c_cancel_flag(i),
p_chg.shipment_changes.c_closed_code(i),
p_chg.shipment_changes.c_item_id(i),
p_chg.shipment_changes.c_ship_to_organization_id(i),
p_chg.shipment_changes.c_drop_ship_flag(i),
p_chg.shipment_changes.c_quantity_received(i),
p_chg.shipment_changes.c_quantity_billed(i),
p_chg.shipment_changes.c_amount_received(i), -- Bug 3524527
p_chg.shipment_changes.c_amount_billed(i), -- Bug 3524527
p_chg.shipment_changes.c_accrue_on_receipt_flag(i),
p_chg.shipment_changes.c_value_basis(i),
p_chg.shipment_changes.c_purchase_basis(i),
p_chg.shipment_changes.c_amount(i),
p_chg.shipment_changes.c_price_override(i),
p_chg.shipment_changes.c_parent_quantity(i),
p_chg.shipment_changes.c_parent_amount(i),
p_chg.shipment_changes.c_secondary_quantity(i),
p_chg.shipment_changes.c_secondary_uom(i),
p_chg.shipment_changes.c_item_number(i),
p_chg.shipment_changes.c_approved_date(i),
p_chg.shipment_changes.c_encumbered_flag(i),
p_chg.shipment_changes.c_shipment_type(i),
p_chg.shipment_changes.c_quantity_shipped(i),
-- :
p_chg.shipment_changes.t_manual_price_change_flag(i),
p_chg.shipment_changes.c_preferred_grade(i) -- INVCONV sschinch 09/07/04
--p_chg.shipment_changes.c_payment_type(i)
FROM po_line_locations PLL,
po_lines POL,
po_line_types PLT,
mtl_system_items_b MSI,
financials_system_parameters FSP
WHERE PLL.line_location_id =
NVL( p_chg.shipment_changes.parent_line_location_id(i), -- split shipment
p_chg.shipment_changes.po_line_location_id(i) ) -- existing shipment
AND POL.po_line_id = PLL.po_line_id -- JOIN
AND PLT.line_type_id = POL.line_type_id -- JOIN
AND MSI.inventory_item_id(+) = POL.item_id -- JOIN
AND NVL(MSI.organization_id, FSP.inventory_organization_id)
= FSP.inventory_organization_id; -- JOIN
SELECT POD.po_line_id,
-- line_location_id:
decode (l_parent_dist_id, null,
POD.line_location_id, -- existing distribution
null), -- split distribution
-- quantity_ordered:
decode (l_parent_dist_id, null,
POD.quantity_ordered, -- existing distribution
0), -- split distribution
PLL.unit_meas_lookup_code, --
POL.item_id,
-- quantity_delivered:
decode (l_parent_dist_id, null,
NVL(POD.quantity_delivered, 0), -- existing distribution
0), -- split distribution
-- quantity_billed:
decode (l_parent_dist_id, null,
NVL(POD.quantity_billed, 0), -- existing distribution
0), -- split distribution
-- amount_delivered: Bug 3524527
decode (l_parent_dist_id, null,
NVL(POD.amount_delivered, 0), -- existing distribution
0), -- split distribution
-- amount_billed: Bug 3524527
decode (l_parent_dist_id, null,
NVL(POD.amount_billed, 0), -- existing distribution
0), -- split distribution
-- value basis:
-- PLT.order_type_lookup_code, --
PLL.value_basis,
PLT.purchase_basis,
-- amount_ordered:
decode (l_parent_dist_id, null,
POD.amount_ordered, -- existing distribution
0), -- split distribution
-- parent_line_location_id:
decode (l_parent_dist_id, null,
null, -- existing distribution
POD.line_location_id), -- split distribution
POD.award_id,
POD.project_id,
POD.task_id,
POD.distribution_num,
-- encumbered_flag:
decode (l_parent_dist_id, null,
NVL(POD.encumbered_flag,'N'), -- existing distribution
'N'), -- split distribution
POD.req_distribution_id,
-- creation_date:
decode (l_parent_dist_id, null,
POD.creation_date, -- existing distribution
NULL) -- split distribution
INTO p_chg.distribution_changes.c_po_line_id(i),
p_chg.distribution_changes.c_line_location_id(i),
p_chg.distribution_changes.c_quantity_ordered(i),
p_chg.distribution_changes.c_unit_meas_lookup_code(i),
p_chg.distribution_changes.c_item_id(i),
p_chg.distribution_changes.c_quantity_delivered(i),
p_chg.distribution_changes.c_quantity_billed(i),
p_chg.distribution_changes.c_amount_delivered(i), -- Bug 3524527
p_chg.distribution_changes.c_amount_billed(i), -- Bug 3524527
p_chg.distribution_changes.c_value_basis(i),
p_chg.distribution_changes.c_purchase_basis(i),
p_chg.distribution_changes.c_amount_ordered(i),
p_chg.distribution_changes.c_parent_line_location_id(i),
p_chg.distribution_changes.c_award_id(i),
p_chg.distribution_changes.c_project_id(i),
p_chg.distribution_changes.c_task_id(i),
p_chg.distribution_changes.c_distribution_num(i),
p_chg.distribution_changes.c_encumbered_flag(i),
p_chg.distribution_changes.c_req_distribution_id(i),
p_chg.distribution_changes.c_creation_date(i)
FROM po_distributions POD, po_lines POL, po_line_types PLT,po_line_locations_all PLL --
SELECT count(*)
INTO l_shipment_count
FROM po_line_locations
WHERE po_line_id = p_chg.line_changes.po_line_id(i)
AND shipment_type in ('STANDARD', 'PLANNED')
AND nvl(cancel_flag,'N') <> 'Y'
AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED';
SELECT NVL(mtl.grade_control_flag,'N')
INTO l_grade_control_flag
FROM mtl_system_items_b mtl,
financials_system_parameters fsp
WHERE mtl.inventory_item_id = p_chg.line_changes.c_item_id(i)
AND mtl.organization_id = fsp.inventory_organization_id;
SELECT msi.tracking_quantity_ind,msi.secondary_default_ind
INTO l_dual_uom_ind,l_secondary_default_ind
FROM mtl_system_items_b msi,
financials_system_parameters fsp
WHERE msi.inventory_item_id = p_chg.line_changes.c_item_id(i)
AND msi.organization_id = fsp.inventory_organization_id;
PO_DOCUMENT_UPDATE_PVT.add_error (
p_api_errors => g_api_errors,
x_return_status => l_return_status,
p_message_name => 'PO_DUALFIXED_NO_CONVERSION',
p_token_name1 => 'PQTY',
p_token_value1 => p_chg.shipment_changes.c_quantity(i),
p_token_name2 => 'SQTY',
p_token_value2 => p_chg.line_changes.secondary_quantity(i));
SELECT msi.tracking_quantity_ind,msi.secondary_default_ind
INTO l_dual_uom_ind,l_secondary_default_ind
FROM mtl_system_items_b msi,
financials_system_parameters fsp
WHERE msi.inventory_item_id = p_chg.line_changes.c_item_id(i)
AND msi.organization_id = fsp.inventory_organization_id;
SELECT NVL(grade_control_flag,'N') INTO l_grade_control_flag
FROM mtl_system_items
WHERE inventory_item_id = p_chg.shipment_changes.c_item_id(i)
AND organization_id = p_chg.shipment_changes.c_ship_to_organization_id(i) ;
SELECT count(*)
INTO l_shipment_count
FROM po_line_locations
WHERE po_line_id = p_chg.shipment_changes.c_po_line_id(i)
AND shipment_num = p_chg.shipment_changes.split_shipment_num(i)
AND NVL(po_release_id,-1) = NVL(g_po_release_id,-1);
IF (p_chg.shipment_changes.delete_record(i) = G_PARAMETER_YES) THEN
add_error ( p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_message_name => 'PO_CHNG_SPLIT_SHIP_NO_DELETE',
p_table_name => 'PO_LINE_LOCATIONS_ALL',
p_column_name => NULL,
p_entity_type => G_ENTITY_TYPE_SHIPMENTS,
p_entity_id => i );
END IF; -- delete_record
IF (p_chg.distribution_changes.delete_record(i) = G_PARAMETER_YES) THEN
add_error ( p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_message_name => 'PO_CHNG_SPLIT_DIST_NO_DELETE',
p_table_name => 'PO_DISTRIBUTIONS_ALL',
p_column_name => NULL,
p_entity_type => G_ENTITY_TYPE_DISTRIBUTIONS,
p_entity_id => i );
END IF; -- delete_record
SELECT po_line_id, manual_price_change_flag
FROM po_lines POL
WHERE POL.po_header_id = p_po_header_id
AND (POL.from_header_id IS NOT NULL OR
--
POL.contract_id IS NOT NULL);
SELECT line_location_id
FROM po_line_locations
WHERE po_line_id = p_po_line_id
AND (NVL(cancel_flag,'N') <> 'Y')
AND (NVL(closed_code,'OPEN') <> 'FINALLY CLOSED')
AND shipment_type IN ('PLANNED','STANDARD');
l_price_updateable VARCHAR2(1); -- Bug 3337426
l_delete_record VARCHAR2(1);
PO_DOCUMENT_CHECKS_GRP.check_rel_price_updateable (
p_api_version => 1.0,
x_return_status => l_return_status,
p_line_location_id => l_line_location_id,
p_from_price_break => G_PARAMETER_YES,
p_add_reasons_to_msg_list => G_PARAMETER_NO,
x_price_updateable => l_price_updateable,
x_retroactive_price_change => l_retro_price_change
);
ELSE -- Split shipment - always allow price updates.
l_price_updateable := G_PARAMETER_YES;
IF (l_price_updateable = G_PARAMETER_YES) THEN
-- Bug 3337426 END
-- Call the Pricing API to get a new price from the price break.
get_release_break_price (
p_line_location_id => l_line_location_id,
p_quantity => l_new_qty,
p_ship_to_location_id => l_new_ship_to_loc_id,
p_need_by_date => l_new_need_by_date,
x_price => l_new_price
);
ELSE -- l_price_updateable = G_PARAMETER_NO
IF (g_fnd_debug = 'Y') THEN
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
FND_LOG.string ( log_level => FND_LOG.LEVEL_EVENT,
module => g_module_prefix||l_proc_name,
message => 'Shipment change on '
||l_line_location_id
||': Skip the call to the Pricing API because'
||' price is not updateable on this shipment.' );
END IF; -- l_price_updateable
PO_DOCUMENT_CHECKS_GRP.check_std_po_price_updateable (
p_api_version => 1.0,
x_return_status => l_return_status,
p_po_line_id => l_po_line_id,
p_from_price_break => G_PARAMETER_YES,
p_add_reasons_to_msg_list => G_PARAMETER_NO,
x_price_updateable => l_price_updateable,
x_retroactive_price_change => l_retro_price_change
);
IF (l_price_updateable = G_PARAMETER_YES) THEN
-- Bug 3337426 END
-- begin bug 3331197:
-- if there is a need-by date or ship-to location change but no
-- price change,
-- then l_line_chg_i is null, and we need to initialize it
-- because we have to change the price on the line
-- after calling the pricing API with the changed shipment info
IF(l_line_chg_i IS NULL) THEN
l_line_chg_i := find_line_change(p_chg,l_po_line_id);
ELSE -- l_price_updateable = G_PARAMETER_NO
IF (g_fnd_debug = 'Y') THEN
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
FND_LOG.string ( log_level => FND_LOG.LEVEL_EVENT,
module => g_module_prefix||l_proc_name,
message => 'Line change on ' || l_po_line_id
||': Skip the call to the Pricing API because'
||' price is not updateable on this line.' );
END IF; -- l_price_updateable
SELECT fsp.inventory_organization_id
INTO l_fsp_org_id
FROM financials_system_parameters fsp;
SELECT msi.tracking_quantity_ind,secondary_default_ind,secondary_uom_code
INTO l_dual_um_ind,l_secondary_default_ind,l_new_sec_uom
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = p_chg.line_changes.c_item_id(i)
AND msi.organization_id = l_fsp_org_id;
SELECT muom.unit_of_measure
INTO l_new_sec_uom
FROM mtl_units_of_measure muom
WHERE uom_code = l_new_sec_uom;
PO_DOCUMENT_UPDATE_PVT.add_error (
p_api_errors => g_api_errors,
x_return_status => l_return_status,
p_message_name => NULL,
p_message_text => x_data);
SELECT msi.tracking_quantity_ind,secondary_default_ind
INTO l_dual_um_ind,l_secondary_default_ind
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = p_chg.line_changes.c_item_id(i)
AND msi.organization_id = l_fsp_org_id;
PO_DOCUMENT_UPDATE_PVT.add_error (
p_api_errors => g_api_errors,
x_return_status => l_return_status,
p_message_name => NULL,
p_message_text => x_data);
p_chg.line_changes.delete_price_adjs(i) := G_PARAMETER_YES;
l_delete_record := p_chg.shipment_changes.delete_record(i);
SELECT msi.tracking_quantity_ind,secondary_default_ind
INTO l_dual_um_ind,l_secondary_default_ind
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = p_chg.shipment_changes.c_item_id(i)
AND msi.organization_id = l_fsp_org_id;
OR (l_delete_record IS NULL OR l_delete_record = G_PARAMETER_NO) THEN
g_sec_qty_grade_only_chge_doc := 'Y' ; /* Bug 5366883 Moved this here from outer If statement */
PO_DOCUMENT_UPDATE_PVT.add_error(p_api_errors => g_api_errors,
x_return_status => l_return_status,
p_message_name => 'PO_SECONDARY_QTY_NOT_REQUIRED');
PO_DOCUMENT_UPDATE_PVT.add_error (
p_api_errors => g_api_errors,
x_return_status => l_return_status,
p_message_name => 'PO_DUALFIXED_NO_CONVERSION',
p_token_name1 => 'PQTY',
p_token_value1 => p_chg.shipment_changes.c_quantity(i),
p_token_name2 => 'SQTY',
p_token_value2 => l_new_secondary_qty);
PO_DOCUMENT_UPDATE_PVT.add_error (
p_api_errors => g_api_errors,
x_return_status => l_return_status,
p_message_name => NULL,
p_message_text => x_data);
PO_DOCUMENT_UPDATE_PVT.add_error (
p_api_errors => g_api_errors,
x_return_status => l_return_status,
p_message_name => NULL,
p_message_text => x_data);
IF (g_update_source = G_UPDATE_SOURCE_OM) THEN
-- For OM (Drop Ship Integration), use the requested Qty2 if it is
-- present. Otherwise, call the OPM API to derive a new Qty2.
IF (l_new_quantity2 IS NOT NULL) THEN
IF (g_fnd_debug = 'Y') THEN
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
FND_LOG.string (
log_level => FND_LOG.LEVEL_EVENT,
module => g_module_prefix||l_proc_name,
message => 'UOM1 and UOM2 are the same between the request and '||
'the document. We do not need to call the OPM API.' );
SELECT NVL(p_quantity, PLL.quantity),
NVL(p_ship_to_location_id, PLL.ship_to_location_id),
NVL(p_need_by_date, NVL(PLL.need_by_date, sysdate)),
PLL.ship_to_organization_id,
PLL.po_line_id,
POL.price_break_lookup_code
INTO l_quantity,
l_ship_to_location_id,
l_need_by_date,
l_ship_to_org_id,
l_po_line_id,
l_price_break_type
FROM po_line_locations PLL, po_lines POL
WHERE PLL.line_location_id = p_line_location_id
AND PLL.po_line_id = POL.po_line_id; -- JOIN
SELECT NVL(p_quantity,POL.quantity),
POL.from_line_id,
NVL(p_ship_to_location_id, PLL.ship_to_location_id),
NVL(p_need_by_date, NVL(PLL.need_by_date, sysdate)),
PLL.ship_to_organization_id,
--
POL.org_id,
POL.contract_id,
POL.po_header_id,
POL.po_line_id,
POL.creation_date,
POL.item_id,
POL.item_revision,
POL.category_id,
POL.line_type_id,
POL.vendor_product_num,
POH.vendor_id,
POH.vendor_site_id,
POL.unit_meas_lookup_code,
-- Bug 3417479
-- NVL(POL.base_unit_price, POL.unit_price)
POL.base_unit_price,
POH.currency_code
--
INTO l_quantity,
l_from_line_id,
l_ship_to_location_id,
l_need_by_date,
l_ship_to_org_id,
--
l_org_id,
l_contract_id,
l_order_header_id,
l_order_line_id,
l_creation_date,
l_item_id,
l_item_revision,
l_category_id,
l_line_type_id,
l_vendor_product_num,
l_vendor_id,
l_vendor_site_id,
l_uom,
l_in_unit_price,
l_currency_code -- Bug 3564863
--
FROM po_line_locations PLL, po_lines POL,
--
po_headers POH
WHERE PLL.line_location_id = p_line_location_id
AND POL.po_line_id = PLL.po_line_id -- JOIN
--
AND POH.po_header_id = POL.po_header_id;
SELECT line_location_id
INTO l_line_location_id
FROM po_line_locations
WHERE po_line_id = p_po_line_id
AND shipment_num = l_min_shipment_num
AND shipment_type IN ('STANDARD','PLANNED');
SELECT po_distribution_id, distribution_num, quantity_ordered, amount_ordered
FROM po_distributions
WHERE line_location_id = p_line_location_id
ORDER by distribution_num ASC;
SELECT line_location_id
INTO l_line_location_id
FROM po_line_locations
WHERE po_line_id = p_chg.line_changes.po_line_id(i)
AND shipment_type in ('STANDARD', 'PLANNED')
AND NVL(cancel_flag,'N') <> 'Y'
AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED';
SELECT max(distribution_num)
INTO l_max_dist_num
FROM po_distributions
WHERE line_location_id = NVL(l_parent_line_loc_id, l_line_location_id);
SELECT grade_code
FROM mtl_grades
WHERE grade_code = p_grade;
l_price_updateable VARCHAR2(1);
select amount
into l_advance_amount
from po_line_locations_all
where payment_type = 'ADVANCE'
and po_line_id =l_po_line_id ;
SELECT SUM(NVL(quantity_received,0)),
SUM(NVL(quantity_billed,0))
INTO l_qty_received,
l_qty_billed
FROM po_line_locations
WHERE po_line_id = l_po_line_id
AND shipment_type IN ('STANDARD', 'PLANNED');
SELECT Max(NVL(quantity_received,0)),
Max(NVL(quantity_billed,0))
INTO l_qty_received,
l_qty_billed
FROM po_line_locations
WHERE po_line_id = l_po_line_id
AND shipment_type IN ('STANDARD', 'PLANNED','PREPAYMENT');
PO_DOCUMENT_CHECKS_GRP.check_std_po_price_updateable (
p_api_version => 1.0,
x_return_status => l_return_status,
p_po_line_id => l_po_line_id,
p_from_price_break => p_chg.line_changes.t_from_price_break(i),
p_add_reasons_to_msg_list => G_PARAMETER_YES,
x_price_updateable => l_price_updateable,
x_retroactive_price_change => l_retroactive_price_change
);
IF (l_price_updateable = G_PARAMETER_NO) THEN
add_message_list_errors (
p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_start_index => l_last_msg_list_index + 1,
p_entity_type => G_ENTITY_TYPE_LINES,
p_entity_id => i
);
SELECT SUM(NVL(amount_received,0)),
SUM(NVL(amount_billed,0))
INTO l_amt_received,
l_amt_billed
FROM po_line_locations
WHERE po_line_id = l_po_line_id
AND shipment_type = 'PREPAYMENT';
SELECT SUM(NVL(amount_received,0)),
SUM(NVL(amount_billed,0))
INTO l_amt_received,
l_amt_billed
FROM po_line_locations
WHERE po_line_id = l_po_line_id
AND shipment_type = 'STANDARD';
IF (p_chg.line_changes.delete_record(i) = G_PARAMETER_YES) THEN
------------------------------------------------------------------------
-- Check: Prevent line deletion on blankets if the header has been
-- approved at least once.
------------------------------------------------------------------------
IF (g_document_type = 'PA') THEN
IF (g_approved_date IS NOT NULL) THEN
add_error ( p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_message_name => 'PO_PO_USE_CANCEL_ON_APRVD_PO2',
p_table_name => 'PO_LINES_ALL',
p_column_name => NULL,
p_entity_type => G_ENTITY_TYPE_LINES,
p_entity_id => i);
SELECT count(*)
INTO l_ship_count
FROM po_line_locations
WHERE po_line_id = l_po_line_id
AND approved_date IS NOT NULL;
SELECT count(*)
INTO l_ship_count
FROM po_line_locations
WHERE po_line_id = l_po_line_id
AND encumbered_flag = 'Y';
END IF; -- l_delete_record
SELECT grade_code
FROM mtl_grades
WHERE grade_code = p_grade;
l_new_sales_order_update_date PO_LINE_LOCATIONS.sales_order_update_date%TYPE;
l_price_updateable VARCHAR2(1);
l_new_sales_order_update_date :=
p_chg.shipment_changes.sales_order_update_date(i);
PO_DOCUMENT_CHECKS_GRP.check_rel_price_updateable (
p_api_version => 1.0,
x_return_status => l_return_status,
p_line_location_id => l_line_location_id,
p_from_price_break => p_chg.shipment_changes.t_from_price_break(i),
p_add_reasons_to_msg_list => G_PARAMETER_YES,
x_price_updateable => l_price_updateable,
x_retroactive_price_change => l_retroactive_price_change
);
IF (l_price_updateable = G_PARAMETER_NO) THEN
add_message_list_errors (
p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_start_index => l_last_msg_list_index + 1,
p_entity_type => G_ENTITY_TYPE_SHIPMENTS,
p_entity_id => i
);
SELECT NVL(PLAN.quantity, 0) - NVL(PLAN.quantity_cancelled, 0)
INTO l_planned_qty
FROM po_line_locations REL, po_line_locations PLAN
WHERE REL.line_location_id = l_line_location_id
AND REL.source_shipment_id = PLAN.line_location_id; -- JOIN
AND (NVL(g_update_source,'DEFAULT') <> G_UPDATE_SOURCE_OM)
AND ((l_new_qty IS NOT NULL) OR (l_new_amt is NOT NULL)
OR (l_is_split_shipment)OR
(l_new_secondary_qty IS NOT NULL) OR (l_new_preferred_grade IS NOT NULL)) THEN -- sschinch INVCONV
add_error ( p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_message_name => 'PO_CHNG_CANNOT_MODIFY_DROPSHIP',
p_table_name => 'PO_LINE_LOCATIONS_ALL',
p_entity_type => G_ENTITY_TYPE_SHIPMENTS,
p_entity_id => i );
SELECT count(*)
INTO l_ship_to_loc_valid
FROM hr_locations_all loc
WHERE loc.location_id = l_new_ship_to_loc_id
AND NVL(loc.business_group_id, g_business_group_id )
= g_business_group_id
AND loc.ship_to_site_flag = 'Y'
AND NVL(loc.inventory_organization_id, l_ship_to_org_id)
= l_ship_to_org_id
AND sysdate < NVL(loc.inactive_date, sysdate+1);
/* SELECT count(*)
INTO l_ship_to_loc_valid
FROM hz_locations hz,
hz_party_sites ps,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all asa,
hz_cust_accounts cu,
oe_order_lines_all oel,
oe_drop_ship_sources oedp
WHERE hz.location_id = l_new_ship_to_loc_id
AND oedp.line_location_id = l_line_location_id
AND oedp.line_id = oel.line_id -- JOIN
AND oel.sold_to_org_id = cu.party_id -- JOIN
AND cu.cust_account_id = asa.cust_account_id -- JOIN
AND asa.cust_acct_site_id = su.cust_acct_site_id -- JOIN
AND su.site_use_code = 'SHIP_TO'
AND asa.party_site_id = ps.party_site_id -- JOIN
AND ps.location_id = hz.location_id; -- JOIN */
SELECT count(*)
into l_ship_to_loc_valid
FROM hz_cust_site_uses_all site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_acct_sites_all acct_site ,
oe_order_lines_all oel,
oe_drop_ship_sources oedp
WHERE oedp.line_location_id = l_line_location_id
AND loc.location_id = l_new_ship_to_loc_id
AND site.site_use_code = 'SHIP_TO'
AND site.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND party_site.location_id = loc.location_id
AND acct_site.cust_account_id = oel.sold_to_org_id
AND oedp.line_id = oel.line_id
AND site.status ='A'
AND acct_site.status ='A' ;
SELECT Count(*)
INTO l_ship_to_loc_valid
FROM hz_cust_site_uses_all site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_acct_sites_all acct_site ,
oe_order_lines_all oel,
oe_drop_ship_sources oedp,
hz_cust_acct_relate_all rel
WHERE oedp.line_location_id = l_line_location_id
AND loc.location_id = l_new_ship_to_loc_id
AND site.site_use_code = 'SHIP_TO'
AND site.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND party_site.location_id = loc.location_id
AND acct_site.cust_account_id = rel.cust_account_id --bug 6401009
AND rel.related_cust_account_id = oel.sold_to_org_id --bug 6401009
AND rel.ship_to_flag = 'Y'
AND rel.status = 'A'
AND rel.org_id = acct_site.org_id
AND oedp.line_id = oel.line_id
AND site.status ='A'
AND acct_site.status ='A' ;
IF (l_new_sales_order_update_date IS NOT NULL) THEN
add_error ( p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_message_name => 'PO_CHNG_DROPSHIP_RCV_SHIP_QTY',
p_table_name => 'PO_LINE_LOCATIONS_ALL',
p_column_name => 'SALES_ORDER_UPDATE_DATE',
p_entity_type => G_ENTITY_TYPE_SHIPMENTS,
p_entity_id => i );
IF (p_chg.shipment_changes.delete_record(i) = G_PARAMETER_YES) THEN
------------------------------------------------------------------------
-- Check: Prevent delete if the shipment has been approved at least once.
------------------------------------------------------------------------
IF (l_approved_date IS NOT NULL) THEN
IF (l_shipment_type = 'PRICE BREAK') THEN
l_message_name := 'PO_CANT_DELETE_PB_ON_APRVD_PO';
END IF; -- l_delete_record
IF (p_chg.distribution_changes.delete_record(i) = G_PARAMETER_YES) THEN
------------------------------------------------------------------------
-- Check: Prevent delete if the distribution was created before the
-- last PO approval date.
------------------------------------------------------------------------
IF (p_chg.distribution_changes.c_creation_date(i) <= g_approved_date) THEN
add_error ( p_api_errors => g_api_errors,
x_return_status => x_return_status,
p_message_name => 'PO_CANT_DELETE_PB_ON_APRVD_PO',
p_table_name => 'PO_DISTRIBUTIONS_ALL',
p_column_name => NULL,
p_entity_type => G_ENTITY_TYPE_DISTRIBUTIONS,
p_entity_id => i);
p_message_name => 'PO_PO_DELETE_DEL_DIST_NA',
p_table_name => 'PO_DISTRIBUTIONS_ALL',
p_column_name => NULL,
p_entity_type => G_ENTITY_TYPE_DISTRIBUTIONS,
p_entity_id => i);
p_message_name => 'PO_PO_DELETE_DIST_BILLED_NA',
p_table_name => 'PO_DISTRIBUTIONS_ALL',
p_column_name => NULL,
p_entity_type => G_ENTITY_TYPE_DISTRIBUTIONS,
p_entity_id => i);
END IF; -- l_delete_record
UPDATE po_lines_all /* Changed po_lines to po_lines_all */
SET last_update_date = sysdate,
last_updated_by = g_user_id,
request_id = decode(g_request_id,null,request_id,-1,request_id,g_request_id), /*bug 7278327, update the request_id with the concerned concurrent_request_id*/
unit_price = nvl(p_chg.line_changes.unit_price(i), unit_price),
--
base_unit_price = nvl(p_chg.line_changes.t_base_unit_price(i),
base_unit_price),
vendor_product_num =
nvl(p_chg.line_changes.vendor_product_num(i), vendor_product_num),
quantity = nvl(p_chg.line_changes.quantity(i), quantity),
start_date = nvl(p_chg.line_changes.start_date(i), start_date),
expiration_date =
nvl(p_chg.line_changes.expiration_date(i), expiration_date),
amount = nvl(p_chg.line_changes.amount(i), amount),
secondary_quantity =
nvl(p_chg.line_changes.secondary_quantity(i), secondary_quantity),
-- t_from_line_location_id has the following meaning:
-- NULL : no change
-- G_NULL_NUM : Set from_line_location_id to NULL.
-- any other value: Set from_line_location_id to that value.
from_line_location_id =
decode ( p_chg.line_changes.t_from_line_location_id(i),
NULL, from_line_location_id,
G_NULL_NUM, NULL,
p_chg.line_changes.t_from_line_location_id(i) ),
--
-- Reset the "Amount Billed notification sent" flag to NULL if there
-- is an Amount change.
svc_amount_notif_sent =
decode ( p_chg.line_changes.amount(i),
NULL, svc_amount_notif_sent, NULL ),
-- Reset the "Assignment Completion notification sent" flag to NULL
-- if there is an Assignment End Date change.
svc_completion_notif_sent =
decode ( p_chg.line_changes.expiration_date(i),
NULL, svc_completion_notif_sent, NULL ),
--
--
manual_price_change_flag =
NVL(p_chg.line_changes.t_manual_price_change_flag(i),
manual_price_change_flag),
--
preferred_grade = nvl(p_chg.line_changes.preferred_grade(i),preferred_grade), --INVCONV sschinch
secondary_unit_of_measure = NVL(p_chg.line_changes.t_new_secondary_uom(i),secondary_unit_of_measure) --INVCONV
,tax_attribute_update_code =
NVL(tax_attribute_update_code,NVL2(g_calculate_tax_flag, 'UPDATE', null)) --
WHERE po_line_id = p_chg.line_changes.po_line_id(i);
UPDATE po_distributions_all /* Changed po_distributions to po_distributions_all */
SET amount_changed_flag = 'Y'
WHERE po_line_id = p_chg.line_changes.po_line_id(i)
AND Nvl(amount_changed_flag, 'N') <> 'Y'
AND distribution_type = 'STANDARD';
UPDATE po_line_locations_all PLL
SET last_update_date = sysdate,
last_updated_by = g_user_id,
request_id = decode(g_request_id,null,PLL.request_id,-1,PLL.request_id,g_request_id), /*bug 7278327, update the request_id with the concerned concurrent_request_id*/
-- approved_flag = decode(approved_flag, 'Y', 'R', approved_flag),
approved_flag = decode(approved_flag, 'Y', decode(p_chg.shipment_changes.t_sec_qty_grade_change_only(i),'Y','Y','R'), approved_flag), /* sschinch 09/08 invconv */
quantity = nvl(p_chg.shipment_changes.quantity(i),quantity),
promised_date =
nvl(p_chg.shipment_changes.promised_date(i), promised_date),
price_override =
nvl(p_chg.shipment_changes.price_override(i), price_override),
need_by_date =
nvl(p_chg.shipment_changes.need_by_date(i), need_by_date),
ship_to_location_id =
nvl(p_chg.shipment_changes.ship_to_location_id(i),
ship_to_location_id),
sales_order_update_date =
nvl(p_chg.shipment_changes.sales_order_update_date(i),
sales_order_update_date),
amount = nvl(p_chg.shipment_changes.amount(i), amount),
secondary_quantity =
nvl(p_chg.shipment_changes.secondary_quantity(i), secondary_quantity),
--
manual_price_change_flag =
NVL(p_chg.shipment_changes.t_manual_price_change_flag(i),
manual_price_change_flag),
--
preferred_grade = nvl(p_chg.shipment_changes.preferred_grade(i),preferred_grade) -- sschinch 09/08 INVCONV
,tax_attribute_update_code =
NVL(tax_attribute_update_code,NVL2(g_calculate_tax_flag, 'UPDATE', null)), --
--
-- update last_accept_date also when promised_date changes
last_accept_date =
NVL(p_chg.shipment_changes.promised_date(i), promised_date)
+ days_late_receipt_allowed
WHERE p_chg.shipment_changes.po_line_location_id(i) IS NOT NULL
AND line_location_id = p_chg.shipment_changes.po_line_location_id(i);
UPDATE po_distributions_all
SET amount_changed_flag = 'Y'
WHERE line_location_id = p_chg.shipment_changes.po_line_location_id(i)
AND Nvl(amount_changed_flag, 'N') <> 'Y'
AND distribution_type = 'STANDARD';
UPDATE po_distributions_all
SET last_update_date = sysdate,
last_updated_by = g_user_id,
request_id = decode(g_request_id,null,request_id,-1,request_id,g_request_id), /*bug 7278327, update the request_id with the concerned concurrent_request_id*/
quantity_ordered =
nvl(p_chg.distribution_changes.quantity_ordered(i), quantity_ordered),
amount_ordered =
nvl(p_chg.distribution_changes.amount_ordered(i), amount_ordered)
,tax_attribute_update_code =
NVL(tax_attribute_update_code,NVL2(g_calculate_tax_flag, 'UPDATE', null)) --
WHERE p_chg.distribution_changes.po_distribution_id(i) IS NOT NULL
AND po_distribution_id = p_chg.distribution_changes.po_distribution_id(i);
UPDATE po_distributions_all
SET amount_changed_flag = 'Y'
WHERE po_distribution_id = p_chg.distribution_changes.po_distribution_id(i)
AND Nvl(amount_changed_flag, 'N') <> 'Y'
AND distribution_type = 'STANDARD';
PO_PRICE_DIFFERENTIALS_PVT.delete_price_differentials (
p_entity_type => l_po_entity_type,
p_entity_id => l_po_entity_id
);
UPDATE po_lines_all POL
SET last_update_date = sysdate,
last_updated_by = g_user_id,
retroactive_date = sysdate
WHERE POL.po_line_id = p_chg.line_changes.po_line_id(i)
AND p_chg.line_changes.unit_price(i) IS NOT NULL
AND NVL(POL.price_break_lookup_code,'NON CUMULATIVE')
= 'NON CUMULATIVE';
UPDATE po_lines_all POL
SET last_update_date = sysdate,
last_updated_by = g_user_id,
retroactive_date = sysdate
WHERE POL.po_line_id = p_chg.shipment_changes.c_po_line_id(i)
AND (p_chg.shipment_changes.price_override(i) IS NOT NULL
OR p_chg.shipment_changes.quantity(i) IS NOT NULL
OR p_chg.shipment_changes.ship_to_location_id(i) IS NOT NULL)
AND NVL(POL.price_break_lookup_code,'NON CUMULATIVE')
= 'NON CUMULATIVE';
UPDATE po_line_locations_all PLL
SET last_update_date = sysdate,
last_updated_by = g_user_id,
price_discount =
(SELECT (POL.unit_price - p_chg.shipment_changes.price_override(i))
* 100 / POL.unit_price
FROM po_lines POL
WHERE POL.po_line_id = PLL.po_line_id
AND POL.unit_price <> 0)
WHERE PLL.line_location_id
= p_chg.shipment_changes.po_line_location_id(i)
AND p_chg.shipment_changes.price_override(i) IS NOT NULL;
delete_records ( p_chg );
UPDATE po_headers_all
SET last_update_date = sysdate,
last_updated_by = g_user_id,
authorization_status =
decode(authorization_status,
'APPROVED','REQUIRES REAPPROVAL', authorization_status),
approved_flag = decode(approved_flag, 'Y', 'R', approved_flag),
revision_num = l_new_revision_num,
revised_date = decode(l_new_revision_num,
g_revision_num, revised_date, sysdate),
--
acceptance_required_flag =
NVL(l_acceptance_required_flag, acceptance_required_flag)
--
WHERE po_header_id = g_po_header_id
RETURNING authorization_status INTO l_new_auth_status; --
UPDATE po_releases_all
SET last_update_date = sysdate,
last_updated_by = g_user_id,
authorization_status =
decode(authorization_status,
'APPROVED', 'REQUIRES REAPPROVAL', authorization_status),
approved_flag = decode(approved_flag, 'Y', 'R', approved_flag),
revision_num = l_new_revision_num,
revised_date = decode(l_new_revision_num,
g_revision_num, revised_date, sysdate)
WHERE po_release_id = g_po_release_id;
SELECT PO_LINE_LOCATIONS_S.nextval
INTO l_line_location_id
FROM dual;
INSERT INTO po_line_locations_all
(
LINE_LOCATION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
PO_LINE_ID ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
QUANTITY ,
QUANTITY_RECEIVED ,
QUANTITY_ACCEPTED ,
QUANTITY_REJECTED ,
QUANTITY_BILLED ,
QUANTITY_CANCELLED ,
UNIT_MEAS_LOOKUP_CODE ,
PO_RELEASE_ID ,
SHIP_TO_LOCATION_ID ,
SHIP_VIA_LOOKUP_CODE ,
NEED_BY_DATE ,
PROMISED_DATE ,
LAST_ACCEPT_DATE ,
PRICE_OVERRIDE ,
ENCUMBERED_FLAG ,
ENCUMBERED_DATE ,
UNENCUMBERED_QUANTITY ,
FOB_LOOKUP_CODE ,
FREIGHT_TERMS_LOOKUP_CODE ,
TAXABLE_FLAG ,
ESTIMATED_TAX_AMOUNT ,
FROM_HEADER_ID ,
FROM_LINE_ID ,
FROM_LINE_LOCATION_ID ,
START_DATE ,
END_DATE ,
LEAD_TIME ,
LEAD_TIME_UNIT ,
PRICE_DISCOUNT ,
TERMS_ID ,
APPROVED_FLAG ,
APPROVED_DATE ,
CLOSED_FLAG ,
CANCEL_FLAG ,
CANCELLED_BY ,
CANCEL_DATE ,
CANCEL_REASON ,
FIRM_STATUS_LOOKUP_CODE ,
FIRM_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
UNIT_OF_MEASURE_CLASS ,
ENCUMBER_NOW ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
INSPECTION_REQUIRED_FLAG ,
RECEIPT_REQUIRED_FLAG ,
QTY_RCV_TOLERANCE ,
QTY_RCV_EXCEPTION_CODE ,
ENFORCE_SHIP_TO_LOCATION_CODE ,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
DAYS_EARLY_RECEIPT_ALLOWED ,
DAYS_LATE_RECEIPT_ALLOWED ,
RECEIPT_DAYS_EXCEPTION_CODE ,
INVOICE_CLOSE_TOLERANCE ,
RECEIVE_CLOSE_TOLERANCE ,
SHIP_TO_ORGANIZATION_ID ,
SHIPMENT_NUM ,
SOURCE_SHIPMENT_ID ,
SHIPMENT_TYPE ,
CLOSED_CODE ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
GOVERNMENT_CONTEXT ,
RECEIVING_ROUTING_ID ,
ACCRUE_ON_RECEIPT_FLAG ,
CLOSED_REASON ,
CLOSED_DATE ,
CLOSED_BY ,
ORG_ID ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
GLOBAL_ATTRIBUTE_CATEGORY ,
QUANTITY_SHIPPED ,
COUNTRY_OF_ORIGIN_CODE ,
TAX_USER_OVERRIDE_FLAG ,
MATCH_OPTION ,
TAX_CODE_ID ,
CALCULATE_TAX_FLAG ,
CHANGE_PROMISED_DATE_REASON ,
NOTE_TO_RECEIVER ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
PREFERRED_GRADE ,
SECONDARY_QUANTITY_RECEIVED ,
SECONDARY_QUANTITY_ACCEPTED ,
SECONDARY_QUANTITY_REJECTED ,
SECONDARY_QUANTITY_CANCELLED ,
VMI_FLAG ,
CONSIGNED_FLAG ,
RETROACTIVE_DATE ,
SUPPLIER_ORDER_LINE_NUMBER ,
AMOUNT ,
AMOUNT_RECEIVED ,
AMOUNT_BILLED ,
AMOUNT_CANCELLED ,
AMOUNT_REJECTED ,
AMOUNT_ACCEPTED ,
DROP_SHIP_FLAG ,
SALES_ORDER_UPDATE_DATE ,
TRANSACTION_FLOW_HEADER_ID ,
-- :
MANUAL_PRICE_CHANGE_FLAG ,
--
PAYMENT_TYPE ,
DESCRIPTION ,
QUANTITY_FINANCED ,
AMOUNT_FINANCED ,
QUANTITY_RECOUPED ,
AMOUNT_RECOUPED ,
RETAINAGE_WITHHELD_AMOUNT ,
RETAINAGE_RELEASED_AMOUNT,
OUTSOURCED_ASSEMBLY,
tax_attribute_update_code, --
original_shipment_id, --
MATCHING_BASIS, -- FPS Enhancement
VALUE_BASIS -- FPS Enhancement
)
SELECT
p_chg.shipment_changes.po_line_location_id(i), -- LINE_LOCATION_ID
sysdate , -- LAST_UPDATE_DATE
g_user_id , -- LAST_UPDATED_BY
PO_HEADER_ID ,
PO_LINE_ID ,
LAST_UPDATE_LOGIN ,
sysdate , -- CREATION_DATE
g_user_id , -- CREATED_BY
nvl(p_chg.shipment_changes.quantity(i),
QUANTITY) , -- QUANTITY
decode(quantity_received, null, null, 0) , -- QUANTITY_RECEIVED
decode(quantity_accepted, null, null, 0) , -- QUANTITY_ACCEPTED
decode(quantity_rejected, null, null, 0) , -- QUANTITY_REJECTED
decode(quantity_billed, null, null, 0) , -- QUANTITY_BILLED
decode(quantity_cancelled, null, null, 0) , -- QUANTITY_CANCELLED
UNIT_MEAS_LOOKUP_CODE ,
PO_RELEASE_ID ,
nvl(p_chg.shipment_changes.ship_to_location_id(i),
SHIP_TO_LOCATION_ID) , -- SHIP_TO_LOCATION_ID
SHIP_VIA_LOOKUP_CODE ,
nvl(p_chg.shipment_changes.need_by_date(i),
NEED_BY_DATE) , -- NEED_BY_DATE
nvl(p_chg.shipment_changes.promised_date(i),
PROMISED_DATE) , -- PROMISED_DATE
LAST_ACCEPT_DATE ,
nvl(p_chg.shipment_changes.price_override(i),
PRICE_OVERRIDE) , -- PRICE_OVERRIDE
NULL , -- ENCUMBERED_FLAG
NULL , -- ENCUMBERED_DATE
NULL , -- UNENCUMBERED_QUANTITY
FOB_LOOKUP_CODE ,
FREIGHT_TERMS_LOOKUP_CODE ,
TAXABLE_FLAG ,
decode(estimated_tax_amount, null, null, 0) , -- ESTIMATED_TAX_AMOUNT
FROM_HEADER_ID ,
FROM_LINE_ID ,
FROM_LINE_LOCATION_ID ,
START_DATE ,
END_DATE ,
LEAD_TIME ,
LEAD_TIME_UNIT ,
PRICE_DISCOUNT ,
TERMS_ID ,
'N' , -- APPROVED_FLAG
NULL , -- APPROVED_DATE
NULL , -- CLOSED_FLAG
'N' , -- CANCEL_FLAG
NULL , -- CANCELLED_BY
NULL , -- CANCEL_DATE
NULL , -- CANCEL_REASON
FIRM_STATUS_LOOKUP_CODE ,
FIRM_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
UNIT_OF_MEASURE_CLASS ,
ENCUMBER_NOW ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
INSPECTION_REQUIRED_FLAG ,
RECEIPT_REQUIRED_FLAG ,
QTY_RCV_TOLERANCE ,
QTY_RCV_EXCEPTION_CODE ,
ENFORCE_SHIP_TO_LOCATION_CODE ,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
DAYS_EARLY_RECEIPT_ALLOWED ,
DAYS_LATE_RECEIPT_ALLOWED ,
RECEIPT_DAYS_EXCEPTION_CODE ,
INVOICE_CLOSE_TOLERANCE ,
RECEIVE_CLOSE_TOLERANCE ,
SHIP_TO_ORGANIZATION_ID ,
p_chg.shipment_changes.split_shipment_num(i), -- SHIPMENT_NUM
SOURCE_SHIPMENT_ID ,
SHIPMENT_TYPE ,
'OPEN' , -- CLOSED_CODE
NULL , -- REQUEST_ID
NULL , -- PROGRAM_APPLICATION_ID
NULL , -- PROGRAM_ID
NULL , -- PROGRAM_UPDATE_DATE
GOVERNMENT_CONTEXT ,
RECEIVING_ROUTING_ID ,
ACCRUE_ON_RECEIPT_FLAG ,
NULL , -- CLOSED_REASON
NULL , -- CLOSED_DATE
NULL , -- CLOSED_BY
ORG_ID ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
GLOBAL_ATTRIBUTE_CATEGORY ,
decode(quantity_shipped, null, null, 0) , -- QUANTITY_SHIPPED
COUNTRY_OF_ORIGIN_CODE ,
TAX_USER_OVERRIDE_FLAG ,
MATCH_OPTION ,
TAX_CODE_ID ,
CALCULATE_TAX_FLAG ,
CHANGE_PROMISED_DATE_REASON ,
NOTE_TO_RECEIVER ,
NVL(p_chg.shipment_changes.secondary_quantity(i),
decode(secondary_quantity, NULL, NULL, 0)), -- SECONDARY_QUANTITY
SECONDARY_UNIT_OF_MEASURE ,
PREFERRED_GRADE ,
decode(secondary_quantity_received,
NULL, NULL, 0) , -- SECONDARY_QUANTITY_RECEIVED
decode(secondary_quantity_accepted,
NULL, NULL, 0) , -- SECONDARY_QUANTITY_ACCEPTED
decode(secondary_quantity_rejected,
NULL, NULL, 0) , -- SECONDARY_QUANTITY_REJECTED
decode(secondary_quantity_cancelled,
NULL, NULL, 0) , -- SECONDARY_QUANTITY_CANCELLED
VMI_FLAG ,
CONSIGNED_FLAG ,
NULL , -- RETROACTIVE_DATE
nvl(p_chg.shipment_changes.new_supp_order_line_no(i),
SUPPLIER_ORDER_LINE_NUMBER),
p_chg.shipment_changes.amount(i) , -- AMOUNT
decode(amount_received, null, null, 0) , -- AMOUNT_RECEIVED
decode(amount_billed, null, null, 0) , -- AMOUNT_BILLED
decode(amount_cancelled, null, null, 0) , -- AMOUNT_CANCELLED
decode(amount_rejected, null, null, 0) , -- AMOUNT_REJECTED
decode(amount_accepted, null, null, 0) , -- AMOUNT_ACCEPTED
DROP_SHIP_FLAG ,
NULL , -- SALES_ORDER_UPDATE_DATE
TRANSACTION_FLOW_HEADER_ID ,
-- :
p_chg.shipment_changes.t_manual_price_change_flag(i),
--
nvl(p_chg.shipment_changes.payment_type(i),'') ,
nvl(p_chg.shipment_changes.description(i),'') ,
decode(quantity_financed,null,null,0) ,
decode(amount_financed,null,null,0) ,
decode(quantity_recouped,null,null,0) ,
decode(amount_recouped,null,null,0) ,
decode(retainage_withheld_amount,null,null,0) ,
decode(retainage_released_amount,null,null,0),
outsourced_assembly,
nvl2(g_calculate_tax_flag, 'CREATE', null), --
line_location_id, --
decode(p_chg.shipment_changes.payment_type(i),'RATE','QUANTITY',matching_basis), -- FPS Enhancement
decode(p_chg.shipment_changes.payment_type(i),'RATE','QUANTITY',value_basis) -- FPS Enhancement
FROM po_line_locations
WHERE p_chg.shipment_changes.parent_line_location_id(i) IS NOT NULL
AND line_location_id = p_chg.shipment_changes.parent_line_location_id(i);
SELECT PO_DISTRIBUTIONS_S.nextval
INTO l_po_distribution_id
FROM dual;
INSERT INTO po_distributions_all (
PO_DISTRIBUTION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
PO_LINE_ID ,
LINE_LOCATION_ID ,
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID ,
QUANTITY_ORDERED ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
PO_RELEASE_ID ,
QUANTITY_DELIVERED ,
QUANTITY_BILLED ,
QUANTITY_CANCELLED ,
REQ_HEADER_REFERENCE_NUM ,
REQ_LINE_REFERENCE_NUM ,
REQ_DISTRIBUTION_ID ,
DELIVER_TO_LOCATION_ID ,
DELIVER_TO_PERSON_ID ,
RATE_DATE ,
RATE ,
AMOUNT_BILLED ,
ACCRUED_FLAG ,
ENCUMBERED_FLAG ,
ENCUMBERED_AMOUNT ,
UNENCUMBERED_QUANTITY ,
UNENCUMBERED_AMOUNT ,
FAILED_FUNDS_LOOKUP_CODE ,
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME ,
GL_CANCELLED_DATE ,
DESTINATION_TYPE_CODE ,
DESTINATION_ORGANIZATION_ID ,
DESTINATION_SUBINVENTORY ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
WIP_ENTITY_ID ,
WIP_OPERATION_SEQ_NUM ,
WIP_RESOURCE_SEQ_NUM ,
WIP_REPETITIVE_SCHEDULE_ID ,
WIP_LINE_ID ,
BOM_RESOURCE_ID ,
BUDGET_ACCOUNT_ID ,
ACCRUAL_ACCOUNT_ID ,
VARIANCE_ACCOUNT_ID ,
PREVENT_ENCUMBRANCE_FLAG ,
GOVERNMENT_CONTEXT ,
DESTINATION_CONTEXT ,
DISTRIBUTION_NUM ,
SOURCE_DISTRIBUTION_ID ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
PROJECT_ACCOUNTING_CONTEXT ,
EXPENDITURE_ORGANIZATION_ID ,
GL_CLOSED_DATE ,
ACCRUE_ON_RECEIPT_FLAG ,
EXPENDITURE_ITEM_DATE ,
ORG_ID ,
KANBAN_CARD_ID ,
AWARD_ID ,
MRC_RATE_DATE ,
MRC_RATE ,
MRC_ENCUMBERED_AMOUNT ,
MRC_UNENCUMBERED_AMOUNT ,
END_ITEM_UNIT_NUMBER ,
TAX_RECOVERY_OVERRIDE_FLAG ,
RECOVERABLE_TAX ,
NONRECOVERABLE_TAX ,
RECOVERY_RATE ,
OKE_CONTRACT_LINE_ID ,
OKE_CONTRACT_DELIVERABLE_ID ,
AMOUNT_ORDERED ,
AMOUNT_DELIVERED ,
AMOUNT_CANCELLED ,
DISTRIBUTION_TYPE ,
AMOUNT_TO_ENCUMBER ,
INVOICE_ADJUSTMENT_FLAG ,
DEST_CHARGE_ACCOUNT_ID ,
DEST_VARIANCE_ACCOUNT_ID ,
tax_attribute_update_code --
)
SELECT
p_chg.distribution_changes.po_distribution_id(i), -- PO_DISTRIBUTION_ID
sysdate , -- LAST_UPDATE_DATE
g_user_id , -- LAST_UPDATED_BY
PO_HEADER_ID ,
PO_LINE_ID ,
p_chg.distribution_changes.c_line_location_id(i), -- LINE_LOCATION_ID
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID ,
nvl(p_chg.distribution_changes.quantity_ordered(i),
QUANTITY_ORDERED) , -- QUANTITY_ORDERED
LAST_UPDATE_LOGIN ,
sysdate , -- CREATION_DATE
g_user_id , -- CREATED_BY
PO_RELEASE_ID ,
decode(quantity_delivered, null, null, 0) , -- QUANTITY_DELIVERED
decode(quantity_billed, null, null, 0) , -- QUANTITY_BILLED
decode(quantity_cancelled, null, null, 0) , -- QUANTITY_CANCELLED
REQ_HEADER_REFERENCE_NUM ,
REQ_LINE_REFERENCE_NUM ,
-- bug 5750240 : the Req distribution id for the split
-- shipment should not be carried from the parent dist.
NULL,
-- REQ_DISTRIBUTION_ID ,
DELIVER_TO_LOCATION_ID ,
DELIVER_TO_PERSON_ID ,
RATE_DATE ,
RATE ,
decode(amount_billed, null, null, 0) , -- AMOUNT_BILLED
ACCRUED_FLAG ,
'N' , -- ENCUMBERED_FLAG Bug 5558172 changed NULL to 'N' so that the distriution will be visble in enter po form.
NULL , -- ENCUMBERED_AMOUNT
NULL , -- UNENCUMBERED_QUANTITY
NULL , -- UNENCUMBERED_AMOUNT
NULL , -- FAILED_FUNDS_LOOKUP_CODE
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME ,
NULL , -- GL_CANCELLED_DATE
DESTINATION_TYPE_CODE ,
DESTINATION_ORGANIZATION_ID ,
DESTINATION_SUBINVENTORY ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
WIP_ENTITY_ID ,
WIP_OPERATION_SEQ_NUM ,
WIP_RESOURCE_SEQ_NUM ,
WIP_REPETITIVE_SCHEDULE_ID ,
WIP_LINE_ID ,
BOM_RESOURCE_ID ,
BUDGET_ACCOUNT_ID ,
ACCRUAL_ACCOUNT_ID ,
VARIANCE_ACCOUNT_ID ,
PREVENT_ENCUMBRANCE_FLAG ,
GOVERNMENT_CONTEXT ,
DESTINATION_CONTEXT ,
DISTRIBUTION_NUM ,
SOURCE_DISTRIBUTION_ID ,
NULL , -- REQUEST_ID
NULL , -- PROGRAM_APPLICATION_ID
NULL , -- PROGRAM_ID
NULL , -- PROGRAM_UPDATE_DATE
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
PROJECT_ACCOUNTING_CONTEXT ,
EXPENDITURE_ORGANIZATION_ID ,
NULL , -- GL_CLOSED_DATE
ACCRUE_ON_RECEIPT_FLAG ,
EXPENDITURE_ITEM_DATE ,
ORG_ID ,
KANBAN_CARD_ID ,
NULL , -- AWARD_ID
MRC_RATE_DATE ,
MRC_RATE ,
NULL , -- MRC_ENCUMBERED_AMOUNT
NULL , -- MRC_UNENCUMBERED_AMOUNT
END_ITEM_UNIT_NUMBER ,
TAX_RECOVERY_OVERRIDE_FLAG ,
--
null , -- RECOVERABLE_TAX
null , -- NONRECOVERABLE_TAX
decode(tax_recovery_override_flag,'Y',recovery_rate, null) , -- RECOVERY_RATE
--
OKE_CONTRACT_LINE_ID ,
OKE_CONTRACT_DELIVERABLE_ID ,
nvl(p_chg.distribution_changes.amount_ordered(i),
AMOUNT_ORDERED) , -- AMOUNT_ORDERED
decode(amount_delivered, null, null, 0) , -- AMOUNT_DELIVERED
decode(amount_cancelled, null, null, 0) , -- AMOUNT_CANCELLED
DISTRIBUTION_TYPE ,
AMOUNT_TO_ENCUMBER ,
NULL , -- INVOICE_ADJUSTMENT_FLAG
DEST_CHARGE_ACCOUNT_ID ,
DEST_VARIANCE_ACCOUNT_ID ,
nvl2(g_calculate_tax_flag, 'CREATE', null) --
FROM po_distributions
WHERE p_chg.distribution_changes.parent_distribution_id(i) IS NOT NULL
AND po_distribution_id =
p_chg.distribution_changes.parent_distribution_id(i);
UPDATE po_distributions
SET last_update_date = sysdate,
last_updated_by = g_user_id,
award_id = l_gms_po_obj.award_set_id_out(i)
WHERE po_distributions.po_distribution_id
= l_gms_po_obj.distribution_id(i);
PROCEDURE delete_records (
p_chg IN PO_CHANGES_REC_TYPE
) IS
l_proc_name CONSTANT VARCHAR2(30) := 'DELETE_RECORDS';
IF (p_chg.distribution_changes.delete_record(i) = G_PARAMETER_YES) THEN
SELECT rowid
INTO l_rowid
FROM po_distributions
WHERE po_distribution_id
= p_chg.distribution_changes.po_distribution_id(i);
PO_DISTRIBUTIONS_PKG2.delete_row ( l_rowid );
IF (p_chg.shipment_changes.delete_record(i) = G_PARAMETER_YES) THEN
/*bug # 6329352 Modified the below query such that it will select
po_line_id and quantity fro the shipment whose delete record flag is set to Yes*/
SELECT rowid,po_line_id,quantity
INTO l_rowid, l_po_line_id, l_quantity
FROM po_line_locations
WHERE line_location_id = p_chg.shipment_changes.po_line_location_id(i);
PO_SHIPMENTS_SV4.delete_shipment (
x_line_location_id => p_chg.shipment_changes.po_line_location_id(i),
x_row_id => l_rowid,
x_doc_header_id => g_po_header_id,
x_shipment_type => p_chg.shipment_changes.c_shipment_type(i)
);
UPDATE po_lines SET quantity = quantity - l_quantity WHERE po_line_id = l_po_line_id; --bug # 6329352
IF (p_chg.line_changes.delete_price_adjs(i) = G_PARAMETER_YES) THEN
PO_PRICE_ADJUSTMENTS_PKG.delete_price_adjustments(
p_po_header_id => p_chg.po_header_id,
p_po_line_id => p_chg.line_changes.po_line_id(i)
);
IF (p_chg.line_changes.delete_record(i) = G_PARAMETER_YES) THEN
SELECT rowid
INTO l_rowid
FROM po_lines
WHERE po_line_id = p_chg.line_changes.po_line_id(i);
PO_LINES_SV.delete_line (
x_type_lookup_code => g_document_subtype,
x_po_line_id => p_chg.line_changes.po_line_id(i),
x_row_id => l_rowid
);
END delete_records;
p_mass_update_releases IN VARCHAR2,
p_retroactive_price_change IN VARCHAR2
) IS
l_proc_name CONSTANT VARCHAR2(30) := 'LAUNCH_PO_APPROVAL_WF';
|| ' mass_update_releases: '||p_mass_update_releases
|| ' retroactive_price: '||p_retroactive_price_change );
MassUpdateReleases => p_mass_update_releases, -- Bug 3373453
RetroactivePriceChange => p_retroactive_price_change,
p_background_flag => NVL(p_approval_background_flag, G_PARAMETER_NO)
);
g_line_changes_index.DELETE;
g_ship_changes_index.DELETE;
g_dist_changes_index.DELETE;
FND_MSG_PUB.delete_msg ( p_msg_index => i );
PROCEDURE validate_delete_action( p_entity IN VARCHAR2
,p_doc_type IN VARCHAR2
,p_doc_header_id IN NUMBER
,p_po_line_id IN NUMBER
,p_line_loc_id IN NUMBER
,p_distribution_id IN NUMBER
,x_error_message OUT NOCOPY VARCHAR2)
IS
l_modify_action_allowed BOOLEAN := FALSE;
l_allow_delete VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_action';
d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_UPDATE_PVT.validate_delete_action';
SELECT type_lookup_code
,nvl(closed_code,'OPEN')
,approved_date
,approved_flag
,nvl(frozen_flag,'N')
,nvl(cancel_flag,'N')
,nvl(authorization_status,'INCOMPLETE')
,nvl(global_agreement_flag, 'N')
,nvl(conterms_exist_flag, 'N')
,nvl(consigned_consumption_flag, 'N')
INTO l_doc_subtype
,l_closed_code
,l_doc_approved_date
,l_doc_approved_flag
,l_frozen_flag
,l_cancel_flag
,l_auth_status
,l_ga_flag
,l_conterms_exist_flag
,l_consigned_consumption_flag
FROM po_headers_all
WHERE po_header_id = p_doc_header_id;
x_error_message := PO_CORE_S.get_translated_text('PO_RQ_DOC_UPDATE_NA');
x_error_message := PO_CORE_S.get_translated_text('PO_CONSIGNED_UPDATE_ERROR');
x_error_message := PO_CORE_S.get_translated_text('PO_RQ_DOC_UPDATE_NA');
PO_HEADERS_SV1.validate_delete_document(
p_doc_type => p_doc_type
,p_doc_header_id => p_doc_header_id
,p_doc_approved_date => l_doc_approved_date
,p_auth_status => l_auth_status
,p_style_disp_name => l_style_disp_name
,x_message_text => x_error_message);
,x_allow_delete => l_allow_delete
,p_token => 'DOCUMENT_TYPE'
,p_token_value => l_style_disp_name
,x_message_text => x_error_message);
PO_SHIPMENTS_SV4.validate_delete_line_loc(
p_line_loc_id => p_line_loc_id
,p_po_line_id => p_po_line_id
,p_doc_type => p_doc_type
,p_style_disp_name => l_style_disp_name
,x_message_text => x_error_message);
PO_DISTRIBUTIONS_SV.validate_delete_distribution(
p_po_distribution_id => p_distribution_id
,p_line_loc_id => p_line_loc_id
,p_approved_date => l_doc_approved_date
,p_style_disp_name => l_style_disp_name
,x_message_text => x_error_message);
END validate_delete_action;
PROCEDURE process_delete_action( p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_calling_program IN VARCHAR2
,p_entity IN VARCHAR2
,p_entity_row_id IN ROWID
,p_doc_type IN VARCHAR2
,p_doc_subtype IN VARCHAR2
,p_doc_header_id IN NUMBER
,p_ga_flag IN VARCHAR2
,p_conterms_exist_flag IN VARCHAR2
,p_po_line_id IN NUMBER
,p_line_loc_id IN NUMBER
,p_distribution_id IN NUMBER
,x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000)
IS
l_error_message VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(30) := 'process_delete_action';
d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_UPDATE_PVT.process_delete_action';
SAVEPOINT PO_PROCESS_DELETE_ACTION;
validate_delete_action( p_entity => p_entity
,p_doc_type => p_doc_type
,p_doc_header_id => p_doc_header_id
,p_po_line_id => p_po_line_id
,p_line_loc_id => p_line_loc_id
,p_distribution_id => p_distribution_id
,x_error_message => l_error_message);
PO_HEADERS_SV1.delete_document( p_doc_type => p_doc_type
,p_doc_subtype => p_doc_subtype
,p_doc_header_id => p_doc_header_id
,p_ga_flag => p_ga_flag
,p_conterms_exist_flag => p_conterms_exist_flag
,x_return_status => x_return_status);
SELECT ROWID
INTO l_entity_row_id
FROM PO_LINES_ALL
WHERE PO_LINE_ID = p_po_line_id;
PO_LINES_SV.delete_line( X_type_lookup_code => p_doc_subtype
,X_po_line_id => p_po_line_id
,X_row_id => l_entity_row_id
,p_skip_validation => 'Y'); --skip validations as we already have called validate_delete_action
PO_SHIPMENTS_SV4.process_delete_line_loc(
p_line_loc_id => p_line_loc_id
,p_line_loc_row_id => p_entity_row_id
,p_po_header_id => p_doc_header_id
,p_po_line_id => p_po_line_id
,p_doc_subtype => p_doc_subtype);
PO_DISTRIBUTIONS_PKG2.delete_row(x_rowid => p_entity_row_id);
ROLLBACK TO PO_PROCESS_DELETE_ACTION;
ROLLBACK TO PO_PROCESS_DELETE_ACTION;
ROLLBACK TO PO_PROCESS_DELETE_ACTION;
END process_delete_action;