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 |
