LD Billing Porcess Email Notification

CREATE OR REPLACE TRIGGER email_nsef_bill
AFTER insert on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(50) := 'NSEF Bill Process Done';
l_message VARCHAR2(500);

BEGIN

if (USER in ('RITESHR')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'User: ' || SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
end if;
END email_nsef_bill;
/

Bulk Client Code Change prg

Set Exclusive OFF
Set Date BRITISH
Set Century on
Set Talk OFF
Set Safety OFF

Close ALL
Clear All
Clear

lcFirmnum='NBS-000001'
gnQryhandle=sqlconnect("NBS1011odbc","ldbo","ldbo")

lnSqlanswer=SQLPrepare(gnQryhandle,"select * from Ldfibs where firmnumber=?lcFirmnum ","ldfibs")
If lnSqlanswer!=-1
lnSqlanswer=SQLEXEC(gnQryhandle)
lnsqlanswer=sqlcommit(gnQryhandle)
EndIf
IF lnSqlanswer=-1
=AError(myErr)
=MessageBox(myErr(2)+" "+myErr(3),64,"Odbc Error")
Return
EndIf
Index on Oowncode tag oownin

Select 0
USE c:\temp\cldetail

Create Cursor erroroowncode(OldOowncode c(10),NewOowncode c(10),cErrortext char(150))
Create Cursor errortermcode(OldTermcode c(10),NewTermcode c(10),cErrortext char(150))

Create Cursor correctoowncode(OldOowncode c(10),NewOowncode c(10),cErrortext char(150))
Create Cursor correcttermcode(OldTermcode c(10),NewTermcode c(10),cErrortext char(150))

=messagebox(str(gnQryhandle))

lnCtr=0
Select Cldetail
Scan
lcOldcode=Cldetail.Oldcode
lcNewcode=Cldetail.Newcode
Select Ldfibs
Seek lcNewcode
If ! Found()
lnSqlanswer=SQLPREPARE(gnQryhandle,"{Call sp_Clientcodechange(?lcFirmnum,?lcOldcode,?lcNewcode,'N')}")
If lnSqlanswer!=-1
lnSqlanswer=SQLEXEC(gnQryhandle)
Insert into Correctoowncode values (lcOldcode,lcNewcode,'Successfully Changed')
EndIf
If lnSqlanswer=-1
=AError(myErr)
** =messagebox(myErr(2)+" "+myErr(3),64,"Odbc Error")
Select erroroowncode
Insert into erroroowncode values (lcOldcode,lcNewcode,x(2)+" "+x(3))
EndIf
lnCtr=lnCtr+1
@10,10 Say lnCtr
Else
Select erroroowncode
Insert into erroroowncode values (lcOldcode,lcNewcode,'Oowncode Already Exists')
EndIf
EndScan

=SQLCommit(gnQryhandle)

Select errorOowncode
Copy To c:\temp\erroroown.dbf

Select errorTermcode
Copy To c:\temp\errorterm.dbf

Close All
Return

LD AR > always release to AF > as per formula query

Client Master > Der+ Gen VI > Demat > Payout Status Quo
Client Master > Der+ Gen VI > Default product > Status Quo

AR > always release
AF > as per formula

Select T1.Oowncode From Accountdetail T1, Ldfibs T2
Where T1.Oowncode=T2.Oowncode And T1.Firmnumber=T2.Firmnumber
And T1.Firmnumber='ACML-00001'
And T1.Cpayoutstatusquo='AR' And T2.Brcode In(

)
;


Select distinct T1.Cclientcode From Tblclientproductactivation T1, Ldfibs T2
Where T1.Cclientcode=T2.Oowncode And T1.Cfirmnumber=T2.Firmnumber
And T1.Cfirmnumber='ACML-00001'
And T1.Cpayoutstatusquo='AR' And T2.Brcode In (

);


UPDATE ACCOUNTDETAIL SET Cpayoutstatusquo= 'AF' WHERE FIRMNUMBER='ACML-00001' and Cpayoutstatusquo= 'AR' AND oowncode= '20100A005';



UPDATE Tblclientproductactivation SET Cpayoutstatusquo='AF' Where Cfirmnumber='ACML-00001' And Nproductcode='0' And Cpayoutstatusquo='AR' and Cclientcode= '20100A005';

LD LOCKTABLE Email Notification

Select * From Tbllocktable Where Dcashbankfinancialentry Not Like ' ';
------------


CREATE OR REPLACE TRIGGER email_lock_table
AFTER UPDATE of Dcashbankreceiptentry,Dcashbankfinancialentry,Djournalentry on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ALERT: UNLOCK Data Files FY1112';
l_message VARCHAR2(500);

BEGIN

if (SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) not in ('LDBO')) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'User: ' || SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END email_lock_table;
/