PO FPDS Compliance

        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