Test

2012-01-27T18:51:00.001-08:00
Test
test

2011-07-03T09:35:00.001-07:00
   1 /* Formatted on 2011/03/28 09:40 (Formatter Plus v4.8.8) */
   2 CREATE OR REPLACE PROCEDURE bafdbqa.speodincomerecognizitiononeom (
   3    p_branchid            IN       VARCHAR2,
   4    p_businnesdate        IN       DATE,
   5    p_startdate           IN       DATE,
   6    p_enddate             IN       DATE,
   7    p_usrupd              IN       VARCHAR2,
   8    p_dtmupd              IN       DATE,
   9    p_incrementdiffdays   OUT      INT,
  10    p_dmlaffected         OUT      INT,
  11    p_cursor              OUT      sys_refcursor
  12 )
  13 AS
  14 /******************************************************************************
  15    NAME       :  speodcolpmtdcr
  16    PURPOSE    :
  17 
  18    REVISIONS  :
  19    Ver        Date        Author           Description
  20    ---------  ----------  ---------------  ------------------------------------
  21    1.0        23/11/2010  KHRS             1. Created this procedure.
  22    1.1        23/11/2010  KHRS             1. Tuned Query
  23 
  24    NOTES:
  25 
  26    Automatically available Auto Replace Keywords:
  27       Object Name  : speodcolpmtdcr
  28       Sysdate      : 27/09/2010
  29 
  30 ******************************* Variable for Cursor ***********************************************/
  31    TYPE s_recognizeamount IS TABLE OF installmentschedule.amountincrecognize%TYPE;
  32    TYPE s_incentiverecognize IS TABLE OF installmentschedule.incentiverecognize%TYPE;
  33    TYPE s_provisionrecognize IS TABLE OF installmentschedule.provisionrecognize%TYPE;
  34    TYPE s_diffraterecognize IS TABLE OF installmentschedule.diffraterecognize%TYPE;
  35    TYPE s_adminfeerecognize IS TABLE OF installmentschedule.adminfeerecognize%TYPE;
  36    TYPE s_insurancerefundrecognize IS TABLE OF installmentschedule.insurancerefundrecognize%TYPE;
  37    TYPE s_insuranceincomerecognize IS TABLE OF installmentschedule.insuranceincomerecognize%TYPE;
  38    TYPE s_otherrefundrecognize IS TABLE OF installmentschedule.otherrefundrecognize%TYPE;
  39    TYPE s_otherfeerecognize IS TABLE OF installmentschedule.otherfeerecognize%TYPE;
  40    TYPE s_surveyfeerecognize IS TABLE OF installmentschedule.surveyfeerecognize%TYPE;
  41    TYPE s_erincentiverecognize IS TABLE OF installmentschedule.erincentiverecognize%TYPE;
  42    TYPE s_erdiscountrecognize IS TABLE OF installmentschedule.erdiscountrecognize%TYPE;
  43    TYPE s_branchid IS TABLE OF installmentschedule.branchid%TYPE;
  44    TYPE s_applicationid IS TABLE OF installmentschedule.applicationid%TYPE;
  45    TYPE s_insseqno IS TABLE OF installmentschedule.insseqno%TYPE;
  46    l_recognizeamount            s_recognizeamount;
  47    l_incentiverecognize         s_incentiverecognize;
  48    l_provisionrecognize         s_provisionrecognize;
  49    l_diffraterecognize          s_diffraterecognize;
  50    l_adminfeerecognize          s_adminfeerecognize;
  51    l_insurancerefundrecognize   s_insurancerefundrecognize;
  52    l_insuranceincomerecognize   s_insuranceincomerecognize;
  53    l_otherrefundrecognize       s_otherrefundrecognize;
  54    l_otherfeerecognize          s_otherfeerecognize;
  55    l_surveyfeerecognize         s_surveyfeerecognize;
  56    l_erincentiverecognize       s_erincentiverecognize;
  57    l_erdiscountrecognize        s_erdiscountrecognize;
  58    l_branchid                   s_branchid;
  59    l_applicationid              s_applicationid;
  60    l_insseqno                   s_insseqno;
  61 /********************************** Variable support ********************************************/
  62    v_count                      INT;
  63    v_isc_rowaffected            INT;
  64    v_inc_rowaffected            INT;
  65    v_incrementdiffdays          INT;
  66 BEGIN
  67    INSERT INTO tmp_increcognizitiononeom_agr
  68       SELECT branchid, applicationid, productid, effectivedate, agreementno,
  69              currencyid
  70       FROM   agreement
  71       WHERE  (contractstatus = 'LIV' OR contractstatus = 'RRD')
  72       AND    defaultstatus = 'NM'
  73       AND    is_et = 0
  74       AND    TRUNC (effectivedate) <
  75                                    TRUNC (TO_DATE ('3/31/2011', 'MM/DD/YYYY'));
  76 
  77    INSERT INTO tmp_incomerecognizitiononeom
  78       SELECT branchid, applicationid, currencyid, insseqno, duedate,
  79              interestamount,
  80              NVL (lastincrecognize, prevduedate) lastincrecognize,
  81              amountincrecognize amountincrecognize, incentive,
  82              incentiveoneom incentiverecognize, provision,
  83              provisiononeom provisionrecognize, diffrateamount,
  84              diffrateoneom diffraterecognize, adminfee,
  85              adminfeeoneom adminfeerecognize,
  86              insurancerefundoneom insurancerefundrecognize,
  87              insuranceincomeoneom insuranceincomerecognize,
  88              otherrefundoneom otherrefundrecognize,
  89              otherfeeoneom otherfeerecognize,
  90              surveyfeeoneom surveyfeerecognize,
  91              erincentiveoneom erincentiverecognize,
  92              erdiscountoneom erdiscountrecognize, prevduedate, effectivedate,
  93              productid, agreementno, journalschemeid,
  94              interestoneom recognizeamount, thedate startperiod,
  95              p_businnesdate endperiod, diffdays + 1 recognizedays,
  96              fundingcoyportion
  97       FROM   (SELECT agreement.branchid, agreement.applicationid,
  98                      agreement.currencyid,
  99                        NVL
 100                           (installmentschedule.incentiveoneom,
 101                            0)
 102                      - NVL (installmentschedule.incentiverecognize, 0)
 103                                                                incentiveoneom,
 104                        NVL
 105                           (installmentschedule.provisiononeom,
 106                            0)
 107                      - NVL (installmentschedule.provisionrecognize, 0)
 108                                                                provisiononeom,
 109                        NVL (installmentschedule.diffrateoneom,
 110                             0)
 111                      - NVL (installmentschedule.diffraterecognize, 0)
 112                                                                 diffrateoneom,
 113                        NVL (installmentschedule.adminfeeoneom,
 114                             0)
 115                      - NVL (installmentschedule.adminfeerecognize, 0)
 116                                                                 adminfeeoneom,
 117                        NVL
 118                           (installmentschedule.insurancerefundoneom,
 119                            0)
 120                      - NVL (installmentschedule.insurancerefundrecognize, 0)
 121                                                          insurancerefundoneom,
 122                        NVL
 123                           (installmentschedule.insuranceincomeoneom,
 124                            0)
 125                      - NVL (installmentschedule.insuranceincomerecognize, 0)
 126                                                          insuranceincomeoneom,
 127                        NVL
 128                           (installmentschedule.otherrefundoneom,
 129                            0)
 130                      - NVL (installmentschedule.otherrefundrecognize, 0)
 131                                                              otherrefundoneom,
 132                        NVL (installmentschedule.otherfeeoneom,
 133                             0)
 134                      - NVL (installmentschedule.otherfeerecognize, 0)
 135                                                                 otherfeeoneom,
 136                        NVL
 137                           (installmentschedule.surveyfeeoneom,
 138                            0)
 139                      - NVL (installmentschedule.surveyfeerecognize, 0)
 140                                                                surveyfeeoneom,
 141                        NVL
 142                           (installmentschedule.erincentiveoneom,
 143                            0)
 144                      - NVL (installmentschedule.erincentiverecognize, 0)
 145                                                              erincentiveoneom,
 146                        NVL
 147                           (installmentschedule.erdiscountoneom,
 148                            0)
 149                      - NVL (installmentschedule.erdiscountrecognize, 0)
 150                                                               erdiscountoneom,
 151                        NVL (installmentschedule.interestoneom,
 152                             0)
 153                      - NVL (installmentschedule.amountincrecognize, 0)
 154                                                                 interestoneom,
 155                      installmentschedule.insseqno,
 156                      installmentschedule.duedate,
 157                      installmentschedule.interestamount,
 158                      installmentschedule.lastincrecognize,
 159                      installmentschedule.amountincrecognize,
 160                      NVL (installmentschedule.incentive, 0) incentive,
 161                      NVL (installmentschedule.provision, 0) provision,
 162                      NVL (installmentschedule.diffrateamount,
 163                           0) diffrateamount,
 164                      NVL (installmentschedule.adminfee, 0) adminfee,
 165                      NVL
 166                         (installmentschedule.insurancerefund,
 167                          0) insurancerefund,
 168                      NVL
 169                         (installmentschedule.insuranceincomeamount,
 170                          0) insuranceincomeamount,
 171                      NVL (installmentschedule.otherrefund, 0) otherrefund,
 172                      NVL (installmentschedule.otherfee, 0) otherfee,
 173                      NVL (installmentschedule.surveyfee, 0) surveyfee,
 174                      NVL (installmentschedule.erincentive, 0) erincentive,
 175                      NVL (installmentschedule.erdiscount, 0) erdiscount,
 176                      NVL (is2.duedate, agreement.effectivedate) prevduedate,
 177                      agreement.effectivedate, agreement.productid,
 178                      agreement.agreementno, productbranch.journalschemeid,
 179                      CASE
 180                         WHEN isdate
 181                                 (installmentschedule.lastincrecognize) =
 182                                                                     1
 183                            THEN installmentschedule.lastincrecognize
 184                         ELSE NVL (is2.duedate,
 185                                   agreement.effectivedate)
 186                      END thedate,
 187                      datediff
 188                         ('DAY',
 189                          (CASE
 190                              WHEN isdate (installmentschedule.lastincrecognize) =
 191                                                                              1
 192                                 THEN installmentschedule.lastincrecognize
 193                              ELSE NVL (is2.duedate, agreement.effectivedate)
 194                           END
 195                          ),
 196                          p_businnesdate) diffdays,
 197                      installmentschedule.fundingcoyportion
 198               FROM   (SELECT *
 199                       FROM   installmentschedule
 200                       WHERE  (installmentschedule.duedate
 201                                  BETWEEN TRUNC (p_startdate)
 202                                      AND TRUNC (p_enddate)
 203                              )
 204                       AND    (   (installmentschedule.amountincrecognize <
 205                                             installmentschedule.interestamount
 206                                  )
 207                               OR (ABS (installmentschedule.incentiverecognize) <
 208                                            ABS (installmentschedule.incentive)
 209                                  )
 210                               OR (ABS (installmentschedule.provisionrecognize) <
 211                                            ABS (installmentschedule.provision)
 212                                  )
 213                               OR (ABS (installmentschedule.diffraterecognize) <
 214                                       ABS (installmentschedule.diffrateamount)
 215                                  )
 216                               OR (ABS (installmentschedule.adminfeerecognize) <
 217                                             ABS (installmentschedule.adminfee)
 218                                  )
 219                               OR (ABS
 220                                      (installmentschedule.insurancerefundrecognize) <
 221                                      ABS (installmentschedule.insurancerefund)
 222                                  )
 223                               OR (ABS
 224                                      (installmentschedule.insuranceincomerecognize) <
 225                                      ABS
 226                                         (installmentschedule.insuranceincomeamount)
 227                                  )
 228                               OR (ABS
 229                                      (installmentschedule.otherrefundrecognize) <
 230                                          ABS (installmentschedule.otherrefund)
 231                                  )
 232                               OR (ABS (installmentschedule.otherfeerecognize) <
 233                                             ABS (installmentschedule.otherfee)
 234                                  )
 235                               OR (ABS (installmentschedule.surveyfeerecognize) <
 236                                            ABS (installmentschedule.surveyfee)
 237                                  )
 238                               OR (ABS
 239                                      (installmentschedule.erincentiverecognize) <
 240                                          ABS (installmentschedule.erincentive)
 241                                  )
 242                               OR (ABS (installmentschedule.erdiscountrecognize) <
 243                                           ABS (installmentschedule.erdiscount)
 244                                  )
 245                              )
 246                       AND    (installmentschedule.branchid = p_branchid)) installmentschedule
 247                      INNER JOIN
 248                      tmp_increcognizitiononeom_agr agreement
 249                      ON  installmentschedule.branchid = agreement.branchid
 250                      AND installmentschedule.applicationid =
 251                                                        agreement.applicationid
 252                      INNER JOIN productbranch
 253                      ON  agreement.branchid = productbranch.branchid
 254                      AND agreement.productid = productbranch.productid
 255                      LEFT JOIN installmentschedule is2
 256                      ON  is2.branchid = installmentschedule.branchid
 257                      AND is2.applicationid = installmentschedule.applicationid
 258                      AND is2.insseqno = installmentschedule.insseqno - 1
 259                      ) qry1
 260       WHERE  datediff ('DAY', prevduedate, duedate) > 0
 261       AND    TRUNC (p_businnesdate) > TRUNC (thedate);
 262 
 263    IF p_cursor%ISOPEN
 264    THEN
 265       CLOSE p_cursor;
 266    END IF;
 267 
 268    OPEN p_cursor FOR
 269       SELECT recognizeamount, incentiverecognize, provisionrecognize,
 270              diffraterecognize, adminfeerecognize, insurancerefundrecognize,
 271              insuranceincomerecognize, otherrefundrecognize,
 272              otherfeerecognize, surveyfeerecognize, erincentiverecognize,
 273              erdiscountrecognize, branchid, applicationid, insseqno
 274       FROM   tmp_incomerecognizitiononeom;
 275 
 276    LOOP
 277       FETCH p_cursor
 278       BULK COLLECT INTO l_recognizeamount, l_incentiverecognize,
 279              l_provisionrecognize, l_diffraterecognize, l_adminfeerecognize,
 280              l_insurancerefundrecognize, l_insuranceincomerecognize,
 281              l_otherrefundrecognize, l_otherfeerecognize,
 282              l_surveyfeerecognize, l_erincentiverecognize,
 283              l_erdiscountrecognize, l_branchid, l_applicationid, l_insseqno LIMIT 50;
 284 
 285       EXIT WHEN l_applicationid.COUNT = 0;
 286       FORALL idx IN 1 .. l_applicationid.COUNT
 287          UPDATE installmentschedule
 288             SET lastincrecognize = TRUNC (p_businnesdate),
 289                 amountincrecognize =
 290                                   amountincrecognize + l_recognizeamount (idx),
 291                 incentiverecognize =
 292                                incentiverecognize + l_incentiverecognize (idx),
 293                 provisionrecognize =
 294                                provisionrecognize + l_provisionrecognize (idx),
 295                 diffraterecognize =
 296                                  diffraterecognize + l_diffraterecognize (idx),
 297                 adminfeerecognize =
 298                                  adminfeerecognize + l_adminfeerecognize (idx),
 299                 insurancerefundrecognize =
 300                    insurancerefundrecognize + l_insurancerefundrecognize (idx),
 301                 insuranceincomerecognize =
 302                    insuranceincomerecognize + l_insuranceincomerecognize (idx),
 303                 otherrefundrecognize =
 304                            otherrefundrecognize + l_otherrefundrecognize (idx),
 305                 otherfeerecognize =
 306                                  otherfeerecognize + l_otherfeerecognize (idx),
 307                 surveyfeerecognize =
 308                                surveyfeerecognize + l_surveyfeerecognize (idx),
 309                 erincentiverecognize =
 310                            erincentiverecognize + l_erincentiverecognize (idx),
 311                 erdiscountrecognize =
 312                              erdiscountrecognize + l_erdiscountrecognize (idx)
 313           WHERE installmentschedule.branchid = l_branchid (idx)
 314          AND    installmentschedule.applicationid = l_applicationid (idx)
 315          AND    installmentschedule.insseqno = l_insseqno (idx);
 316    END LOOP;
 317    DBMS_OUTPUT.put_line ('2');
 318    v_isc_rowaffected := p_cursor%ROWCOUNT;
 319    l_recognizeamount.DELETE;
 320    l_incentiverecognize.DELETE;
 321    l_provisionrecognize.DELETE;
 322    l_diffraterecognize.DELETE;
 323    l_adminfeerecognize.DELETE;
 324    l_insurancerefundrecognize.DELETE;
 325    l_insuranceincomerecognize.DELETE;
 326    l_otherrefundrecognize.DELETE;
 327    l_otherfeerecognize.DELETE;
 328    l_surveyfeerecognize.DELETE;
 329    l_erincentiverecognize.DELETE;
 330    l_erdiscountrecognize.DELETE;
 331    l_branchid.DELETE;
 332    l_applicationid.DELETE;
 333    l_insseqno.DELETE;
 334 
 335    SELECT NVL (MAX (seqno), 0) + 1
 336    INTO   v_count
 337    FROM   installmentincomerecognize;
 338 
 339    v_inc_rowaffected := 0;
 340    FOR indx IN (SELECT branchid, applicationid, currencyid, insseqno, duedate,
 341                        interestamount, lastincrecognize, amountincrecognize,
 342                        incentive, incentiverecognize, provision,
 343                        provisionrecognize, diffrateamount, diffraterecognize,
 344                        adminfee, adminfeerecognize, insurancerefundrecognize,
 345                        insuranceincomerecognize, otherrefundrecognize,
 346                        otherfeerecognize, surveyfeerecognize,
 347                        erincentiverecognize, erdiscountrecognize, prevduedate,
 348                        effectivedate, productid, agreementno, journalschemeid,
 349                        recognizeamount, startperiod, endperiod, recognizedays,
 350                        fundingcoyportion
 351                 FROM   tmp_incomerecognizitiononeom)
 352    LOOP
 353       INSERT INTO installmentincomerecognize
 354                   (branchid, applicationid, currencyid,
 355                    recognizedate, insseqno, lastdue,
 356                    nextdue, startperiod, endperiod,
 357                    recognizeamount, recognizedays, dailymonthly,
 358                    fundingcoyportion, incentiverecognize,
 359                    provisionrecognize, diffraterecognize,
 360                    adminfeerecognize, insurancerefundrecognize,
 361                    insuranceincomerecognize, otherrefundrecognize,
 362                    otherfeerecognize, surveyfeerecognize,
 363                    erincentiverecognize, erdiscountrecognize,
 364                    seqno
 365                   )
 366            VALUES (indx.branchid, indx.applicationid, indx.currencyid,
 367                    TRUNC (p_businnesdate), indx.insseqno, indx.prevduedate,
 368                    indx.duedate, indx.startperiod, indx.endperiod,
 369                    indx.recognizeamount, indx.recognizedays, 'M',
 370                    indx.fundingcoyportion, indx.incentiverecognize,
 371                    indx.provisionrecognize, indx.diffraterecognize,
 372                    indx.adminfeerecognize, indx.insurancerefundrecognize,
 373                    indx.insuranceincomerecognize, indx.otherrefundrecognize,
 374                    indx.otherfeerecognize, indx.surveyfeerecognize,
 375                    indx.erincentiverecognize, indx.erdiscountrecognize,
 376                    -- (SELECT NVL (MAX (seqno), 0) FROM   installmentincomerecognize)
 377                    v_count
 378                   );
 379 
 380       v_count := v_count + 1;
 381       v_inc_rowaffected := v_inc_rowaffected + 1;
 382    END LOOP;
 383    DBMS_OUTPUT.put_line ('3');
 384 
 385    SELECT gsvalue
 386    INTO   p_incrementdiffdays
 387    FROM   generalsetting
 388    WHERE  gsid = 'ACCEOMDAY';
 389 
 390    DBMS_OUTPUT.put_line ('4');
 391 
 392    IF (v_inc_rowaffected = v_isc_rowaffected)
 393    THEN
 394       p_dmlaffected := v_inc_rowaffected;
 395       DBMS_OUTPUT.put_line ('5');
 396    ELSE
 397       raise_application_error
 398          (-20100,
 399           'Row affected is different between installment schedule and installmentincomerecognize');
 400       DBMS_OUTPUT.put_line ('6');
 401    END IF;
 402 
 403    OPEN p_cursor FOR
 404       SELECT branchid, applicationid, currencyid, insseqno, duedate,
 405              interestamount, lastincrecognize, amountincrecognize, incentive,
 406              incentiverecognize, provision, provisionrecognize,
 407              diffrateamount, diffraterecognize, adminfee, adminfeerecognize,
 408              insurancerefundrecognize, insuranceincomerecognize,
 409              otherrefundrecognize, otherfeerecognize, surveyfeerecognize,
 410              erincentiverecognize, erdiscountrecognize, prevduedate,
 411              effectivedate, productid, agreementno, journalschemeid,
 412              recognizeamount, startperiod, endperiod, recognizedays,
 413              fundingcoyportion,
 414              (SELECT dailyexchangerate
 415               FROM   currency
 416               WHERE  currencyid =
 417                         tmp_incomerecognizitiononeom.currencyid)
 418                                                             dailyexchangerate
 419       FROM   tmp_incomerecognizitiononeom;
 420 END speodincomerecognizitiononeom;
 421 /
asdaasdasd

2009-08-19T06:07:00.001-07:00
zxczxx zxc zx cz x zx
seosblogs

Test

test

asdaasdasd
