joybanerjee

 Profile Answers by joybanerjee Questions by joybanerjee

• Dec 20th, 2007

 

create or replace procedure sql_to_procedure as

declare

cursor c1 is

SELECT APE.DAT_INSERT_DATE as "Payment Entry Date",

APE.TXT_INTERMEDIARY_CD AS "Intermediary ID",

APE.TXT_PAYER_CUSTOMER_ID as "CustomerID/Payer ID",

TXT_CUSTOMER_NAME as "Customer name",

GI.txt_intermediary_name as "Intermediary Name",

GPI.TXT_DEAL_ID as "Deal ID",

APE.Num_instrument_id as "Payment ID",APE.txt_instrument_no as "Payment No",

APE.txt_alt_instrument_no as "Alternate Payment No",

APE.TXT_DRAWEE_BANK_NAME as "Bank Name",APE.TXT_DRAWEE_BANK_BRANCH as "Branch Name",

APE.dat_instrument_date as "Payment Date",APE.num_instrument_amt as "Payment Amount",

APE.num_balance_amt as "Balance Amount",

decode(APE.TXT_PAYMENT_MODE_CD,'C','Cheque','S','Cash','DA','DA','D','DD')

as "Payment Mode",

decode(APE.NUM_INSTRUMENT_TYPE_CD,0,'Not Valid',1,'Local',2,'Upcountry Cheque',3,'High Value Cheque',4,

'Local ICICI Bank Cheques',5,'Foreign Cheques',6,'UCC ICICI cheques') as "Payment Type",

GOF.TXT_OFFICE as "Office Name",AF.TXT_FINANCIER_NAME as "House Bank",

APE.DAT_ACCEPTANCE_DATE as "Acceptance Date",

DCT.TXT_PRIMARY_VERTICAL_NAME as "Primary Vertical",

DCT.TXT_PRIMARY_MO_NAME as "Primary RM ",

DCT.TXT_SECONDARY_VERTICAL_NAME as "Secondary Vertical",

DCT.TXT_SECONDARY_MO_NAME as "Secondary RM",

DCT.TXT_TERTIARY_VERTICAL_NAME as "Tertiary Vertical",

DCT.TXT_TERTIARY_MO_NAME as "Tertiary RM",

AMP.NUM_PROPOSAL_NO as "Proposal No"

FROM ACC_PAYMENT_ENTRY APE,ACC_MAP_INSTRPROPOSAL AMP,GENMST_INTERMEDIARY GI,

ACCMST_FINANCIER AF,GENMST_office GOF, GEN_PROP_INFORMATION_TAB GPI,DISTRIBUTION_CHANNEL_TAB DCT ,GENMST_CUSTOMER

where

WHERE APE.NUM_INSTRUMENT_ID=AMP.NUM_INSTRUMENT_ID(+)

and APE.TXT_INTERMEDIARY_CD=GI.TXT_INTERMEDIARY_CD(+)

and nvl(APE.NUM_HOUSE_BANK_BRANCH_CD,1001)=AF.NUM_FINANCIER_CD

and upper(APE.TXT_STATUS) = 'CANCELLED'

and GPI.NUM_REFERENCE_NUMBER(+) = AMP.NUM_PROPOSAL_NO

and APE.NUM_OFFICE_CD = GOF.NUM_OFFICE_CD(+)

and GPI.NUM_REFERENCE_NUMBER=DCT.NUM_REFERENCE_NUMBER(+)

and GPI.DAT_REFERENCE_DATE= DCT.DAT_REFERENCE_DATE(+)

and TXT_CUSTOMER_CD = APE.TXT_PAYER_CUSTOMER_ID

and GOF.NUM_OFFICE_CD in

(select distinct NUM_OFFICE_CD from genmst_office where NUM_OFFICE_CD=2) ORDER BY APE.NUM_INSTRUMENT_ID DESC ;

x1 varchar2(10);

y1 varchar2(20);

z1 varchar2(30);

begin

for i in C1 loop

if (i.TXT_INTERMEDIARY_CD is null or i.TXT_PAYER_CUSTOMER_ID <> i.TXT_INTERMEDIARY_CD) then

Select TXT_CUSTOMER_NAME into x1

from GENMST_CUSTOMER

where cTXT_CUSTOMER_CD = i.TXT_PAYER_CUSTOMER_ID;

else

Select TXT_INTERMEDIARY_NAME into x1

from GENMST_INTERMEDIARY

where TXT_INTERMEDIARY_CD = i.TXT_PAYER_CUSTOMER_ID ;

end if;

if

i.TXT_PAYER_CUSTOMER_ID=i.txt_intermediary_cd then

y1:='Intermediary';

else

y1:='Customer';

end if;

i:=i+1;

end loop;

end;