[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT interface_header_id,
draft_id,
po_header_id,
action,
document_num,
document_type_code,
document_subtype,
rate_type,
rate_type_code,
rate_date,
rate,
agent_id,
agent_name,
ship_to_location_id,
ship_to_location,
bill_to_location_id,
bill_to_location,
payment_terms,
terms_id,
vendor_name,
vendor_num,
vendor_id,
vendor_site_code,
vendor_site_id,
vendor_contact,
vendor_contact_id,
from_rfq_num,
from_header_id,
fob,
freight_carrier,
freight_terms,
pay_on_code,
shipping_control,
currency_code,
quote_warning_delay,
approval_required_flag,
reply_date,
approval_status,
approved_date,
from_type_lookup_code,
revision_num,
confirming_order_flag,
acceptance_required_flag,
min_release_amount,
closed_code,
print_count,
frozen_flag,
encumbrance_required_flag,
vendor_doc_num,
org_id,
acceptance_due_date,
amount_to_encumber,
effective_date,
expiration_date,
po_release_id,
release_num,
release_date,
revised_date,
printed_date,
closed_date,
amount_agreed,
amount_limit, -- bug5352625
firm_flag,
gl_encumbered_date,
gl_encumbered_period_name,
budget_account_id,
budget_account,
budget_account_segment1,
budget_account_segment2,
budget_account_segment3,
budget_account_segment4,
budget_account_segment5,
budget_account_segment6,
budget_account_segment7,
budget_account_segment8,
budget_account_segment9,
budget_account_segment10,
budget_account_segment11,
budget_account_segment12,
budget_account_segment13,
budget_account_segment14,
budget_account_segment15,
budget_account_segment16,
budget_account_segment17,
budget_account_segment18,
budget_account_segment19,
budget_account_segment20,
budget_account_segment21,
budget_account_segment22,
budget_account_segment23,
budget_account_segment24,
budget_account_segment25,
budget_account_segment26,
budget_account_segment27,
budget_account_segment28,
budget_account_segment29,
budget_account_segment30,
created_language,
style_id,
style_display_name,
global_agreement_flag,
-- standard who columns
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
FND_API.g_FALSE, -- initial value for error_flag
-- txn table columns
NULL, -- status_lookup_code
NULL, -- cancel_flag
NULL, -- vendor_order_num
NULL, -- quote_vendor_quote_num
NULL, -- doc_creation_method
NULL, -- quotation_class_code
NULL, -- approved_flag
NULL, -- tax_attribute_update_code_tbl
-- blanket dist columns
NULL -- po_dist_id -- bug5252250
FROM po_headers_interface
WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
AND processing_round_num = PO_PDOI_PARAMS.g_current_round_num
AND interface_header_id > p_max_intf_header_id
AND action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
PO_PDOI_CONSTANTS.g_ACTION_REPLACE)
ORDER by interface_header_id;
x_headers.last_update_date_tbl,
x_headers.last_updated_by_tbl,
x_headers.last_update_login_tbl,
x_headers.creation_date_tbl,
x_headers.created_by_tbl,
x_headers.request_id_tbl,
x_headers.program_application_id_tbl,
x_headers.program_id_tbl,
x_headers.program_update_date_tbl,
x_headers.error_flag_tbl, -- set initial value on error_flag
-- tan table columns
x_headers.status_lookup_code_tbl,
x_headers.cancel_flag_tbl,
x_headers.vendor_order_num_tbl,
x_headers.quote_vendor_quote_num_tbl,
x_headers.doc_creation_method_tbl,
x_headers.quotation_class_code_tbl,
x_headers.approved_flag_tbl,
x_headers.tax_attribute_update_code_tbl,
-- blanket dist columns
x_headers.po_dist_id_tbl -- bug5252250
LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
x_headers.tax_attribute_update_code_tbl(i) := 'CREATE';
x_last_update_date_tbl => x_headers.last_update_date_tbl,
x_last_updated_by_tbl => x_headers.last_updated_by_tbl,
x_last_update_login_tbl => x_headers.last_update_login_tbl,
x_creation_date_tbl => x_headers.creation_date_tbl,
x_created_by_tbl => x_headers.created_by_tbl,
x_request_id_tbl => x_headers.request_id_tbl,
x_program_application_id_tbl => x_headers.program_application_id_tbl,
x_program_id_tbl => x_headers.program_id_tbl,
x_program_update_date_tbl => x_headers.program_update_date_tbl
);
l_headers.last_update_date := x_headers.last_update_date_tbl;
l_headers.last_updated_by := x_headers.last_updated_by_tbl;
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
location_id
FROM po_locations_val_v
WHERE x_location_id_tbl(i) IS NULL
AND p_location_tbl(i) IS NOT NULL
AND location_code = p_location_tbl(i)
AND nvl(ship_to_site_flag, 'N') = 'Y';
INSERT INTO po_session_gt(key, num1, num2)
SELECT p_key,
p_index_tbl(i),
location_id
FROM po_locations_val_v
WHERE x_location_id_tbl(i) IS NULL
AND p_location_tbl(i) IS NOT NULL
AND location_code = p_location_tbl(i)
AND nvl(bill_to_site_flag, 'N') = '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),
term_id
FROM ap_terms
WHERE x_terms_id_tbl(i) IS NULL
AND p_payment_terms_tbl(i) IS NOT NULL
AND name = p_payment_terms_tbl(i)
AND enabled_flag = 'Y'
AND TRUNC(sysdate) between TRUNC(nvl(start_date_active, sysdate))
AND TRUNC(nvl(end_date_active, 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),
vendor_id
FROM po_vendors
WHERE x_vendor_id_tbl(i) IS NULL
AND (p_vendor_name_tbl(i) IS NOT NULL OR p_vendor_num_tbl(i) IS NOT NULL)
AND (vendor_name = p_vendor_name_tbl(i) OR
segment1 = p_vendor_num_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),
conversion_type
FROM gl_daily_conversion_types
WHERE x_rate_type_code_tbl(i) IS NULL
AND p_rate_type_tbl(i) IS NOT NULL
AND user_conversion_type = p_rate_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),
employee_id
FROM po_buyers_val_v
WHERE x_agent_id_tbl(i) IS NULL
AND p_agent_name_tbl(i) IS NOT NULL
AND full_name = p_agent_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, num2)
SELECT p_key,
p_index_tbl(i),
vendor_site_id
FROM po_supplier_sites_val_v
WHERE x_vendor_site_id_tbl(i) IS NULL
AND p_vendor_site_code_tbl(i) IS NOT NULL
AND p_vendor_id_tbl(i) IS NOT NULL
AND vendor_id = p_vendor_id_tbl(i)
AND vendor_site_code = p_vendor_site_code_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),
vendor_contact_id
FROM po_vendor_contacts
WHERE x_vendor_contact_id_tbl(i) IS NULL
AND p_vendor_contact_tbl(i) IS NOT NULL
AND p_vendor_site_id_tbl(i) IS NOT NULL
AND last_name||' '||first_name = p_vendor_contact_tbl(i)
AND vendor_site_id = p_vendor_site_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),
style_id
FROM po_doc_style_lines_tl pds
WHERE x_style_id_tbl(i) IS NULL AND
pds.display_name = p_style_display_name_tbl(i) AND
pds.LANGUAGE = USERENV('LANG');
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),
po_header_id
FROM po_headers
WHERE x_from_header_id_tbl(i) IS NULL
AND p_from_rfq_num_tbl(i) IS NOT NULL
AND segment1 = p_from_rfq_num_tbl(i)
AND type_lookup_code = 'RFQ'; -- PO_PDOI_CONSTANTS.g_DOC_TYPE_RFQ;
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, num2)
SELECT p_key,
p_index_tbl(i),
invoice_currency_code,
terms_id
FROM po_vendors
WHERE vendor_id = p_vendor_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1, num2
BULK COLLECT INTO
l_index_tbl,
l_currency_code_tbl,
l_terms_id_tbl;
INSERT INTO po_session_gt(key, num1, num2, num3)
SELECT p_key,
p_index_tbl(i),
min(vendor_site_id),
vendor_id
FROM po_vendor_sites
WHERE p_vendor_id_tbl(i) IS NOT NULL
AND x_vendor_site_id_tbl(i) IS NULL
AND vendor_id = p_vendor_id_tbl(i)
AND purchasing_site_flag = 'Y'
AND (sysdate) < nvl(inactive_date, TRUNC(sysdate + 1))
AND DECODE(PO_PDOI_PARAMS.g_request.document_type,
PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION, 'N',
NVL(rfq_only_site_flag, 'N')) <> 'Y'
GROUP BY vendor_id
HAVING count(vendor_site_id) = 1;
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_vendor_site_id_tbl;
INSERT INTO po_session_gt(
key, num1, char1, char2, char3, num2, num3, char4, num4, char5, char6)
SELECT p_key,
p_index_tbl(i),
fob_lookup_code,
ship_via_lookup_code,
freight_terms_lookup_code,
ship_to_location_id,
bill_to_location_id,
invoice_currency_code,
terms_id,
shipping_control,
pay_on_code
FROM po_vendor_sites_all
WHERE vendor_site_id = x_vendor_site_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, char1, char2, char3, num2, num3, char4, num4, char5, char6
BULK COLLECT INTO
l_index_tbl,
l_fob_tbl,
l_freight_carrier_tbl,
l_freight_term_tbl,
l_ship_to_loc_id_tbl,
l_bill_to_loc_id_tbl,
l_currency_code_tbl,
l_terms_id_tbl,
l_shipping_control_tbl,
l_pay_on_code_tbl;
INSERT INTO po_session_gt(key, num1, num2, num3)
SELECT p_key,
p_index_tbl(i),
max(vendor_contact_id),
vendor_site_id
FROM po_vendor_contacts
WHERE p_vendor_site_id_tbl(i) IS NOT NULL
AND x_vendor_contact_id_tbl(i) IS NULL
AND vendor_site_id = p_vendor_site_id_tbl(i)
AND TRUNC(sysdate) < NVL(inactive_date, TRUNC(sysdate + 1))
GROUP BY vendor_site_id
HAVING count(vendor_contact_id) = 1;
DELETE FROM po_session_gt
WHERE key = p_key
RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;