[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ NO_INDEX(DRAFT_LINES PO_LINES_DRAFT_N0) */
intf_locs.interface_line_location_id,
intf_locs.interface_line_id,
intf_locs.interface_header_id,
intf_locs.shipment_num,
intf_locs.shipment_type,
intf_locs.line_location_id,
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.payment_terms,
intf_locs.terms_id,
intf_locs.receiving_routing,
intf_locs.receiving_routing_id,
intf_locs.inspection_required_flag,
intf_locs.receipt_required_flag,
intf_locs.price_override,
intf_locs.qty_rcv_tolerance,
intf_locs.qty_rcv_exception_code,
intf_locs.enforce_ship_to_location_code,
intf_locs.allow_substitute_receipts_flag,
intf_locs.days_early_receipt_allowed,
intf_locs.days_late_receipt_allowed,
intf_locs.receipt_days_exception_code,
intf_locs.invoice_close_tolerance,
intf_locs.receive_close_tolerance,
intf_locs.accrue_on_receipt_flag,
intf_locs.firm_flag,
intf_locs.fob,
intf_locs.freight_carrier,
intf_locs.freight_terms,
intf_locs.need_by_date,
intf_locs.promised_date,
intf_locs.quantity,
intf_locs.start_date,
intf_locs.end_date,
intf_locs.note_to_receiver,
intf_locs.price_discount,
intf_locs.tax_code_id,
intf_locs.tax_name,
intf_locs.secondary_quantity,
intf_locs.secondary_unit_of_measure,
intf_locs.preferred_grade,
intf_locs.unit_of_measure,
intf_locs.value_basis,
intf_locs.matching_basis,
-- attributes in txn table but not in intf table
NULL, -- outsourced_assembly - no such column in intf table
NULL, -- invoice match option - no such column in intf table
NULL, -- txn_flow_header_id
NULL, -- tax_attribute_update_code
-- standard who columns
intf_locs.last_updated_by,
intf_locs.last_update_date,
intf_locs.last_update_login,
intf_locs.creation_date,
intf_locs.created_by,
intf_locs.request_id,
intf_locs.program_application_id,
intf_locs.program_id,
intf_locs.program_update_date,
-- attributes read from the line record
draft_lines.po_line_id,
draft_lines.item_id,
draft_lines.order_type_lookup_code,
intf_lines.action,
draft_lines.unit_price,
draft_lines.line_type_id,
draft_lines.unit_meas_lookup_code,
draft_lines.closed_code,
draft_lines.purchase_basis,
draft_lines.matching_basis,
draft_lines.item_revision,
draft_lines.expiration_date,
draft_lines.government_context,
draft_lines.closed_reason,
draft_lines.closed_date,
draft_lines.closed_by,
draft_lines.from_header_id,
draft_lines.from_line_id,
draft_lines.price_break_lookup_code, -- bug5016163
-- attributes read from the header record
intf_headers.draft_id,
intf_headers.po_header_id,
NVL(draft_headers.ship_to_location_id, txn_headers.ship_to_location_id),
NVL(draft_headers.vendor_id, txn_headers.vendor_id),
NVL(draft_headers.vendor_site_id, txn_headers.vendor_site_id),
NVL(draft_headers.terms_id, txn_headers.terms_id),
NVL(draft_headers.fob_lookup_code, txn_headers.fob_lookup_code),
NVL(draft_headers.ship_via_lookup_code, txn_headers.ship_via_lookup_code),
NVL(draft_headers.freight_terms_lookup_code, txn_headers.freight_terms_lookup_code),
NVL(draft_headers.approved_flag, txn_headers.approved_flag),
NVL(draft_headers.start_date, txn_headers.start_date),
NVL(draft_headers.end_date, txn_headers.end_date),
NVL(draft_headers.style_id, txn_headers.style_id),
-- set initial value for error_flag
FND_API.g_FALSE
FROM po_line_locations_interface intf_locs,
po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_lines_draft_all draft_lines,
po_headers_draft_all draft_headers,
po_headers_all txn_headers
WHERE intf_locs.interface_line_id = intf_lines.interface_line_id
AND intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_lines.po_line_id = draft_lines.po_line_id
AND intf_headers.draft_id = draft_lines.draft_id
AND draft_lines.po_header_id = draft_headers.po_header_id(+)
AND draft_lines.draft_id = draft_headers.draft_id(+)
AND draft_lines.po_header_id = txn_headers.po_header_id(+)
AND intf_locs.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
AND intf_locs.interface_line_location_id > p_max_intf_line_loc_id
AND NVL(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
<> PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
ORDER BY 1;
x_line_locs.tax_attribute_update_code_tbl,
-- standard who columns
x_line_locs.last_updated_by_tbl,
x_line_locs.last_update_date_tbl,
x_line_locs.last_update_login_tbl,
x_line_locs.creation_date_tbl,
x_line_locs.created_by_tbl,
x_line_locs.request_id_tbl,
x_line_locs.program_application_id_tbl,
x_line_locs.program_id_tbl,
x_line_locs.program_update_date_tbl,
-- attributes read from the line record
x_line_locs.ln_po_line_id_tbl,
x_line_locs.ln_item_id_tbl,
x_line_locs.ln_order_type_lookup_code_tbl,
x_line_locs.ln_action_tbl,
x_line_locs.ln_unit_price_tbl,
x_line_locs.ln_line_type_id_tbl,
x_line_locs.ln_unit_of_measure_tbl,
x_line_locs.ln_closed_code_tbl,
x_line_locs.ln_purchase_basis_tbl,
x_line_locs.ln_matching_basis_tbl,
x_line_locs.ln_item_revision_tbl,
x_line_locs.ln_expiration_date_tbl,
x_line_locs.ln_government_context_tbl,
x_line_locs.ln_closed_reason_tbl,
x_line_locs.ln_closed_date_tbl,
x_line_locs.ln_closed_by_tbl,
x_line_locs.ln_from_header_id_tbl,
x_line_locs.ln_from_line_id_tbl,
x_line_locs.ln_price_break_lookup_code_tbl,
-- attributes read from the header record
x_line_locs.draft_id_tbl,
x_line_locs.hd_po_header_id_tbl,
x_line_locs.hd_ship_to_loc_id_tbl,
x_line_locs.hd_vendor_id_tbl,
x_line_locs.hd_vendor_site_id_tbl,
x_line_locs.hd_terms_id_tbl,
x_line_locs.hd_fob_tbl,
x_line_locs.hd_freight_carrier_tbl,
x_line_locs.hd_freight_term_tbl,
x_line_locs.hd_approved_flag_tbl,
x_line_locs.hd_effective_date_tbl,
x_line_locs.hd_expiration_date_tbl,
x_line_locs.hd_style_id_tbl,
-- set initial value for error_flag
x_line_locs.error_flag_tbl
LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
x_last_update_date_tbl => x_line_locs.last_update_date_tbl,
x_last_updated_by_tbl => x_line_locs.last_updated_by_tbl,
x_last_update_login_tbl => x_line_locs.last_update_login_tbl,
x_creation_date_tbl => x_line_locs.creation_date_tbl,
x_created_by_tbl => x_line_locs.created_by_tbl,
x_request_id_tbl => x_line_locs.request_id_tbl,
x_program_application_id_tbl => x_line_locs.program_application_id_tbl,
x_program_id_tbl => x_line_locs.program_id_tbl,
x_program_update_date_tbl => x_line_locs.program_update_date_tbl
);
l_parameter_name_tbl(1) := 'CREATE_OR_UPDATE_ITEM';
PROCEDURE update_line_loc_interface
(
p_intf_line_loc_id_tbl IN PO_TBL_NUMBER,
p_line_loc_id_tbl IN PO_TBL_NUMBER,
p_error_flag_tbl IN PO_TBL_VARCHAR1
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_line_loc_interface';
UPDATE po_line_locations_interface
SET line_location_id = p_line_loc_id_tbl(i),
process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
WHERE interface_line_location_id = p_intf_line_loc_id_tbl(i)
AND p_error_flag_tbl(i) = FND_API.g_FALSE;
END update_line_loc_interface;
PROCEDURE update_amount_quantity_on_line
(
p_po_line_id_tbl IN DBMS_SQL.NUMBER_TABLE,
p_draft_id_tbl IN DBMS_SQL.NUMBER_TABLE
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_amount_quantity_on_line';
UPDATE po_lines_draft_all draft_lines
SET (amount, quantity) =
(select sum(amount), sum(quantity)
FROM po_line_locations_draft_all
WHERE po_line_id = draft_lines.po_line_id
AND draft_id = draft_lines.draft_id)
WHERE po_line_id = p_po_line_id_tbl(i)
AND draft_id = p_draft_id_tbl(i);
END update_amount_quantity_on_line;
PROCEDURE delete_exist_price_breaks
(
p_po_line_id_tbl IN DBMS_SQL.NUMBER_TABLE,
p_draft_id_tbl IN DBMS_SQL.NUMBER_TABLE
) IS
d_api_name CONSTANT VARCHAR2(30) := 'delete_exist_price_breaks';
l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
INSERT INTO po_session_gt(key, num1, num2)
SELECT l_key,
line_location_id,
p_draft_id_tbl(i)
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2 BULK COLLECT INTO l_line_loc_id_tbl, l_draft_id_tbl;
l_delete_flag_tbl.EXTEND(l_line_loc_id_tbl.COUNT);
l_delete_flag_tbl(i) := 'Y';
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exist_tbl
);
END delete_exist_price_breaks;
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
line_location_id
FROM po_line_locations
WHERE p_shipment_num_tbl(i) IS NOT NULL
AND x_line_loc_id_tbl(i) IS NULL
AND po_line_id = p_po_line_id_tbl(i)
AND shipment_num = p_shipment_num_tbl(i)
AND shipment_type = 'PRICE BREAK';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
organization_id
FROM org_organization_definitions
WHERE p_ship_to_org_code_tbl(i) IS NOT NULL
AND x_ship_to_org_id_tbl(i) IS NULL
AND organization_code = p_ship_to_org_code_tbl(i)
AND TRUNC(sysdate) < nvl(disable_date, TRUNC(sysdate+1))
AND inventory_enabled_flag = 'Y';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
routing_header_id
FROM rcv_routing_headers
WHERE p_receiving_routing_tbl(i) IS NOT NULL
AND x_receiving_routing_id_tbl(i) IS NULL
AND routing_name = p_receiving_routing_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
INSERT INTO po_session_gt(key, num1, char1)
SELECT p_key,
p_index_tbl(i),
tax_classification_code
FROM zx_id_tcc_mapping
WHERE p_tax_code_id_tbl(i) IS NOT NULL
AND x_tax_name_tbl(i) IS NULL
AND tax_rate_code_id = p_tax_code_id_tbl(i)
AND source = 'AP'
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(effective_from, sysdate))
AND TRUNC(NVL(effective_to, sysdate));
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
x_line_locs.ln_action_tbl(i) <> PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
x_line_locs.price_override_tbl(i) := x_line_locs.ln_unit_price_tbl(i);
x_line_locs.tax_attribute_update_code_tbl(i) := 'CREATE';
INSERT INTO po_session_gt(key, num1, char1)
SELECT p_key,
p_index_tbl(i),
inspection_required_flag
FROM mtl_system_items
WHERE p_item_id_tbl(i) IS NOT NULL
AND x_inspection_required_flag_tbl(i) IS NULL
AND inventory_item_id = p_item_id_tbl(i)
AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
inventory_organization_id
FROM hr_locations_v
WHERE p_ship_to_loc_id_tbl(i) IS NOT NULL
AND x_ship_to_org_id_tbl(i) IS NULL
AND location_id = p_ship_to_loc_id_tbl(i)
AND ship_to_site_flag = 'Y';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
INSERT INTO po_session_gt(key, num1, num2, num3)
SELECT p_key,
p_index_tbl(i),
invoice_close_tolerance,
receive_close_tolerance
FROM mtl_system_items
WHERE p_item_id_tbl(i) IS NOT NULL
AND (x_invoice_close_tolerance_tbl(i) IS NULL OR
x_receive_close_tolerance_tbl(i) IS NULL)
AND inventory_item_id = p_item_id_tbl(i)
AND organization_id = p_ship_to_org_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_invoice_tolerance_tbl, l_receive_tolerance_tbl;
INSERT INTO po_session_gt(key, num1, num2, num3)
SELECT p_key,
p_index_tbl(i),
invoice_close_tolerance,
receive_close_tolerance
FROM mtl_system_items
WHERE p_item_id_tbl(i) IS NOT NULL
AND (x_invoice_close_tolerance_tbl(i) IS NULL OR
x_receive_close_tolerance_tbl(i) IS NULL)
AND inventory_item_id = p_item_id_tbl(i)
AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_invoice_tolerance_tbl, l_receive_tolerance_tbl;
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
receipt_close
FROM po_line_types_v
WHERE p_line_type_id_tbl(i) IS NOT NULL
AND x_receive_close_tolerance_tbl(i) IS NULL
AND line_type_id = p_line_type_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO
l_index_tbl, l_receive_tolerance_tbl;
INSERT INTO po_session_gt(key, num1, char1)
SELECT p_key,
p_index_tbl(i),
match_option
FROM po_vendor_sites
WHERE p_vendor_site_id_tbl(i) IS NOT NULL
AND x_match_option_tbl(i) IS NULL
AND vendor_site_id = p_vendor_site_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
INSERT INTO po_session_gt(key, num1, char1)
SELECT p_key,
p_index_tbl(i),
match_option
FROM po_vendors
WHERE p_vendor_id_tbl(i) IS NOT NULL
AND x_match_option_tbl(i) IS NULL
AND vendor_id = p_vendor_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
INSERT INTO po_session_gt(key, num1, char1, char2)
SELECT p_key,
p_index_tbl(i),
outside_operation_flag,
nvl(stock_enabled_flag,'N')
FROM mtl_system_items
WHERE p_item_id_tbl(i) IS NOT NULL
AND x_accrue_on_receipt_flag_tbl(i) IS NULL
AND inventory_item_id = p_item_id_tbl(i)
AND organization_id = p_ship_to_org_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1, char2 BULK COLLECT INTO
l_index_tbl, l_outside_op_flag_tbl, l_stock_enabled_flag_tbl;
INSERT INTO po_session_gt(key, num1, char1)
SELECT p_key,
p_index_tbl(i),
uom.unit_of_measure
FROM mtl_system_items item,
mtl_units_of_measure uom
WHERE p_item_id_tbl(i) IS NOT NULL
AND p_ship_to_org_id_tbl(i) IS NOT NULL
AND x_secondary_unit_of_meas_tbl(i) IS NULL
AND item.inventory_item_id = p_item_id_tbl(i)
AND item.organization_id = p_ship_to_org_id_tbl(i)
AND item.tracking_quantity_ind = 'PS'
AND item.secondary_uom_code = uom.uom_code;
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
DELETE FROM PO_INTERFACE_ERRORS
WHERE interface_line_location_id = l_remove_err_line_loc_tbl(i)
AND interface_line_id = l_remove_err_line_tbl(i);