PO Modification Data template
Description
Categories: BI Publisher
Application: Purchasing
Source:
Short Name: PO_MODIFICATION
DB package: PO_SGD_PKG
Source:
Short Name: PO_MODIFICATION
DB package: PO_SGD_PKG
Run
PO Modification Data template and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |