[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_update_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE
);
PROCEDURE insert_master_item
(
p_org_type IN VARCHAR2,
p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
);
PROCEDURE update_master_item
(
p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
);
l_update_index_tbl DBMS_SQL.NUMBER_TABLE;
l_unprocessed_row_tbl.DELETE(i);
x_update_index_tbl => l_update_index_tbl
);
l_index := l_update_index_tbl.FIRST;
PO_LOG.stmt(d_module, d_position, 'l_update_index_tbl('||l_index||')',
l_update_index_tbl(l_index));
l_index := l_update_index_tbl.NEXT(l_index);
insert_master_item
(
p_org_type => 'MASTER',
p_index_tbl => l_create_in_master_index_tbl,
x_items => l_items
);
insert_master_item
(
p_org_type => 'INV',
p_index_tbl => l_create_in_inv_index_tbl,
x_items => l_items
);
insert_master_item
(
p_org_type => 'SHIP_TO',
p_index_tbl => l_create_in_ship_to_index_tbl,
x_items => l_items
);
update_master_item
(
p_index_tbl => l_update_index_tbl,
x_items => l_items
);
l_unprocessed_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1, -- index
num2, -- interface_line_id
num3, -- po_header_id,
num4 -- draft_id
)
SELECT
l_key,
l_index_tbl(i),
p_lines.intf_line_id_tbl(i),
p_lines.hd_po_header_id_tbl(i),
p_lines.draft_id_tbl(i)
FROM DUAL
WHERE p_lines.error_flag_tbl(i) = FND_API.g_FALSE
AND p_lines.need_to_reject_flag_tbl(i) = FND_API.g_FALSE
AND p_lines.purchase_basis_tbl(i) NOT IN ('TEMP LABOR', 'SERVICES')
AND (p_lines.item_tbl(i) IS NOT NULL OR
p_lines.item_id_tbl(i) IS NOT NULL);
/* Bug 6926550 modified the where clause to select
only matched key records from po_session_gt*/
SELECT
-- attributes from headers
NVL(draft_headers.ship_to_location_id, txn_headers.ship_to_location_id),
NVL(draft_headers.vendor_id, txn_headers.vendor_id),
-- attributes from line interface
intf_lines.unit_weight,
intf_lines.unit_volume,
intf_lines.item_attribute_category,
intf_lines.item_attribute1,
intf_lines.item_attribute2,
intf_lines.item_attribute3,
intf_lines.item_attribute4,
intf_lines.item_attribute5,
intf_lines.item_attribute6,
intf_lines.item_attribute7,
intf_lines.item_attribute8,
intf_lines.item_attribute9,
intf_lines.item_attribute10,
intf_lines.item_attribute11,
intf_lines.item_attribute12,
intf_lines.item_attribute13,
intf_lines.item_attribute14,
intf_lines.item_attribute15,
-- attributes from location inteface
intf_locs.inspection_required_flag,
intf_locs.receipt_required_flag,
intf_locs.invoice_close_tolerance,
intf_locs.receive_close_tolerance,
intf_locs.days_early_receipt_allowed,
intf_locs.days_late_receipt_allowed,
intf_locs.enforce_ship_to_location_code,
intf_locs.allow_substitute_receipts_flag,
intf_locs.receiving_routing,
intf_locs.receiving_routing_id,
intf_locs.receipt_days_exception_code,
intf_locs.ship_to_organization_code,
intf_locs.ship_to_organization_id,
intf_locs.ship_to_location,
intf_locs.ship_to_location_id,
intf_locs.taxable_flag,
intf_locs.qty_rcv_exception_code,
intf_locs.qty_rcv_tolerance,
-- assign dummay values on these columns
-- so they won't be defaulted in location default logic
'DUMMY', -- shipment_type
0, -- shipment_num
0, -- line_location_id
'DUMMY', -- match_option
NULL, -- accrue_on_receipt_flag
NULL, -- firm_flag
NULL, -- tax_name
NULL, -- payment_terms
NULL, -- terms_id
NULL, -- header terms_id
NULL, -- fob
NULL, -- header fob
NULL, -- freight_carrier
NULL, -- header freight_carrier
NULL, -- freight_term
NULL, -- header freight_term
-1, -- price_override
-1, -- price_discount
-1, -- outsourced_assembly
NULL, -- value_basis
NULL, -- matching_basis
NULL, -- unit_of_measure
-- standard who columns
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
-- error_flag
FND_API.g_FALSE,
-- reference index in po_lines
gt.num1
BULK COLLECT INTO
-- attributes from headers
x_items.hd_ship_to_loc_id_tbl,
x_items.hd_vendor_id_tbl,
-- attributes from lines
x_items.ln_unit_weight_tbl,
x_items.ln_unit_volume_tbl,
x_items.ln_item_attribute_category_tbl,
x_items.ln_item_attribute1_tbl,
x_items.ln_item_attribute2_tbl,
x_items.ln_item_attribute3_tbl,
x_items.ln_item_attribute4_tbl,
x_items.ln_item_attribute5_tbl,
x_items.ln_item_attribute6_tbl,
x_items.ln_item_attribute7_tbl,
x_items.ln_item_attribute8_tbl,
x_items.ln_item_attribute9_tbl,
x_items.ln_item_attribute10_tbl,
x_items.ln_item_attribute11_tbl,
x_items.ln_item_attribute12_tbl,
x_items.ln_item_attribute13_tbl,
x_items.ln_item_attribute14_tbl,
x_items.ln_item_attribute15_tbl,
-- attributes from location inteface
x_items.inspection_required_flag_tbl,
x_items.receipt_required_flag_tbl,
x_items.invoice_close_tolerance_tbl,
x_items.receive_close_tolerance_tbl,
x_items.days_early_receipt_allowed_tbl,
x_items.days_late_receipt_allowed_tbl,
x_items.enforce_ship_to_loc_code_tbl,
x_items.allow_sub_receipts_flag_tbl,
x_items.receiving_routing_tbl,
x_items.receiving_routing_id_tbl,
x_items.receipt_days_except_code_tbl,
x_items.ship_to_org_code_tbl,
x_items.ship_to_org_id_tbl,
x_items.ship_to_loc_tbl,
x_items.ship_to_loc_id_tbl,
x_items.taxable_flag_tbl,
x_items.qty_rcv_exception_code_tbl,
x_items.qty_rcv_tolerance_tbl,
-- columns with dummay non-empty values
x_items.shipment_type_tbl,
x_items.shipment_num_tbl,
x_items.line_loc_id_tbl,
x_items.match_option_tbl,
x_items.accrue_on_receipt_flag_tbl,
x_items.firm_flag_tbl,
x_items.tax_name_tbl,
x_items.payment_terms_tbl,
x_items.terms_id_tbl,
x_items.hd_terms_id_tbl,
x_items.fob_tbl,
x_items.hd_fob_tbl,
x_items.freight_carrier_tbl,
x_items.hd_freight_carrier_tbl,
x_items.freight_term_tbl,
x_items.hd_freight_term_tbl,
x_items.price_override_tbl,
x_items.price_discount_tbl,
x_items.outsourced_assembly_tbl,
x_items.value_basis_tbl,
x_items.matching_basis_tbl,
x_items.unit_of_measure_tbl,
-- standard who columns
x_items.last_update_date_tbl,
x_items.last_updated_by_tbl,
x_items.last_update_login_tbl,
x_items.creation_date_tbl,
x_items.created_by_tbl,
x_items.request_id_tbl,
x_items.program_application_id_tbl,
x_items.program_id_tbl,
x_items.program_update_date_tbl,
-- error flag
x_items.error_flag_tbl,
-- reference index in p_lines
x_items.line_ref_index_tbl
FROM po_line_locations_interface intf_locs,
po_lines_interface intf_lines,
po_headers_draft_all draft_headers,
po_headers_all txn_headers,
po_session_gt gt
WHERE gt.num2 = intf_lines.interface_line_id
AND intf_lines.interface_line_id = intf_locs.interface_line_id
AND intf_locs.processing_id = PO_PDOI_PARAMS.g_processing_id
AND gt.num3 = draft_headers.po_header_id(+)
AND gt.num4 = draft_headers.draft_id(+)
AND gt.num3 = txn_headers.po_header_id(+)
AND gt.key = l_key
ORDER BY gt.num1, intf_locs.interface_line_location_id;
x_update_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE
) IS
d_api_name CONSTANT VARCHAR2(30) := 'identify_actions';
x_unprocessed_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
l_key,
x_proc_row_in_round_tbl(i),
organization_id,
inventory_item_id
FROM mtl_system_items_vl
WHERE organization_id IN
(PO_PDOI_PARAMS.g_sys.def_inv_org_id,
PO_PDOI_PARAMS.g_sys.master_inv_org_id,
x_items.ship_to_org_id_tbl(i)
)
AND (concatenated_segments = x_items.ln_item_tbl(i)
OR inventory_item_id = x_items.ln_item_id_tbl(i)) ; --6956962
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_org_id_tbl, l_item_id_tbl;
x_update_index_tbl(l_index) := l_index;
PROCEDURE insert_master_item
(
p_org_type IN VARCHAR2,
p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
) IS
d_api_name CONSTANT VARCHAR2(30) := 'insert_master_item';
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
process_flag,
set_process_id,
transaction_type,
item_number,
inventory_item_id,
description,
purchasing_item_flag,
inventory_item_flag,
purchasing_enabled_flag,
primary_unit_of_measure,
list_price_per_unit,
market_price,
un_number_id,
hazard_class_id,
taxable_flag,
inspection_required_flag,
receipt_required_flag,
invoice_close_tolerance,
receive_close_tolerance,
days_early_receipt_allowed,
days_late_receipt_allowed,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
receiving_routing_id,
qty_rcv_tolerance,
qty_rcv_exception_code,
receipt_days_exception_code,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
organization_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
unit_weight,
weight_uom_code,
volume_uom_code,
unit_volume,
template_id
)
VALUES
(
1, -- process_flag
l_set_process_id_tbl(i),
'CREATE', -- transaction_type
x_items.ln_item_tbl(i),
x_items.ln_item_id_tbl(i),
x_items.ln_item_desc_tbl(i),
'Y', -- purchasing_item_flag
decode(x_items.ln_template_id_tbl(i), NULL, 'Y', NULL), -- inventory_item_flag
'Y', -- purchasing_enabled_flag,
x_items.ln_unit_of_measure_tbl(i),
x_items.ln_list_price_per_unit_tbl(i),
x_items.ln_market_price_tbl(i),
x_items.ln_un_number_id_tbl(i),
x_items.ln_hazard_class_id_tbl(i),
x_items.taxable_flag_tbl(i),
x_items.inspection_required_flag_tbl(i),
x_items.receipt_required_flag_tbl(i),
x_items.invoice_close_tolerance_tbl(i),
x_items.receive_close_tolerance_tbl(i),
x_items.days_early_receipt_allowed_tbl(i),
x_items.days_late_receipt_allowed_tbl(i),
x_items.enforce_ship_to_loc_code_tbl(i),
x_items.allow_sub_receipts_flag_tbl(i),
x_items.receiving_routing_id_tbl(i),
x_items.qty_rcv_tolerance_tbl(i),
x_items.qty_rcv_exception_code_tbl(i),
x_items.receipt_days_except_code_tbl(i),
x_items.last_update_date_tbl(i),
x_items.last_updated_by_tbl(i),
x_items.last_update_login_tbl(i),
x_items.creation_date_tbl(i),
x_items.created_by_tbl(i),
x_items.request_id_tbl(i),
x_items.program_application_id_tbl(i),
x_items.program_id_tbl(i),
x_items.program_update_date_tbl(i),
DECODE(p_org_type, 'MASTER', PO_PDOI_PARAMS.g_sys.master_inv_org_id,
'INV', PO_PDOI_PARAMS.g_sys.def_inv_org_id,
x_items.ship_to_org_id_tbl(i)), -- organization_id
x_items.ln_item_attribute_category_tbl(i),
x_items.ln_item_attribute1_tbl(i),
x_items.ln_item_attribute2_tbl(i),
x_items.ln_item_attribute3_tbl(i),
x_items.ln_item_attribute4_tbl(i),
x_items.ln_item_attribute5_tbl(i),
x_items.ln_item_attribute6_tbl(i),
x_items.ln_item_attribute7_tbl(i),
x_items.ln_item_attribute8_tbl(i),
x_items.ln_item_attribute9_tbl(i),
x_items.ln_item_attribute10_tbl(i),
x_items.ln_item_attribute11_tbl(i),
x_items.ln_item_attribute12_tbl(i),
x_items.ln_item_attribute13_tbl(i),
x_items.ln_item_attribute14_tbl(i),
x_items.ln_item_attribute15_tbl(i),
x_items.ln_unit_weight_tbl(i),
x_items.ln_weight_uom_code_tbl(i),
x_items.ln_volume_uom_code_tbl(i),
x_items.ln_unit_volume_tbl(i),
x_items.ln_template_id_tbl(i)
);
del_rec_flag => 2, -- do not delete the record
prog_appid => fnd_global.prog_appl_id,
prog_id => -1, -- Inventory does not gather statistics when processing the records inserted into its interface table
request_id => fnd_global.conc_request_id,
user_id => fnd_global.user_id,
login_id => fnd_global.login_id,
err_text => l_err_text,
xset_id => l_set_process_id_tbl(l_index),
commit_flag => 2 -- no commit
);
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3,
num4,
num5,
num6,
char1
)
SELECT
l_key,
p_index_tbl(i),
set_process_id,
process_flag,
transaction_id,
inventory_item_id,
organization_id,
revision
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2, num3, num4, num5, num6, char1
BULK COLLECT INTO
l_index_tbl,
l_set_process_id_tbl,
l_process_flag_tbl,
l_transaction_id_tbl,
l_item_id_tbl,
l_org_id_tbl,
l_revision_tbl;
l_index_tbl.DELETE(i);
UPDATE mtl_item_categories
SET category_id = l_category_id_tbl(i)
WHERE inventory_item_id = l_item_id_tbl(i)
AND organization_id = l_org_id_tbl(i)
AND category_set_id = PO_PDOI_PARAMS.g_sys.def_cat_set_id;
DELETE FROM mtl_system_items_interface
WHERE transaction_id = l_transaction_id_tbl(i)
AND set_process_id = l_set_process_id_tbl(i);
DELETE FROM mtl_item_categories_interface
WHERE inventory_item_id = l_item_id_tbl(i)
AND organization_id = l_org_id_tbl(i);
DELETE FROM mtl_item_revisions_interface
WHERE inventory_item_id = l_item_id_tbl(i)
AND organization_id = l_org_id_tbl(i)
AND revision = l_revision_tbl(i);
INSERT INTO po_session_gt
(
key,
num1,
char1,
char2,
char3
)
SELECT
l_key,
l_error_index_tbl(i),
table_name,
message_name,
column_name
FROM mtl_interface_errors
WHERE transaction_id = l_transaction_id_tbl(i)
OR transaction_id = (
SELECT transaction_id
FROM mtl_item_categories_interface
WHERE organization_id = l_org_id_tbl(i)
AND inventory_item_id = l_item_id_tbl(i))
OR transaction_id = (
SELECT transaction_id
FROM mtl_item_revisions_interface
WHERE organization_id = l_org_id_tbl(i)
AND inventory_item_id = l_item_id_tbl(i)
AND revision = l_revision_tbl(i));
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, char1, char2, char3 BULK COLLECT INTO
l_error_index_tbl,
l_table_name_tbl,
l_message_name_tbl,
l_column_name_tbl;
END insert_master_item;
PROCEDURE update_master_item
(
p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_master_item';
l_update_index_tbl DBMS_SQL.NUMBER_TABLE;
l_update_index_tbl := p_index_tbl;
FORALL i IN INDICES OF l_update_index_tbl
INSERT INTO po_session_gt(key, num1, char1, num2)
SELECT l_key,
l_update_index_tbl(i),
description,
list_price_per_unit
FROM mtl_system_items
WHERE inventory_item_id = x_items.ln_item_id_tbl(i)
AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, char1, num2 BULK COLLECT INTO
l_index_tbl, l_orig_desc_tbl, l_orig_list_price_tbl;
l_update_index_tbl.DELETE(l_index);
l_update_index_tbl.DELETE(l_index);
l_index := l_update_index_tbl.FIRST;
p_Transaction_Type => 'UPDATE',
p_Inventory_Item_Id => x_items.ln_item_id_tbl(l_index),
p_Organization_Id => PO_PDOI_PARAMS.g_sys.def_inv_org_id,
p_description => NVL(x_items.ln_item_desc_tbl(l_index), EGO_ITEM_PUB.G_MISS_CHAR),
p_list_price_per_unit => NVL(x_items.ln_list_price_per_unit_tbl(l_index), EGO_ITEM_PUB.G_MISS_NUM),
p_Item_Number => x_items.ln_item_tbl(l_index),
x_Inventory_Item_Id => l_inventory_item_id,
x_Organization_Id => l_organization_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
PO_LOG.stmt(d_module, d_position, 'return status for item update',
l_return_status);
l_index := l_update_index_tbl.NEXT(l_index);
END update_master_item;