---version banner. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production --table create table tblfiscalmonth (FISCALMONTH NOT NULL VARCHAR2(6), BEGINDATE DATE, ENDDATE DATE, ); ---insert data INSERT INTO TBLFISCALMONTH VALUES ('1-Jan-96','28-Jan-96','199601'); INSERT INTO TBLFISCALMONTH VALUES ('29-Jan-96','25-Feb-96','199602'); INSERT INTO TBLFISCALMONTH VALUES ('26-Feb-96','31-Mar-96','199603'); INSERT INTO TBLFISCALMONTH VALUES ('1-Apr-96','28-Apr-96','199604'); INSERT INTO TBLFISCALMONTH VALUES ('29-Apr-96','26-May-96','199605'); INSERT INTO TBLFISCALMONTH VALUES ('27-May-96','30-Jun-96','199606'); INSERT INTO TBLFISCALMONTH VALUES ('1-Jul-96','28-Jul-96','199607'); INSERT INTO TBLFISCALMONTH VALUES ('29-Jul-96','25-Aug-96','199608'); INSERT INTO TBLFISCALMONTH VALUES ('26-Aug-96','29-Sep-96','199609'); INSERT INTO TBLFISCALMONTH VALUES ('30-Sep-96','27-Oct-96','199610'); INSERT INTO TBLFISCALMONTH VALUES ('28-Oct-96','24-Nov-96','199611'); INSERT INTO TBLFISCALMONTH VALUES ('25-Nov-96','29-Dec-96','199612'); INSERT INTO TBLFISCALMONTH VALUES ('30-Dec-96','26-Jan-97','199701'); INSERT INTO TBLFISCALMONTH VALUES ('27-Jan-97','23-Feb-97','199702'); INSERT INTO TBLFISCALMONTH VALUES ('24-Feb-97','30-Mar-97','199703'); INSERT INTO TBLFISCALMONTH VALUES ('31-Mar-97','27-Apr-97','199704'); INSERT INTO TBLFISCALMONTH VALUES ('28-Apr-97','25-May-97','199705'); INSERT INTO TBLFISCALMONTH VALUES ('26-May-97','29-Jun-97','199706'); INSERT INTO TBLFISCALMONTH VALUES ('30-Jun-97','27-Jul-97','199707'); INSERT INTO TBLFISCALMONTH VALUES ('28-Jul-97','24-Aug-97','199708'); INSERT INTO TBLFISCALMONTH VALUES ('25-Aug-97','28-Sep-97','199709'); INSERT INTO TBLFISCALMONTH VALUES ('29-Sep-97','26-Oct-97','199710'); INSERT INTO TBLFISCALMONTH VALUES ('27-Oct-97','23-Nov-97','199711'); INSERT INTO TBLFISCALMONTH VALUES ('24-Nov-97','28-Dec-97','199712'); INSERT INTO TBLFISCALMONTH VALUES ('29-Dec-97','25-Jan-98','199801'); INSERT INTO TBLFISCALMONTH VALUES ('26-Jan-98','22-Feb-98','199802'); INSERT INTO TBLFISCALMONTH VALUES ('23-Feb-98','29-Mar-98','199803'); INSERT INTO TBLFISCALMONTH VALUES ('30-Mar-98','26-Apr-98','199804'); INSERT INTO TBLFISCALMONTH VALUES ('27-Apr-98','24-May-98','199805'); INSERT INTO TBLFISCALMONTH VALUES ('25-May-98','28-Jun-98','199806'); INSERT INTO TBLFISCALMONTH VALUES ('29-Jun-98','26-Jul-98','199807'); INSERT INTO TBLFISCALMONTH VALUES ('27-Jul-98','23-Aug-98','199808'); INSERT INTO TBLFISCALMONTH VALUES ('24-Aug-98','27-Sep-98','199809'); INSERT INTO TBLFISCALMONTH VALUES ('28-Sep-98','25-Oct-98','199810'); INSERT INTO TBLFISCALMONTH VALUES ('26-Oct-98','22-Nov-98','199811'); INSERT INTO TBLFISCALMONTH VALUES ('23-Nov-98','3-Jan-99','199812'); INSERT INTO TBLFISCALMONTH VALUES ('4-Jan-99','31-Jan-99','199901'); INSERT INTO TBLFISCALMONTH VALUES ('1-Feb-99','28-Feb-99','199902'); INSERT INTO TBLFISCALMONTH VALUES ('1-Mar-99','4-Apr-99','199903'); INSERT INTO TBLFISCALMONTH VALUES ('5-Apr-99','2-May-99','199904'); INSERT INTO TBLFISCALMONTH VALUES ('3-May-99','30-May-99','199905'); INSERT INTO TBLFISCALMONTH VALUES ('31-May-99','4-Jul-99','199906'); INSERT INTO TBLFISCALMONTH VALUES ('5-Jul-99','1-Aug-99','199907'); INSERT INTO TBLFISCALMONTH VALUES ('2-Aug-99','29-Aug-99','199908'); INSERT INTO TBLFISCALMONTH VALUES ('30-Aug-99','3-Oct-99','199909'); INSERT INTO TBLFISCALMONTH VALUES ('4-Oct-99','31-Oct-99','199910'); INSERT INTO TBLFISCALMONTH VALUES ('1-Nov-99','28-Nov-99','199911'); INSERT INTO TBLFISCALMONTH VALUES ('29-Nov-99','2-Jan-00','199912'); INSERT INTO TBLFISCALMONTH VALUES ('3-Jan-00','30-Jan-00','200001'); INSERT INTO TBLFISCALMONTH VALUES ('31-Jan-00','27-Feb-00','200002'); INSERT INTO TBLFISCALMONTH VALUES ('28-Feb-00','2-Apr-00','200003'); INSERT INTO TBLFISCALMONTH VALUES ('3-Apr-00','30-Apr-00','200004'); INSERT INTO TBLFISCALMONTH VALUES ('1-May-00','28-May-00','200005'); INSERT INTO TBLFISCALMONTH VALUES ('29-May-00','2-Jul-00','200006'); INSERT INTO TBLFISCALMONTH VALUES ('3-Jul-00','30-Jul-00','200007'); INSERT INTO TBLFISCALMONTH VALUES ('31-Jul-00','27-Aug-00','200008'); INSERT INTO TBLFISCALMONTH VALUES ('28-Aug-00','1-Oct-00','200009'); INSERT INTO TBLFISCALMONTH VALUES ('2-Oct-00','29-Oct-00','200010'); INSERT INTO TBLFISCALMONTH VALUES ('30-Oct-00','26-Nov-00','200011'); INSERT INTO TBLFISCALMONTH VALUES ('27-Nov-00','31-Dec-00','200012'); INSERT INTO TBLFISCALMONTH VALUES ('1-Jan-01','28-Jan-01','200101'); INSERT INTO TBLFISCALMONTH VALUES ('29-Jan-01','25-Feb-01','200102'); INSERT INTO TBLFISCALMONTH VALUES ('26-Feb-01','1-Apr-01','200103'); INSERT INTO TBLFISCALMONTH VALUES ('2-Apr-01','29-Apr-01','200104'); INSERT INTO TBLFISCALMONTH VALUES ('30-Apr-01','27-May-01','200105'); INSERT INTO TBLFISCALMONTH VALUES ('28-May-01','1-Jul-01','200106'); INSERT INTO TBLFISCALMONTH VALUES ('2-Jul-01','29-Jul-01','200107'); INSERT INTO TBLFISCALMONTH VALUES ('30-Jul-01','26-Aug-01','200108'); INSERT INTO TBLFISCALMONTH VALUES ('27-Aug-01','30-Sep-01','200109'); INSERT INTO TBLFISCALMONTH VALUES ('1-Oct-01','28-Oct-01','200110'); INSERT INTO TBLFISCALMONTH VALUES ('29-Oct-01','25-Nov-01','200111'); INSERT INTO TBLFISCALMONTH VALUES ('26-Nov-01','30-Dec-01','200112'); INSERT INTO TBLFISCALMONTH VALUES ('31-Dec-01','27-Jan-02','200201'); INSERT INTO TBLFISCALMONTH VALUES ('28-Jan-02','24-Feb-02','200202'); INSERT INTO TBLFISCALMONTH VALUES ('25-Feb-02','31-Mar-02','200203'); INSERT INTO TBLFISCALMONTH VALUES ('1-Apr-02','28-Apr-02','200204'); INSERT INTO TBLFISCALMONTH VALUES ('29-Apr-02','26-May-02','200205'); INSERT INTO TBLFISCALMONTH VALUES ('27-May-02','30-Jun-02','200206'); INSERT INTO TBLFISCALMONTH VALUES ('1-Jul-02','28-Jul-02','200207'); INSERT INTO TBLFISCALMONTH VALUES ('29-Jul-02','25-Aug-02','200208'); INSERT INTO TBLFISCALMONTH VALUES ('26-Aug-02','29-Sep-02','200209'); INSERT INTO TBLFISCALMONTH VALUES ('30-Sep-02','27-Oct-02','200210'); INSERT INTO TBLFISCALMONTH VALUES ('28-Oct-02','24-Nov-02','200211'); INSERT INTO TBLFISCALMONTH VALUES ('25-Nov-02','29-Dec-02','200212'); INSERT INTO TBLFISCALMONTH VALUES ('30-Dec-02','26-Jan-03','200301'); INSERT INTO TBLFISCALMONTH VALUES ('27-Jan-03','23-Feb-03','200302'); INSERT INTO TBLFISCALMONTH VALUES ('24-Feb-03','30-Mar-03','200303'); INSERT INTO TBLFISCALMONTH VALUES ('31-Mar-03','27-Apr-03','200304'); INSERT INTO TBLFISCALMONTH VALUES ('28-Apr-03','25-May-03','200305'); INSERT INTO TBLFISCALMONTH VALUES ('26-May-03','29-Jun-03','200306'); INSERT INTO TBLFISCALMONTH VALUES ('30-Jun-03','27-Jul-03','200307'); INSERT INTO TBLFISCALMONTH VALUES ('28-Jul-03','24-Aug-03','200308'); INSERT INTO TBLFISCALMONTH VALUES ('25-Aug-03','28-Sep-03','200309'); INSERT INTO TBLFISCALMONTH VALUES ('29-Sep-03','26-Oct-03','200310'); INSERT INTO TBLFISCALMONTH VALUES ('27-Oct-03','23-Nov-03','200311'); INSERT INTO TBLFISCALMONTH VALUES ('24-Nov-03','28-Dec-03','200312'); INSERT INTO TBLFISCALMONTH VALUES ('29-Dec-03','25-Jan-04','200401'); INSERT INTO TBLFISCALMONTH VALUES ('26-Jan-04','22-Feb-04','200402'); INSERT INTO TBLFISCALMONTH VALUES ('23-Feb-04','28-Mar-04','200403'); INSERT INTO TBLFISCALMONTH VALUES ('29-Mar-04','25-Apr-04','200404'); INSERT INTO TBLFISCALMONTH VALUES ('26-Apr-04','23-May-04','200405'); INSERT INTO TBLFISCALMONTH VALUES ('24-May-04','27-Jun-04','200406'); INSERT INTO TBLFISCALMONTH VALUES ('28-Jun-04','25-Jul-04','200407'); INSERT INTO TBLFISCALMONTH VALUES ('26-Jul-04','22-Aug-04','200408'); INSERT INTO TBLFISCALMONTH VALUES ('23-Aug-04','26-Sep-04','200409'); INSERT INTO TBLFISCALMONTH VALUES ('27-Sep-04','24-Oct-04','200410'); INSERT INTO TBLFISCALMONTH VALUES ('25-Oct-04','21-Nov-04','200411'); INSERT INTO TBLFISCALMONTH VALUES ('22-Nov-04','2-Jan-05','200412'); INSERT INTO TBLFISCALMONTH VALUES ('3-Jan-05','30-Jan-05','200501'); INSERT INTO TBLFISCALMONTH VALUES ('31-Jan-05','27-Feb-05','200502'); INSERT INTO TBLFISCALMONTH VALUES ('28-Feb-05','3-Apr-05','200503'); INSERT INTO TBLFISCALMONTH VALUES ('4-Apr-05','1-May-05','200504'); INSERT INTO TBLFISCALMONTH VALUES ('2-May-05','29-May-05','200505'); INSERT INTO TBLFISCALMONTH VALUES ('30-May-05','3-Jul-05','200506'); INSERT INTO TBLFISCALMONTH VALUES ('4-Jul-05','31-Jul-05','200507'); INSERT INTO TBLFISCALMONTH VALUES ('1-Aug-05','28-Aug-05','200508'); INSERT INTO TBLFISCALMONTH VALUES ('29-Aug-05','2-Oct-05','200509'); INSERT INTO TBLFISCALMONTH VALUES ('3-Oct-05','30-Oct-05','200510'); INSERT INTO TBLFISCALMONTH VALUES ('31-Oct-05','27-Nov-05','200511'); INSERT INTO TBLFISCALMONTH VALUES ('28-Nov-05','1-Jan-06','200512'); INSERT INTO TBLFISCALMONTH VALUES ('2-Jan-06','29-Jan-06','200601'); INSERT INTO TBLFISCALMONTH VALUES ('30-Jan-06','26-Feb-06','200602'); INSERT INTO TBLFISCALMONTH VALUES ('27-Feb-06','2-Apr-06','200603'); INSERT INTO TBLFISCALMONTH VALUES ('3-Apr-06','30-Apr-06','200604'); INSERT INTO TBLFISCALMONTH VALUES ('1-May-06','28-May-06','200605'); INSERT INTO TBLFISCALMONTH VALUES ('29-May-06','2-Jul-06','200606'); INSERT INTO TBLFISCALMONTH VALUES ('3-Jul-06','30-Jul-06','200607'); INSERT INTO TBLFISCALMONTH VALUES ('31-Jul-06','27-Aug-06','200608'); INSERT INTO TBLFISCALMONTH VALUES ('28-Aug-06','1-Oct-06','200609'); INSERT INTO TBLFISCALMONTH VALUES ('2-Oct-06','29-Oct-06','200610'); INSERT INTO TBLFISCALMONTH VALUES ('30-Oct-06','26-Nov-06','200611'); INSERT INTO TBLFISCALMONTH VALUES ('27-Nov-06','31-Dec-06','200612'); --table create table tblclaimdetmonth_s_part (CLAIMID NUMBER(10) NOT NULL, CLAIMLINENUMB NUMBER(10) NOT NULL, REGIONALNETWORKCENTER VARCHAR2(8), PAYMENTFISCALMONTH VARCHAR2(6), FEEFORSERVICE VARCHAR2(1), SERVICECATID VARCHAR2(8), FROMDATE DATE, TODATE DATE, FROMDATEFISCALMONTH VARCHAR2(6), PROVAMOUNT NUMBER(15,4), PLANAMOUNT NUMBER(15,4), CLAIMDETAILHISTRECTYPECD VARCHAR2(1)NOT NULL, CARID NUMBER(10), PROVPAYMENTTYPE VARCHAR2(1), STATUSCD VARCHAR2(8), FISCALDATE DATE NOT NULL, FISCALMONTH VARCHAR2(6) ) / ---insert data insert into tblclaimdetmonth_s_part values ( 2156851,1,'PHOENIX','200002',1,'RESP','25/DEC/1999','23/JUN/5300','199912',177,249.53,'E',1,3,'PROC','01/AUG/2005','199912'); insert into tblclaimdetmonth_s_part values ( 2156851,1,'PHOENIX','200006',1,'RESP','25/DEC/1999','23/JUN/5300','199912',-177,-249.53,'O',1,3,'COMP','01/AUG/2005','199912'); insert into tblclaimdetmonth_s_part values ( 2156851,1,'PHOENIX','PEND',1,'RESP','25/DEC/1999','23/JUN/5300','199912',0,0,'A',1,3,'ACCPTD','01/AUG/2005','199912'); ---destination table Create table w_claimsplit ( CLAIMID NOT NULL NUMBER(10), CLAIMLINENUMB NOT NULL NUMBER(10), CLAIMDETAILHISTSEQNUMB NOT NULL NUMBER(10), CLAIMDETAILHISTRECTYPECD NOT NULL VARCHAR2(1), OPERATIONCENTERCODE VARCHAR2(8), FEEFORSERVICE VARCHAR2(1), SERVICECATID VARCHAR2(8), SVCFISCALMONTH VARCHAR2(6), PAYMENTFISCALMONTH VARCHAR2(6), PROVAMOUNT NUMBER(15,4), CARID NUMBER(10), PROVTYPECD NUMBER(3), CREATEDBY VARCHAR2(30), CREATEDDATETIME DATE, UPDATEDBY VARCHAR2(30), UPDATEDDATETIME DATE, PLANAMOUNT NUMBER(15,4) ); --desired result 2156851,1,1,E,PHOENIX,1,RESP,199912,200002,0,1,3,,,,,0 2156851,1,1,O,PHOENIX,1,RESP,199912,200006,0,1,3,,,,,0 2156851,1,2,E,PHOENIX,1,RESP,200001,200002,0,1,3,,,,,0 2156851,1,2,O,PHOENIX,1,RESP,200001,200006,0,1,3,,,,,0 2156851,1,3,E,PHOENIX,1,RESP,200002,200002,0,1,3,,,,,0 2156851,1,3,O,PHOENIX,1,RESP,200002,200006,0,1,3,,,,,0 2156851,1,4,E,PHOENIX,1,RESP,200003,200002,0,1,3,,,,,0 2156851,1,4,O,PHOENIX,1,RESP,200003,200006,0,1,3,,,,,0 2156851,1,5,E,PHOENIX,1,RESP,200004,200002,0,1,3,,,,,0 2156851,1,5,O,PHOENIX,1,RESP,200004,200006,0,1,3,,,,,0 2156851,1,6,E,PHOENIX,1,RESP,200005,200002,0,1,3,,,,,0 2156851,1,6,O,PHOENIX,1,RESP,200005,200006,0,1,3,,,,,0 2156851,1,7,E,PHOENIX,1,RESP,200006,200002,0,1,3,,,,,0 2156851,1,7,O,PHOENIX,1,RESP,200006,200006,0,1,3,,,,,0 2156851,1,8,E,PHOENIX,1,RESP,200007,200002,0,1,3,,,,,0 2156851,1,8,O,PHOENIX,1,RESP,200007,200006,0,1,3,,,,,0 2156851,1,9,E,PHOENIX,1,RESP,200008,200002,0,1,3,,,,,0 2156851,1,9,O,PHOENIX,1,RESP,200008,200006,0,1,3,,,,,0 2156851,1,10,E,PHOENIX,1,RESP,200009,200002,0,1,3,,,,,0 2156851,1,10,O,PHOENIX,1,RESP,200009,200006,0,1,3,,,,,0 2156851,1,11,E,PHOENIX,1,RESP,200010,200002,0,1,3,,,,,0 2156851,1,11,O,PHOENIX,1,RESP,200010,200006,0,1,3,,,,,0 2156851,1,12,E,PHOENIX,1,RESP,200011,200002,0,1,3,,,,,0 2156851,1,12,O,PHOENIX,1,RESP,200011,200006,0,1,3,,,,,0 2156851,1,13,E,PHOENIX,1,RESP,200012,200002,0,1,3,,,,,0 2156851,1,13,O,PHOENIX,1,RESP,200012,200006,0,1,3,,,,,0 2156851,1,14,E,PHOENIX,1,RESP,200101,200002,0,1,3,,,,,0 2156851,1,14,O,PHOENIX,1,RESP,200101,200006,0,1,3,,,,,0 2156851,1,15,E,PHOENIX,1,RESP,200102,200002,0,1,3,,,,,0 2156851,1,15,O,PHOENIX,1,RESP,200102,200006,0,1,3,,,,,0 2156851,1,16,E,PHOENIX,1,RESP,200103,200002,0,1,3,,,,,0 2156851,1,16,O,PHOENIX,1,RESP,200103,200006,0,1,3,,,,,0 2156851,1,17,E,PHOENIX,1,RESP,200104,200002,0,1,3,,,,,0 2156851,1,17,O,PHOENIX,1,RESP,200104,200006,0,1,3,,,,,0 2156851,1,18,E,PHOENIX,1,RESP,200105,200002,0,1,3,,,,,0 2156851,1,18,O,PHOENIX,1,RESP,200105,200006,0,1,3,,,,,0 2156851,1,19,E,PHOENIX,1,RESP,200106,200002,0,1,3,,,,,0 2156851,1,19,O,PHOENIX,1,RESP,200106,200006,0,1,3,,,,,0 2156851,1,20,E,PHOENIX,1,RESP,200107,200002,0,1,3,,,,,0 2156851,1,20,O,PHOENIX,1,RESP,200107,200006,0,1,3,,,,,0 2156851,1,21,E,PHOENIX,1,RESP,200108,200002,0,1,3,,,,,0 2156851,1,21,O,PHOENIX,1,RESP,200108,200006,0,1,3,,,,,0 2156851,1,22,E,PHOENIX,1,RESP,200109,200002,0,1,3,,,,,0 2156851,1,22,O,PHOENIX,1,RESP,200109,200006,0,1,3,,,,,0 2156851,1,23,E,PHOENIX,1,RESP,200110,200002,0,1,3,,,,,0 2156851,1,23,O,PHOENIX,1,RESP,200110,200006,0,1,3,,,,,0 2156851,1,24,E,PHOENIX,1,RESP,200111,200002,0,1,3,,,,,0 2156851,1,24,O,PHOENIX,1,RESP,200111,200006,0,1,3,,,,,0 2156851,1,25,E,PHOENIX,1,RESP,200112,200002,0,1,3,,,,,0 2156851,1,25,O,PHOENIX,1,RESP,200112,200006,0,1,3,,,,,0 2156851,1,26,E,PHOENIX,1,RESP,200201,200002,0,1,3,,,,,0 2156851,1,26,O,PHOENIX,1,RESP,200201,200006,0,1,3,,,,,0 2156851,1,27,E,PHOENIX,1,RESP,200202,200002,0,1,3,,,,,0 2156851,1,27,O,PHOENIX,1,RESP,200202,200006,0,1,3,,,,,0 2156851,1,28,E,PHOENIX,1,RESP,200203,200002,0,1,3,,,,,0 2156851,1,28,O,PHOENIX,1,RESP,200203,200006,0,1,3,,,,,0 2156851,1,29,E,PHOENIX,1,RESP,200204,200002,0,1,3,,,,,0 2156851,1,29,O,PHOENIX,1,RESP,200204,200006,0,1,3,,,,,0 2156851,1,30,E,PHOENIX,1,RESP,200205,200002,0,1,3,,,,,0 2156851,1,30,O,PHOENIX,1,RESP,200205,200006,0,1,3,,,,,0 2156851,1,31,E,PHOENIX,1,RESP,200206,200002,0,1,3,,,,,0 2156851,1,31,O,PHOENIX,1,RESP,200206,200006,0,1,3,,,,,0 2156851,1,32,E,PHOENIX,1,RESP,200207,200002,0,1,3,,,,,0 2156851,1,32,O,PHOENIX,1,RESP,200207,200006,0,1,3,,,,,0 2156851,1,33,E,PHOENIX,1,RESP,200208,200002,0,1,3,,,,,0 2156851,1,33,O,PHOENIX,1,RESP,200208,200006,0,1,3,,,,,0 2156851,1,34,E,PHOENIX,1,RESP,200209,200002,0,1,3,,,,,0 2156851,1,34,O,PHOENIX,1,RESP,200209,200006,0,1,3,,,,,0 2156851,1,35,E,PHOENIX,1,RESP,200210,200002,0,1,3,,,,,0 2156851,1,35,O,PHOENIX,1,RESP,200210,200006,0,1,3,,,,,0 2156851,1,36,E,PHOENIX,1,RESP,200211,200002,0,1,3,,,,,0 2156851,1,36,O,PHOENIX,1,RESP,200211,200006,0,1,3,,,,,0 2156851,1,37,E,PHOENIX,1,RESP,200212,200002,0,1,3,,,,,0 2156851,1,37,O,PHOENIX,1,RESP,200212,200006,0,1,3,,,,,0 2156851,1,38,E,PHOENIX,1,RESP,200301,200002,0,1,3,,,,,0 2156851,1,38,O,PHOENIX,1,RESP,200301,200006,0,1,3,,,,,0 2156851,1,39,E,PHOENIX,1,RESP,200302,200002,0,1,3,,,,,0 2156851,1,39,O,PHOENIX,1,RESP,200302,200006,0,1,3,,,,,0 2156851,1,40,E,PHOENIX,1,RESP,200303,200002,0,1,3,,,,,0 2156851,1,40,O,PHOENIX,1,RESP,200303,200006,0,1,3,,,,,0 2156851,1,41,E,PHOENIX,1,RESP,200304,200002,0,1,3,,,,,0 2156851,1,41,O,PHOENIX,1,RESP,200304,200006,0,1,3,,,,,0 2156851,1,42,E,PHOENIX,1,RESP,200305,200002,0,1,3,,,,,0 2156851,1,42,O,PHOENIX,1,RESP,200305,200006,0,1,3,,,,,0 2156851,1,43,E,PHOENIX,1,RESP,200306,200002,0,1,3,,,,,0 2156851,1,43,O,PHOENIX,1,RESP,200306,200006,0,1,3,,,,,0 2156851,1,44,E,PHOENIX,1,RESP,200307,200002,0,1,3,,,,,0 2156851,1,44,O,PHOENIX,1,RESP,200307,200006,0,1,3,,,,,0 2156851,1,45,E,PHOENIX,1,RESP,200308,200002,0,1,3,,,,,0 2156851,1,45,O,PHOENIX,1,RESP,200308,200006,0,1,3,,,,,0 2156851,1,46,E,PHOENIX,1,RESP,200309,200002,0,1,3,,,,,0 2156851,1,46,O,PHOENIX,1,RESP,200309,200006,0,1,3,,,,,0 2156851,1,47,E,PHOENIX,1,RESP,200310,200002,0,1,3,,,,,0 2156851,1,47,O,PHOENIX,1,RESP,200310,200006,0,1,3,,,,,0 2156851,1,48,E,PHOENIX,1,RESP,200311,200002,0,1,3,,,,,0 2156851,1,48,O,PHOENIX,1,RESP,200311,200006,0,1,3,,,,,0 2156851,1,49,E,PHOENIX,1,RESP,200312,200002,0,1,3,,,,,0 2156851,1,49,O,PHOENIX,1,RESP,200312,200006,0,1,3,,,,,0 2156851,1,50,E,PHOENIX,1,RESP,200401,200002,0,1,3,,,,,0 2156851,1,50,O,PHOENIX,1,RESP,200401,200006,0,1,3,,,,,0 2156851,1,51,E,PHOENIX,1,RESP,200402,200002,0,1,3,,,,,0 2156851,1,51,O,PHOENIX,1,RESP,200402,200006,0,1,3,,,,,0 2156851,1,52,E,PHOENIX,1,RESP,200403,200002,0,1,3,,,,,0 2156851,1,52,O,PHOENIX,1,RESP,200403,200006,0,1,3,,,,,0 2156851,1,53,E,PHOENIX,1,RESP,200404,200002,0,1,3,,,,,0 2156851,1,53,O,PHOENIX,1,RESP,200404,200006,0,1,3,,,,,0 2156851,1,54,E,PHOENIX,1,RESP,200405,200002,0,1,3,,,,,0 2156851,1,54,O,PHOENIX,1,RESP,200405,200006,0,1,3,,,,,0 2156851,1,55,E,PHOENIX,1,RESP,200406,200002,0,1,3,,,,,0 2156851,1,55,O,PHOENIX,1,RESP,200406,200006,0,1,3,,,,,0 2156851,1,56,E,PHOENIX,1,RESP,200407,200002,0,1,3,,,,,0 2156851,1,56,O,PHOENIX,1,RESP,200407,200006,0,1,3,,,,,0 2156851,1,57,E,PHOENIX,1,RESP,200408,200002,0,1,3,,,,,0 2156851,1,57,O,PHOENIX,1,RESP,200408,200006,0,1,3,,,,,0 2156851,1,58,E,PHOENIX,1,RESP,200409,200002,0,1,3,,,,,0 2156851,1,58,O,PHOENIX,1,RESP,200409,200006,0,1,3,,,,,0 2156851,1,59,E,PHOENIX,1,RESP,200410,200002,0,1,3,,,,,0 2156851,1,59,O,PHOENIX,1,RESP,200410,200006,0,1,3,,,,,0 2156851,1,60,E,PHOENIX,1,RESP,200411,200002,0,1,3,,,,,0 2156851,1,60,O,PHOENIX,1,RESP,200411,200006,0,1,3,,,,,0 2156851,1,61,E,PHOENIX,1,RESP,200412,200002,0,1,3,,,,,0 2156851,1,61,O,PHOENIX,1,RESP,200412,200006,0,1,3,,,,,0 2156851,1,62,E,PHOENIX,1,RESP,200501,200002,0,1,3,,,,,0 2156851,1,62,O,PHOENIX,1,RESP,200501,200006,0,1,3,,,,,0 2156851,1,63,E,PHOENIX,1,RESP,200502,200002,0,1,3,,,,,0 2156851,1,63,O,PHOENIX,1,RESP,200502,200006,0,1,3,,,,,0 2156851,1,64,E,PHOENIX,1,RESP,200503,200002,0,1,3,,,,,0 2156851,1,64,O,PHOENIX,1,RESP,200503,200006,0,1,3,,,,,0 2156851,1,65,E,PHOENIX,1,RESP,200504,200002,0,1,3,,,,,0 2156851,1,65,O,PHOENIX,1,RESP,200504,200006,0,1,3,,,,,0 2156851,1,66,E,PHOENIX,1,RESP,200505,200002,0,1,3,,,,,0 2156851,1,66,O,PHOENIX,1,RESP,200505,200006,0,1,3,,,,,0 2156851,1,67,E,PHOENIX,1,RESP,200506,200002,0,1,3,,,,,0 2156851,1,67,O,PHOENIX,1,RESP,200506,200006,0,1,3,,,,,0 2156851,1,68,E,PHOENIX,1,RESP,200507,200002,0,1,3,,,,,0 2156851,1,68,O,PHOENIX,1,RESP,200507,200006,0,1,3,,,,,0 2156851,1,69,E,PHOENIX,1,RESP,200508,200002,0,1,3,,,,,0 2156851,1,69,O,PHOENIX,1,RESP,200508,200006,0,1,3,,,,,0 2156851,1,70,E,PHOENIX,1,RESP,PEND,200002,177,1,3,,,,,249.53 2156851,1,70,O,PHOENIX,1,RESP,PEND,200006,-177,1,3,,,,,-249.53 --your suggested code. CREATE SEQUENCE SeqNumb / CREATE OR REPLACE TRIGGER w_claimsplit_bir BEFORE INSERT ON w_claimsplit FOR EACH ROW BEGIN SELECT SeqNumb.NEXTVAL INTO :NEW.ClaimDetailHistSeqNumb FROM DUAL; END w_claimpslit_bir; CREATE OR REPLACE PROCEDURE p_ClaimSplit (v_LastDay IN DATE) AS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit'; INSERT INTO w_claimsplit ( ClaimID ,ClaimLineNumb ,ClaimDetailHistRecTypeCd ,OperationCenterCode ,FeeForService ,ServiceCatId ,SvcFiscalMonth ,PaymentFiscalMonth ,ProvAmount ,CarId ,PlanAmount ) SELECT /*+ FIRST_ROWS(30) */ ClaimID ,ClaimLineNumb ,NVL (o.claimdetailhistrectypecd, 'A') ,RegionalNetworkCenter ,DECODE (o.accountingdepartmentrptcd ,'1', '1' ,'2', '2' ,'3', '3' ,'4', '4' ,'5', '3' ,'6', '3' ,'7', '3' ,'9', '9' ,'1') ,ServiceCatId ,t.FiscalMonth ,PaymentFiscalMonth ,CASE WHEN (SELECT EndDate FROM tblFiscalMonth WHERE BeginDate <= o.FromDate AND EndDate >= o.FromDate) > t.EndDate THEN o.ProvAmount WHEN (SELECT EndDate FROM tblFiscalMonth WHERE BeginDate <= o.FromDate AND EndDate >= o.FromDate) = (SELECT EndDate FROM tblFiscalMonth WHERE BeginDate <= o.ToDate AND EndDate >= o.ToDate) THEN o.ProvAmount WHEN (SELECT fiscalmonth ... THEN ... END ,CarId ..................... FROM tblclaimdetmonth_s_part o ,(SELECT BeginDate, Enddate, FiscalMonth FROM tblFiscalMonth WHERE BeginDate <= v_LastDay AND EndDate >= v_LastDay) t WHERE o.FiscalDate = t.BeginDate AND (O.ProvAmount <> 0 OR O.PlanAmount <> 0); COMMIT; END p_ClaimSplit; / --- HERE IS MY OLD CODE - WORKING FINE EXCEPT f_getfiscalinfo FUNCTION IS TAKING TOO MUCH TIME ----WHEN IT GET CALL FROM THIS PROCEDURE AND THAT IS WHY I WAS LOOKING FOR PIPELINE FUNTION OPTION ----TO EXPEDITE IT , BUT NOW YOU HAVE SUGGESTED ME ABOVE SIMPLE SOLUTION. PROCEDURE p_ClaimSplit (v_LastDay IN DATE) -----,v_Rc OUT NUMBER) IS /* VARIABLES */ v_ThisFiscalMonth tblclaimdetmonth_s_part.FiscalMonth%TYPE; --'200402'; v_PendingLastDay tblclaimdetmonth_s_part.FromDate%TYPE; --Current fiscal month. v_SvcFiscalMonth tblclaimdetmonth_s_part.FiscalMonth%TYPE; v_ProvAmount tblclaimdetmonth_s_part.ProvAmount%TYPE; v_PlanAmount tblclaimdetmonth_s_part.PlanAmount%TYPE; --LJK 06/27/2005 New field Added. v_Date tblclaimdetmonth_s_part.Fromdate%TYPE := NULL; v_FiscalDate tblclaimdetmonth_s_part.Fromdate%TYPE := NULL; v_TOFiscalMonth tblclaimdetmonth_s_part.FiscalMonth%TYPE := NULL; v_AmtUsed tblclaimdetmonth_s_part.ProvAmount%TYPE := 0; v_PlanAmtUsed tblclaimdetmonth_s_part.ProvAmount%TYPE := 0; --LJK 06/27/2005 New field Added. v_ClaimID tblClaimDetail.ClaimID%TYPE := NULL; v_ClaimLineNumb tblClaimDetail.ClaimLineNumb%TYPE := NULL; v_Ct PLS_INTEGER :=0; v_FMPart PLS_INTEGER; v_CTX PLS_INTEGER := 0; error_number number; error_message varchar2(200); v_SeqNumb NUMBER := 0; --v_EmailTo EMAIL.AddressList_Tab; --v_EmailFrom VARCHAR2(50) := USER || '@GENTIVA.COM'; --v_EmailSubject VARCHAR2(50) := 'MONTH-END CLAIM SPLIT'; --v_EmailText VARCHAR2(2000) := NULL; x PLS_INTEGER := 0; y PLS_INTEGER := 100000; z PLS_INTEGER := 0; v_FromFiscalInfo tFiscalMonthRec; v_ToFiscalInfo tFiscalMonthRec; v_CurrentInfo tFiscalMonthRec; CURSOR curcd IS --- SELECT /*+ FIRST_ROWS(30) */ o.claimid, o.claimlinenumb, o.regionalnetworkcenter, o.paymentfiscalmonth, DECODE(o.accountingdepartmentrptcd, '1', '1', '2', '2', '3', '3', '4', '4', '5', '3', '6', '3', '7', '3', '9', '9', '1') feeforservice, o.servicecatid, o.fromdate, o.todate, o.fromdatefiscalmonth, o.provamount, o.planamount, NVL(o.claimdetailhistrectypecd, 'A') claimdetailhistrectypecd, NVL(o.claimdetailhistseqnumb, 0) claimdetailhistseqnumb, o.todate - o.fromdate + 1 totalservicedays, carid, o.provpaymenttype provtype FROM tblclaimdetmonth_s_part o WHERE FISCALDATE = v_fiscaldate AND (O.ProvAmount < 0 OR O.ProvAmount > 0 OR O.PlanAmount < 0 OR O.PlanAmount > 0); TYPE curcd_type IS TABLE OF curcd%ROWTYPE INDEX BY PLS_INTEGER; reccd curcd_type; TYPE claimsplit_type IS TABLE OF w_claimsplit%ROWTYPE; claimsplit claimsplit_type := claimsplit_type (); bulk_errors EXCEPTION; PRAGMA EXCEPTION_INIT (bulk_errors, -24381); BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit'; v_CurrentInfo := f_GetFiscalInfo(v_lastday); v_PendingLastDay := v_CurrentInfo.LastDay; v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth; v_FiscalDate := TO_DATE(SUBSTR(v_ThisFiscalMonth,5) || '/01/' || SUBSTR(v_ThisFiscalMonth,1,4),'mm/dd/yyyy'); OPEN curcd; LOOP ----loop 1st FETCH curcd BULK COLLECT INTO reccd LIMIT 200000; --> It depends EXIT WHEN reccd.COUNT = 0; FOR i IN 1 .. reccd.LAST LOOP v_SeqNumb := 0; v_SvcFiscalMonth := recCD(i).FromDateFiscalMonth; v_FromFiscalInfo := f_GetFiscalInfo(recCD(i).FromDate); v_ToFiscalInfo := f_GetFiscalInfo(recCD(i).ToDate); IF v_ToFiscalInfo.LastDay IS NULL THEN v_ToFiscalInfo.LastDay := v_PendingLastDay + 360; END IF; -----loop 2nd IF v_ToFiscalInfo.LastDay > v_PendingLastDay THEN v_ToFiscalInfo.FiscalMonth := 'PEND'; END IF; IF v_FromFiscalInfo.LastDay > v_PendingLastDay OR v_FromFiscalInfo.FiscalMonth = 'PEND' OR recCD(i).TotalServiceDays <= 0 THEN v_ProvAmount := recCD(i).ProvAmount; v_PlanAmount := recCD(i).PlanAmount; v_SvcFiscalMonth := 'PEND'; --lk 10/04/2004 v_SeqNumb := v_SeqNumb + 1; reccd(i).claimdetailhistseqnumb := v_seqnumb; claimsplit.EXTEND; claimsplit (claimsplit.LAST).claimid := reccd (i).claimid; claimsplit (claimsplit.LAST).claimlinenumb := reccd (i).claimlinenumb; claimsplit (claimsplit.LAST).claimdetailhistseqnumb := reccd(i).claimdetailhistseqnumb; claimsplit (claimsplit.LAST).claimdetailhistrectypecd := reccd (i).claimdetailhistrectypecd; claimsplit (claimsplit.LAST).operationcentercode := reccd (i).regionalnetworkcenter; claimsplit (claimsplit.LAST).feeforservice := reccd (i).feeforservice; claimsplit (claimsplit.LAST).servicecatid := reccd (i).servicecatid; claimsplit (claimsplit.LAST).svcfiscalmonth := v_svcfiscalmonth; claimsplit (claimsplit.LAST).paymentfiscalmonth := reccd (i).paymentfiscalmonth; claimsplit (claimsplit.LAST).provamount := reccd (i).provamount; claimsplit (claimsplit.LAST).carid := reccd (i).carid; claimsplit (claimsplit.LAST).provtypecd := reccd (i).provtype; claimsplit (claimsplit.LAST).planamount := reccd (i).planamount; v_FromFiscalInfo.FiscalMonth := 'PEND'; ELSE v_AmtUsed := 0; v_PlanAmtUsed := 0; --LJK 06/27/2005 Added to initialize new field. v_Date := recCD(i).FromDate; WHILE v_FromFiscalInfo.LastDay <= NVL(v_ToFiscalInfo.LastDay,(v_CurrentInfo.LastDay + 1)) LOOP v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; IF v_FromFiscalInfo.LastDay > v_PendingLastDay THEN --LK 10/04/2004 v_ProvAmount := recCD(i).ProvAmount - v_AmtUsed; -- Into the Pending month v_PlanAmount := recCD(i).PlanAmount - v_PlanAmtUsed; --LJK 08/23/2005. Set the PlanAmount not the Used Amount. v_FromFiscalInfo.FiscalMonth := 'PEND'; v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; ELSIF v_FromFiscalInfo.LastDay = v_ToFiscalInfo.LastDay THEN v_ProvAmount := recCD(i).ProvAmount - v_AmtUsed; v_PlanAmount := recCD(i).PlanAmount - v_PlanAmtUsed; v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; --v_ToFiscalMonth; v_FromFiscalInfo.FiscalMonth := 'PEND'; v_FromFiscalInfo.LastDay := v_ToFiscalInfo.LastDay + 1; ELSE v_FMPart := ((v_FromFiscalInfo.LastDay - v_Date) + 1) / recCD(i).TotalServiceDays; v_ProvAmount := Round(recCD(i).ProvAmount * v_FMPart,2); v_PlanAmount := Round(recCD(i).PlanAmount * v_FMPart,2); v_AmtUsed := v_AmtUsed + v_ProvAmount; v_PlanAmtUsed := v_PlanAmtUsed + v_PlanAmount; v_FromFiscalInfo := f_GetFiscalInfo(v_FromFiscalInfo.LastDay + 1); v_Date := v_FromFiscalInfo.FirstDay; END IF; v_SeqNumb := v_SeqNumb + 1; ClaimSplit.EXTEND; ClaimSplit(ClaimSplit.LAST).ClaimID := recCD(i).ClaimId; ClaimSplit(ClaimSplit.LAST).ClaimLineNumb := recCD(i).ClaimLineNumb; ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb := v_seqnumb; ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd; ClaimSplit(ClaimSplit.LAST).OperationCenterCode := recCD(i).RegionalNetworkCenter; ClaimSplit(ClaimSplit.LAST).FeeForService := recCD(i).FeeForService; ClaimSplit(ClaimSplit.LAST).ServiceCatId := recCD(i).ServiceCatId; ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth := v_SvcFiscalMonth; ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth := recCD(i).PaymentFiscalMonth; ClaimSplit(ClaimSplit.LAST).ProvAmount := v_ProvAmount; ClaimSplit(ClaimSplit.LAST).CarId := recCD(i).CarId; ClaimSplit(ClaimSplit.LAST).ProvTypeCD := recCD(i).ProvType; ClaimSplit(ClaimSplit.LAST).PlanAmount := v_PlanAmount; IF v_svcfiscalmonth = 'PEND' THEN EXIT; END IF; END LOOP; END IF; END LOOP; BEGIN FORALL i IN 1 .. claimsplit.COUNT SAVE EXCEPTIONS INSERT INTO w_claimsplit VALUES claimsplit (i); COMMIT; EXCEPTION WHEN bulk_errors THEN FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP DBMS_OUTPUT.put_line ( 'Error from element #' || TO_CHAR (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX) || ': ' || SQLERRM (SQL%BULK_EXCEPTIONS (j).ERROR_CODE) ); END LOOP; END; claimsplit.DELETE; EXIT WHEN curcd%NOTFOUND; END LOOP; COMMIT; CLOSE curCD; EXCEPTION WHEN OTHERS THEN error_number := SQLCODE; error_message := substr(SQLERRM, 1, 200); dbms_output.put_line('error: ' || error_number || ' ' || error_message); DBMS_OUTPUT.PUT_LINE(v_ClaimID || ' ' || v_ClaimLineNumb); END p_claimsplit;