[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ INDEX (intf_dists PO_DISTRIBUTIONS_INTERFACE_N1) */
intf_dists.interface_distribution_id,
intf_dists.interface_header_id,
intf_dists.interface_line_id,
intf_dists.interface_line_location_id,
intf_dists.po_distribution_id,
intf_dists.distribution_num,
intf_dists.deliver_to_location,
intf_dists.deliver_to_location_id,
intf_dists.deliver_to_person_full_name,
intf_dists.deliver_to_person_id,
intf_dists.destination_type,
intf_dists.destination_type_code,
intf_dists.destination_organization,
intf_dists.destination_organization_id,
intf_dists.wip_entity,
intf_dists.wip_entity_id,
intf_dists.wip_line_code,
intf_dists.wip_line_id,
intf_dists.bom_resource_code,
intf_dists.bom_resource_id,
intf_dists.charge_account,
intf_dists.charge_account_id,
intf_dists.dest_charge_account_id,
intf_dists.project_accounting_context,
intf_dists.award_number,
intf_dists.award_id,
intf_dists.project,
intf_dists.project_id,
intf_dists.task,
intf_dists.task_id,
intf_dists.expenditure,
intf_dists.expenditure_type,
intf_dists.expenditure_organization,
intf_dists.expenditure_organization_id,
intf_dists.expenditure_item_date,
intf_dists.end_item_unit_number,
intf_dists.destination_context,
intf_dists.gl_encumbered_date,
intf_dists.gl_encumbered_period_name,
intf_dists.variance_account_id,
intf_dists.accrual_account_id,
intf_dists.budget_account_id,
intf_dists.dest_variance_account_id,
intf_dists.destination_subinventory,
intf_dists.amount_ordered,
intf_dists.quantity_ordered,
intf_dists.wip_repetitive_schedule_id,
intf_dists.wip_operation_seq_num,
intf_dists.wip_resource_seq_num,
intf_dists.prevent_encumbrance_flag,
intf_dists.recovery_rate,
intf_dists.tax_recovery_override_flag,
intf_dists.charge_account_segment1,
intf_dists.charge_account_segment2,
intf_dists.charge_account_segment3,
intf_dists.charge_account_segment4,
intf_dists.charge_account_segment5,
intf_dists.charge_account_segment6,
intf_dists.charge_account_segment7,
intf_dists.charge_account_segment8,
intf_dists.charge_account_segment9,
intf_dists.charge_account_segment10,
intf_dists.charge_account_segment11,
intf_dists.charge_account_segment12,
intf_dists.charge_account_segment13,
intf_dists.charge_account_segment14,
intf_dists.charge_account_segment15,
intf_dists.charge_account_segment16,
intf_dists.charge_account_segment17,
intf_dists.charge_account_segment18,
intf_dists.charge_account_segment19,
intf_dists.charge_account_segment20,
intf_dists.charge_account_segment21,
intf_dists.charge_account_segment22,
intf_dists.charge_account_segment23,
intf_dists.charge_account_segment24,
intf_dists.charge_account_segment25,
intf_dists.charge_account_segment26,
intf_dists.charge_account_segment27,
intf_dists.charge_account_segment28,
intf_dists.charge_account_segment29,
intf_dists.charge_account_segment30,
intf_dists.attribute1,
intf_dists.attribute2,
intf_dists.attribute3,
intf_dists.attribute4,
intf_dists.attribute5,
intf_dists.attribute6,
intf_dists.attribute7,
intf_dists.attribute8,
intf_dists.attribute9,
intf_dists.attribute10,
intf_dists.attribute11,
intf_dists.attribute12,
intf_dists.attribute13,
intf_dists.attribute14,
intf_dists.attribute15,
-- standard who columns
intf_dists.last_updated_by,
intf_dists.last_update_date,
intf_dists.last_update_login,
intf_dists.creation_date,
intf_dists.created_by,
intf_dists.request_id,
intf_dists.program_application_id,
intf_dists.program_id,
intf_dists.program_update_date,
-- attributes read from line location record
draft_locs.ship_to_organization_id,
draft_locs.line_location_id,
draft_locs.shipment_type,
draft_locs.transaction_flow_header_id,
draft_locs.accrue_on_receipt_flag,
draft_locs.need_by_date,
draft_locs.promised_date,
draft_locs.price_override,
draft_locs.outsourced_assembly,
draft_locs.attribute1,
draft_locs.attribute2,
draft_locs.attribute3,
draft_locs.attribute4,
draft_locs.attribute5,
draft_locs.attribute6,
draft_locs.attribute7,
draft_locs.attribute8,
draft_locs.attribute9,
draft_locs.attribute10,
draft_locs.attribute11,
draft_locs.attribute12,
draft_locs.attribute13,
draft_locs.attribute14,
draft_locs.attribute15,
-- attributes read from line record
draft_lines.order_type_lookup_code,
draft_lines.purchase_basis,
draft_lines.item_id,
draft_lines.category_id,
draft_lines.line_type_id,
draft_lines.po_line_id,
draft_lines.attribute1,
draft_lines.attribute2,
draft_lines.attribute3,
draft_lines.attribute4,
draft_lines.attribute5,
draft_lines.attribute6,
draft_lines.attribute7,
draft_lines.attribute8,
draft_lines.attribute9,
draft_lines.attribute10,
draft_lines.attribute11,
draft_lines.attribute12,
draft_lines.attribute13,
draft_lines.attribute14,
draft_lines.attribute15,
-- attributes read from header record
intf_headers.draft_id,
NVL(draft_headers.agent_id, txn_headers.agent_id),
draft_lines.po_header_id,
NVL(draft_headers.rate_date, txn_headers.rate_date),
NVL(draft_headers.rate, txn_headers.rate),
NVL(draft_headers.type_lookup_code, txn_headers.type_lookup_code),
NVL(draft_headers.vendor_id, txn_headers.vendor_id),
NVL(draft_headers.attribute1, txn_headers.attribute1),
NVL(draft_headers.attribute2, txn_headers.attribute2),
NVL(draft_headers.attribute3, txn_headers.attribute3),
NVL(draft_headers.attribute4, txn_headers.attribute4),
NVL(draft_headers.attribute5, txn_headers.attribute5),
NVL(draft_headers.attribute6, txn_headers.attribute6),
NVL(draft_headers.attribute7, txn_headers.attribute7),
NVL(draft_headers.attribute8, txn_headers.attribute8),
NVL(draft_headers.attribute9, txn_headers.attribute9),
NVL(draft_headers.attribute10, txn_headers.attribute10),
NVL(draft_headers.attribute11, txn_headers.attribute11),
NVL(draft_headers.attribute12, txn_headers.attribute12),
NVL(draft_headers.attribute13, txn_headers.attribute13),
NVL(draft_headers.attribute14, txn_headers.attribute14),
NVL(draft_headers.attribute15, txn_headers.attribute15),
-- set initial value for error_flag
FND_API.g_FALSE,
NULL, -- gms_txn_required_flag
NULL, -- tax_attribute_update_code
NULL, -- ship_to_ou_coa_id_tbl
NULL -- award_set_id (bug5201306)
FROM po_distributions_interface intf_dists,
po_line_locations_interface intf_locs,
po_headers_interface intf_headers,
po_line_locations_draft_all draft_locs,
po_lines_draft_all draft_lines,
po_headers_draft_all draft_headers,
po_headers_all txn_headers
WHERE intf_dists.interface_line_location_id =
intf_locs.interface_line_location_id
AND intf_dists.interface_header_id = intf_headers.interface_header_id
AND intf_locs.line_location_id = draft_locs.line_location_id
AND intf_headers.draft_id = draft_locs.draft_id
AND draft_locs.po_line_id = draft_lines.po_line_id
AND draft_locs.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_dists.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_dists.interface_distribution_id > p_max_intf_dist_id
ORDER BY intf_dists.interface_distribution_id;
x_dists.last_updated_by_tbl,
x_dists.last_update_date_tbl,
x_dists.last_update_login_tbl,
x_dists.creation_date_tbl,
x_dists.created_by_tbl,
x_dists.request_id_tbl,
x_dists.program_application_id_tbl,
x_dists.program_id_tbl,
x_dists.program_update_date_tbl,
-- attributes read from line location record
x_dists.loc_ship_to_org_id_tbl,
x_dists.loc_line_loc_id_tbl,
x_dists.loc_shipment_type_tbl,
x_dists.loc_txn_flow_header_id_tbl,
x_dists.loc_accrue_on_receipt_flag_tbl,
x_dists.loc_need_by_date_tbl,
x_dists.loc_promised_date_tbl,
x_dists.loc_price_override_tbl,
x_dists.loc_outsourced_assembly_tbl,
x_dists.loc_attribute1_tbl,
x_dists.loc_attribute2_tbl,
x_dists.loc_attribute3_tbl,
x_dists.loc_attribute4_tbl,
x_dists.loc_attribute5_tbl,
x_dists.loc_attribute6_tbl,
x_dists.loc_attribute7_tbl,
x_dists.loc_attribute8_tbl,
x_dists.loc_attribute9_tbl,
x_dists.loc_attribute10_tbl,
x_dists.loc_attribute11_tbl,
x_dists.loc_attribute12_tbl,
x_dists.loc_attribute13_tbl,
x_dists.loc_attribute14_tbl,
x_dists.loc_attribute15_tbl,
-- attributes read from line record
x_dists.ln_order_type_lookup_code_tbl,
x_dists.ln_purchase_basis_tbl,
x_dists.ln_item_id_tbl,
x_dists.ln_category_id_tbl,
x_dists.ln_line_type_id_tbl,
x_dists.ln_po_line_id_tbl,
x_dists.ln_attribute1_tbl,
x_dists.ln_attribute2_tbl,
x_dists.ln_attribute3_tbl,
x_dists.ln_attribute4_tbl,
x_dists.ln_attribute5_tbl,
x_dists.ln_attribute6_tbl,
x_dists.ln_attribute7_tbl,
x_dists.ln_attribute8_tbl,
x_dists.ln_attribute9_tbl,
x_dists.ln_attribute10_tbl,
x_dists.ln_attribute11_tbl,
x_dists.ln_attribute12_tbl,
x_dists.ln_attribute13_tbl,
x_dists.ln_attribute14_tbl,
x_dists.ln_attribute15_tbl,
-- attributes read from header record
x_dists.draft_id_tbl,
x_dists.hd_agent_id_tbl,
x_dists.hd_po_header_id_tbl,
x_dists.hd_rate_date_tbl,
x_dists.hd_rate_tbl,
x_dists.hd_type_lookup_code_tbl,
x_dists.hd_vendor_id_tbl,
x_dists.hd_attribute1_tbl,
x_dists.hd_attribute2_tbl,
x_dists.hd_attribute3_tbl,
x_dists.hd_attribute4_tbl,
x_dists.hd_attribute5_tbl,
x_dists.hd_attribute6_tbl,
x_dists.hd_attribute7_tbl,
x_dists.hd_attribute8_tbl,
x_dists.hd_attribute9_tbl,
x_dists.hd_attribute10_tbl,
x_dists.hd_attribute11_tbl,
x_dists.hd_attribute12_tbl,
x_dists.hd_attribute13_tbl,
x_dists.hd_attribute14_tbl,
x_dists.hd_attribute15_tbl,
-- set initial value for error_flag
x_dists.error_flag_tbl,
x_dists.gms_txn_required_flag_tbl,
x_dists.tax_attribute_update_code_tbl,
x_dists.ship_to_ou_coa_id_tbl,
x_dists.award_set_id_tbl -- bug5201306
LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
x_dists.tax_attribute_update_code_tbl(i) := 'CREATE';
x_last_update_date_tbl => x_dists.last_update_date_tbl,
x_last_updated_by_tbl => x_dists.last_updated_by_tbl,
x_last_update_login_tbl => x_dists.last_update_login_tbl,
x_creation_date_tbl => x_dists.creation_date_tbl,
x_created_by_tbl => x_dists.created_by_tbl,
x_request_id_tbl => x_dists.request_id_tbl,
x_program_application_id_tbl => x_dists.program_application_id_tbl,
x_program_id_tbl => x_dists.program_id_tbl,
x_program_update_date_tbl => x_dists.program_update_date_tbl
);
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
TO_NUMBER(org_info.org_information3)
FROM hr_organization_information org_info,
mtl_parameters param
WHERE param.organization_id = p_ship_to_org_id_tbl(i)
AND param.organization_id = org_info.organization_id
AND org_info.org_information_context = 'Accounting Information';
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),
location_id
FROM hr_locations
WHERE p_deliver_to_loc_tbl(i) IS NOT NULL
AND x_deliver_to_loc_id_tbl(i) IS NULL
AND location_code = p_deliver_to_loc_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),
employee_id
FROM hr_employees_all_v
WHERE p_person_name_tbl(i) IS NOT NULL
AND x_person_id_tbl(i) IS NULL
AND full_name = p_person_name_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),
lookup_code
FROM po_destination_types_all_v
WHERE p_dest_type_tbl(i) IS NOT NULL
AND x_dest_type_code_tbl(i) IS NULL
AND displayed_field = p_dest_type_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),
organization_id
FROM org_organization_definitions
WHERE p_dest_org_tbl(i) IS NOT NULL
AND x_dest_org_id_tbl(i) IS NULL
AND organization_code = p_dest_org_tbl(i)
AND TRUNC(sysdate) < NVL(disable_date, TRUNC(sysdate+1))
AND inventory_enabled_flag = 'Y';
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
p_ship_to_org_id_tbl(i)
FROM dual
WHERE p_dest_org_tbl (i) IS NULL
AND x_dest_org_id_tbl(i) IS NULL;
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),
wip_entity_id
FROM wip_entities
WHERE p_wip_entity_tbl(i) IS NOT NULL
AND x_wip_entity_id_tbl(i) IS NULL
AND p_dest_type_code_tbl(i) = 'SHOP FLOOR'
AND wip_entity_name = p_wip_entity_tbl(i)
AND organization_id = p_dest_org_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),
line_id
FROM wip_lines
WHERE p_wip_line_code_tbl(i) IS NOT NULL
AND x_wip_line_id_tbl(i) IS NULL
AND p_dest_type_code_tbl(i) = 'SHOP FLOOR'
AND line_code = p_wip_line_code_tbl(i)
AND organization_id = p_dest_org_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),
sob.chart_of_accounts_id
FROM gl_sets_of_books sob,
hr_organization_information org_info,
mtl_parameters param
WHERE p_txn_flow_header_id_tbl(i) IS NOT NULL
AND p_dest_charge_account_id_tbl(i) IS NULL
AND param.organization_id = p_dest_org_id_tbl(i)
AND param.organization_id = org_info.organization_id
AND org_info.org_information_context = 'Accounting Information'
AND TO_NUMBER(org_info.org_information1) = sob.set_of_books_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, num2)
SELECT p_key,
p_index_tbl(i),
resource_id
FROM bom_resources
WHERE p_bom_resource_code_tbl(i) IS NOT NULL
AND x_bom_resource_id_tbl(i) IS NULL
AND resource_code = p_bom_resource_code_tbl(i)
AND organization_id = p_dest_org_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
l_sql := 'SELECT GCC.code_combination_id FROM gl_code_combinations GCC ' ||
'WHERE GCC.chart_of_accounts_id = :p_chart_of_accounts_id ';
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
project_id
FROM pa_projects_expend_v
WHERE p_project_tbl(i) IS NOT NULL
AND x_project_id_tbl(i) IS NULL
AND p_dest_type_code_tbl(i) = 'EXPENSE'
AND project_name = p_project_tbl(i);
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
project_id
FROM pjm_projects_org_ou_v
WHERE p_project_tbl(i) IS NOT NULL
AND x_project_id_tbl(i) IS NULL
AND p_dest_type_code_tbl(i) <> 'EXPENSE'
AND project_name = p_project_tbl(i)
AND inventory_organization_id = p_ship_to_org_id_tbl(i)
AND NVL(org_id, p_ship_to_ou_id_tbl(i)) = p_ship_to_ou_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),
task_id
FROM pa_tasks_expend_v
WHERE p_task_tbl(i) IS NOT NULL
AND x_task_id_tbl(i) IS NULL
AND p_dest_type_code_tbl(i) = 'EXPENSE'
AND project_id = p_project_id_tbl(i)
AND task_number = p_task_tbl(i);
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
task_id
FROM pa_tasks_all_expend_v
WHERE p_task_tbl(i) IS NOT NULL
AND x_task_id_tbl(i) IS NULL
AND p_dest_type_code_tbl(i) <> 'EXPENSE'
AND project_id = p_project_id_tbl(i)
AND task_number = p_task_tbl(i)
AND NVL(expenditure_org_id, p_ship_to_ou_id_tbl(i)) = p_ship_to_ou_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, char1)
SELECT p_key,
p_index_tbl(i),
expenditure_type
FROM pa_expenditure_types_v
WHERE p_expenditure_tbl(i) IS NOT NULL
AND x_expenditure_type_tbl(i) IS NULL
AND p_project_id_tbl(i) IS NOT NULL
AND description = p_expenditure_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),
organization_id
FROM pa_organizations_expend_v
WHERE p_expenditure_org_tbl(i) IS NOT NULL
AND x_expenditure_org_id_tbl(i) IS NULL
AND p_project_id_tbl(i) IS NOT NULL
AND name = p_expenditure_org_tbl(i)
AND active_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, char1)
SELECT p_key,
p_index_tbl(i),
DECODE(outside_operation_flag,'Y','O',
DECODE(stock_enabled_flag,'Y','E','D'))
FROM mtl_system_items
WHERE organization_id = p_ship_to_org_id_tbl(i)
AND inventory_item_id = p_item_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
SELECT accrued_code_combination_id
INTO g_sys_accrual_account_id
FROM po_system_parameters;
SELECT ap_accrual_account, invoice_price_var_account
INTO g_mtl_accrual_account_id_tbl(p_dest_org_id),
g_mtl_variance_account_id_tbl(p_dest_org_id)
FROM mtl_parameters
WHERE organization_id = p_dest_org_id;
SELECT NVL(misi.encumbrance_account, msci.encumbrance_account)
INTO x_budget_account_id
FROM mtl_item_sub_inventories misi,
mtl_secondary_inventories msci
WHERE misi.organization_id = p_dest_org_id
AND misi.inventory_item_id = p_item_id
AND misi.secondary_inventory = p_dest_subinventory
AND msci.organization_id = p_dest_org_id
AND msci.secondary_inventory_name = p_dest_subinventory
AND p_dest_subinventory is not NULL;
SELECT NVL(msi.encumbrance_account, mp.encumbrance_account)
INTO x_budget_account_id
FROM mtl_system_items msi,
mtl_parameters mp
WHERE p_item_id = msi.inventory_item_id
AND p_dest_org_id = msi.organization_id
AND mp.organization_id = p_dest_org_id;