.......... MAIN PROCEDURE...........
create or replace
PROCEDURE                                              DAILY_RPT
As 
P_date varchar2(20):=to_char(sysdate-1,'mm/dd/yyyy');
V_Process_Name Varchar2(50):='DAILY_RPT';
V_Seq Number :=0;

BEGIN 
 select SEQ_DATAXPROCESSLOG_MONTHEND.NEXTVAL into v_seq from dual;
Insert Into Log_Month_End
  (SEQ_NUM,PROCESS_NAME, STATUS,PROCESS_DATE,module_name) 
  Values
  (V_Seq,V_Process_Name,'STA',Sysdate,'CONSOLIDATED');
  Commit;
FOR REC IN  (
select distinct CSO_ID,ST_Code from  csoid_Stcode 
where --cso_id>=9 
 (cso_id,st_code) not   in  (select cso_id,st_code from test_stores)

 and cso_id in (9,900)
 
 order by cso_id

)
 
Loop

INSERT INTO TIMINGS (CSO_ID ,PROCESS_NAME ,STATUS ,DATE_CREATED )
VALUES(REC.CSO_ID,v_process_name,'STA',SYSDATE)
;

Agin_Detai (P_Date,rec.cso_id);
ACHPORT_CONSOL (P_DATE,REC.CSO_ID);
Revenue_olidated (P_Date,rec.cso_id);
teral_Type_Consol (P_Date,rec.cso_id);
dloan_Consoli (P_Date,rec.cso_id);
s_until_DON(P_Date,rec.cso_id);
proff_Loa(P_Date,rec.cso_id);
DBMS_OUTPUT.PUT_LINE('P_Date..........'||P_DATE);

INSERT INTO TIMINGS (CSO_ID ,PROCESS_NAME ,STATUS ,DATE_CREATED )
VALUES(REC.CSO_ID,v_process_name,'COM',SYSDATE)
;
end LOOP;
commit;
UPDATE LOG_MONTH_END SET JOB_END_TIME=SYSDATE WHERE SEQ_NUM=V_SEQ;

Commit;
MAIL_ALERT_rpt();

END;

.................SUB-PROCEDURE...................
create or replace
PROCEDURE Agin_Detai (P_date varchar2,p_cso_id number)
AS 
V_FILE UTL_FILE.FILE_TYPE ;
V_FILENAME VARCHAR(100);
v_delimitedchar char(1);
V_PROCESS_NAME VARCHAR(100):='AGIN_DETAIL';
V_CNT NUMBER:=0;
V_SQLERRORMSG VARCHAR(150);
v_sqlerrorcd varchar(150);
v_dt date:=to_date(P_date,'mm/dd/yy');v_path varchar2(50);v_seq number;

BEGIN 
--select path into v_path from extract_csoid_Stcode where cso_id=p_cso_id;
select distinct trim(path) into v_path from csoid_Stcode where cso_id=p_cso_id ;
SELECT SEQ_DATAXPROCESSLOG.NEXTVAL INTO V_SEQ FROM DUAL;
INSERT INTO error_log
  (SEQ_NUM,PROCESS_NAME, STATUS,PROCESS_DATE) 
  VALUES
  (v_seq,V_PROCESS_NAME,'STA',SYSDATE);commit;
  
  V_Delimitedchar:=Chr(124);
  V_Filename:='AGIN_DETAIL_CONSOLIDATED_'||V_Dt||'.TXT';
  V_File:=Utl_File.Fopen(V_Path,V_Filename,'W',32767);
  Utl_File.Put_Line (V_File,'Customer #|First Name|Last Name|Store #|Loan #|Loan Date|Loan Amt|CSO Fee|CRF Fee|Total Due|Due Date|Loan Age|Days until due|Pay Frequency|Scheduled ACH date|Scheduled ACH Amount|ACH source|Installment Due Amount|Product Type|Street Address|City|State ID|ZIP|Phone #|Phone Type'||Chr(13));
  FOR REC IN (

SELECT BO.BO_CODE AS CUSTOMER,
       bo.first_name as FirstNAME, 
       bo.last_name as LastName,
       Mas.Cso_St_Code As Store,
       Mas.Loan_Code As Loan,
       TO_CHAR(MAS.LOAN_DATE,'mm/dd/yyyy') AS LOANDATE, 
      ......

FROM LOAN_MASTER MAS
INNER JOIN CUST_MASTER BO ON BO.BO_CODE = MAS.BO_CODE
inner join cust_address ba on ba.bo_code = mas.bo_code
--inner join bo_phone bp on bp.bo_code = mas.bo_code and bp.is_primary = 'Y'
--inner join lt_pa_phone_type pt on pt.phone_id = bp.phone_id

where loan_status_id = 'OPN'  --and mas.cso_st_code not in (1)
--and trunc(mas.loan_date) >= to_date(v_Start_date,'mm/dd/yyyy')
 and trunc(mas.loan_date) <= to_date(P_date,'mm/dd/yyyy') and mas.cso_id=p_cso_id
and (mas.cso_id,mas.cso_st_code) not   in  (select cso_id,cso_st_code from cso_test_stores)
order by 13,5

)
Loop
Utl_File.Put_Line (V_FILE,REC.	Customer	||V_DELIMITEDCHAR||
REC.	FIRSTNAME	||V_DELIMITEDCHAR||
REC.	LASTNAME	||V_DELIMITEDCHAR||
REC.	Store	||V_DELIMITEDCHAR||rec.loan||v_delimitedchar||
REC.	LoanDate	||V_DELIMITEDCHAR||
Rec.	Loanamt	||V_Delimitedchar||
REC.	CSOFee	||V_DELIMITEDCHAR||rec.credit_fee||V_DELIMITEDCHAR||
REC.	TotalDue	||V_DELIMITEDCHAR||
REC.	DueDate	||V_DELIMITEDCHAR||
REC.	DaysfromLoanDate	||V_DELIMITEDCHAR||
rec.DaysFromCurrentDay||v_delimitedchar||
REC.	PayFrequency	||V_DELIMITEDCHAR||
REC.	ScheduledACHDate	||V_DELIMITEDCHAR||
REC.	ScheduledACHAmount	||V_DELIMITEDCHAR||
REC.	ACHsource	||V_DELIMITEDCHAR||
REC.	InstallmentDueAmount	||V_DELIMITEDCHAR||
--REC.	FirstMissedDueDate	||V_DELIMITEDCHAR||
--REC.	SecondMissedDueDate	||V_DELIMITEDCHAR||
REC.	ProductType	||V_DELIMITEDCHAR||
REC.	StreetAddress	||V_DELIMITEDCHAR||
REC.	City	||V_DELIMITEDCHAR||
REC.	StateId	||V_DELIMITEDCHAR||
REC.	Zip	||V_DELIMITEDCHAR||
Rec.	Phone	||V_Delimitedchar||
Rec.	Phonetype	||CHR(13)  
);

V_CNT:=V_CNT+1;
END LOOP;

UTL_FILE.FCLOSE(V_FILE);

INSERT INTO error_log
 (SEQ_NUM, PROCESS_NAME, 
                 STATUS,PROCESS_DATE,CNT
               )
        VALUES (SEQ_DATAXPROCESSLOG.NEXTVAL, V_PROCESS_NAME, 
                'COM',SYSDATE,V_CNT
               );
  
COMMIT;
EXCEPTION 
WHEN OTHERS
THEN 
V_SQLERRORCD:=SQLCODE;
V_SQLERRORMSG :=SUBSTR(SQLERRM,1,125);

INSERT INTO error_logt
 (SEQ_NUM, PROCESS_NAME, 
                 STATUS,PROCESS_DATE,ERROR_MSG,ERROR_ID
               )
        VALUES (SEQ_DATAXPROCESSLOG.NEXTVAL, V_PROCESS_NAME, 
                'ERR',SYSDATE,V_SQLERRORMSG,V_SQLERRORCD
               );
 
commit;

END;