PO Modification Data template

SELECT ph.po_header_id headerId,
		ph.draft_id draftId,
		ph.clm_document_number,
		pdr.modification_number,
		ph.type_lookup_code ph_type_lookup_code,
		ph.clm_document_format,
		DECODE(PDR.STATUS, 'DRAFT', fnd_message.get_string('PO', 'PO_FO_DRAFT'), 
                   'IN PROCESS', decode((SELECT nvl(pdt.can_approver_modify_doc_flag, 'N') from po_document_types_all pdt, po_headers_draft_all phd where pdt.document_type_code = 'PO' 
										and pdt.document_subtype = phd.type_lookup_code and pdt.org_id = phd.org_id and phd.draft_id = :p_draft_id), 
									'Y', fnd_message.get_string('PO', 'PO_FO_DRAFT'), 
									NULL),'REJECTED', fnd_message.get_string('PO', 'PO_FO_DRAFT'),NULL) DRAFT_TEXT,
		ph.clm_award_type,
		ph.currency_code,
		PO_CORE_S2.GET_BASE_CURRENCY(ph.ORG_ID) BASE_CURRENCY_CODE,
		(PO_SGD_PKG.getDiffXML(decode(ph.type_lookup_code, 'STANDARD', 'AWARD', 'IDV'), 'HEADER', ph.po_header_id, ph.draft_id).getClobVal()) header_changes,
		fnd_message.get_string('PO','PO_SGD_TEXT_CHANGE',null) text_change, 
		fnd_message.get_string('PO','PO_SGD_TEXT_UPDATE',null) text_update, 
		fnd_message.get_string('PO','PO_SGD_NUM_DEC',null) num_dec,
		fnd_message.get_string('PO','PO_SGD_NUM',null) def_first,
		fnd_message.get_string('PO','PO_SGD_NUM_INC',null) num_inc,
		fnd_message.get_string('PO','PO_SGD_ROLLUP_TEXT_CHANGE',null) rollup_text_change, 
		fnd_message.get_string('PO','PO_SGD_ROLLUP_TEXT_UPDATE',null) rollup_text_update,
		fnd_message.get_string('PO','PO_SGD_HDR_UNDEF',null) sgd_hdr_undef,
		fnd_message.get_string('PO','PO_SGD_HDR_DEF',null) sgd_hdr_def,
		PO_UCA.get_header_amount_ordered_uca(ph.po_header_id,ph.draft_id,ph.AUTHORIZATION_STATUS) mod_total,
		PO_UCA.is_definitizing_mod('HEADER', ph.po_header_id,ph.draft_id) is_def_mod,
		(PO_SGD_PKG.getLineSummaryDiffXML(decode(ph.type_lookup_code, 'STANDARD', 'AWARD', 'IDV'), 'LINE', ph.po_header_id, ph.draft_id).getClobVal()) line_summary_changes,
		(PO_SGD_PKG.getLineLocSummaryDiffXML(decode(ph.type_lookup_code, 'STANDARD', 'AWARD', 'IDV'), 'LINELOCATION', ph.po_header_id, ph.draft_id).getClobVal()) location_summary_changes,
		(PO_SGD_PKG.getDistSummaryDiffXML(decode(ph.type_lookup_code, 'STANDARD', 'AWARD', 'IDV'), 'DISTRIBUTION', ph.po_header_id, ph.draft_id).getClobVal()) distribution_summary_changes,
		(PO_SGD_PKG.get_addressxml(ph.po_header_id, ph.draft_id)) addresses,
		(PO_SGD_PKG.get_contactsxml(ph.po_header_id, ph.draft_id)) contact_details,
		0 as total_no_of_pages,
		FND_MESSAGE.GET_STRING('PO', 'PO_FO_DRAFT')  AS DRAFT_TAG,
		pdr.mod_effective_date,
		(PO_SGD_PKG.get_uda_header_xml(ph.po_header_id, ph.draft_id).getClobVal()) uda_attr,
                (PO_DATATEMPLATE_PKG.get_vendor_address_details(ph.uda_template_id, ph.po_header_id, ph.draft_id)) vendor_address_details,
                (PO_DATATEMPLATE_PKG.get_vendor_rmt_address_details(uda_template_id, ph.po_header_id, ph.draft_id)) VENDOR_REMIT_ADDRESS_DETAILS,
         	(PO_DATATEMPLATE_PKG.get_vendor_contact_details(ph.uda_template_id, ph.po_header_id, ph.draft_id)) vendor_contact_details,
		ph.clm_effective_date,
		decode(PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'mod_auth', 'INTERNAL_VALUE' ), 'CHANGE_PURSUNT_TO',
		       PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'auth_text', 'INTERNAL_VALUE' )) auth_text_change,
       		decode(PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'mod_auth', 'INTERNAL_VALUE' ), 'SUPPLEMENTAL_AGREE_PURSUANT', 
		       PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'auth_text', 'INTERNAL_VALUE' )) auth_text_supplemental,
       		decode(PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'mod_auth', 'INTERNAL_VALUE' ), 'OTHER_AUTHORITY', 
		       PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'auth_text', 'INTERNAL_VALUE' )) auth_text_other,
                (CASE WHEN Length(PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'justifiction', 'INTERNAL_VALUE' )) > 500 THEN 
                fnd_message.get_string('PO', 'PO_SEE_SCHEDULE')
                ELSE PO_UDA_PUB.get_single_attr_value(ph.uda_template_id, null, ph.po_header_id, ph.draft_id,  null, null, null, null, 'mod_Just', NULL, 'justifiction', 'INTERNAL_VALUE' ) 
                END) justification_summary,
		decode(nvl(ph.acceptance_required_flag, 'N'), 'N', 'N', 'Y') acceptance_required_flag,
		ph.clm_no_signed_copies_to_return,
                pdr.clm_noofcopies,
		pdr.clm_contract_officer mcco,
		ph.clm_contract_officer cco,
		ph.uda_template_id uti,
		po_conterms_utl_grp.get_po_contract_doctype(ph.type_lookup_code) base_contract_doctype,
		po_conterms_utl_grp.get_po_contract_doctype_mod(ph.type_lookup_code) mod_contract_doctype,
		(select approved_date from po_headers_archive_all where po_header_id = ph.po_header_id and revision_num = 0) conform_doc_eff_dt,
		decode(ph.type_lookup_code, 'STANDARD',
		--If the header type lookup code is standard
		decode((select count(distinct backing_pr) 
		        from 
				(SELECT h.segment1 AS backing_pr 
                 FROM po_distributions_merge_v pod, 
					  po_req_distributions_all prd, 
					  po_requisition_headers_all h, 
					  po_requisition_lines_all l  
	             WHERE pod.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id) 
                      and pod.draft_id = :p_draft_id 
                      and pod.req_distribution_id = prd.distribution_id 
                      and prd.requisition_line_id = l.requisition_line_id 
                      and l.requisition_header_id = h.requisition_header_id
	                  and nvl(par_flag,'N') = 'N'
                union 
                SELECT dft.modification_number AS backing_pr
				 FROM po_headers_draft_all header,po_drafts dft
				 WHERE dft.draft_id = header.draft_id
				 AND header.mod_draft_id = :p_draft_id
  		        UNION
	            SELECT dft.modification_number AS backing_pr
				 FROM po_lines_draft_all lines,po_drafts dft
				 WHERE dft.draft_id = lines.draft_id
				 AND lines.mod_draft_id = :p_draft_id
				union
				SELECT h.segment1 AS backing_pr
				 FROM po_line_locations_merge_v pol, po_requisition_headers_all h, po_requisition_lines_all l
				 WHERE pol.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id)  	 
				       and pol.draft_id = :p_draft_id
   	                   and pol.line_location_id = l.line_location_id
  					   and l.requisition_header_id = h.requisition_header_id
                       and Nvl(fund_source_not_known,'N') = 'Y'
               )
			 ) , 
			    --If no PR or PAR exists then simply null is returned and nothing is displayed       
				0, null,  
				1, 
				--If only one of PR or PAR exists then it has to be fetched
				(select distinct backing_pr 
				 from 
				 (SELECT h.segment1 AS backing_pr 
                 FROM po_distributions_merge_v pod, 
					  po_req_distributions_all prd, 
					  po_requisition_headers_all h, 
					  po_requisition_lines_all l  
	             WHERE pod.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id) 
                      and pod.draft_id = :p_draft_id 
                      and pod.req_distribution_id = prd.distribution_id 
                      and prd.requisition_line_id = l.requisition_line_id 
                      and l.requisition_header_id = h.requisition_header_id
	                  and nvl(par_flag,'N') = 'N'
                union 
                 SELECT dft.modification_number AS backing_pr
				 FROM po_headers_draft_all header,po_drafts dft
				 WHERE dft.draft_id = header.draft_id
				 AND header.mod_draft_id = :p_draft_id
  		        UNION
	            SELECT dft.modification_number AS backing_pr
				 FROM po_lines_draft_all lines,po_drafts dft
				 WHERE dft.draft_id = lines.draft_id
				 AND lines.mod_draft_id = :p_draft_id
				union 
				 SELECT h.segment1 AS backing_pr
				 FROM po_line_locations_merge_v pol, po_requisition_headers_all h, po_requisition_lines_all l
				 WHERE pol.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id)  	 
				       and pol.draft_id = :p_draft_id
   	                   and pol.line_location_id = l.line_location_id
  					   and l.requisition_header_id = h.requisition_header_id
                       and Nvl(fund_source_not_known,'N') = 'Y'
               )
				), 
				--If more than one PR or PAR exists then "See Schedule" is displayed
				fnd_message.get_string('PO', 'PO_SEE_SCHEDULE')
			  ),
		--If the header type lookup code is not standard
		decode((select count(distinct pbr.requisition_number ) 
		        from pon_backing_requisitions pbr, 
				     po_lines_merge_v l
	            where pbr.auction_header_id = l.auction_header_id 
				      and pbr.line_number = l.auction_line_number 
					  and l.po_header_id = ph.po_header_id 
					  and l.draft_id = ph.draft_id
				),
				0, NULL ,
                1, (select distinct pbr.requisition_number 
				    FROM pon_backing_requisitions pbr, 
					      po_lines_merge_v l 
				    where pbr.auction_header_id = l.auction_header_id 
					      and pbr.line_number = l.auction_line_number 
						  and l.po_header_id = ph.po_header_id 
						  and l.draft_id = ph.draft_id
					), 
				fnd_message.get_string('PO', 'PO_SEE_SCHEDULE')) ) ref_pr_num_t,
		(SELECT (XMLAgg(XMLElement("SF30_MOD_REF_PR_NUM",backing_pr))).getClobval()                                                      from 
		  (SELECT h.segment1 AS backing_pr 
			FROM po_distributions_draft_all pod, po_req_distributions_all prd, po_requisition_headers_all h, po_requisition_lines_all l  
			WHERE pod.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id) and pod.draft_id = :p_draft_id and pod.change_status = 'NEW' and pod.req_distribution_id = prd.distribution_id 
			and prd.requisition_line_id = l.requisition_line_id and l.requisition_header_id = h.requisition_header_id
			and nvl(par_flag,'N') = 'N'
		  union 
			 SELECT dft.modification_number AS backing_pr
				 FROM po_headers_draft_all header,po_drafts dft
				 WHERE dft.draft_id = header.draft_id
				 AND header.mod_draft_id = :p_draft_id
  		  UNION
	         SELECT dft.modification_number AS backing_pr
				 FROM po_lines_draft_all lines,po_drafts dft
				 WHERE dft.draft_id = lines.draft_id
				 AND lines.mod_draft_id = :p_draft_id
          union 
				 SELECT h.segment1 AS backing_pr
				 FROM po_line_locations_merge_v pol, po_requisition_headers_all h, po_requisition_lines_all l
				 WHERE pol.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id)  	 
				       and pol.draft_id = :p_draft_id
   	                   and pol.line_location_id = l.line_location_id
  					   and l.requisition_header_id = h.requisition_header_id
                       and Nvl(fund_source_not_known,'N') = 'Y'				 
		  )) SF30_MOD_REF_PR_NUM,
        Decode ((SELECT Count(DISTINCT backing_pr) 
          from 
          --Union of both backing par and pr
		  --Checks the count of both PR and PAR exists on the award
          (SELECT h.segment1 AS backing_pr 
           FROM po_distributions_draft_all pod, 
                po_req_distributions_all prd, 
                po_requisition_headers_all h, 
                po_requisition_lines_all l  
	         WHERE pod.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id) 
                 and pod.draft_id = :p_draft_id 
                 and pod.change_status = 'NEW' 
                 and pod.req_distribution_id = prd.distribution_id 
	               AND Nvl(pod.delete_flag, 'N') != 'Y' 
                 and prd.requisition_line_id = l.requisition_line_id 
                 and l.requisition_header_id = h.requisition_header_id
	               and nvl(par_flag,'N') = 'N'
           union 
			 SELECT dft.modification_number AS backing_pr
				 FROM po_headers_draft_all header,po_drafts dft
				 WHERE dft.draft_id = header.draft_id
				 AND header.mod_draft_id = :p_draft_id
  		  UNION
	         SELECT dft.modification_number AS backing_pr
				 FROM po_lines_draft_all lines,po_drafts dft
				 WHERE dft.draft_id = lines.draft_id
				 AND lines.mod_draft_id = :p_draft_id 
          union 
				 SELECT h.segment1 AS backing_pr
				 FROM po_line_locations_merge_v pol, po_requisition_headers_all h, po_requisition_lines_all l
				 WHERE pol.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id)  	 
				       and pol.draft_id = :p_draft_id
   	                   and pol.line_location_id = l.line_location_id
  					   and l.requisition_header_id = h.requisition_header_id
                       and Nvl(fund_source_not_known,'N') = 'Y'				 
          ))
         --If no PR or PAR exists then simply null is returned and nothing is displayed       
	     ,0,NULL
         ,1 
         --If only one of PR or PAR exists then it is displayed
		 ,(SELECT DISTINCT backing_pr 
           from
		   (SELECT h.segment1 AS backing_pr 
            FROM po_distributions_draft_all pod, 
                po_req_distributions_all prd, 
                po_requisition_headers_all h, 
                po_requisition_lines_all l  
	        WHERE pod.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id) 
                 and pod.draft_id = :p_draft_id 
                 and pod.change_status = 'NEW' 
                 and pod.req_distribution_id = prd.distribution_id 
	               AND Nvl(pod.delete_flag, 'N') != 'Y' 
                 and prd.requisition_line_id = l.requisition_line_id 
                 and l.requisition_header_id = h.requisition_header_id
	               and nvl(par_flag,'N') = 'N'
           union 
			 SELECT dft.modification_number AS backing_pr
				 FROM po_headers_draft_all header,po_drafts dft
				 WHERE dft.draft_id = header.draft_id
				 AND header.mod_draft_id = :p_draft_id
  		  UNION
	         SELECT dft.modification_number AS backing_pr
				 FROM po_lines_draft_all lines,po_drafts dft
				 WHERE dft.draft_id = lines.draft_id
				 AND lines.mod_draft_id = :p_draft_id
          union 
				 SELECT h.segment1 AS backing_pr
				 FROM po_line_locations_merge_v pol, po_requisition_headers_all h, po_requisition_lines_all l
				 WHERE pol.po_header_id = (select document_id from po_drafts where draft_id = :p_draft_id)  	 
				       and pol.draft_id = :p_draft_id
   	                   and pol.line_location_id = l.line_location_id
  					   and l.requisition_header_id = h.requisition_header_id
                       and Nvl(fund_source_not_known,'N') = 'Y'				 
            )
		 ), 
        --If more than one PR or PAR exists then 'See Schedule' is displayed
		fnd_message.Get_string('PO', 'PO_SEE_SCHEDULE'))  FP_SF30_MOD_REF_PR_NUM_T,
        fnd_message.get_string('PO', 'PO_SEE_SCHEDULE') SEE_SCHEDULE_MSG
	FROM po_drafts pdr,
	     po_headers_draft_all ph
	WHERE pdr.draft_id = :p_draft_id
	      and ph.po_header_id = pdr.document_id
	      and ph.draft_id = :p_draft_id