select pcc1.clm_issuing_office issue_office,
pcc1.clm_document_number ,
pcc1.approved_date ,
pcc1.agent_id document_buyer_agent_id ,
pcc1.creation_date ,
pcc1.display_name ,
pcc1.currency_code ,
(select ppf.full_name
from per_people_f ppf
where ppf.person_id = pcc1.clm_contract_officer
and trunc(sysdate) between effective_start_date and effective_end_date
) contract_ofc_contact ,
po_uda_pub.get_address_attr_value(pcc1.uda_template_id, null, pcc1.po_header_id,
-1, null, null, null, null, 'addresses', null, 'addresscode',
'ISSUING_OFFICE', 'DISPLAY_VALUE')issue_office_code,
po_uda_pub.get_address_attr_value(pcc1.uda_template_id, null, pcc1.po_header_id,
-1, null, null, null, null, 'addresses', null, 'location',
'ISSUING_OFFICE', 'DISPLAY_VALUE')issue_office_name ,
po_document_totals_pvt.getamountordered('HEADER',pcc1.po_header_id, 'TRANSACTION' , null, -1) total_amount ,
(select displayed_field
from po_lookup_codes
where lookup_type = 'PO_CLM_CAR_REL_WO_REP_REASON'
and lookup_code = pcc1.rel_without_rpt_reason
) rel_wo_rsn_name ,
pcc1.car_status ,
pcc1.reporting_method,
decode(pcc1.car_status, 'APPROVED', pcc1.date_signed, null) date_reported
from
(select pha.clm_issuing_office , pha.clm_document_number, pha.approved_date,
pha.agent_id , pha.creation_date , pds.display_name ,
pha.currency_code , pha.clm_contract_officer, pha.uda_template_id,
pha.org_id , pha.po_header_id , pcc.rel_without_rpt_reason,
pcc.car_status, nvl(pcc.car_status, 'NOT_REPORTED') where_car_status,
pcc.reporting_method,nvl(pcc.reporting_method, 'NONE') where_reporting_method,
pcc.date_signed
from po_headers pha ,
po_clm_cars pcc ,
po_doc_style_lines_vl pds,
po_doc_style_headers pdsh
where pds.style_id = pha.style_id
and pds.document_subtype = pha.type_lookup_code
and pdsh.style_id = pha.style_id
and pdsh.clm_flag = 'Y'
and pha.authorization_status = 'APPROVED'
and pha.po_header_id = pcc.po_header_id(+)
and pcc.po_draft_id(+) = -1
) pcc1
where pcc1.where_reporting_method <> 'EXEMPT'
and &rep_status_where
and &doc_date_where
union all
select pcc1.clm_mod_issuing_office issue_office,
pcc1.modification_number clm_document_number ,
pcc1.approved_date ,
pcc1.agent_id document_buyer_agent_id ,
pcc1.creation_date ,
pcc1.display_name ,
pcc1.currency_code ,
(select ppf.full_name
from per_people_f ppf
where ppf.person_id = pcc1.clm_contract_officer
and trunc(sysdate) between effective_start_date and effective_end_date
) contract_ofc_contact ,
po_uda_pub.get_address_attr_value(pcc1.uda_template_id, null, pcc1.po_header_id,
pcc1.draft_id, null, null, null, null, 'addresses', null, 'addresscode',
'MOD_ISSUING_OFFICE', 'DISPLAY_VALUE') issue_office_code,
po_uda_pub.get_address_attr_value(pcc1.uda_template_id, null, pcc1.po_header_id,
pcc1.draft_id, null, null, null, null, 'addresses', null, 'location',
'MOD_ISSUING_OFFICE', 'DISPLAY_VALUE') issue_office_name ,
po_document_totals_pvt.getamountordered('HEADER',pcc1.po_header_id, 'TRANSACTION' , null, pcc1.draft_id) total_amount ,
(select displayed_field
from po_lookup_codes
where lookup_type = 'PO_CLM_CAR_REL_WO_REP_REASON'
and lookup_code = pcc1.rel_without_rpt_reason
) rel_wo_rsn_name ,
pcc1.car_status ,
pcc1.reporting_method,
decode(pcc1.car_status, 'APPROVED', pcc1.date_signed, null) date_reported
from
(select pha.clm_mod_issuing_office , pd.modification_number, pha.approved_date,
pha.agent_id, pha.creation_date, pds.display_name,
pha.currency_code, pd.clm_contract_officer, pha.uda_template_id,
pha.org_id, pha.po_header_id , pha.draft_id ,
pcc.rel_without_rpt_reason,
pcc.car_status, nvl(pcc.car_status, 'NOT_REPORTED') where_car_status,
pcc.reporting_method, nvl(pcc.reporting_method, 'NONE') where_reporting_method,
pcc.date_signed
from po_headers_draft_all pha,
po_headers ph ,
po_drafts pd ,
po_clm_cars pcc ,
po_doc_style_lines_vl pds ,
po_doc_style_headers pdsh
where pds.style_id = pha.style_id
and pds.document_subtype = pha.type_lookup_code
and pdsh.style_id = pha.style_id
and pdsh.clm_flag = 'Y'
and pd.status = 'COMPLETED'
and pha.po_header_id = ph.po_header_id
and pd.draft_id = pha.draft_id
AND PD.DRAFT_TYPE = 'MOD'
and pd.document_id = pha.po_header_id
and pha.po_header_id = pcc.po_header_id(+)
and pha.draft_id = pcc.po_draft_id(+)
) pcc1
where pcc1.where_reporting_method <> 'EXEMPT'
and &rep_status_where
and &doc_date_where
ORDER BY 1, 2 |