[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(pl.org_id, -2) org_id,
po_tlp.language language,
ph.type_lookup_code source_type,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', ga.purchasing_org_id, pl.org_id) purchasing_org_id,
pl.category_id po_category_id,
NVL(ph.vendor_id, -2) supplier_id,
NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', NVL(ga.vendor_site_id, -2),
NVL(ph.vendor_site_id, -2)) supplier_site_id,
pl.ip_category_id ip_category_id,
ic.category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.CLM_DOCUMENT_NUMBER document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
NVL(pl.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,
ph.created_by
FROM po_headers_all ph,
po_lines_all pl,
po_ga_org_assignments ga,
po_session_gt pogt,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE ph.po_header_id = pl.po_header_id
AND ph.type_lookup_code = 'BLANKET'
AND ph.po_header_id = ga.po_header_id (+)
AND ph.org_id = ga.organization_id (+)
AND pogt.key = p_key
AND pl.po_line_id = pogt.index_num1
AND ((pogt.char1 IS NULL)
OR
(pogt.char1 = 'Y' AND po_tlp.language = pogt.char2))
AND pl.po_line_id = po_tlp.po_line_id
AND pl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND NVL(pltb.outside_operation_flag, 'N') = 'N'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND ph.vendor_id = pv.vendor_id(+)
AND pl.po_line_id >= p_po_line_id
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+)
ORDER BY doc.po_line_id;
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(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(pl.org_id, -2) org_id,
po_tlp.language language,
ph.type_lookup_code source_type,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', ga.purchasing_org_id, pl.org_id) purchasing_org_id,
pl.category_id po_category_id,
NVL(ph.vendor_id, -2) supplier_id,
NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', NVL(ga.vendor_site_id, -2),
NVL(ph.vendor_site_id, -2)) supplier_site_id,
pl.ip_category_id ip_category_id,
ic.category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.CLM_DOCUMENT_NUMBER document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
NVL(pl.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,
ph.created_by
FROM po_headers_all ph,
po_lines_all pl,
po_ga_org_assignments ga,
po_session_gt pogt,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE ph.po_header_id = pl.po_header_id
AND ph.type_lookup_code = 'BLANKET'
AND ph.po_header_id = ga.po_header_id (+)
AND ph.org_id = ga.organization_id (+)
AND pogt.key = p_key
AND ga.po_header_id = pogt.index_num1
AND ga.org_assignment_id = pogt.index_num2
AND pl.po_line_id = po_tlp.po_line_id
AND pl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND NVL(pltb.outside_operation_flag, 'N') = 'N'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND ph.vendor_id = pv.vendor_id(+)
AND pl.po_line_id >= p_po_line_id
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+)
ORDER BY doc.po_line_id;
l_inv_item_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
l_source_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_part_auxid_tbl.DELETE;
l_supplier_site_id_tbl.DELETE;
l_ip_category_id_tbl.DELETE;
l_ip_category_name_tbl.DELETE;
l_item_revision_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_document_number_tbl.DELETE;
l_line_num_tbl.DELETE;
l_allow_prc_override_flag_tbl.DELETE;
l_not_to_exceed_price_tbl.DELETE;
l_line_type_id_tbl.DELETE;
l_unit_meas_lookup_code_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_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_created_by_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;
SELECT /*+ LEADING(doc) use_nl(ctx) index(ctxIn, ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) */
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(ph) leading(ph,pv,t,pl,pltb,ctxIn) use_nl(pl,ctxIn,t) index(t,PO_GA_ORG_ASSIGN_U1)
index(ctxIn,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_MERGE */
NVL(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(t.organization_id, -2) org_id,
ctxIn.language language,
'GLOBAL_BLANKET' source_type,
t.purchasing_org_id purchasing_org_id,
ctxIn.org_id owning_org_id,
ctxIn.po_category_id po_category_id,
ctxIn.supplier_id supplier_id,
ctxIn.supplier_part_num supplier_part_num,
ctxIn.supplier_part_auxid supplier_part_auxid,
t.vendor_site_id supplier_site_id,
ctxIn.ip_category_id ip_category_id,
ctxIn.ip_category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.segment1 document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag,
--For global agreement status
NVL(t.enabled_flag, 'N') enabled_flag
FROM po_ga_org_assignments t,
po_headers_all ph,
po_lines_all pl,
icx_cat_items_ctx_hdrs_tlp ctxIn,
po_vendors pv,
po_line_types_b pltb
WHERE ph.global_agreement_flag = 'Y'
AND ph.po_header_id = t.po_header_id
AND ph.org_id <> t.organization_id
AND t.po_header_id = pl.po_header_id
AND pl.po_line_id = ctxIn.po_line_id
AND pl.org_id = ctxIn.org_id
AND pl.line_type_id = pltb.line_type_id
AND ph.vendor_id = pv.vendor_id(+)
AND ph.rowid BETWEEN g_start_rowid AND g_end_rowid
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+);
SELECT /*+ LEADING(doc) use_nl(ctx) index(ctxIn, ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) */
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(ph) leading(ph,pv,t,pl,pltb,ctxIn) use_nl(pl,ctxIn,t) index(t,PO_GA_ORG_ASSIGN_U1)
index(ctxIn,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_MERGE */
NVL(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(t.organization_id, -2) org_id,
ctxIn.language language,
'GLOBAL_BLANKET' source_type,
t.purchasing_org_id purchasing_org_id,
ctxIn.org_id owning_org_id,
ctxIn.po_category_id po_category_id,
ctxIn.supplier_id supplier_id,
ctxIn.supplier_part_num supplier_part_num,
ctxIn.supplier_part_auxid supplier_part_auxid,
t.vendor_site_id supplier_site_id,
ctxIn.ip_category_id ip_category_id,
ctxIn.ip_category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.segment1 document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag,
--For global agreement status
NVL(t.enabled_flag, 'N') enabled_flag
FROM po_ga_org_assignments t,
po_headers_all ph,
po_lines_all pl,
icx_cat_items_ctx_hdrs_tlp ctxIn,
po_vendors pv,
po_line_types_b pltb
WHERE ph.global_agreement_flag = 'Y'
AND ph.po_header_id = t.po_header_id
AND ph.org_id <> t.organization_id
AND t.po_header_id = pl.po_header_id
AND pl.po_line_id = ctxIn.po_line_id
AND pl.org_id = ctxIn.org_id
AND pl.line_type_id = pltb.line_type_id
AND ph.vendor_id = pv.vendor_id(+)
AND ph.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 t.last_update_date > g_upgrade_last_run_date)
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
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(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(t.organization_id, -2) org_id,
ctxIn.language language,
'GLOBAL_BLANKET' source_type,
t.purchasing_org_id purchasing_org_id,
ctxIn.org_id owning_org_id,
ctxIn.po_category_id po_category_id,
ctxIn.supplier_id supplier_id,
ctxIn.supplier_part_num supplier_part_num,
ctxIn.supplier_part_auxid supplier_part_auxid,
t.vendor_site_id supplier_site_id,
ctxIn.ip_category_id ip_category_id,
ctxIn.ip_category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.CLM_DOCUMENT_NUMBER document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag,
NVL(t.enabled_flag, 'N') enabled_flag
FROM po_ga_org_assignments t,
po_headers_all ph,
po_lines_all pl,
po_session_gt pogt,
icx_cat_items_ctx_hdrs_tlp ctxIn,
po_vendors pv,
po_line_types_b pltb
WHERE ph.global_agreement_flag = 'Y'
AND ph.po_header_id = t.po_header_id
AND ph.org_id <> t.organization_id
AND ph.po_header_id = pl.po_header_id
AND pogt.key = p_key
AND NVL(pogt.char3, 'N') = 'Y'
AND pl.po_line_id = pogt.index_num1
AND ((pogt.char1 IS NULL)
OR
(pogt.char1 = 'Y' AND ctxIn.language = pogt.char2))
AND pl.po_line_id = ctxIn.po_line_id
AND pl.org_id = ctxIn.org_id
AND pl.line_type_id = pltb.line_type_id
AND ph.vendor_id = pv.vendor_id(+)
AND pl.po_line_id >= p_po_line_id
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+)
ORDER BY doc.po_line_id;
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(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(t.organization_id, -2) org_id,
ctxIn.language language,
'GLOBAL_BLANKET' source_type,
t.purchasing_org_id purchasing_org_id,
ctxIn.org_id owning_org_id,
ctxIn.po_category_id po_category_id,
ctxIn.supplier_id supplier_id,
ctxIn.supplier_part_num supplier_part_num,
ctxIn.supplier_part_auxid supplier_part_auxid,
t.vendor_site_id supplier_site_id,
ctxIn.ip_category_id ip_category_id,
ctxIn.ip_category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.CLM_DOCUMENT_NUMBER document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag,
NVL(t.enabled_flag, 'N') enabled_flag
FROM po_ga_org_assignments t,
po_headers_all ph,
po_lines_all pl,
po_session_gt pogt,
icx_cat_items_ctx_hdrs_tlp ctxIn,
po_vendors pv,
po_line_types_b pltb
WHERE ph.global_agreement_flag = 'Y'
AND ph.po_header_id = t.po_header_id
AND ph.org_id <> t.organization_id
AND t.po_header_id = pl.po_header_id
AND pogt.key = p_key
AND t.po_header_id = pogt.index_num1
AND t.org_assignment_id = pogt.index_num2
AND pl.po_line_id = ctxIn.po_line_id
AND pl.org_id = ctxIn.org_id
ANd pl.line_type_id = pltb.line_type_id
AND ph.vendor_id = pv.vendor_id(+)
AND pl.po_line_id >= p_po_line_id
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+)
ORDER BY doc.po_line_id;
l_inv_item_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
l_source_type_tbl.DELETE;
l_purchasing_org_id_tbl.DELETE;
l_owning_org_id_tbl.DELETE;
l_po_category_id_tbl.DELETE;
l_supplier_id_tbl.DELETE;
l_supplier_part_num_tbl.DELETE;
l_supplier_part_auxid_tbl.DELETE;
l_supplier_site_id_tbl.DELETE;
l_ip_category_id_tbl.DELETE;
l_ip_category_name_tbl.DELETE;
l_item_revision_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_document_number_tbl.DELETE;
l_line_num_tbl.DELETE;
l_allow_prc_override_flag_tbl.DELETE;
l_not_to_exceed_price_tbl.DELETE;
l_line_type_id_tbl.DELETE;
l_unit_meas_lookup_code_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_negotiated_preparer_flag_tbl.DELETE;
l_order_type_lookup_code_tbl.DELETE;
l_supplier_tbl.DELETE;
l_global_agreement_flag_tbl.DELETE;
l_enabled_flag_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;
SELECT NVL(authorization_status, 'INCOMPLETE')
INTO l_authorization_status
FROM po_headers_all
WHERE po_header_id = l_po_header_id_tbl(i);
SELECT /*+ LEADING(doc) use_nl_with_index(ctx,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_EXPAND */
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(poh) use_nl(pl,ga,po_tlp,ic) */
NVL(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(pl.org_id, -2) org_id,
po_tlp.language language,
ph.type_lookup_code source_type,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', NVL(ga.purchasing_org_id, pl.org_id),
NVL(pl.org_id, -2)) purchasing_org_id,
pl.category_id po_category_id,
NVL(ph.vendor_id, -2) supplier_id,
NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', NVL(ga.vendor_site_id, -2),
NVL(ph.vendor_site_id, -2)) supplier_site_id,
pl.ip_category_id ip_category_id,
ic.category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.segment1 document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
DECODE(ph.type_lookup_code, 'QUOTATION', 'Y',
NVL(pl.negotiated_by_preparer_flag, 'N')) negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag global_agreement_flag,
--For Quote line status
DECODE(ph.type_lookup_code, 'QUOTATION',
ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(pl.po_line_id),
NULL) quote_status,
--For blanket line status
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,
ph.created_by
FROM po_headers_all ph,
po_lines_all pl,
po_ga_org_assignments ga,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE ph.po_header_id = pl.po_header_id
AND ph.type_lookup_code IN ('BLANKET', 'QUOTATION')
AND ph.po_header_id = ga.po_header_id (+)
AND ph.org_id = ga.organization_id (+)
AND pl.po_line_id = po_tlp.po_line_id
AND pl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND NVL(pltb.outside_operation_flag, 'N') = 'N'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND ph.vendor_id = pv.vendor_id(+)
AND ph.rowid BETWEEN g_start_rowid AND g_end_rowid
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+)
AND (doc.source_type = 'BLANKET'
OR (ctx.rowid IS NOT NULL OR doc.quote_status = 0));
SELECT /*+ LEADING(doc) use_nl_with_index(ctx,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_EXPAND */
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(poh) use_nl(pl,ga,po_tlp,ic) */
NVL(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(pl.org_id, -2) org_id,
po_tlp.language language,
ph.type_lookup_code source_type,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', NVL(ga.purchasing_org_id, pl.org_id),
NVL(pl.org_id, -2)) purchasing_org_id,
pl.category_id po_category_id,
NVL(ph.vendor_id, -2) supplier_id,
NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
DECODE(NVL(ph.global_agreement_flag, 'N'),
'Y', NVL(ga.vendor_site_id, -2),
NVL(ph.vendor_site_id, -2)) supplier_site_id,
pl.ip_category_id ip_category_id,
ic.category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.segment1 document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
DECODE(ph.type_lookup_code, 'QUOTATION', 'Y',
NVL(pl.negotiated_by_preparer_flag, 'N')) negotiated_by_preparer_flag,
pltb.order_type_lookup_code,
pv.vendor_name supplier,
ph.global_agreement_flag global_agreement_flag,
--For Quote line status
DECODE(ph.type_lookup_code, 'QUOTATION',
ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(pl.po_line_id),
NULL) quote_status,
--For blanket line status
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,
ph.created_by
FROM po_headers_all ph,
po_lines_all pl,
po_ga_org_assignments ga,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE ph.po_header_id = pl.po_header_id
AND ph.type_lookup_code IN ('BLANKET', 'QUOTATION')
AND ph.po_header_id = ga.po_header_id (+)
AND ph.org_id = ga.organization_id (+)
AND pl.po_line_id = po_tlp.po_line_id
AND pl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND NVL(pltb.outside_operation_flag, 'N') = 'N'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND ph.vendor_id = pv.vendor_id(+)
AND ph.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 ga.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 doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+)
AND (doc.source_type = 'BLANKET'
OR (ctx.rowid IS NOT NULL OR doc.quote_status = 0));
l_inv_item_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
l_source_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_part_auxid_tbl.DELETE;
l_supplier_site_id_tbl.DELETE;
l_ip_category_id_tbl.DELETE;
l_ip_category_name_tbl.DELETE;
l_item_revision_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_document_number_tbl.DELETE;
l_line_num_tbl.DELETE;
l_allow_prc_override_flag_tbl.DELETE;
l_not_to_exceed_price_tbl.DELETE;
l_line_type_id_tbl.DELETE;
l_unit_meas_lookup_code_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_negotiated_preparer_flag_tbl.DELETE;
l_order_type_lookup_code_tbl.DELETE;
l_supplier_tbl.DELETE;
l_global_agreement_flag_tbl.DELETE;
l_quote_status_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_created_by_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;
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(pl.item_id, -2) inventory_item_id,
pl.po_line_id po_line_id,
NVL(pl.org_id, -2) org_id,
po_tlp.language language,
ph.type_lookup_code source_type,
NVL(pl.org_id, -2) purchasing_org_id,
pl.category_id po_category_id,
NVL(ph.vendor_id, -2) supplier_id,
NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
NVL(ph.vendor_site_id, -2) supplier_site_id,
ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(pl.po_line_id) status,
pl.ip_category_id ip_category_id,
ic.category_name ip_category_name,
NVL(pl.item_revision, '-2') item_revision,
ph.po_header_id,
ph.segment1 document_number,
pl.line_num,
UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
pl.not_to_exceed_price,
pl.line_type_id,
pl.unit_meas_lookup_code,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
ph.currency_code,
ph.rate_type,
ph.rate_date,
ph.rate,
ph.agent_id buyer_id,
ph.vendor_contact_id supplier_contact_id,
pltb.order_type_lookup_code,
pv.vendor_name supplier
FROM po_headers_all ph,
po_lines_all pl,
po_session_gt pogt,
po_attribute_values_tlp po_tlp,
po_line_types_b pltb,
icx_cat_categories_tl ic,
po_vendors pv
WHERE ph.po_header_id = pl.po_header_id
AND ph.type_lookup_code = 'QUOTATION'
AND pogt.key = p_key
AND pl.po_line_id = pogt.index_num1
AND pl.po_line_id = po_tlp.po_line_id
AND pl.line_type_id = pltb.line_type_id
AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
AND NVL(pltb.outside_operation_flag, 'N') = 'N'
AND po_tlp.ip_category_id = ic.rt_category_id (+)
AND po_tlp.language = ic.language (+)
AND ph.vendor_id = pv.vendor_id(+)
AND pl.po_line_id >= p_po_line_id
) doc,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE doc.po_line_id = ctx.po_line_id (+)
AND doc.org_id = ctx.org_id (+)
AND doc.source_type = ctx.source_type (+)
AND doc.language = ctx.language (+)
-- AND (ctx.rowid IS NOT NULL OR doc.status = 0)
ORDER BY doc.po_line_id;
l_inv_item_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
l_source_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_part_auxid_tbl.DELETE;
l_supplier_site_id_tbl.DELETE;
l_status_tbl.DELETE;
l_ip_category_id_tbl.DELETE;
l_ip_category_name_tbl.DELETE;
l_item_revision_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_document_number_tbl.DELETE;
l_line_num_tbl.DELETE;
l_allow_prc_override_flag_tbl.DELETE;
l_not_to_exceed_price_tbl.DELETE;
l_line_type_id_tbl.DELETE;
l_unit_meas_lookup_code_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_order_type_lookup_code_tbl.DELETE;
l_supplier_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;