[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ LEADING(doc) use_nl(ctx) */
doc.*,
ctx.inventory_item_id ctx_inventory_item_id,
ctx.source_type ctx_source_type,
ctx.item_type ctx_item_type,
ctx.purchasing_org_id ctx_purchasing_org_id,
ctx.supplier_id ctx_supplier_id,
ctx.supplier_site_id ctx_supplier_site_id,
ctx.supplier_part_num ctx_supplier_part_num,
ctx.supplier_part_auxid ctx_supplier_part_auxid,
ctx.ip_category_id ctx_ip_category_id,
ctx.po_category_id ctx_po_category_id,
ctx.item_revision ctx_item_revision,
ROWIDTOCHAR(ctx.rowid) ctx_rowid
FROM
(
SELECT /*+ ROWID(prl) use_nl(prh,ph,pl,po_tlp,ic) */
NVL(prl.item_id, -2) inventory_item_id,
prl.express_name req_template_name,
prl.sequence_num req_template_line_num,
NVL(prl.org_id, -2) org_id,
po_tlp.language language,
DECODE(prl.source_type_code, 'VENDOR', 'TEMPLATE', 'INTERNAL_TEMPLATE') source_type,
DECODE(prl.source_type_code, 'VENDOR', 'PURCHASE', 'INTERNAL') item_type,
NVL(prl.org_id, -2) purchasing_org_id,
prl.category_id po_category_id,
NVL(prl.suggested_vendor_id, -2) supplier_id,
NVL(prl.suggested_vendor_product_code, '##NULL##') supplier_part_num,
NVL(prl.suggested_vendor_site_id, -2) supplier_site_id,
prl.ip_category_id,
ic.category_name ip_category_name,
-- For template line status
prh.inactive_date,
--For blanket line status
prl.po_line_id,
prl.po_line_id req_template_po_line_id,
NVL(prl.item_revision, '-2'),
prl.po_header_id,
ph.segment1 document_number,
pl.line_num,
prl.line_type_id,
prl.unit_meas_lookup_code,
prl.suggested_quantity,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', prl.unit_price, 'AMOUNT', prl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), prl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate rate,
prl.suggested_buyer_id buyer_id,
prl.suggested_vendor_contact_id supplier_contact_id,
prl.rfq_required_flag,
NVL(prl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag,
ph.approved_date,
NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
NVL(ph.frozen_flag, 'N') frozen_flag,
NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
NVL(pl.cancel_flag, 'N') line_cancel_flag,
NVL(ph.closed_code, 'OPEN') hdr_closed_code,
NVL(pl.closed_code, 'OPEN') line_closed_code,
NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE+1)) expiration_date,
TRUNC(SYSDATE) system_date
FROM po_reqexpress_headers_all prh,
po_reqexpress_lines_all prl,
po_headers_all ph,
po_lines_all pl,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE prl.express_name = prh.express_name
AND prl.org_id = prh.org_id
AND prl.po_line_id = pl.po_line_id (+)
AND prl.po_header_id = pl.po_header_id (+)
AND prl.po_header_id = ph.po_header_id (+)
AND -2 = po_tlp.po_line_id
AND prl.express_name = po_tlp.req_template_name
AND prl.sequence_num = po_tlp.req_template_line_num
AND prl.org_id = po_tlp.org_id
AND prl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND prl.suggested_vendor_id = pv.vendor_id(+)
AND prl.rowid BETWEEN g_start_rowid and g_end_rowid
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE -2 = ctx.po_line_id (+)
AND doc.inventory_item_id=ctx.inventory_item_id(+)
AND doc.req_template_name = ctx.req_template_name (+)
AND doc.req_template_line_num = ctx.req_template_line_num (+)
AND doc.source_type = ctx.source_type (+)
AND doc.org_id = ctx.org_id (+)
AND doc.language = ctx.language (+);
SELECT /*+ LEADING(doc) use_nl(ctx) */
doc.*,
ctx.inventory_item_id ctx_inventory_item_id,
ctx.source_type ctx_source_type,
ctx.item_type ctx_item_type,
ctx.purchasing_org_id ctx_purchasing_org_id,
ctx.supplier_id ctx_supplier_id,
ctx.supplier_site_id ctx_supplier_site_id,
ctx.supplier_part_num ctx_supplier_part_num,
ctx.supplier_part_auxid ctx_supplier_part_auxid,
ctx.ip_category_id ctx_ip_category_id,
ctx.po_category_id ctx_po_category_id,
ctx.item_revision ctx_item_revision,
ROWIDTOCHAR(ctx.rowid) ctx_rowid
FROM
(
SELECT /*+ ROWID(prl) use_nl(prh,ph,pl,po_tlp,ic) */
NVL(prl.item_id, -2) inventory_item_id,
prl.express_name req_template_name,
prl.sequence_num req_template_line_num,
NVL(prl.org_id, -2) org_id,
po_tlp.language language,
DECODE(prl.source_type_code, 'VENDOR', 'TEMPLATE', 'INTERNAL_TEMPLATE') source_type,
DECODE(prl.source_type_code, 'VENDOR', 'PURCHASE', 'INTERNAL') item_type,
NVL(prl.org_id, -2) purchasing_org_id,
prl.category_id po_category_id,
NVL(prl.suggested_vendor_id, -2) supplier_id,
NVL(prl.suggested_vendor_product_code, '##NULL##') supplier_part_num,
NVL(prl.suggested_vendor_site_id, -2) supplier_site_id,
prl.ip_category_id,
ic.category_name ip_category_name,
-- For template line status
prh.inactive_date,
--For blanket line status
prl.po_line_id,
prl.po_line_id req_template_po_line_id,
NVL(prl.item_revision, '-2'),
prl.po_header_id,
ph.segment1 document_number,
pl.line_num,
prl.line_type_id,
prl.unit_meas_lookup_code,
prl.suggested_quantity,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', prl.unit_price, 'AMOUNT', prl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), prl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate rate,
prl.suggested_buyer_id buyer_id,
prl.suggested_vendor_contact_id supplier_contact_id,
prl.rfq_required_flag,
NVL(prl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag,
ph.approved_date,
NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
NVL(ph.frozen_flag, 'N') frozen_flag,
NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
NVL(pl.cancel_flag, 'N') line_cancel_flag,
NVL(ph.closed_code, 'OPEN') hdr_closed_code,
NVL(pl.closed_code, 'OPEN') line_closed_code,
NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE+1)) expiration_date,
TRUNC(SYSDATE) system_date
FROM po_reqexpress_headers_all prh,
po_reqexpress_lines_all prl,
po_headers_all ph,
po_lines_all pl,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE prl.express_name = prh.express_name
AND prl.org_id = prh.org_id
AND prl.po_line_id = pl.po_line_id (+)
AND prl.po_header_id = pl.po_header_id (+)
AND prl.po_header_id = ph.po_header_id (+)
AND -2 = po_tlp.po_line_id
AND prl.express_name = po_tlp.req_template_name
AND prl.sequence_num = po_tlp.req_template_line_num
AND prl.org_id = po_tlp.org_id
AND prl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND prl.suggested_vendor_id = pv.vendor_id(+)
AND prl.rowid BETWEEN g_start_rowid and g_end_rowid
AND (ph.last_update_date > g_upgrade_last_run_date
OR pl.last_update_date > g_upgrade_last_run_date
OR prh.last_update_date > g_upgrade_last_run_date
OR prl.last_update_date > g_upgrade_last_run_date
OR po_tlp.last_update_date > g_upgrade_last_run_date)
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE -2 = ctx.po_line_id (+)
AND doc.inventory_item_id=ctx.inventory_item_id(+)
AND doc.req_template_name = ctx.req_template_name (+)
AND doc.req_template_line_num = ctx.req_template_line_num (+)
AND doc.source_type = ctx.source_type (+)
AND doc.org_id = ctx.org_id (+)
AND doc.language = ctx.language (+);
SELECT /*+ LEADING(doc) */
doc.*,
ctx.inventory_item_id ctx_inventory_item_id,
ctx.source_type ctx_source_type,
ctx.item_type ctx_item_type,
ctx.purchasing_org_id ctx_purchasing_org_id,
ctx.supplier_id ctx_supplier_id,
ctx.supplier_site_id ctx_supplier_site_id,
ctx.supplier_part_num ctx_supplier_part_num,
ctx.supplier_part_auxid ctx_supplier_part_auxid,
ctx.ip_category_id ctx_ip_category_id,
ctx.po_category_id ctx_po_category_id,
ctx.item_revision ctx_item_revision,
ROWIDTOCHAR(ctx.rowid) ctx_rowid
FROM
(
SELECT NVL(prl.item_id, -2) inventory_item_id,
prl.express_name req_template_name,
prl.sequence_num req_template_line_num,
NVL(prl.org_id, -2) org_id,
po_tlp.language language,
DECODE(prl.source_type_code, 'VENDOR', 'TEMPLATE', 'INTERNAL_TEMPLATE') source_type,
DECODE(prl.source_type_code, 'VENDOR', 'PURCHASE', 'INTERNAL') item_type,
NVL(prl.org_id, -2) purchasing_org_id,
prl.category_id po_category_id,
NVL(prl.suggested_vendor_id, -2) supplier_id,
NVL(prl.suggested_vendor_product_code, '##NULL##') supplier_part_num,
NVL(prl.suggested_vendor_site_id, -2) supplier_site_id,
prl.ip_category_id,
ic.category_name ip_category_name,
prh.inactive_date,
prl.po_line_id,
prl.po_line_id req_template_po_line_id,
NVL(prl.item_revision, '-2'),
prl.po_header_id,
ph.segment1 document_number,
pl.line_num,
prl.line_type_id,
prl.unit_meas_lookup_code,
prl.suggested_quantity,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', prl.unit_price, 'AMOUNT', prl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), prl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate rate,
prl.suggested_buyer_id buyer_id,
prl.suggested_vendor_contact_id supplier_contact_id,
prl.rfq_required_flag,
NVL(prl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag,
ph.approved_date,
NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
NVL(ph.frozen_flag, 'N') frozen_flag,
NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
NVL(pl.cancel_flag, 'N') line_cancel_flag,
NVL(ph.closed_code, 'OPEN') hdr_closed_code,
NVL(pl.closed_code, 'OPEN') line_closed_code,
NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE+1)) expiration_date,
TRUNC(SYSDATE) system_date
FROM po_reqexpress_headers_all prh,
po_reqexpress_lines_all prl,
po_session_gt pogt,
po_headers_all ph,
po_lines_all pl,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE prl.express_name = prh.express_name
AND prl.org_id = prh.org_id
AND pogt.key = p_key
AND prl.express_name = pogt.index_char1
AND prl.sequence_num = pogt.index_num1
AND prl.org_id = pogt.index_num2
AND prl.po_line_id = pl.po_line_id (+)
AND prl.po_header_id = pl.po_header_id (+)
AND prl.po_header_id = ph.po_header_id (+)
AND prl.express_name = po_tlp.req_template_name
AND prl.sequence_num = po_tlp.req_template_line_num
AND prl.org_id = po_tlp.org_id
AND prl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND prl.suggested_vendor_id = pv.vendor_id(+)
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE -2 = ctx.po_line_id (+)
AND doc.inventory_item_id=ctx.inventory_item_id(+)
AND doc.req_template_name = ctx.req_template_name (+)
AND doc.req_template_line_num = ctx.req_template_line_num (+)
AND doc.source_type = ctx.source_type (+)
AND doc.org_id = ctx.org_id (+)
AND doc.language = ctx.language (+);
l_inv_item_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
l_source_type_tbl.DELETE;
l_item_type_tbl.DELETE;
l_purchasing_org_id_tbl.DELETE;
l_po_category_id_tbl.DELETE;
l_supplier_id_tbl.DELETE;
l_supplier_part_num_tbl.DELETE;
l_supplier_site_id_tbl.DELETE;
l_ip_category_id_tbl.DELETE;
l_ip_category_name_tbl.DELETE;
l_inactive_date_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_req_template_po_line_id_tbl.DELETE;
l_item_revision_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_document_number_tbl.DELETE;
l_line_num_tbl.DELETE;
l_line_type_id_tbl.DELETE;
l_unit_meas_lookup_code_tbl.DELETE;
l_suggested_quantity_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_amount_tbl.DELETE;
l_currency_code_tbl.DELETE;
l_rate_type_tbl.DELETE;
l_rate_date_tbl.DELETE;
l_rate_tbl.DELETE;
l_buyer_id_tbl.DELETE;
l_supplier_contact_id_tbl.DELETE;
l_rfq_required_flag_tbl.DELETE;
l_negotiated_preparer_flag_tbl.DELETE;
l_order_type_lookup_code_tbl.DELETE;
l_supplier_tbl.DELETE;
l_global_agreement_flag_tbl.DELETE;
l_approved_date_tbl.DELETE;
l_authorization_status_tbl.DELETE;
l_frozen_flag_tbl.DELETE;
l_hdr_cancel_flag_tbl.DELETE;
l_line_cancel_flag_tbl.DELETE;
l_hdr_closed_code_tbl.DELETE;
l_line_closed_code_tbl.DELETE;
l_end_date_tbl.DELETE;
l_expiration_date_tbl.DELETE;
l_system_date_tbl.DELETE;
l_ctx_inventory_item_id_tbl.DELETE;
l_ctx_source_type_tbl.DELETE;
l_ctx_item_type_tbl.DELETE;
l_ctx_purchasing_org_id_tbl.DELETE;
l_ctx_supplier_id_tbl.DELETE;
l_ctx_supplier_site_id_tbl.DELETE;
l_ctx_supplier_part_num_tbl.DELETE;
l_ctx_supplier_part_auxid_tbl.DELETE;
l_ctx_ip_category_id_tbl.DELETE;
l_ctx_po_category_id_tbl.DELETE;
l_ctx_item_revision_tbl.DELETE;
l_ctx_rowid_tbl.DELETE;