PON Amendment Data Template

Description
Categories: BI Publisher
Columns: Unique Id, Structure Level, Object Type, Parent Id, Document Id, Document Type, Display Sequence, Label, Title, Print Text Yn ...
Application: Sourcing
Source:
Short Name: PON_AMEND
DB package: PON_SGD_PKG
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'), '*')