Loan IQ Function Syntax

The purpose of this document is to provide a breakdown of the structure of MySis LoanIQ functions. This one does an extraction of Bills using the XML Inquirey API function

<!--[if gte mso 9]> James Cooke James Cooke 2 50 2013-03-05T13:28:00Z 2013-03-05T13:28:00Z 2 475 2710 Farm Credit Bank of Texas 22 6 3179 14.00 <![endif]

declare function BillTest(){

       <FacilitySection>

{ for $fac in /Facility[LIQ.XQS.EQUAL(@id,';P9KH7WQ')] return

                     <facilityId>{$fac/fac/id}</facilityId>

                     <facilityName>{$fac/fac/name}</facilityName>

                     <facilityNum>{$fac/fac/controlNumber}</facilityNum>

                     <type>{$fac/fac/type}</type>

                     <displayStatus>{$fac/fac/displayStatus}</displayStatus>

                     { for $ost in /Outstanding[LIQ.XQS.EQUAL(@objectStateCode,'LRELS')][LIQ.XQS.EQUAL(@facilityId,$fac/fac/id)] return

                            <OutstandingSection>

                                  <ostId>{$ost/ost/id}</ostId>

                                  <alias>{$ost/ost/alias}</alias>

                                  <currency>{$ost/ost/currency}</currency>

                                  <type>{$ost/ost/outstandingType}</type>

                                  <borrowerId>{$ost/ost/borrowerId}</borrowerId>

                                  <balance>{$ost/ost/currentAmount}</balance>

                                   {BillInterest($ost/ost/id)}

                                  {BillPrincipal($ost/ost/id)}

                            </OutstandingSection>

                     }

                     { for $fee in /AccruedFee[LIQ.XQS.EQUAL(@objectStateCode,'RELSD')][LIQ.XQS.EQUAL(@facilityId,$fac/fac/id)] return

                     <CommitmentFeeSection>

                            <feeId>{$fee/fee/id}</feeId>     

                            <currency>{$fee/fee/currency}</currency>

                            <type>{$fee/fee/type}</type>

                            <borrowerId>{$fee/fee/borrower}</borrowerId>

                            <balance>{$fee/fee/balanceAmount}</balance>

                            <rate>{$fee/fee/currentRate}</rate>

                            {BillFee($fee/fee/id)}

                     </CommitmentFeeSection>

                     }

}

       </FacilitySection>

};

declare function BillInterest($ostId){ <InterestCycle> { for $int in AccrualCycle[LIQ.XQS.EQUAL(@ownerId,$ostId)] return   <InterestRecord>        startDate{$intaccstartDate}startDate        endDate{$intaccendDate}endDate        dueDate{$intaccdueDate}dueDate        adjustedDueDate{$intaccadjustedDueDate}adjustedDueDate        cycleDue{$intacccycleDue}cycleDue        adjustedCycleDue{$intaccadjustedCycleDueThruLastNight}adjustedCycleDue        cycleBilledAmount{$intacccycleBilledAmount}cycleBilledAmount   <InterestRecord/> } </InterestCycle> };

declare function BillPrincipal($ostId){ <Principal Section> { for $sci in /LoanPaymentSchedule[LIQ.XQS.EQUAL(@ownerId,$ostId)]/LoanPaymentScheduleItem return   <Principal Record>     <effectiveDate>{$sci/sci/effectiveDate}</effectiveDate>     <principalDue>{$sci/sci/principal}</principalDue>     <principalPaid>{$sci/sci/principalPaid}</principalPaid>     <principalBilledAmount>{$sci/sci/principalBilledAmount}</principalBilledAmount>   </Principal Record> } </Principal Section> };

declare function BillFee($feeId){ <FeeCycle> { for $acc in /AccrualCycle[LIQ.XQS.EQUAL(@ownerId,$feeId)] return        <startDate>{$acc/acc/startDate}</startDate>        <endDate>{$acc/acc/endDate}</endDate>        <dueDate>{$acc/acc/dueDate}</dueDate>        <adjustedDueDate>{$acc/acc/adjustedDueDate}</adjustedDueDate>        <cycleDue>{$acc/acc/cycleDue}</cycleDue>        <adjustedCycleDue>{$acc/acc/adjustedCycleDueThruLastNight}</adjustedCycleDue>        <cycleBilledAmount>{$acc/acc/cycleBilledAmount}</cycleBilledAmount> } </FeeCycle> };

For-loop syntax <mySectionTag>   {for $myCustomVariableName in LIQObject[LIQComparFunc(targetVar, myVar)] return     <WhatIWantToCallThisValue>$myCustomVariableName/LIQObjectPrefix/LIQColumnName</WhatIWantToCallThisValue>   } </mySectionTag> Function Declaration syntax declare function MyFunc($myVariable)} <MyXMLTag> //my stuff goes in here </MyXMLTag>

Results

<FacilitySection>

<facilityId>;P9KH7WQ</facilityId>

                        <facilityName>$7MM TERM</facilityName>

                        <facilityNum>00000715</facilityNum>

                        <type>TAM</type>

                        <displayStatus>Active</displayStatus>

                        <OutstandingSection>

                        <ostId>K(9KRRWM</ostId>

                        <alias>89793502</alias>

                        <currency>USD</currency>

                        <type>LOAN</type>

                        <borrowerId>;P9KH7KO</borrowerId>

                        <balance>4312769.07</balance>

                        <InterestCycle>

<InterestRecord>

                        <startDate>2011-08-01</startDate>

                        <endDate>2011-10-31</endDate>

                        <dueDate>2011-11-01</dueDate>

                        <adjustedDueDate>2011-11-01</adjustedDueDate>

                        <cycleDue>0</cycleDue>

                        <adjustedCycleDue>0</adjustedCycleDue>

                        <cycleBilledAmount>0</cycleBilledAmount>

</InterestRecord>

<InterestRecord>

                        <startDate>2011-11-01</startDate>

                        <endDate>2012-01-31</endDate>

                        <dueDate>2012-02-01</dueDate>

                        <adjustedDueDate>2012-02-01</adjustedDueDate>

                        <cycleDue>0</cycleDue>

                        <adjustedCycleDue>0</adjustedCycleDue>

                        <cycleBilledAmount>0</cycleBilledAmount>

</InterestRecord>

<InterestRecord>

                        <startDate>2012-02-01</startDate>

                        <endDate>2012-04-30</endDate>

                        <dueDate>2012-05-01</dueDate>

                        <adjustedDueDate>2012-05-01</adjustedDueDate>

                        <cycleDue>0</cycleDue>

                        <adjustedCycleDue>0</adjustedCycleDue>

                        <cycleBilledAmount>0</cycleBilledAmount>

</InterestRecord>

<InterestRecord>

                        <startDate>2012-05-01</startDate>

                        <endDate>2012-07-31</endDate>

                        <dueDate>2012-08-01</dueDate>

                        <adjustedDueDate>2012-08-01</adjustedDueDate>

                        <cycleDue>0</cycleDue>

                        <adjustedCycleDue>0</adjustedCycleDue>

                        <cycleBilledAmount>0</cycleBilledAmount>

</InterestRecord>

<InterestRecord>

                        <startDate>2012-08-01</startDate>

                        <endDate>2012-10-31</endDate>

                        <dueDate>2012-11-01</dueDate>

                        <adjustedDueDate>2013-02-01</adjustedDueDate>

                        <cycleDue>53.83</cycleDue>

                        <adjustedCycleDue>53.83</adjustedCycleDue>

                        <cycleBilledAmount>0</cycleBilledAmount>

</InterestRecord>

<InterestRecord>

                        <startDate>2011-05-06</startDate>

                        <endDate>2011-07-31</endDate>

                        <dueDate>2011-08-01</dueDate>

                        <adjustedDueDate>2011-08-01</adjustedDueDate>

                        <cycleDue>0</cycleDue>

                        <adjustedCycleDue>0</adjustedCycleDue>

                        <cycleBilledAmount>0</cycleBilledAmount>

</InterestRecord>

<InterestRecord>

                        <startDate>2012-11-01</startDate>

                        <endDate>2013-01-31</endDate>

                        <dueDate>2013-02-01</dueDate>

                        <adjustedDueDate>2013-02-01</adjustedDueDate>

                        <cycleDue>12020.65</cycleDue>

                        <adjustedCycleDue>12020.65</adjustedCycleDue>

                        <cycleBilledAmount>0</cycleBilledAmount>

</InterestRecord>

</InterestCycle>

                        <PrincipalSection>

<PrincipalRecord>

<effectiveDate>2013-08-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2013-11-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2013-02-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2013-05-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2014-02-03</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2014-05-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2014-08-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2015-11-02</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2016-02-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2016-05-02</effectiveDate>

<principalDue>-3312407.5</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2014-11-03</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2015-02-02</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2015-05-01</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

<PrincipalRecord>

<effectiveDate>2015-08-03</effectiveDate>

<principalDue>-76950.89</principalDue>

<principalPaid>0</principalPaid>

<principalBilledAmount>0</principalBilledAmount>

</PrincipalRecord>

</PrincipalSection>

                        </OutstandingSection>

                       

                        </FacilitySection>