[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT intf_headers.interface_header_id,
intf_lines.interface_line_id,
intf_lines.line_num
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_lines txn_lines
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.po_header_id = txn_lines.po_header_id
AND intf_lines.processing_id = p_request_processing_id
AND intf_headers.processing_round_num = p_request_processing_round_num
AND intf_headers.processing_id = p_request_processing_id
AND intf_lines.line_num = txn_lines.line_num
AND intf_headers.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
SELECT intf_headers.interface_header_id,
intf_lines.interface_line_id,
intf_lines.line_num
FROM po_headers_interface intf_headers,
po_lines_interface intf_lines,
po_lines_draft_all draft_lines
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.draft_id = draft_lines.draft_id
AND intf_headers.po_header_id = draft_lines.po_header_id
AND intf_lines.processing_id = p_request_processing_id
AND intf_headers.processing_round_num = p_request_processing_round_num
AND intf_headers.processing_id = p_request_processing_id
AND intf_lines.line_num = draft_lines.line_num
AND intf_headers.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
SELECT intf_lines.interface_line_id,
intf_headers.interface_header_id,
intf_lines.action
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_lines.processing_id = p_request_processing_id
AND intf_headers.processing_round_num = p_request_processing_round_num
AND intf_headers.processing_id = p_request_processing_id
AND NVL(intf_lines.action, PO_PDOI_CONSTANTS.g_ACTION_ADD) <>
PO_PDOI_CONSTANTS.g_ACTION_ADD;
SELECT intf_lines.interface_line_id,
intf_lines.interface_header_id,
intf_lines.po_header_id,
NULL, -- intf_lines.po_line_id,
intf_lines.action,
intf_lines.document_num,
intf_lines.item,
intf_lines.vendor_product_num,
intf_lines.supplier_part_auxid,
intf_lines.item_id,
intf_lines.item_revision,
intf_lines.job_business_group_name,
intf_lines.job_business_group_id,
intf_lines.job_name,
intf_lines.job_id,
intf_lines.category,
intf_lines.category_id,
intf_lines.ip_category_name,
intf_lines.ip_category_id,
intf_lines.uom_code,
intf_lines.unit_of_measure,
intf_lines.line_type,
intf_lines.line_type_id,
intf_lines.un_number,
intf_lines.un_number_id,
intf_lines.hazard_class,
intf_lines.hazard_class_id,
intf_lines.template_name,
intf_lines.template_id,
intf_lines.item_description,
intf_lines.unit_price,
intf_lines.base_unit_price,
intf_lines.from_header_id,
intf_lines.from_line_id,
intf_lines.list_price_per_unit,
intf_lines.market_price,
intf_lines.capital_expense_flag,
intf_lines.min_release_amount,
intf_lines.allow_price_override_flag,
intf_lines.price_type,
intf_lines.price_break_lookup_code,
intf_lines.closed_code,
intf_lines.quantity,
intf_lines.line_num,
intf_lines.shipment_num,
intf_lines.price_chg_accept_flag,
intf_lines.effective_date,
intf_lines.expiration_date,
intf_lines.line_attribute14,
intf_lines.price_update_tolerance,
intf_lines.line_loc_populated_flag,
intf_lines.negotiated_by_preparer_flag,
intf_lines.amount,
intf_lines.contractor_last_name,
intf_lines.contractor_first_name,
intf_lines.over_tolerance_error_flag,
intf_lines.not_to_exceed_price,
intf_lines.po_release_id,
intf_lines.release_num,
intf_lines.source_shipment_id,
intf_lines.contract_num,
intf_lines.contract_id,
intf_lines.type_1099,
intf_lines.closed_by,
intf_lines.closed_date,
intf_lines.committed_amount,
intf_lines.qty_rcv_exception_code,
intf_lines.weight_uom_code,
intf_lines.volume_uom_code,
intf_lines.secondary_unit_of_measure,
intf_lines.secondary_quantity,
intf_lines.preferred_grade,
intf_lines.process_code,
NULL, -- parent_interface_line_id -- bug5149827
intf_lines.file_line_language, -- bug 5489942
-- standard who columns
intf_lines.last_updated_by,
intf_lines.last_update_date,
intf_lines.last_update_login,
intf_lines.creation_date,
intf_lines.created_by,
intf_lines.request_id,
intf_lines.program_application_id,
intf_lines.program_id,
intf_lines.program_update_date,
-- attributes read from headers
intf_headers.draft_id,
intf_headers.action,
intf_headers.po_header_id,
draft_headers.vendor_id,
draft_headers.min_release_amount,
draft_headers.start_date,
draft_headers.end_date,
draft_headers.global_agreement_flag,
draft_headers.currency_code,
draft_headers.created_language,
draft_headers.style_id,
draft_headers.rate_type,
-- txn table columns
NULL, -- order_type_lookup_code
NULL, -- purchase_basis
NULL, -- matching_basis
NULL, -- unordered_flag
NULL, -- cancel_flag
NULL, -- quantity_committed
NULL, -- tax_attribute_update_code
DECODE(intf_lines.process_code,
PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT,
FND_API.g_TRUE, FND_API.g_FALSE), -- error_flag_tbl
FND_API.g_FALSE, -- need_to_reject_flag_tbl
FND_API.g_FALSE, -- create_line_loc_tbl
-1, -- group_num
NULL, -- origin_line_num
FND_API.g_FALSE, -- match_line_found
NULL -- allow_desc_update_flag_tbl
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_headers_draft_all draft_headers
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.draft_id = draft_headers.draft_id
AND intf_headers.po_header_id = draft_headers.po_header_id
AND intf_lines.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_lines.interface_line_id > p_max_intf_line_id
AND intf_headers.action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
PO_PDOI_CONSTANTS.g_ACTION_REPLACE)
UNION ALL
SELECT intf_lines.interface_line_id,
intf_lines.interface_header_id,
intf_lines.po_header_id,
NULL, -- intf_lines.po_line_id,
intf_lines.action,
intf_lines.document_num,
intf_lines.item,
intf_lines.vendor_product_num,
intf_lines.supplier_part_auxid,
intf_lines.item_id,
intf_lines.item_revision,
intf_lines.job_business_group_name,
intf_lines.job_business_group_id,
intf_lines.job_name,
intf_lines.job_id,
intf_lines.category,
intf_lines.category_id,
intf_lines.ip_category_name,
intf_lines.ip_category_id,
intf_lines.uom_code,
intf_lines.unit_of_measure,
intf_lines.line_type,
intf_lines.line_type_id,
intf_lines.un_number,
intf_lines.un_number_id,
intf_lines.hazard_class,
intf_lines.hazard_class_id,
intf_lines.template_name,
intf_lines.template_id,
intf_lines.item_description,
intf_lines.unit_price,
intf_lines.base_unit_price,
intf_lines.from_header_id,
intf_lines.from_line_id,
intf_lines.list_price_per_unit,
intf_lines.market_price,
intf_lines.capital_expense_flag,
intf_lines.min_release_amount,
intf_lines.allow_price_override_flag,
intf_lines.price_type,
intf_lines.price_break_lookup_code,
intf_lines.closed_code,
intf_lines.quantity,
intf_lines.line_num,
intf_lines.shipment_num,
intf_lines.price_chg_accept_flag,
intf_lines.effective_date,
intf_lines.expiration_date,
intf_lines.line_attribute14,
intf_lines.price_update_tolerance,
intf_lines.line_loc_populated_flag,
intf_lines.negotiated_by_preparer_flag,
intf_lines.amount,
intf_lines.contractor_last_name,
intf_lines.contractor_first_name,
intf_lines.over_tolerance_error_flag,
intf_lines.not_to_exceed_price,
intf_lines.po_release_id,
intf_lines.release_num,
intf_lines.source_shipment_id,
intf_lines.contract_num,
intf_lines.contract_id,
intf_lines.type_1099,
intf_lines.closed_by,
intf_lines.closed_date,
intf_lines.committed_amount,
intf_lines.qty_rcv_exception_code,
intf_lines.weight_uom_code,
intf_lines.volume_uom_code,
intf_lines.secondary_unit_of_measure,
intf_lines.secondary_quantity,
intf_lines.preferred_grade,
intf_lines.process_code,
NULL, -- parent_interface_line_id -- bug5149827
intf_lines.file_line_language, -- bug 5489942
-- standard who columns
intf_lines.last_updated_by,
intf_lines.last_update_date,
intf_lines.last_update_login,
intf_lines.creation_date,
intf_lines.created_by,
intf_lines.request_id,
intf_lines.program_application_id,
intf_lines.program_id,
intf_lines.program_update_date,
-- attributes read from header
intf_headers.draft_id,
intf_headers.action,
intf_headers.po_header_id,
txn_headers.vendor_id,
txn_headers.min_release_amount,
txn_headers.start_date,
txn_headers.end_date,
txn_headers.global_agreement_flag,
txn_headers.currency_code,
txn_headers.created_language,
txn_headers.style_id,
txn_headers.rate_type,
-- txn table columns
NULL, -- order_type_lookup_code
NULL, -- purchase_basis
NULL, -- matching_basis
NULL, -- unordered_flag
NULL, -- cancel_flag
NULL, -- quantity_committed
NULL, -- tax_attribute_update_code
DECODE(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT,
FND_API.g_TRUE, FND_API.g_FALSE), -- error_flag_tbl
FND_API.G_FALSE, -- need_to_reject_flag_tbl
FND_API.g_FALSE, -- create_line_loc_tbl
-1, -- group_num
NULL, -- origin_line_num
FND_API.g_FALSE, -- match_line_found
NULL -- allow_desc_update_flag_tbl
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_headers txn_headers
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.po_header_id = txn_headers.po_header_id
AND intf_lines.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_lines.interface_line_id > p_max_intf_line_id
AND intf_headers.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE
AND PO_PDOI_PARAMS.g_request.document_type = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD
ORDER BY 1;
SELECT intf_lines.interface_line_id,
intf_lines.interface_header_id,
intf_lines.po_header_id,
NULL, -- intf_lines.po_line_id,
intf_lines.action,
intf_lines.document_num,
intf_lines.item,
intf_lines.vendor_product_num,
intf_lines.supplier_part_auxid,
intf_lines.item_id,
intf_lines.item_revision,
intf_lines.job_business_group_name,
intf_lines.job_business_group_id,
intf_lines.job_name,
intf_lines.job_id,
intf_lines.category,
intf_lines.category_id,
intf_lines.ip_category_name,
intf_lines.ip_category_id,
intf_lines.uom_code,
intf_lines.unit_of_measure,
intf_lines.line_type,
intf_lines.line_type_id,
intf_lines.un_number,
intf_lines.un_number_id,
intf_lines.hazard_class,
intf_lines.hazard_class_id,
intf_lines.template_name,
intf_lines.template_id,
intf_lines.item_description,
intf_lines.unit_price,
intf_lines.base_unit_price,
intf_lines.from_header_id,
intf_lines.from_line_id,
intf_lines.list_price_per_unit,
intf_lines.market_price,
intf_lines.capital_expense_flag,
intf_lines.min_release_amount,
intf_lines.allow_price_override_flag,
intf_lines.price_type,
intf_lines.price_break_lookup_code,
intf_lines.closed_code,
intf_lines.quantity,
intf_lines.line_num,
intf_lines.shipment_num,
intf_lines.price_chg_accept_flag,
intf_lines.effective_date,
intf_lines.expiration_date,
intf_lines.line_attribute14,
intf_lines.price_update_tolerance,
intf_lines.line_loc_populated_flag,
intf_lines.negotiated_by_preparer_flag,
intf_lines.amount,
intf_lines.contractor_last_name,
intf_lines.contractor_first_name,
intf_lines.over_tolerance_error_flag,
intf_lines.not_to_exceed_price,
intf_lines.po_release_id,
intf_lines.release_num,
intf_lines.source_shipment_id,
intf_lines.contract_num,
intf_lines.contract_id,
intf_lines.type_1099,
intf_lines.closed_by,
intf_lines.closed_date,
intf_lines.committed_amount,
intf_lines.qty_rcv_exception_code,
intf_lines.weight_uom_code,
intf_lines.volume_uom_code,
intf_lines.secondary_unit_of_measure,
intf_lines.secondary_quantity,
intf_lines.preferred_grade,
intf_lines.process_code,
NULL, -- parent_interface_line_id -- bug5149827
intf_lines.file_line_language, -- bug 5489942
-- standard who columns
intf_lines.last_updated_by,
intf_lines.last_update_date,
intf_lines.last_update_login,
intf_lines.creation_date,
intf_lines.created_by,
intf_lines.request_id,
intf_lines.program_application_id,
intf_lines.program_id,
intf_lines.program_update_date,
-- attributes read from header
intf_headers.draft_id,
intf_headers.action,
intf_headers.po_header_id,
txn_headers.vendor_id,
txn_headers.min_release_amount,
txn_headers.start_date,
txn_headers.end_date,
txn_headers.global_agreement_flag,
txn_headers.currency_code,
txn_headers.created_language,
txn_headers.style_id,
txn_headers.rate_type,
-- txn table columns
NULL, -- order_type_lookup_code
NULL, -- purchase_basis
NULL, -- matching_basis
NULL, -- unordered_flag
NULL, -- cancel_flag
NULL, -- quantity_committed
NULL, -- tax_attribute_update_code
DECODE(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT,
FND_API.g_TRUE, FND_API.g_FALSE), -- error_flag_tbl
FND_API.G_FALSE, -- need_to_reject_flag_tbl
FND_API.g_FALSE, -- create_line_loc_tbl
-1, -- group_num
NULL, -- origin_line_num
FND_API.g_FALSE, -- match_line_found
NULL -- allow_desc_update_flag_tbl
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_headers txn_headers
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.po_header_id = txn_headers.po_header_id
AND intf_lines.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_lines.interface_line_id > p_max_intf_line_id
AND intf_headers.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE
AND PO_PDOI_PARAMS.g_request.document_type IN
(PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION)
AND intf_lines.action = PO_PDOI_CONSTANTS.g_ACTION_ADD
ORDER BY intf_lines.interface_line_id;
SELECT intf_lines.interface_line_id,
intf_lines.interface_header_id,
intf_lines.po_header_id,
NULL, -- intf_lines.po_line_id,
intf_lines.action,
intf_lines.document_num,
intf_lines.item,
intf_lines.vendor_product_num,
intf_lines.supplier_part_auxid,
intf_lines.item_id,
intf_lines.item_revision,
intf_lines.job_business_group_name,
intf_lines.job_business_group_id,
intf_lines.job_name,
intf_lines.job_id,
intf_lines.category,
intf_lines.category_id,
intf_lines.ip_category_name,
intf_lines.ip_category_id,
intf_lines.uom_code,
intf_lines.unit_of_measure,
intf_lines.line_type,
intf_lines.line_type_id,
intf_lines.un_number,
intf_lines.un_number_id,
intf_lines.hazard_class,
intf_lines.hazard_class_id,
intf_lines.template_name,
intf_lines.template_id,
intf_lines.item_description,
intf_lines.unit_price,
intf_lines.base_unit_price,
intf_lines.from_header_id,
intf_lines.from_line_id,
intf_lines.list_price_per_unit,
intf_lines.market_price,
intf_lines.capital_expense_flag,
intf_lines.min_release_amount,
intf_lines.allow_price_override_flag,
intf_lines.price_type,
intf_lines.price_break_lookup_code,
intf_lines.closed_code,
intf_lines.quantity,
intf_lines.line_num,
intf_lines.shipment_num,
intf_lines.price_chg_accept_flag,
intf_lines.effective_date,
intf_lines.expiration_date,
intf_lines.line_attribute14,
intf_lines.price_update_tolerance,
intf_lines.line_loc_populated_flag,
intf_lines.negotiated_by_preparer_flag,
intf_lines.amount,
intf_lines.contractor_last_name,
intf_lines.contractor_first_name,
intf_lines.over_tolerance_error_flag,
intf_lines.not_to_exceed_price,
intf_lines.po_release_id,
intf_lines.release_num,
intf_lines.source_shipment_id,
intf_lines.contract_num,
intf_lines.contract_id,
intf_lines.type_1099,
intf_lines.closed_by,
intf_lines.closed_date,
intf_lines.committed_amount,
intf_lines.qty_rcv_exception_code,
intf_lines.weight_uom_code,
intf_lines.volume_uom_code,
intf_lines.secondary_unit_of_measure,
intf_lines.secondary_quantity,
intf_lines.preferred_grade,
intf_lines.process_code,
NULL, -- parent_interface_line_id -- bug5149827
intf_lines.file_line_language, -- bug 5489942
-- standard who columns
intf_lines.last_updated_by,
intf_lines.last_update_date,
intf_lines.last_update_login,
intf_lines.creation_date,
intf_lines.created_by,
intf_lines.request_id,
intf_lines.program_application_id,
intf_lines.program_id,
intf_lines.program_update_date,
-- attributes read from header
intf_headers.draft_id,
intf_headers.action,
intf_headers.po_header_id,
txn_headers.vendor_id,
txn_headers.min_release_amount,
txn_headers.start_date,
txn_headers.end_date,
txn_headers.global_agreement_flag,
txn_headers.currency_code,
txn_headers.created_language,
txn_headers.style_id,
txn_headers.rate_type,
-- txn table columns
NULL, -- order_type_lookup_code
NULL, -- purchase_basis
NULL, -- matching_basis
NULL, -- unordered_flag
NULL, -- cancel_flag
NULL, -- quantity_committed
NULL, -- tax_attribute_update_code
DECODE(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT,
FND_API.g_TRUE, FND_API.g_FALSE), -- error_flag_tbl
FND_API.G_FALSE, -- need_to_reject_flag_tbl
FND_API.g_FALSE, -- create_line_loc_tbl
-1, -- group_num
NULL, -- origin_line_num
FND_API.g_FALSE, -- match_line_found
NULL -- allow_desc_update_flag_tbl
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_headers txn_headers
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.po_header_id = txn_headers.po_header_id
AND intf_lines.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_lines.interface_line_id > p_max_intf_line_id
AND intf_headers.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE
AND PO_PDOI_PARAMS.g_request.document_type IN
(PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION)
AND intf_lines.action IS NULL
AND NOT (intf_lines.item IS NULL AND
intf_lines.vendor_product_num IS NULL AND
intf_lines.job_name IS NULL AND
intf_lines.item_description IS NOT NULL)
ORDER BY intf_lines.interface_line_id;
SELECT intf_lines.interface_line_id,
intf_lines.interface_header_id,
intf_lines.po_header_id,
NULL, -- intf_lines.po_line_id,
intf_lines.action,
intf_lines.document_num,
intf_lines.item,
intf_lines.vendor_product_num,
intf_lines.supplier_part_auxid,
intf_lines.item_id,
intf_lines.item_revision,
intf_lines.job_business_group_name,
intf_lines.job_business_group_id,
intf_lines.job_name,
intf_lines.job_id,
intf_lines.category,
intf_lines.category_id,
intf_lines.ip_category_name,
intf_lines.ip_category_id,
intf_lines.uom_code,
intf_lines.unit_of_measure,
intf_lines.line_type,
intf_lines.line_type_id,
intf_lines.un_number,
intf_lines.un_number_id,
intf_lines.hazard_class,
intf_lines.hazard_class_id,
intf_lines.template_name,
intf_lines.template_id,
intf_lines.item_description,
intf_lines.unit_price,
intf_lines.base_unit_price,
intf_lines.from_header_id,
intf_lines.from_line_id,
intf_lines.list_price_per_unit,
intf_lines.market_price,
intf_lines.capital_expense_flag,
intf_lines.min_release_amount,
intf_lines.allow_price_override_flag,
intf_lines.price_type,
intf_lines.price_break_lookup_code,
intf_lines.closed_code,
intf_lines.quantity,
intf_lines.line_num,
intf_lines.shipment_num,
intf_lines.price_chg_accept_flag,
intf_lines.effective_date,
intf_lines.expiration_date,
intf_lines.line_attribute14,
intf_lines.price_update_tolerance,
intf_lines.line_loc_populated_flag,
intf_lines.negotiated_by_preparer_flag,
intf_lines.amount,
intf_lines.contractor_last_name,
intf_lines.contractor_first_name,
intf_lines.over_tolerance_error_flag,
intf_lines.not_to_exceed_price,
intf_lines.po_release_id,
intf_lines.release_num,
intf_lines.source_shipment_id,
intf_lines.contract_num,
intf_lines.contract_id,
intf_lines.type_1099,
intf_lines.closed_by,
intf_lines.closed_date,
intf_lines.committed_amount,
intf_lines.qty_rcv_exception_code,
intf_lines.weight_uom_code,
intf_lines.volume_uom_code,
intf_lines.secondary_unit_of_measure,
intf_lines.secondary_quantity,
intf_lines.preferred_grade,
intf_lines.process_code,
NULL, -- parent_interface_line_id -- bug5149827
intf_lines.file_line_language, -- bug 5489942
-- standard who columns
intf_lines.last_updated_by,
intf_lines.last_update_date,
intf_lines.last_update_login,
intf_lines.creation_date,
intf_lines.created_by,
intf_lines.request_id,
intf_lines.program_application_id,
intf_lines.program_id,
intf_lines.program_update_date,
-- attributes read from header
intf_headers.draft_id,
intf_headers.action,
intf_headers.po_header_id,
txn_headers.vendor_id,
txn_headers.min_release_amount,
txn_headers.start_date,
txn_headers.end_date,
txn_headers.global_agreement_flag,
txn_headers.currency_code,
txn_headers.created_language,
txn_headers.style_id,
txn_headers.rate_type,
-- txn table columns
NULL, -- order_type_lookup_code
NULL, -- purchase_basis
NULL, -- matching_basis
NULL, -- unordered_flag
NULL, -- cancel_flag
NULL, -- quantity_committed
NULL, -- tax_attribute_update_code
DECODE(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT,
FND_API.g_TRUE, FND_API.g_FALSE), -- error_flag_tbl
FND_API.G_FALSE, -- need_to_reject_flag_tbl
FND_API.g_FALSE, -- create_line_loc_tbl
-1, -- group_num
NULL, -- origin_line_num
FND_API.g_FALSE, -- match_line_found
NULL -- allow_desc_update_flag_tbl
FROM po_lines_interface intf_lines,
po_headers_interface intf_headers,
po_headers txn_headers
WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
AND intf_headers.po_header_id = txn_headers.po_header_id
AND intf_lines.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_lines.interface_line_id > p_max_intf_line_id
AND intf_headers.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE
AND PO_PDOI_PARAMS.g_request.document_type IN
(PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION)
AND intf_lines.action IS NULL
AND intf_lines.item IS NULL
AND intf_lines.vendor_product_num IS NULL
AND intf_lines.job_name IS NULL
AND intf_lines.item_description IS NOT NULL
ORDER BY intf_lines.interface_line_id;
x_lines.price_update_tolerance_tbl,
x_lines.line_loc_populated_flag_tbl,
x_lines.negotiated_flag_tbl,
x_lines.amount_tbl,
x_lines.contractor_last_name_tbl,
x_lines.contractor_first_name_tbl,
x_lines.over_tolerance_err_flag_tbl,
x_lines.not_to_exceed_price_tbl,
x_lines.po_release_id_tbl,
x_lines.release_num_tbl,
x_lines.source_shipment_id_tbl,
x_lines.contract_num_tbl,
x_lines.contract_id_tbl,
x_lines.type_1099_tbl,
x_lines.closed_by_tbl,
x_lines.closed_date_tbl,
x_lines.committed_amount_tbl,
x_lines.qty_rcv_exception_code_tbl,
x_lines.weight_uom_code_tbl,
x_lines.volume_uom_code_tbl,
x_lines.secondary_unit_of_meas_tbl,
x_lines.secondary_quantity_tbl,
x_lines.preferred_grade_tbl,
x_lines.process_code_tbl,
x_lines.parent_interface_line_id_tbl, -- bug5149827
x_lines.file_line_language_tbl, -- bug 5489942
-- standard who columns
x_lines.last_updated_by_tbl,
x_lines.last_update_date_tbl,
x_lines.last_update_login_tbl,
x_lines.creation_date_tbl,
x_lines.created_by_tbl,
x_lines.request_id_tbl,
x_lines.program_application_id_tbl,
x_lines.program_id_tbl,
x_lines.program_update_date_tbl,
-- attributes read from headers
x_lines.draft_id_tbl,
x_lines.hd_action_tbl,
x_lines.hd_po_header_id_tbl,
x_lines.hd_vendor_id_tbl,
x_lines.hd_min_release_amount_tbl,
x_lines.hd_start_date_tbl,
x_lines.hd_end_date_tbl,
x_lines.hd_global_agreement_flag_tbl,
x_lines.hd_currency_code_tbl,
x_lines.hd_created_language_tbl,
x_lines.hd_style_id_tbl,
x_lines.hd_rate_type_tbl,
-- txn table columns
x_lines.order_type_lookup_code_tbl,
x_lines.purchase_basis_tbl,
x_lines.matching_basis_tbl,
x_lines.unordered_flag_tbl,
x_lines.cancel_flag_tbl,
x_lines.quantity_committed_tbl,
x_lines.tax_attribute_update_code_tbl,
x_lines.error_flag_tbl,
x_lines.need_to_reject_flag_tbl,
x_lines.create_line_loc_tbl,
x_lines.group_num_tbl,
x_lines.origin_line_num_tbl,
x_lines.match_line_found_tbl,
x_lines.allow_desc_update_flag_tbl -- bug5107324
LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
PROCEDURE derive_lines_for_update
(
x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
) IS
d_api_name CONSTANT VARCHAR2(30) := 'derive_lines_for_update';
END derive_lines_for_update;
x_lines.tax_attribute_update_code_tbl(i) := 'CREATE';
x_last_update_date_tbl => x_lines.last_update_date_tbl,
x_last_updated_by_tbl => x_lines.last_updated_by_tbl,
x_last_update_login_tbl => x_lines.last_update_login_tbl,
x_creation_date_tbl => x_lines.creation_date_tbl,
x_created_by_tbl => x_lines.created_by_tbl,
x_request_id_tbl => x_lines.request_id_tbl,
x_program_application_id_tbl => x_lines.program_application_id_tbl,
x_program_id_tbl => x_lines.program_id_tbl,
x_program_update_date_tbl => x_lines.program_update_date_tbl
);
PROCEDURE default_lines_for_update
(
x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
) IS
d_api_name CONSTANT VARCHAR2(30) := 'default_lines_for_update';
INSERT INTO po_session_gt(key, num1, char1, num2, num3)
SELECT l_key,
l_num_list(i),
order_type_lookup_code,
item_id,
job_id
FROM po_lines_draft_all
WHERE po_line_id = x_lines.po_line_id_tbl(i)
AND draft_id = x_lines.draft_id_tbl(i);
INSERT INTO po_session_gt(key, num1, char1, num2, num3)
SELECT l_key,
l_num_list(i),
order_type_lookup_code,
item_id,
job_id
FROM po_lines_all
WHERE po_line_id = x_lines.po_line_id_tbl(i)
AND NOT EXISTS (SELECT 1
FROM po_lines_draft_all
WHERE po_line_id = x_lines.po_line_id_tbl(i)
AND draft_id = x_lines.draft_id_tbl(i));
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, char1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_order_type_tbl, l_item_id_tbl, l_job_id_tbl;
END default_lines_for_update;
l_update_lines PO_PDOI_TYPES.lines_rec_type;
x_update_lines => l_update_lines
);
PO_LOG.stmt(d_module, d_position, 'num of updated lines', l_update_lines.rec_count);
FORALL i IN 1..l_update_lines.rec_count
UPDATE po_lines_interface
SET po_line_id = l_update_lines.po_line_id_tbl(i),
price_break_flag = 'Y'
WHERE interface_line_id = l_update_lines.intf_line_id_tbl(i);
FOR i IN 1..l_update_lines.rec_count
LOOP
IF (PO_LOG.d_stmt) THEN
PO_LOG.stmt(d_module, d_position, 'index', i);
l_update_lines.create_line_loc_tbl(i));
IF (l_update_lines.create_line_loc_tbl(i) = FND_API.g_FALSE) THEN
PO_PDOI_ERR_UTL.add_fatal_error
(
p_interface_header_id => l_update_lines.intf_header_id_tbl(i),
p_interface_line_id => l_update_lines.intf_line_id_tbl(i),
p_error_message_name => 'PO_PDOI_INVALID_INTER_LINE_REC',
p_table_name => 'PO_LINES_INTERFACE',
p_column_name => 'CREATE_PO_LINES_FLAG',
p_column_value => 'N',
p_token1_name => 'COLUMN_NAME',
p_token1_value => 'CREATE_PO_LINES_FLAG',
p_token2_name => 'VALUE',
p_token2_value => 'N'
);
l_rej_intf_line_id_tbl(l_rej_intf_line_id_tbl.COUNT) := l_update_lines.intf_line_id_tbl(i);
l_update_lines.intf_line_id_tbl(i));
x_lines.action_tbl(i) = PO_PDOI_CONSTANTS.g_ACTION_UPDATE AND
(x_lines.quantity_tbl(i) > 0 OR x_lines.shipment_num_tbl(i) IS NOT NULL)) THEN
x_lines.create_line_loc_tbl(i) := FND_API.g_TRUE;
UPDATE po_line_locations_interface
SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_OBSOLETE
WHERE interface_line_id = l_obsoleted_loc_tbl(i);
PROCEDURE update_line_intf_tbl
(
x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_line_intf_tbl';
PO_LOG.stmt(d_module, d_position, 'update line process code to ACCEPTED. Index = ', i);
UPDATE po_lines_interface
SET po_line_id = x_lines.po_line_id_tbl(i),
price_update_tolerance = x_lines.price_update_tolerance_tbl(i),
action = x_lines.action_tbl(i),
price_break_flag = DECODE(x_lines.action_tbl(i), 'UPDATE',
DECODE(x_lines.create_line_loc_tbl(i), FND_API.g_TRUE, 'Y', NULL), NULL),
process_code = x_lines.process_code_tbl(i),
parent_interface_line_id = x_lines.parent_interface_line_id_tbl(i) -- bug5149827
WHERE interface_line_id = x_lines.intf_line_id_tbl(i);
END update_line_intf_tbl;
x_processing_row_tbl.DELETE(i);
INSERT INTO po_session_gt(key, num1)
SELECT l_key,
l_index_tbl(i)
FROM po_lines txn_lines
WHERE txn_lines.po_line_id = x_lines.po_line_id_tbl(i)
AND x_lines.unit_of_measure_tbl(i) IS NOT NULL
AND txn_lines.unit_meas_lookup_code IS NOT NULL
AND txn_lines.unit_meas_lookup_code <>
x_lines.unit_of_measure_tbl(i)
AND (EXISTS (SELECT 1
FROM po_line_locations
WHERE po_line_id = txn_lines.po_line_id
AND shipment_type = 'BLANKET')
OR
EXISTS (SELECT 1
FROM po_lines_all
WHERE from_line_id = txn_lines.po_line_id)
);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1 BULK COLLECT INTO l_expire_line_index_tbl;
x_update_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
) IS
d_api_name CONSTANT VARCHAR2(30) := 'split_lines';
l_update_index_tbl DBMS_SQL.NUMBER_TABLE;
ELSIF (p_lines.action_tbl(i) = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
IF (PO_LOG.d_stmt) THEN
PO_LOG.stmt(d_module, d_position, 'lines to update', i);
l_update_index_tbl(i) := i;
p_source_index_tbl => l_update_index_tbl,
x_target_lines => x_update_lines
);
l_parameter_name_tbl(1) := 'CREATE_OR_UPDATE_ITEM';
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
inventory_item_id
FROM mtl_system_items_kfv
WHERE p_item_tbl(i) IS NOT NULL
AND x_item_id_tbl(i) IS NULL
AND concatenated_segments = p_item_tbl(i)
AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
INSERT INTO po_session_gt(key, num1, num2)
SELECT DISTINCT
p_key,
p_index_tbl(i),
txn_lines.item_id
FROM po_headers txn_headers, po_lines txn_lines
WHERE txn_headers.po_header_id = txn_lines.po_header_id
AND p_item_tbl(i) IS NULL
AND x_item_id_tbl(i) IS NULL
AND p_vendor_product_num_tbl(i) IS NOT NULL
AND txn_lines.vendor_product_num = p_vendor_product_num_tbl(i)
AND txn_headers.vendor_id = p_vendor_id_tbl(i)
AND txn_lines.item_id IS NOT NULL
AND ( (p_category_id_tbl(i) IS NOT NULL --bug 7374337
AND txn_lines.category_id = p_category_id_tbl(i))
OR (p_category_id_tbl(i) IS NULL ) --bug 7374337
);
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),
business_group_id
FROM per_business_groups_perf
WHERE p_job_business_group_name_tbl(i) IS NOT NULL
AND x_job_business_group_id_tbl(i) IS NULL
AND name = p_job_business_group_name_tbl(i)
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(date_from, sysdate))
AND TRUNC(NVL(date_to, sysdate));
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),
jobs_b.job_id
FROM per_jobs jobs_b,
per_jobs_tl jobs_tl
WHERE p_job_name_tbl(i) IS NOT NULL
AND x_job_id_tbl(i) IS NULL
AND jobs_b.job_id = jobs_tl.job_id
AND jobs_tl.language = NVL(p_file_line_language_tbl(i), userenv('LANG'))
AND jobs_tl.name = p_job_name_tbl(i)
AND jobs_b.business_group_id = PO_PDOI_PARAMS.g_sys.def_business_group_id
AND PO_PDOI_PARAMS.g_sys.def_business_group_id =
DECODE(p_job_business_group_name_tbl(i), NULL,
DECODE(x_job_business_group_id_tbl(i), NULL,
PO_PDOI_PARAMS.g_sys.def_business_group_id,
x_job_business_group_id_tbl(i)), x_job_business_group_id_tbl(i))
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(jobs_b.date_from, sysdate))
AND TRUNC(NVL(jobs_b.date_to, sysdate));
select job_id, name, business_group_id
bulk collect into d_job_id_tbl, d_job_name_tbl, d_bg_id_tbl
from per_jobs_vl
where TRUNC(sysdate) BETWEEN TRUNC(NVL(date_from, sysdate))
AND TRUNC(NVL(date_to, sysdate));
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
jobs_b.job_id
FROM per_jobs jobs_b,
per_jobs_tl jobs_tl
WHERE p_job_name_tbl(i) IS NOT NULL
AND x_job_id_tbl(i) IS NULL
AND x_job_business_group_id_tbl(i) IS NULL
AND jobs_b.job_id = jobs_tl.job_id
AND jobs_tl.language = NVL(p_file_line_language_tbl(i), userenv('LANG'))
AND jobs_tl.name = p_job_name_tbl(i)
AND jobs_b.business_group_id = PO_PDOI_PARAMS.g_sys.def_business_group_id
AND PO_PDOI_PARAMS.g_sys.def_business_group_id =
DECODE(p_job_business_group_name_tbl(i), NULL,
PO_PDOI_PARAMS.g_sys.def_business_group_id,
x_job_business_group_id_tbl(i))
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(jobs_b.date_from, sysdate))
AND TRUNC(NVL(jobs_b.date_to, sysdate));
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
jobs_b.job_id
FROM per_jobs jobs_b,
per_jobs_tl jobs_tl,
per_business_groups_perf groups
WHERE p_job_name_tbl(i) IS NOT NULL
AND x_job_id_tbl(i) IS NULL
AND x_job_business_group_id_tbl(i) IS NOT NULL
AND jobs_b.job_id = jobs_tl.job_id
AND jobs_tl.language = NVL(p_file_line_language_tbl(i), userenv('LANG'))
AND jobs_tl.name = p_job_name_tbl(i)
AND jobs_b.business_group_id = x_job_business_group_id_tbl(i)
AND jobs_b.business_group_id = groups.business_group_id
AND TRUNC(sysdate) BETWEEN NVL(jobs_b.date_from, TRUNC(sysdate))
AND NVL(jobs_b.date_to, TRUNC(sysdate))
AND TRUNC(sysdate) BETWEEN NVL(groups.date_from, TRUNC(sysdate))
AND NVL(groups.date_to, TRUNC(sysdate));
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),
cat.rt_category_id
FROM icx_cat_categories_v cat
WHERE p_ip_category_tbl(i) IS NOT NULL
AND x_ip_category_id_tbl(i) IS NULL
AND cat.key = p_ip_category_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),
rt_category_id
FROM icx_cat_categories_v
WHERE p_ip_category_tbl(i) IS NOT NULL
AND x_ip_category_id_tbl(i) IS NULL
AND category_name = p_ip_category_tbl(i)
AND language = NVL(p_file_line_language_tbl(i),
userenv('LANG'));
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),
unit_of_measure
FROM po_units_of_measure_val_v
WHERE p_uom_code_tbl(i) IS NOT NULL
AND x_unit_of_measure_tbl(i) IS NULL
AND uom_code = p_uom_code_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, num2)
SELECT p_key,
p_index_tbl(i),
b.line_type_id
FROM po_line_types_b b,
po_line_types_tl tl
WHERE p_line_type_tbl(i) IS NOT NULL
AND x_line_type_id_tbl(i) IS NULL
AND b.line_type_id = tl.line_type_id
AND tl.language = NVL(p_file_line_language_tbl(i),
userenv('LANG'))
AND SYSDATE < NVL(b.inactive_date, SYSDATE +1)
AND tl.line_type = p_line_type_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, num2)
SELECT p_key,
p_index_tbl(i),
un_number_id
FROM po_un_numbers_vl
WHERE p_un_number_tbl(i) IS NOT NULL
AND x_un_number_id_tbl(i) IS NULL
AND sysdate < nvl(inactive_date, sysdate +1)
AND un_number = p_un_number_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, num2)
SELECT p_key,
p_index_tbl(i),
hazard_class_id
FROM po_hazard_classes_val_v
WHERE p_hazard_class_tbl(i) IS NOT NULL
AND x_hazard_class_id_tbl(i) IS NULL
AND hazard_class = p_hazard_class_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, num2)
SELECT p_key,
p_index_tbl(i),
template_id
FROM mtl_item_templates
WHERE p_template_name_tbl(i) IS NOT NULL
AND x_template_id_tbl(i) IS NULL
AND template_name = p_template_name_tbl(i)
AND NVL(context_organization_id,
PO_PDOI_PARAMS.g_sys.def_inv_org_id) =
PO_PDOI_PARAMS.g_sys.def_inv_org_id;
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, char2, char3, num2, char4, num3)
SELECT p_key,
p_index_tbl(i),
order_type_lookup_code,
purchase_basis,
matching_basis,
category_id,
unit_of_measure,
unit_price
FROM po_line_types_b
WHERE line_type_id = p_line_type_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1, char2, char3, num2, char4, num3
BULK COLLECT INTO
l_index_tbl,
l_order_type_lookup_code_tbl,
l_purchase_basis_tbl,
l_matching_basis_tbl,
l_category_id_tbl,
l_unit_of_measure_tbl,
l_unit_price_tbl;
INSERT INTO po_session_gt(key, num1, char1, char2, num2, num3,
num4, num5, num6, char3)
SELECT p_key,
p_index_tbl(i),
item_tl.description,
item.primary_unit_of_measure,
item.list_price_per_unit,
item_cat.category_id,
item.un_number_id,
item.hazard_class_id,
item.market_price,
decode(item.tracking_quantity_ind, 'PS', uom.unit_of_measure, NULL)
FROM mtl_item_categories item_cat,
mtl_system_items item,
mtl_system_items_tl item_tl,
mtl_units_of_measure uom
WHERE item.inventory_item_id = p_item_id_tbl(i)
AND item_tl.inventory_item_id = item.inventory_item_id
AND item_cat.inventory_item_id = item.inventory_item_id
AND item.organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id
AND item_tl.language = USERENV('LANG')
AND item_tl.organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id
AND item_cat.category_set_id = PO_PDOI_PARAMS.g_sys.def_cat_set_id
AND item_cat.organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id
AND item.secondary_uom_code = uom.uom_code(+);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1, char2, num2, num3, num4, num5, num6, char3
BULK COLLECT INTO
l_index_tbl,
l_item_desc_tbl,
l_unit_of_measure_tbl,
l_unit_price_tbl,
l_category_id_tbl,
l_un_number_id_tbl,
l_hazard_class_id_tbl,
l_market_price_tbl,
l_secondary_unit_of_meas_tbl;
INSERT INTO po_session_gt(key, num1, char1, num2)
SELECT p_key,
p_index_tbl(i),
association.job_description,
association.category_id
FROM po_job_associations association,
per_jobs_vl job
WHERE job.job_id = p_job_id_tbl(i)
AND association.job_id = job.job_id
AND TRUNC(sysdate) < TRUNC(NVL(association.inactive_date, sysdate+1))
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(job.date_from, sysdate))
AND TRUNC(NVL(job.date_to, sysdate+1));
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1, num2
BULK COLLECT INTO
l_index_tbl,
l_item_desc_tbl,
l_category_id_tbl;
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
po_category_id
FROM icx_cat_shopping_cat_map_v
WHERE p_ip_category_id_tbl(i) IS NOT NULL
AND x_po_category_id_tbl(i) IS NULL
AND shopping_category_id = p_ip_category_id_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, num2)
SELECT p_key,
p_index_tbl(i),
shopping_category_id
FROM icx_cat_purchasing_cat_map_v
WHERE p_po_category_id_tbl(i) IS NOT NULL
AND x_ip_category_id_tbl(i) IS NULL
AND po_category_id = p_po_category_id_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, num2)
SELECT p_key,
p_index_tbl(i),
hazard_class_id
FROM po_un_numbers_vl
WHERE p_un_number_tbl(i) IS NOT NULL
AND x_hazard_class_id_tbl(i) IS NULL
AND sysdate < nvl(inactive_date, sysdate +1)
AND un_number = p_un_number_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, num2)
SELECT l_key,
p_index_tbl(i),
po_line_id
FROM po_lines_draft_all
WHERE draft_id = x_lines.draft_id_tbl(i)
AND po_header_id = x_lines.po_header_id_tbl(i)
AND line_num = x_lines.line_num_tbl(i)
AND NVL(delete_flag, 'N') <> 'Y';
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2
BULK COLLECT INTO l_index_tbl, l_result_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
x_lines.action_tbl(i) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
IF (x_lines.hd_action_tbl(i) <> PO_PDOI_CONSTANTS.g_ACTION_UPDATE AND
x_lines.error_flag_tbl(i) = FND_API.g_FALSE AND
NOT (x_lines.order_type_lookup_code_tbl(i) = 'AMOUNT' AND
PO_PDOI_PARAMS.g_request.document_type =
PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET)) THEN
IF (PO_LOG.d_stmt) THEN
PO_LOG.stmt(d_module, d_position, 'processing index', i);
INSERT INTO po_session_gt(key, num1, num2, num3)
SELECT l_key,
l_processing_line_tbl(i),
po_line_id,
line_num
FROM po_lines_draft_all
WHERE x_lines.item_id_tbl(i) IS NOT NULL
AND draft_id = x_lines.draft_id_tbl(i)
AND po_header_id = x_lines.po_header_id_tbl(i)
AND item_id = x_lines.item_id_tbl(i)
AND (item_revision = x_lines.item_revision_tbl(i) OR
item_revision IS NULL OR
x_Lines.item_revision_tbl(i) IS NULL)
AND unit_meas_lookup_code = x_lines.unit_of_measure_tbl(i)
AND line_type_id = x_lines.line_type_id_tbl(i)
AND category_id = x_lines.category_id_tbl(i)
AND NVL(delete_flag, 'N') <> 'Y';
INSERT INTO po_session_gt(key, num1, num2, num3)
SELECT l_key,
l_processing_line_tbl(i),
po_line_id,
line_num
FROM po_lines_draft_all
WHERE x_lines.item_id_tbl(i) IS NULL
AND draft_id = x_lines.draft_id_tbl(i)
AND po_header_id = x_lines.po_header_id_tbl(i)
AND item_description = x_lines.item_desc_tbl(i)
AND unit_meas_lookup_code = x_lines.unit_of_measure_tbl(i)
AND line_type_id = x_lines.line_type_id_tbl(i)
AND category_id = x_lines.category_id_tbl(i)
AND NVL(delete_flag, 'N') <> 'Y';
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2, num3
BULK COLLECT INTO l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
l_processing_line_tbl.DELETE(i);
INSERT INTO po_session_gt(key, num1, num2, num3, char1, char2,
num4, num5, char3, char4, num6)
VALUES (l_data_key,
x_lines.intf_line_id_tbl(i), -- num1
x_lines.po_header_id_tbl(i), -- num2
x_lines.item_id_tbl(i), -- num3
x_lines.item_revision_tbl(i), -- char1
x_lines.unit_of_measure_tbl(i), -- char2
x_lines.line_type_id_tbl(i), -- num4
x_lines.category_id_tbl(i), -- num5
x_lines.item_desc_tbl(i), -- char3
x_lines.order_type_lookup_code_tbl(i), -- char4
l_num_list(i) -- num6
);
INSERT INTO po_session_gt(key, num1)
SELECT l_key,
l_processing_line_tbl(i)
FROM DUAL
WHERE x_lines.item_id_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_session_gt gt
WHERE gt.num2 = x_lines.po_header_id_tbl(i)
AND gt.num1 < x_lines.intf_line_id_tbl(i)
AND gt.num3 = x_lines.item_id_tbl(i)
AND (gt.char1 = x_lines.item_revision_tbl(i) OR
gt.char1 IS NULL OR
x_lines.item_revision_tbl(i) IS NULL)
AND gt.char2 = x_lines.unit_of_measure_tbl(i)
AND gt.num4 = x_lines.line_type_id_tbl(i)
AND gt.num5 = x_lines.category_id_tbl(i)
);
INSERT INTO po_session_gt(key, num1)
SELECT l_key,
l_processing_line_tbl(i)
FROM DUAL
WHERE x_lines.item_id_tbl(i) IS NULL
AND NOT EXISTS(
SELECT 1
FROM po_session_gt gt
WHERE gt.num2 = x_lines.po_header_id_tbl(i)
AND gt.num1 < x_lines.intf_line_id_tbl(i)
AND gt.char3 = x_lines.item_desc_tbl(i)
AND gt.char2 = x_lines.unit_of_measure_tbl(i)
AND gt.num4 = x_lines.line_type_id_tbl(i)
AND gt.num5 = x_lines.category_id_tbl(i)
);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1 BULK COLLECT INTO l_index_tbl;
l_processing_line_tbl.DELETE(i);
INSERT INTO po_session_gt(key, num1, num2)
SELECT l_key,
l_processing_line_tbl(i),
v.min_index
FROM (SELECT min(num6) AS min_index
FROM po_session_gt
WHERE x_lines.item_id_tbl(i) IS NOT NULL
AND num2 = x_lines.po_header_id_tbl(i)
AND num1 < x_lines.intf_line_id_tbl(i)
AND num3 = x_lines.item_id_tbl(i)
AND (char1 = x_lines.item_revision_tbl(i) OR
char1 IS NULL OR
x_lines.item_revision_tbl(i) IS NULL)
AND char2 = x_lines.unit_of_measure_tbl(i)
AND num4 = x_lines.line_type_id_tbl(i)
AND num5 = x_lines.category_id_tbl(i)) v
WHERE v.min_index IS NOT NULL;
INSERT INTO po_session_gt(key, num1, num2)
SELECT l_key,
l_processing_line_tbl(i),
v.min_index
FROM (SELECT min(num6) AS min_index
FROM po_session_gt
WHERE x_lines.item_id_tbl(i) IS NULL
AND num2 = x_lines.po_header_id_tbl(i)
AND num1 < x_lines.intf_line_id_tbl(i)
AND char3 = x_lines.item_desc_tbl(i)
AND char2 = x_lines.unit_of_measure_tbl(i)
AND num4 = x_lines.line_type_id_tbl(i)
AND num5 = x_lines.category_id_tbl(i)) v
WHERE v.min_index IS NOT NULL;
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_match_index_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
x_target_lines.price_update_tolerance_tbl(l_target_index) := p_source_lines.price_update_tolerance_tbl(l_source_index);
x_target_lines.last_updated_by_tbl(l_target_index) := p_source_lines.last_updated_by_tbl(l_source_index);
x_target_lines.last_update_date_tbl(l_target_index) := p_source_lines.last_update_date_tbl(l_source_index);
x_target_lines.last_update_login_tbl(l_target_index) := p_source_lines.last_update_login_tbl(l_source_index);
x_target_lines.program_update_date_tbl(l_target_index) := p_source_lines.program_update_date_tbl(l_source_index);
x_target_lines.tax_attribute_update_code_tbl(l_target_index) := p_source_lines.tax_attribute_update_code_tbl(l_source_index);
x_target_lines.allow_desc_update_flag_tbl(l_target_index) := p_source_lines.allow_desc_update_flag_tbl(l_source_index); -- bug5107324
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
draft_lines.po_line_id,
draft_lines.line_num
FROM po_lines_draft_all draft_lines
WHERE draft_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND draft_lines.draft_id = x_lines.draft_id_tbl(i)
AND NVL(draft_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND draft_lines.item_description = x_lines.item_desc_tbl(i)
AND (x_lines.category_tbl(i) IS NULL OR
EXISTS ( SELECT 1
FROM mtl_categories_kfv mck
WHERE mck.concatenated_segments = x_lines.category_tbl(i)
AND mck.category_id = draft_lines.category_id));
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
txn_lines.po_line_id,
txn_lines.line_num
FROM po_lines txn_lines
WHERE txn_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND NOT EXISTS ( SELECT 1
FROM po_lines_draft_all draft_lines
WHERE draft_lines.po_line_id = txn_lines.po_line_id
AND draft_lines.draft_id = x_lines.draft_id_tbl(i))
AND txn_lines.item_description = x_lines.item_desc_tbl(i)
AND (x_lines.category_tbl(i) IS NULL OR
EXISTS ( SELECT 1
FROM mtl_categories_kfv mck
WHERE mck.concatenated_segments = x_lines.category_tbl(i)
AND mck.category_id = txn_lines.category_id))
AND NVL(txn_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND NVL(txn_lines.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
x_processing_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1,
num2,
char1,
char2
)
SELECT
l_data_key,
x_lines.intf_line_id_tbl(i), -- num1
x_lines.hd_po_header_id_tbl(i), -- num2
x_lines.item_desc_tbl(i), -- char1
x_lines.category_tbl(i) -- char2
FROM DUAL;
INSERT INTO po_session_gt
(
key,
num1
)
SELECT p_key,
x_processing_row_tbl(i)
FROM DUAL
WHERE NOT EXISTS(
SELECT 1
FROM po_session_gt gt
WHERE key = l_data_key
AND gt.num1 < x_lines.intf_line_id_tbl(i)
AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
AND gt.char1 = x_lines.item_desc_tbl(i)
AND NVL(x_lines.category_tbl(i), NVL(gt.char2, -99))=
NVL(gt.char2, -99));
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1 BULK COLLECT INTO l_index_tbl;
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
draft_lines.po_line_id,
draft_lines.line_num
FROM po_lines_draft_all draft_lines
WHERE x_lines.item_tbl(i) IS NOT NULL
AND draft_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND draft_lines.draft_id = x_lines.draft_id_tbl(i)
AND NVL(draft_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND EXISTS (SELECT 1
FROM mtl_system_items items
WHERE items.inventory_item_id = draft_lines.item_id
AND items.segment1 = x_lines.item_tbl(i)
AND items.organization_id =
NVL(l_def_master_org_id, items.organization_id))
AND NVL(x_lines.item_revision_tbl(i), NVL(draft_lines.item_revision, -99)) =
NVL(draft_lines.item_revision, -99)
AND NVL(x_lines.vendor_product_num_tbl(i), NVL(draft_lines.vendor_product_num, -99)) =
NVL(draft_lines.vendor_product_num, -99)
AND NVL(x_lines.supplier_part_auxid_tbl(i),
NVL(draft_lines.supplier_part_auxid, FND_API.g_NULL_CHAR))=
NVL(draft_lines.supplier_part_auxid, FND_API.g_NULL_CHAR);
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
txn_lines.po_line_id,
txn_lines.line_num
FROM po_lines txn_lines
WHERE x_lines.item_tbl(i) IS NOT NULL
AND txn_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND NOT EXISTS ( SELECT 1
FROM po_lines_draft_all draft_lines
WHERE draft_lines.po_line_id = txn_lines.po_line_id
AND draft_lines.draft_id = x_lines.draft_id_tbl(i))
AND EXISTS (SELECT 1
FROM mtl_system_items items
WHERE items.inventory_item_id = txn_lines.item_id
AND items.segment1 = x_lines.item_tbl(i)
AND items.organization_id =
NVL(l_def_master_org_id, items.organization_id))
AND NVL(x_lines.item_revision_tbl(i), NVL(txn_lines.item_revision, -99)) =
NVL(txn_lines.item_revision, -99)
AND NVL(x_lines.vendor_product_num_tbl(i), NVL(txn_lines.vendor_product_num, -99)) =
NVL(txn_lines.vendor_product_num, -99)
AND NVL(x_lines.supplier_part_auxid_tbl(i),
NVL(txn_lines.supplier_part_auxid, FND_API.g_NULL_CHAR)) =
NVL(txn_lines.supplier_part_auxid, FND_API.g_NULL_CHAR)
AND NVL(txn_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND NVL(txn_lines.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
x_processing_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1,
num2,
char1,
char2,
char3,
char4
)
SELECT
l_data_key,
x_lines.intf_line_id_tbl(i), -- num1
x_lines.hd_po_header_id_tbl(i), -- num2
x_lines.item_tbl(i), -- char1
x_lines.item_revision_tbl(i), -- char2
x_lines.vendor_product_num_tbl(i), -- char3
x_lines.supplier_part_auxid_tbl(i) -- char4
FROM DUAL;
INSERT INTO po_session_gt
(
key,
num1
)
SELECT p_key,
x_processing_row_tbl(i)
FROM DUAL
WHERE x_lines.item_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_session_gt gt
WHERE key = l_data_key
AND gt.num1 < x_lines.intf_line_id_tbl(i)
AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
AND gt.char1 = x_lines.item_tbl(i)
AND NVL(x_lines.item_revision_tbl(i), NVL(gt.char2, -99)) =
NVL(gt.char2, -99)
AND NVL(x_lines.vendor_product_num_tbl(i), NVL(gt.char3, -99)) =
NVL(gt.char3, -99)
AND NVL(x_lines.supplier_part_auxid_tbl(i),
NVL(gt.char4, FND_API.g_NULL_CHAR))=
NVL(gt.char4, FND_API.g_NULL_CHAR));
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1 BULK COLLECT INTO l_index_tbl;
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
draft_lines.po_line_id,
draft_lines.line_num
FROM po_lines_draft_all draft_lines
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NOT NULL
AND draft_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND draft_lines.draft_id = x_lines.draft_id_tbl(i)
AND NVL(draft_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND draft_lines.vendor_product_num = x_lines.vendor_product_num_tbl(i)
AND NVL(x_lines.supplier_part_auxid_tbl(i),
NVL(draft_lines.supplier_part_auxid, FND_API.g_NULL_CHAR))=
NVL(draft_lines.supplier_part_auxid, FND_API.g_NULL_CHAR);
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
txn_lines.po_line_id,
txn_lines.line_num
FROM po_lines txn_lines
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NOT NULL
AND txn_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND NOT EXISTS ( SELECT 1
FROM po_lines_draft_all draft_lines
WHERE draft_lines.po_line_id = txn_lines.po_line_id
AND draft_lines.draft_id = x_lines.draft_id_tbl(i))
AND txn_lines.vendor_product_num = x_lines.vendor_product_num_tbl(i)
AND NVL(x_lines.supplier_part_auxid_tbl(i),
NVL(txn_lines.supplier_part_auxid, FND_API.g_NULL_CHAR))=
NVL(txn_lines.supplier_part_auxid, FND_API.g_NULL_CHAR)
AND NVL(txn_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND NVL(txn_lines.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
x_processing_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1,
num2,
char1,
char2
)
SELECT
l_data_key,
x_lines.intf_line_id_tbl(i), -- num1
x_lines.hd_po_header_id_tbl(i), -- num2
x_lines.vendor_product_num_tbl(i), -- char1
x_lines.supplier_part_auxid_tbl(i) -- char2
FROM DUAL;
INSERT INTO po_session_gt
(
key,
num1
)
SELECT p_key,
x_processing_row_tbl(i)
FROM DUAL
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_session_gt gt
WHERE key = l_data_key
AND gt.num1 < x_lines.intf_line_id_tbl(i)
AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
AND gt.char1 = x_lines.vendor_product_num_tbl(i)
AND NVL(x_lines.supplier_part_auxid_tbl(i),
NVL(gt.char2, FND_API.g_NULL_CHAR))=
NVL(gt.char2, FND_API.g_NULL_CHAR));
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1 BULK COLLECT INTO l_index_tbl;
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
draft_lines.po_line_id,
draft_lines.line_num
FROM po_lines_draft_all draft_lines
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NULL
AND x_lines.job_name_tbl(i) IS NOT NULL
AND draft_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND draft_lines.draft_id = x_lines.draft_id_tbl(i)
AND NVL(draft_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND EXISTS (SELECT 1
FROM per_jobs_vl
WHERE name = x_lines.job_name_tbl(i)
AND job_id = draft_lines.job_id)
AND NVL(draft_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate);
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
txn_lines.po_line_id,
txn_lines.line_num
FROM po_lines txn_lines
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NULL
AND x_lines.job_name_tbl(i) IS NOT NULL
AND txn_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND NOT EXISTS ( SELECT 1
FROM po_lines_draft_all draft_lines
WHERE draft_lines.po_line_id = txn_lines.po_line_id
AND draft_lines.draft_id = x_lines.draft_id_tbl(i))
AND EXISTS (SELECT 1
FROM per_jobs_vl
WHERE name = x_lines.job_name_tbl(i)
AND job_id = txn_lines.job_id)
AND NVL(txn_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND NVL(txn_lines.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
x_processing_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1,
num2,
char1
)
SELECT
l_data_key,
x_lines.intf_line_id_tbl(i), -- num1
x_lines.hd_po_header_id_tbl(i), -- num2
x_lines.job_name_tbl(i) -- char1
FROM DUAL;
INSERT INTO po_session_gt
(
key,
num1
)
SELECT p_key,
x_processing_row_tbl(i)
FROM DUAL
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NULL
AND x_lines.job_name_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_session_gt gt
WHERE key = l_data_key
AND gt.num1 < x_lines.intf_line_id_tbl(i)
AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
AND gt.char1 = x_lines.job_name_tbl(i));
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1 BULK COLLECT INTO l_index_tbl;
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
draft_lines.po_line_id,
draft_lines.line_num
FROM po_lines_draft_all draft_lines
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NULL
AND x_lines.job_name_tbl(i) IS NULL
AND x_lines.line_num_tbl(i) IS NOT NULL
AND draft_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND draft_lines.draft_id = x_lines.draft_id_tbl(i)
AND NVL(draft_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND draft_lines.line_num = x_lines.line_num_tbl(i);
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
p_key,
x_processing_row_tbl(i),
txn_lines.po_line_id,
txn_lines.line_num
FROM po_lines txn_lines
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NULL
AND x_lines.job_name_tbl(i) IS NULL
AND x_lines.line_num_tbl(i) IS NOT NULL
AND txn_lines.po_header_id = x_lines.hd_po_header_id_tbl(i)
AND NOT EXISTS ( SELECT 1
FROM po_lines_draft_all draft_lines
WHERE draft_lines.po_line_id = txn_lines.po_line_id
AND draft_lines.draft_id = x_lines.draft_id_tbl(i))
AND txn_lines.line_num = x_lines.line_num_tbl(i)
AND NVL(txn_lines.expiration_date, TRUNC(sysdate)) >= TRUNC(sysdate)
AND NVL(txn_lines.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2, num3 BULK COLLECT INTO
l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
x_lines.action_tbl(l_index) := PO_PDOI_CONSTANTS.g_ACTION_UPDATE;
x_processing_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1,
num2,
num3
)
SELECT
l_data_key,
x_lines.intf_line_id_tbl(i), -- num1
x_lines.hd_po_header_id_tbl(i), -- num2
x_lines.line_num_tbl(i) -- num3
FROM DUAL;
INSERT INTO po_session_gt
(
key,
num1
)
SELECT p_key,
x_processing_row_tbl(i)
FROM DUAL
WHERE x_lines.item_tbl(i) IS NULL
AND x_lines.vendor_product_num_tbl(i) IS NULL
AND x_lines.job_name_tbl(i) IS NULL
AND x_lines.line_num_tbl(i) IS NOT NULL
AND NOT EXISTS(
SELECT 1
FROM po_session_gt gt
WHERE key = l_data_key
AND gt.num1 < x_lines.intf_line_id_tbl(i)
AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
AND gt.num3 = x_lines.line_num_tbl(i));
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1 BULK COLLECT INTO l_index_tbl;
x_processing_row_tbl.DELETE(l_index);
INSERT INTO po_session_gt
(
key,
num1
)
SELECT
l_key,
l_index_tbl(i)
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM po_attr_values_tlp_interface
WHERE interface_line_id = x_lines.intf_line_id_tbl(i)
AND language = x_lines.hd_created_language_tbl(i)
)
AND EXISTS
(
SELECT 1
FROM po_attr_values_tlp_interface
WHERE interface_line_id = x_lines.intf_line_id_tbl(i)
AND language <> x_lines.hd_created_language_tbl(i)
);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1 BULK COLLECT INTO l_index_tbl;
DELETE FROM PO_INTERFACE_ERRORS
WHERE interface_line_id = l_remove_err_line_tbl(i);