SELECT 'ARTICLE' || kart.ID unique_id,
okc_terms_disp_pvt.get_terms_structure_level(1,
'FALSE',
kart.DOCUMENT_ID,
kart.DOCUMENT_TYPE,
'ARTICLE',
kart.ID,
to_char(sysdate, 'DDMMYYYY HH24MISS')
) structure_level,
'ARTICLE' OBJECT_TYPE,
KART.scn_id PARENT_ID,
KART.document_id ,
KART.document_type,
KART.DISPLAY_SEQUENCE,
KART.label,
NVL(ver.display_name,ART.article_title) TITLE,
KART.print_text_yn,
ART.article_number,
VER.article_text,
VER.Article_Version_Id,
ART.article_id,
kart.ID CAT_ID,
kart.sav_sae_id,
kart.orig_article_id,
art.standard_yn,
VER.insert_by_reference ,
VER.reference_text,
okc_terms_disp_pvt.get_terms_display_order(1,
'FALSE',
kart.DOCUMENT_ID,
kart.DOCUMENT_TYPE,
'ARTICLE',
kart.ID,
to_char(sysdate, 'DDMMYYYY HH24MISS')
) display_order
FROM okc_k_articles_b KART,
OKC_ARTICLES_ALL ART,
OKC_ARTICLE_VERSIONS VER,
okc_sections_b sec
WHERE KART.sav_sae_id = ART.Article_Id
AND KART.article_version_id = VER.Article_Version_Id
AND KART.document_type <> 'TEMPLATE'
and kart.document_id = :p_auction_header_id
and kart.document_type = :mod_contract_doctype
and nvl2(kart.summary_amend_operation_code, kart.summary_amend_operation_code, kart.amendment_operation_code) = 'UPDATED'
and kart.scn_id = sec.id
and sec.heading <> nvl(fnd_profile.value('OKC_AMENDMENT_SPECIFIC_SECTION'), '*')
UNION ALL
SELECT 'ARTICLE' || kart.ID unique_id,
okc_terms_disp_pvt.get_terms_structure_level(1,
'FALSE',
kart.DOCUMENT_ID,
kart.DOCUMENT_TYPE,
'ARTICLE',
kart.ID,
to_char(sysdate, 'DDMMYYYY HH24MISS')
) structure_level,
'ARTICLE' OBJECT_TYPE,
KART.scn_id PARENT_ID,
KART.document_id ,
KART.document_type,
KART.DISPLAY_SEQUENCE,
KART.label,
NVL(ver.display_name,ART.article_title) TITLE,
KART.print_text_yn,
ART.article_number,
VER.article_text,
VER.Article_Version_Id,
ART.article_id,
kart.ID CAT_ID,
kart.sav_sae_id,
kart.orig_article_id,
art.standard_yn,
VER.insert_by_reference ,
VER.reference_text,
okc_terms_disp_pvt.get_terms_display_order(1,
'FALSE',
kart.DOCUMENT_ID,
kart.DOCUMENT_TYPE,
'ARTICLE',
kart.ID,
to_char(sysdate, 'DDMMYYYY HH24MISS')
) display_order
FROM okc_k_articles_b KART,
OKC_ARTICLES_ALL ART,
OKC_ARTICLE_VERSIONS VER,
okc_sections_b sec
WHERE KART.sav_sae_id = ART.Article_Id
AND KART.article_version_id = VER.Article_Version_Id
AND KART.document_type <> 'TEMPLATE'
and kart.document_id = :p_auction_header_id
and kart.document_type = :mod_contract_doctype
and nvl2(kart.summary_amend_operation_code, kart.summary_amend_operation_code, kart.amendment_operation_code) IS NULL
and kart.id in ( select mod.cat_id
from po_okc_vars_gt mod, po_okc_vars_gt base
where mod.doc_type = :mod_contract_doctype
and mod.doc_id = :p_auction_header_id
and mod.variable_code = base.variable_code
and base.doc_type = :base_contract_doctype
and base.doc_id = :base_auction_header_id
and nvl(mod.variable_value, '*') <> nvl(base.variable_value, '*')
union
select mod.cat_id
from OKC_TERMS_CLM_UDV_T mod, OKC_TERMS_CLM_UDV_T base
where mod.doc_type = :mod_contract_doctype
and mod.doc_id = :p_auction_header_id
and mod.variable_code = base.variable_code
and base.doc_type = :base_contract_doctype
and base.doc_id = :base_auction_header_id
and nvl(mod.variable_value, '*') <> nvl(base.variable_value, '*')
union
select mod.cat_id
from OKC_TERMS_UDV_WITH_PROCEDURE_T mod, OKC_TERMS_UDV_WITH_PROCEDURE_T base
where mod.doc_type = :mod_contract_doctype
and mod.doc_id = :p_auction_header_id
and mod.variable_code = base.variable_code
and base.doc_type = :base_contract_doctype
and base.doc_id = :base_auction_header_id
and nvl(mod.variable_value, '*') <> nvl(base.variable_value, '*'))
and kart.scn_id = sec.id
and sec.heading <> nvl(fnd_profile.value('OKC_AMENDMENT_SPECIFIC_SECTION'), '*') |
