P_INSURANCEOPENAPPLICATIONJRNL

@VDM:           {   viewType:           #COMPOSITE,
                    private:            true}
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED

define view entity P_InsuranceOpenApplicationJrnl
  as select from            I_InsurApplJournalHeaderBasic as JournalHeader
    left outer to many join I_InsurApplJournalEntryBasic  as JournalEntry          on  JournalHeader.InsurPlcyApplUUID   = JournalEntry.InsurPlcyApplUUID
                                                                                   and JournalHeader.InsurPlcyInsurContr = JournalEntry.InsurPlcyInsurContr
  // Exclude those applications, which were already copied to contract journal

    left outer to one join  I_InsurPlcyJournalHeaderBasic as ContractJournalHeader on  ContractJournalHeader.InsurPlcyApplUUID = JournalHeader.InsurPlcyApplUUID
                                                                                   and ContractJournalHeader.InsurPlcyUUID     = JournalHeader.InsurPlcyUUID
  // Exclude those postdated applications, which were already released

    left outer to one join  I_InsurApplActnJrnlEntryBasic as ActionEntry           on  JournalHeader.InsurPlcyApplUUID   = ActionEntry.InsurPlcyApplUUID
                                                                                   and JournalHeader.InsurPlcyInsurContr = ActionEntry.InsurPlcyInsurContr
                                                                                   and 'APPROVEPOSTDT'                   = ActionEntry.InsurPlcyChangeProcessID // released postdated application

{

  key JournalHeader.InsurPlcyApplUUID,
  key JournalHeader.InsuranceApplication,
  key JournalHeader.InsurPlcyUUID,
  key JournalHeader.InsurPlcyInsurContr,
      JournalHeader.InsuranceApplicationStatus,
      JournalHeader.InsurPlcyBusProcType,
      JournalHeader.InsurPlcyBusProcessNumerator,
      JournalHeader.InsurApplRcptDte,
      JournalHeader.InsurApplSgntrDte,
      JournalHeader.InsurPlcyBusProcEffectiveDate,
      JournalHeader.InsurApplChgRsn,
      JournalHeader.InsurApplChgRsnText,
      JournalHeader.CreatedByUser,
      // For very new open applications it could happen, that there are no processings in table /PM0/ABDJAPL2. Therefore we have to use

      // the load-journalnumber in table /PM0/ABDJAPL1 instead.

      case
      when max( JournalEntry.InsurPlcyJrnlNmbr ) > JournalHeader.InsurPlcyCreateJrnlNmbr then max( JournalEntry.InsurPlcyJrnlNmbr )
      else JournalHeader.InsurPlcyCreateJrnlNmbr
      end as InsurPlcyContrDataDetnJrnlNmbr

}
where
  (
        JournalHeader.InsuranceApplicationStatus = '100' // open

    or  JournalHeader.InsuranceApplicationStatus = '105' // notfora2p

    or  JournalHeader.InsuranceApplicationStatus = '135' // predated

    or  JournalHeader.InsuranceApplicationStatus = '136' // pred_pol

    or  JournalHeader.InsuranceApplicationStatus = '145' // cancelled

    or  JournalHeader.InsuranceApplicationStatus = '170' // ext_chng

    or  JournalHeader.InsuranceApplicationStatus = '180' // post_ext

  )

  and   ContractJournalHeader.InsurPlcyApplUUID  is null // Nothing in contract journal ==> take it

  and   not(
      JournalHeader.InsuranceApplicationStatus   = '135'
      and ActionEntry.InsurPlcyApplUUID          is not null
    ) // Postdated application not yet released ==> take it

group by
  JournalHeader.InsurPlcyApplUUID,
  JournalHeader.InsurPlcyUUID,
  JournalHeader.InsurPlcyInsurContr,
  JournalHeader.InsurPlcyCreateJrnlNmbr,
  JournalHeader.InsuranceApplication,
  JournalHeader.InsuranceApplicationStatus,
  JournalHeader.InsurPlcyBusProcType,
  JournalHeader.InsurPlcyBusProcessNumerator,
  JournalHeader.InsurApplRcptDte,
  JournalHeader.InsurApplSgntrDte,
  JournalHeader.InsurPlcyBusProcEffectiveDate,
  JournalHeader.InsurApplChgRsn,
  JournalHeader.CreatedByUser,
  JournalHeader.InsurApplChgRsnText
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_INSURAPPLACTNJRNLENTRYBASIC",
"I_INSURAPPLJOURNALENTRYBASIC",
"I_INSURAPPLJOURNALHEADERBASIC",
"I_INSURPLCYJOURNALHEADERBASIC"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/