SELECT CMP_CMP_COD,CMP_ARB_CMP_NAM,CMPACTV_LICN_COD,STSACTV_ARAB_DESC,CMPACTV_LICN_STS_COD 
FROM CMTCMP,CMTCMPACTV,CMTSTSACTV 
WHERE CMTCMP.CMP_CMP_COD = CMTCMPACTV.CMPACTV_CMP_COD 
AND CMTCMPACTV.CMPACTV_LICN_STS_COD = CMTSTSACTV.STSACTV_COD 
AND CMTCMP.CMP_LAW_COD IN (6,9,11) --- ??????? ?????? : 6=92/95 ????? ??? ??? ????? & 9=????? 230 /95 & 11=95/8 ????? ????????? 
AND CMTCMPACTV.CMPACTV_ACTV_COD = 7 --- ??????: 7= ????? ?? ??????? ??????? 
AND CMTCMPACTV.CMPACTV_LICN_STS_COD  IN (1,2,3,5) --- ???? ??????? ??????:1= ???? ?????? ?????? 
AND CMTCMP.CMP_MAIN_STSC_COD IN (3,9,4)
------------------------------------------------------------------------------------------------
SELECT Get_Cmt_Desc('CMTCMP',BRANCHES.COMP_ID),BRANCHES.BRANCH_NAME_AR,
       BRANCH_TYPES.BRANCH_TYPE_NAME,BRANCH_ADDRESSES.BRANCH_ADDRESS,
       CMTGOV.GOV_ARB_GOV_NAM,CMTCTY.CTY_ARB_CTY_NAM,BRANCHES.EFSA_APPROVAL_DATE
FROM BRANCHES,BRANCH_ADDRESSES,BRANCH_PURPOSES,BRANCH_TYPES,CMTGOV,CMTCTY
WHERE BRANCHES.COMP_ID = BRANCH_ADDRESSES.COMP_ID
AND BRANCHES.BRANCH_ID = BRANCH_ADDRESSES.BRANCH_ID
AND BRANCHES.COMP_ID = BRANCH_PURPOSES.COMP_ID
AND BRANCHES.BRANCH_ID = BRANCH_PURPOSES.BRANCH_ID
AND BRANCH_PURPOSES.BRANCH_TYPE_ID = BRANCH_TYPES.BRANCH_TYPE_ID
AND BRANCH_ADDRESSES.GOV_ID = CMTGOV.GOV_GOV_COD(+)
AND BRANCH_ADDRESSES.GOV_ID = CMTCTY.CTY_GOV_COD(+)
AND BRANCH_ADDRESSES.CITY_ID = CMTCTY.CTY_CTY_COD(+)
AND BRANCHES.APPROVAL_FLAG <>0
AND BRANCH_ADDRESSES.BRANCH_ADDRESS_STATUS = 'C'
AND BRANCH_PURPOSES.BRN_PRP_STATUS = 'C'
AND BRANCHES.COMP_ID  = nvl(:CMP_CMP_COD,BRANCHES.COMP_ID)
-------------------------------------------------------------------------------------
select CMPACTV_CMP_COD,CMP_ARB_ADRS   CMPACTV_LICN_DATE ,CMP_ARB_CMP_NAM ,CMP_TEL ,CMP_CMA_ABRV_DATE ,Get_Cmt_Desc('cmtprs',CMP_PRS_COD )
from cmtcmpactv ,cmtcmp
where cmtcmpactv.CMPACTV_CMP_COD=cmtcmp.CMP_CMP_COD
and CMPACTV_ACTV_COD =24
and CMPACTV_LICN_STS_COD =1;
-----------------------------------------------------------------------------------------------
select  CMP_CMP_COD, CMP_ARB_CMP_NAM, Get_Cmt_Desc('cmtlawf',CMP_LAW_FRM_COD )
from cmtcmp
where CMP_LISTING_TYPE =273