give source column and target columns and give the source column names in format schema.table.column. Make sure its exact names are there and not aliases or something. make sure it has all the columns in the output table
WITH PREV_EXECUTION_DATE AS (SELECT CONCAT(SUBSTR(LWD,1,4),'-',SUBSTR(LWD,5,2),'-',SUBSTR(LWD,7,2) ) LWD FROM
(SELECT CAST(MAX(t4pf_prcssng_dte +19000000) AS STRING) LWD FROM RAW_DATA_VAULT.t4pf_processing_character_history WHERE t4pf_load_country=${P_LOAD_COUNTRY}
AND partition_t4pf_load_date<=${PRCESS_DTE} ) A ) ,
deals_stg as (SELECT * from ifrs9.deals_funded_unfunded_report join PREV_EXECUTION_DATE P on 1=1 where partition_load_date=P.LWD AND load_country= ${P_LOAD_COUNTRY} ),
EXT_RATING AS (SELECT * from ifrs9.external_ratings join PREV_EXECUTION_DATE P on 1=1 where partition_load_date=P.LWD AND load_country=${P_LOAD_COUNTRY})
SELECT DISTINCT A.* ,
case
when Load_Country in ('QA', 'EG') and
max_stage =2 AND
PREVIOUS_STAGE_2_ is null then
CAST(LAST_DAY(Load_Date ) AS TIMESTAMP)
when Stage = 2 and staging_Reason not in ( 'Rating Downgrade')
then CAST(LAST_DAY(Load_Date ) AS TIMESTAMP)
when Load_Country in ('QA', 'EG') and
max_stage =2 and
PREVIOUS_STAGE_2_='2' then
PREVIOUS_STAGE_2_DATE_
when (Stage =1 or (Stage = 2 and staging_Reason = 'Rating Downgrade')) and PREVIOUS_STAGE_2_DATE_ is not null
and PREVIOUS_STAGE_2_ = '2'
then PREVIOUS_STAGE_2_DATE_
when (Stage =1 or (Stage = 2 and staging_Reason = 'Rating Downgrade'))
and PREVIOUS_STAGE_2_ = '3'
then CAST(LAST_DAY(Load_Date ) AS TIMESTAMP)
when Stage = 1 and PREVIOUS_STAGE_2_DATE_ is null then null
when Stage = 3 then CAST(LAST_DAY(Load_Date ) AS TIMESTAMP)
when Stage = 2 then CAST(LAST_DAY(Load_Date ) AS TIMESTAMP)
end as effective_staging_date ,
case
when (Stage =1 or (Stage = 2 and staging_Reason = 'Rating Downgrade'))
and PREVIOUS_STAGE_2_DATE_ is not null and PREVIOUS_STAGE_2_ = '2'
then '2'
when (Stage =1 or (Stage = 2 and staging_Reason = 'Rating Downgrade'))
and PREVIOUS_STAGE_2_DATE_ is not null and PREVIOUS_STAGE_2_ = '3'
then '2'
when Load_Country in ('QA', 'EG')
and max_stage = 2
then '2'
when staging_Reason <> 'Rating Downgrade'
and Stage = 3 then '3'
when staging_Reason <> 'Rating Downgrade'
and Stage = 2 then '2'
when staging_Reason <> 'Rating Downgrade'
and Stage = 1 then '1'
when Stage = 1 and PREVIOUS_STAGE_2_DATE_ is null then '1'
when Stage = 2 and staging_Reason = 'Rating Downgrade' and PREVIOUS_STAGE_2_DATE_ is null then '2'
end effective_stage ,
case
when Load_Country in ('QA', 'EG') and
max_stage =2 AND
PREVIOUS_STAGE_2_ is null then
max(staging_Reason) OVER (partition by Customer_Id,max_stage)
when Stage = 2 and staging_Reason NOT IN ( 'Rating Downgrade')
then staging_Reason
when Load_Country in ('QA', 'EG') AND
max_stage=2 and
PREVIOUS_STAGE_2_='2'
then previous_stage_2_reason_
when (Stage =1 or (Stage = 2 and staging_Reason = 'Rating Downgrade')) and PREVIOUS_STAGE_2_DATE_ is not null
and PREVIOUS_STAGE_2_ = '2'
then previous_stage_2_reason_
when (Stage =1 or (Stage = 2 and staging_Reason = 'Rating Downgrade')) and PREVIOUS_STAGE_2_DATE_ is not null
and PREVIOUS_STAGE_2_ = '3'
then 'Reclassified from Stage 3'
when (Stage =1 and PREVIOUS_STAGE_2_DATE_ is null ) then null
when Stage = 1 and PREVIOUS_STAGE_2_DATE_ is null then null
when Stage = 3 then 'NPL Facilities'
when Stage = 2 then staging_Reason
end as effective_staging_reason
,
case when Load_Country in ('PS','BH','B2','QA') then
(
case when nvl(cash_collateral,0) + 0 >= outstanding_balance_usd then 0
else
( case when (outstanding_balance_usd -NVL(collateral_value_usd,0)) /(outstanding_balance_usd - (nvl(cash_collateral,0) + 0 ) )*0.45 < 0.1
then (0.1 * (outstanding_balance_usd - (nvl(cash_collateral,0) )) /outstanding_balance_usd )
else
((outstanding_balance_usd -NVL(collateral_value_usd,0) ) /(outstanding_balance_usd - (nvl(cash_collateral,0) + 0 ) )*0.45)
*
(outstanding_balance_usd - (nvl(cash_collateral,0) ))/outstanding_balance_usd
end
)
end
)
else
(case when nvl(cash_collateral,0) + 0 >= outstanding_balance_usd then 0
else
( case when (outstanding_balance_usd -NVL(collateral_value_usd,0) ) /(outstanding_balance_usd - (nvl(cash_collateral,0) + 0 ) )*0.45 < 0.1
then (0.1 *
(outstanding_balance_usd - (nvl(cash_collateral,0) + 0 ))/outstanding_balance_usd
)
else
((outstanding_balance_usd -NVL(collateral_value_usd,0) ) /(outstanding_balance_usd - (nvl(cash_collateral,0) + 0 ) )*0.45)
*
(outstanding_balance_usd - (nvl(cash_collateral,0) + 0 ))/outstanding_balance_usd
end
)
end)
end LGD_FINAL
FROM (
SELECT max(Stage) OVER (partition by Customer_Id) as max_stage, P.*,
case
when Stage = 3 then 'NPL Facilities'
when (AB_default_rating = 7 ) then 'Watch List'
when ((Load_Country in ('B2','BH','PS') and CAST(customer_delinquency_status AS INT) >= 30 ) or CAST(customer_delinquency_status AS INT) >= 30 ) then 'Past Dues'
when (Rescheduled_flag = 'Y') then 'Rescheduled'
when (restructured_flag = 'Y') then 'Restructured'
when (rating_at_origin in (1,2,3,4) and rating_at_reporting_date -rating_at_origin >= 2 ) or
(rating_at_origin in (5,6) and rating_at_reporting_date -rating_at_origin >= 1) then 'Rating Downgrade'
when coa_item like '148%' or AB_default_rating > 7 OR Stage = 3 then 'NPL Facilities'
else ' '
end staging_reason FROM
(
SELECT B.*,
case when coa_item like '148%' or AB_default_rating > 7 then 3
when (
(Load_Country in ('B2','BH','PS') and CAST(customer_delinquency_status AS INT) >= 30 ) or
CAST(customer_delinquency_status AS INT) >= 30)
or
(restructured_flag = 'Y')
or
(Rescheduled_flag = 'Y')
or
(AB_default_rating = 7 )
or
(
rating_at_origin in (1,2,3,4)
and
rating_at_reporting_date -rating_at_origin >= 2
)
or
(
rating_at_origin in (5,6)
and
rating_at_reporting_date -rating_at_origin >= 1
)
then 2
else 1
end stage
FROM
(select CASE WHEN type= 'Unfunded' THEN 'EXM' ELSE ALL1.deal_type END ab_sourcesystem,ALL1.facility_ref facility_id,
---CONCAT(ALL1.customer_equation_id,CAST(STAGE AS STRING) ) as cust_stage,
CASE WHEN ALL1.product_segment = 'FI' then ALL1.bank_new ELSE NVL(UNFUN_CUST.customer_equation_id,ALL1.customer_equation_id_new) END as customer_id ,
ALL1.account_branch_number_new as branch,
ALL1.external_account_number_new AS basic,
---case when ALL1.load_country='JO' then external_account_number else
---NVL(UNFUN_CUST.customer_equation_id,ALL1.customer_equation_id_new) end basic,
ALL1.account_suffix_NEW as sfx,
customer_group_name,
CASE WHEN ALL1.product_segment = 'FI' then orbis.BANKDESC ELSE NVL(UNFUN_CUST.full_name_in_arabic,ALL1.full_name_in_arabic) END customer_name_arabic,
CASE WHEN ALL1.product_segment = 'FI' then orbis.bankdesc ELSE NVL(UNFUN_CUST.full_name_in_english,ALL1.full_name_in_english) END full_name_in_english,
CASE WHEN ALL1.product_segment = 'FI' THEN 'CB' ELSE NVL(UNFUN_CUST.customer_segment,ALL1.customer_segment ) END customer_segment ,
case when ALL1.product_segment = 'FI' then '651' ELSE NVL(UNFUN_CUST.economic_sector, ALL1.economic_sector) END AS industry_code,
case when ALL1.product_segment = 'FI' then 'BANKS AND S&LS' ELSE NVL(UNFUN_CUST.economic_sector_description, ALL1.economic_sector_description ) END AS industry_description,
CASE WHEN ALL1.product_segment = 'FI' THEN SUBSTR(ALL1.bank_new,5,2) ELSE cust_info.risk_country END country_of_risk,
CASE WHEN type= 'Funded' THEN
(case when ALL1.product_segment = 'FI' then cast(er.external_rating_int as integer) else NVL(cast( UNFUN_CUST.DEFAULT_RISK_RATING as integer),
cast( ALL1.DEFAULT_RISK_RATING as integer) ) end )
WHEN type= 'Unfunded' THEN
(case when ALL1.product_segment = 'FI' then cast(trim(curr_rating.ABRE) as integer) else cast( ALL1.DEFAULT_RISK_RATING as integer) end ) end
AB_default_rating ,
case when ALL1.product_segment = 'FI' then null else mra.mra end mra_rating,
case when ALL1.product_segment = 'FI' then null else ROUND(mra.system_pd,5) end mra_pd,
case when UPPER(res_restr.FLAG)='RESTRUCTURING' THEN 'Y' ELSE 'N' END AS restructured_flag,
case when UPPER(res_restr.FLAG)='RESCHEDULING' THEN 'Y' ELSE 'N' END AS Rescheduled_flag,
ALL1.customer_delinquency_status_new customer_delinquency_status ,
case when ALL1.product_segment = 'FI' then null else customer_classification_new END customer_classification ,
case when ALL1.product_segment = 'FI' then null else classification_desc_new END classification_desc ,
ALL1.agreement_reference_id facility_ref,
ALL1.product_segment,
case when UPPER(ALL1.product_name) = UPPER('Retail Overdrafts') then UPPER('Overdraft' )
when UPPER(REPLACE(ALL1.product_name,' ','')) = 'SINGLELG' OR UPPER(REPLACE(ALL1.product_name,' ','')) = 'MULTIPLELG'
then CONCAT(ALL1.product_name , '-', DEAL_DESC.field_value_description )
else UPPER(ALL1.product_name) end product_type,
start_full_date facility_start_date,
maturity_full_date facility_maturity_date,
currency_code ,
outstanding_balance_usd,
outstanding_balance outstanding_balance_original_ccy,
CASE WHEN ALL1.LOAD_COUNTRY IN ('B2','EG' ) THEN outstanding_balance_usd ELSE outstanding_balance_lcl END outstanding_balance_base_ccy,
CASE WHEN ALL1.LOAD_COUNTRY IN ('B2','EG' ) THEN si.suspended_interest_usd ELSE si.suspended_interest END iis_balance_base_ccy,
'TBD' limit_base_currency,
CASE WHEN type= 'Unfunded' THEN
(CASE WHEN ALL1.deal_type ='EXM' THEN '0' ELSE ALL1.payment_frequency_description_new END )
ELSE
(case when (payment_frequency_description is null
or trim(payment_frequency_description) = '')
and ALL1.Amortization_Type = 'Bullet' then '0'
when (payment_frequency_description is null
or trim(payment_frequency_description) = '') and ALL1.Amortization_Type <> 'Bullet'
then interest_rate_v.interestperiodicity_desc else payment_frequency_description
end) END principal_repayment_frequency ,
case when type= 'Unfunded' THEN 'Bullet' else
(case when deal_type = 'EXM' then 'Bullet'
when deal_type = 'DBL' then 'Exponential' else
(case when Amortization_Type_NEW = 'BehAmortization' and (CASE WHEN ALL1.deal_type ='EXM' THEN '0' ELSE ALL1.payment_frequency_description_new END) = '0' then 'Bullet'
when Amortization_Type_NEW = 'BehAmortization' and (CASE WHEN ALL1.deal_type ='EXM' THEN '0' ELSE ALL1.payment_frequency_description_new END) <> '0' then 'Linear'
WHEN Amortization_Type_NEW = 'ConstantPrincipalAnnuity' THEN 'Linear'
WHEN Amortization_Type_NEW = 'Constant ConstantPrincipalAndInterestAnnuity' THEN 'Exponential'
else Amortization_Type_NEW end)
end) end amortization_type ,
case when type= 'Unfunded' THEN 'Fixed' else (case when interest_rate_v.interestratetype is null then 'Fixed' else interest_rate_v.interestratetype end ) end as interestratetype ,
case when type= 'Unfunded' THEN NULL ELSE int_adv_v.interest_rate*100 END int_rate_discounted_bills,
---ALL1.interest_rate as applied_interest_rate,
CASE WHEN type= 'Unfunded' THEN NULL ELSE
MAX(nvl(CASE WHEN type= 'Funded' THEN (CASE WHEN type= 'Funded' THEN (case when UPPER(INTEREST_RATE_SPREAD_fund.interestratetype) ='FIXED' THEN INTEREST_RATE_SPREAD_fund.MAX_FIXEDRATE
ELSE INTEREST_RATE_SPREAD_fund.MAX_clientratespread END ) ELSE NULL END) ELSE NULL END,0) + nvl(nvl(CASE WHEN TRIM(interest_rate_v.FIXINGATBSD)='' THEN 0 ELSE CAST(TRIM(REPLACE(interest_rate_v.FIXINGATBSD,'%','')) AS DOUBLE) END ,0) ,0) + nvl(case when type= 'Unfunded' THEN NULL ELSE int_adv_v.interest_rate*100 END ,0) ) over (
partition by ALL1.DEAL_FACT_KEY ) END applied_interest_rate ,
case when type= 'Unfunded' THEN '0' else (CASE WHEN ALL1.DEAL_TYPE='EXM' THEN '0' ELSE (CASE WHEN (interest_rate_v.interestperiodicity_desc IS NULL OR
TRIM(interest_rate_v.interestperiodicity_desc)='' ) THEN
(CASE WHEN type= 'Unfunded' THEN
(CASE WHEN ALL1.deal_type ='EXM' THEN '0' ELSE ALL1.payment_frequency_description_new END )
ELSE
(case when (payment_frequency_description is null
or trim(payment_frequency_description) = '')
and ALL1.Amortization_Type = 'Bullet' then '0'
when (payment_frequency_description is null
or trim(payment_frequency_description) = '') and ALL1.Amortization_Type <> 'Bullet'
then interest_rate_v.interestperiodicity_desc else payment_frequency_description
end) END) ELSE interest_rate_v.interestperiodicity_desc END ) END
) END interest_frequency ,
CASE WHEN type= 'Funded' THEN
(case when ALL1.product_segment = 'FI' then cast(er.external_rating_int as integer) else NVL(cast( UNFUN_CUST.DEFAULT_RISK_RATING as integer),
cast( ALL1.DEFAULT_RISK_RATING as integer) ) end )
WHEN type= 'Unfunded' THEN
(case when ALL1.product_segment = 'FI' then cast(trim(curr_rating.ABRE) as integer) else cast( ALL1.DEFAULT_RISK_RATING as integer) end ) end as rating_at_reporting_date,
facility_dpd ,
ALL1.limit_category_new limit_category ,
case when funded_unfunded='unfunded' then mrgn_bal_usd else 0 end as cash_margin,
NVL(CC.cash_collateral,0) as cash_collateral,
NVL(CC.other_collateral,0) as other_collateral,
NVL(CC.cash_collateral,0)+NVL(CC.other_collateral,0) total_collateral,
ALL1.chart_of_account_key coa_item,
'TBD' LGD,
bankcode bank_swift_code,
trim(CASE WHEN type= 'Unfunded' THEN curr_rating.moody ELSE (CASE WHEN (CASE WHEN ALL1.product_segment = 'FI' THEN 'CB' ELSE NVL(UNFUN_CUST.customer_segment,ALL1.customer_segment ) END )='CB' THEN external_rating.moody ELSE NULL END) END) moodys_rating,
trim(CASE WHEN type= 'Unfunded' THEN curr_rating.sp ELSE
(CASE WHEN (CASE WHEN ALL1.product_segment = 'FI' THEN 'CB' ELSE NVL(UNFUN_CUST.customer_segment,ALL1.customer_segment ) END )='CB' THEN external_rating.sp ELSE NULL END) END ) SP_rating,
trim(CASE WHEN type= 'Unfunded' THEN curr_rating.fitch ELSE
(CASE WHEN (CASE WHEN ALL1.product_segment = 'FI' THEN 'CB' ELSE NVL(UNFUN_CUST.customer_segment,ALL1.customer_segment ) END )='CB' THEN external_rating.fitch ELSE NULL END) END )
fitch_rating,
funded_unfunded ,
collateral_type,
CASE WHEN type= 'Unfunded' THEN (CASE WHEN ALL1.load_country in ('QA','PS') THEN NVL(CC.cash_collateral,0)+NVL(CC.other_collateral,0)
ELSE NVL(CC.cash_collateral,0)+NVL(CC.other_collateral,0) +NVL(mrgn_bal_usd,0) END ) ELSE
(case when NVL(CC.cash_collateral,0)+NVL(CC.other_collateral,0) <0 then 0 else NVL(CC.cash_collateral,0)+NVL(CC.other_collateral,0) end ) END collateral_value_usd,
haircut,
cltrl_value_usd_after_haircut,
'TBD' facility_balance_usd,
'TBD' total_facility_balance,
'' gfpf_cstmr_bsc,
facility_collateral_cover ,
facility_ref_id,
ALL1.product_name,
---product_segment product_segment,
---account_branch_number as branch,
---account_suffix as sfx,
external_account_number ,
---ALL1.customer_equation_id_new customer_id,
due_date,
start_full_date start_date ,
ALL1.Load_Country,
ALL1.partition_load_date,
ALL1.load_date,
prev_stg_dtls.effective_stage prev_effective_stage,
prev_stg_dtls.previous_stage_2_date previous_stage_date,
prev_stg_dtls.previous_stage_2_reason previous_stage_reason,
CASE WHEN type= 'Funded' THEN
nvl(CASE WHEN TRIM(interest_rate_v.FIXINGATBSD)='' THEN 0 ELSE CAST(TRIM(REPLACE(interest_rate_v.FIXINGATBSD,'%','')) AS DOUBLE) END ,0) ELSE
0 END BASE_RATE ,
CASE WHEN type= 'Funded' THEN (case when UPPER(INTEREST_RATE_SPREAD_fund.interestratetype) ='FIXED' THEN INTEREST_RATE_SPREAD_fund.MAX_FIXEDRATE
ELSE INTEREST_RATE_SPREAD_fund.MAX_clientratespread END ) ELSE NULL END AS INTEREST_RATE_SPREAD,
CASE WHEN type= 'Funded' THEN
(case when ALL1.product_segment = 'FI' then cast(ero. EXTERNAL_RATINGS_ORIGIN_int as integer) else
(CASE WHEN AB_RTNG.gfpf_ab_rating_int is not null then AB_RTNG.gfpf_ab_rating_int else
(case when ALL1.product_segment = 'FI' then cast(er. external_rating_int as integer) else cast( NVL(cast( UNFUN_CUST.DEFAULT_RISK_RATING as integer),
cast( ALL1.DEFAULT_RISK_RATING as integer) ) as integer) end) end ) end)
WHEN type= 'Unfunded' THEN
(case when ALL1.product_segment = 'FI' then cast(ero. EXTERNAL_RATINGS_ORIGIN_int as integer) else
AB_RTNG.gfpf_ab_rating_int end ) end as rating_at_origin ,
CASE WHEN type= 'Funded' THEN (CASE WHEN ALL1.bank_new_code IS NULL OR ALL1.bank_new_code='' THEN external_rating.BANK ELSE ALL1.bank_new_code END )
ELSE ALL1.bank_new_code END as bnk
from (
SELECT * FROM (
select unfunded.*,banks.bankdesc,'unfunded' as funded_unfunded,
case when UPPER(unfunded.product_name) in ('CAR LOANS'
,'CASH TO CASH'
,'CREDIT CARDS'
,'GROUP GUARANTEE'
,'HOUSING LOANS'
,'LOANS SETTLEMENTS - PROBLEMATIC INTERESTS'
,'PERSONAL LOANS'
,'RETAIL DISCOUNTED BILLS'
,'SAVING ACCOUNT'
,'TIME DEPOSITS') then 'Retail'
when UPPER(unfunded.product_name) in ('SINGLE LG'
,'MULTIPLE LG','PAID LG') and
TRIM(unfunded.customer_segment) in ('CB') then 'FI'
when UPPER(unfunded.product_name) in ('SYNDICATED LC','UNFUNDED RISK PARTICIPATION') and
TRIM(unfunded.customer_segment) in ('CB','TR') then 'FI'
when UPPER(unfunded.product_name) in ('ACCEPTANCES EXPORT LC'
,'SIGHT EXPORT LC'
,'DEFERRED EXPORT LC'
,'DISCOUNTED EXPORT LC', 'OUTWARD BC AVALIZED DRAFT','DISCOUNTED OUTWARD BC') then 'FI'
when UPPER(unfunded.product_name) in ('RETAIL OVERDRAFTS','CURRENT ACCOUNT') and TRIM(unfunded.customer_segment) in ('RA','RT') then 'Retail'
else 'Corporate' end product_segment,
CASE WHEN ABS(unfunded.outstanding_balance_usd) >=1 THEN customer_delinquency_status_new ELSE '0' END customer_delinquency_status_new,
NVL(unfunded.bank,banks.code) bank_new ,
unfunded.customer_id customer_id_new ,
unfunded.customer_equation_id customer_equation_id_new ,
case when UPPER(UNfunded.product_name) in ('BANK DISCOUNTING', 'COMMERCIAL DISCOUNTING') then '12 '
when UPPER(UNfunded.limit_category) in ('LS002','LS003','LS011','LS257','LS258','LS259','LS260','LS331','LS334','LS343','LS355','LS372','LS385') then '4'
when
UPPER(UNfunded.limit_category) in ('LS004','LS005','LS006','LS007','LS008','LS009','LS010','LS306','LS308','LS312','LS314','LS316','LS330','LS332','LS340','LS386','LS300') then '6'
else (CASE WHEN UNFUNDED.payment_frequency_description IS NULL THEN '0' ELSE UNFUNDED.payment_frequency_description END ) end payment_frequency_description_new,
unfunded.account_branch_number account_branch_number_new,
unfunded.account_suffix account_suffix_new,
unfunded.customer_classification customer_classification_new,
unfunded.classification_desc classification_desc_new,
unfunded.limit_category limit_category_new,
CASE WHEN trim(unfunded.bank)='' OR unfunded.bank IS NULL THEN banks.code
WHEN UPPER(unfunded.product_name) ='SINGLE LG' AND trim(unfunded.bank)<>'' AND trim(unfunded.bank) IS NOT NULL THEN SUBSTR(unfunded.advising_bank,1,6)
ELSE unfunded.bank END bank_new_code,
----NVL(unfunded.bank,banks.code) bank_new_code ,
unfunded.deal_fact_key AS DEAL_KEY_UNFUNDED,
unfunded.external_account_number external_account_number_new ,
unfunded.facility_ref facility_ref_new ,
unfunded.Amortization_Type Amortization_Type_NEW
from
(select NULL AS AGREEMENT_NUM, * from deals_stg where type= 'Unfunded' and load_country= ${P_LOAD_COUNTRY} ) unfunded
left join (SELECT * FROM ifrs9.banks_v join PREV_EXECUTION_DATE P on 1=1 WHERE partition_load_date =P.LWD and load_country=${P_LOAD_COUNTRY} ) banks on REPLACE(nvl(unfunded.deal_fact_key,unfunded.deal_key),' ','') =REPLACE(banks.fact_deal_id,' ','')
left join (select case when UPPER(product_name) = 'DISCOUNTED EXPORT LC' then replace(agreement_number,'C','D') else agreement_number end AGREEMENT_NUM, * from deals_stg where type= 'Funded' and load_country= ${P_LOAD_COUNTRY} ) funded on unfunded.agreement_number=funded.AGREEMENT_NUM
left join (select distinct customer_equation_id, A.customer_delinquency_status customer_delinquency_status_new from deals_stg A where type= 'Funded' and load_country= ${P_LOAD_COUNTRY} )
funded_cust_delinquency on unfunded.customer_equation_id= funded_cust_delinquency.customer_equation_id
where funded.AGREEMENT_NUM is null
union
select funded.*,NVL(banks_unfunded.bankdesc,banks.bankdesc) bankdesc ,'funded' as funded_unfunded,
case when UPPER(funded.product_name) in ('CAR LOANS'
,'CASH TO CASH'
,'CREDIT CARDS'
,'GROUP GUARANTEE'
,'HOUSING LOANS'
,'LOANS SETTLEMENTS - PROBLEMATIC INTERESTS'
,'PERSONAL LOANS'
,'RETAIL DISCOUNTED BILLS'
,'SAVING ACCOUNT'
,'TIME DEPOSITS') then 'Retail'
when UPPER(funded.product_name) in ('SINGLE LG'
,'MULTIPLE LG','PAID LG') and
TRIM(funded.customer_segment) in ('CB') then 'FI'
when UPPER(funded.product_name) in ('SYNDICATED LC','UNFUNDED RISK PARTICIPATION') and
TRIM(funded.customer_segment) in ('CB','TR') then 'FI'
when UPPER(funded.product_name) in ('ACCEPTANCES EXPORT LC'
,'SIGHT EXPORT LC'
,'DEFERRED EXPORT LC'
,'DISCOUNTED EXPORT LC', 'OUTWARD BC AVALIZED DRAFT','DISCOUNTED OUTWARD BC') then 'FI'
when UPPER(funded.product_name) in ('RETAIL OVERDRAFTS','CURRENT ACCOUNT') and TRIM(funded.customer_segment) in ('RA','RT') then 'Retail'
else 'Corporate' end product_segment ,
CASE WHEN ABS(funded.outstanding_balance_usd) >=1 THEN customer_delinquency_status_new ELSE '0' END customer_delinquency_status_new,
NVL(unfunded.bank,funded.bank) bank_new ,
NVL(unfunded.customer_id,funded.customer_id) customer_id_new ,
NVL(unfunded.customer_equation_id,funded.customer_equation_id) customer_equation_id_new ,
case when UPPER(funded.DEAL_TYPE) IN ('DBL','AC-DBL','ECC') and UPPER(funded.product_name) not in ('CURRENT ACCOUNT', 'DUES & OVERDRAWN') then '12'
when UPPER(funded.DEAL_TYPE) IN ('DBL','AC-DBL','ECC') and UPPER(funded.product_name) in ('CURRENT ACCOUNT', 'DUES & OVERDRAWN') then '0'
when UPPER(funded.DEAL_TYPE) = 'EXM' then '0'
when
UPPER(funded.product_name) in ('CURRENT ACCOUNT', 'DUES & OVERDRAWN') then '0'
when UPPER(funded.DEAL_TYPE) in ('EQ','DP') and UPPER(funded.product_name) not in ('BANK DISCOUNTING', 'COMMERCIAL DISCOUNTING', 'CURRENT ACCOUNT', 'DUES & OVERDRAWN') then '4'
else funded.payment_frequency_description
end payment_frequency_description_new,
NVL(unfunded.account_branch_number,funded.account_branch_number) account_branch_number_new,
NVL(unfunded.account_suffix,funded.account_suffix) account_suffix_new,
NVL(unfunded.customer_classification,funded.customer_classification) customer_classification_new,
NVL(unfunded.classification_desc,funded.classification_desc) classification_desc_new ,
NVL(unfunded.limit_category,funded.limit_category) limit_category_new ,
NVL(NVL(unfunded.bank,funded.bank),banks_unfunded.code) bank_new_code ,
unfunded.deal_fact_key AS DEAL_KEY_UNFUNDED,
NVL(unfunded.external_account_number,funded.external_account_number) external_account_number_new ,
NVL(unfunded.facility_ref,funded.facility_ref ) facility_ref_new ,
case when interest_rate_v.amortizationtype = 'BehAmortization' and funded.payment_frequency_description = '0' then 'Bullet'
when interest_rate_v.amortizationtype = 'BehAmortization' and funded.Payment_Frequency_Description <> 0 then 'Linear'
WHEN interest_rate_v.amortizationtype = 'ConstantPrincipalAnnuity' THEN 'Linear'
WHEN interest_rate_v.amortizationtype = 'ConstantPrincipalAndInterestAnnuity' THEN 'Exponential'
else interest_rate_v.amortizationtype end Amortization_Type_NEW
from (select case when UPPER(product_name) = 'DISCOUNTED EXPORT LC' then replace(agreement_number,'C','D') else agreement_number end AGREEMENT_NUM,* from ifrs9.deals_funded_unfunded_report join PREV_EXECUTION_DATE P on 1=1 where type= 'Funded' and partition_load_date=P.LWD AND load_country= ${P_LOAD_COUNTRY} ) funded left join
(SELECT * FROM ifrs9.banks_v join PREV_EXECUTION_DATE P on 1=1 WHERE partition_load_date =P.LWD and load_country=${P_LOAD_COUNTRY} ) banks on
REPLACE(funded.deal_fact_key,' ','')=REPLACE(banks.fact_deal_id,' ','')
left join (select * from deals_stg where type= 'Unfunded' and load_country= ${P_LOAD_COUNTRY} )
unfunded on funded.AGREEMENT_NUM=unfunded.agreement_number
left join
(SELECT * FROM ifrs9.banks_v join PREV_EXECUTION_DATE P on 1=1 WHERE partition_load_date =P.LWD and load_country=${P_LOAD_COUNTRY} ) banks_unfunded on
REPLACE(unfunded.deal_fact_key,' ','')=REPLACE(banks_unfunded.fact_deal_id,' ','')
left join (select distinct customer_equation_id, A.customer_delinquency_status customer_delinquency_status_new from deals_stg A where type= 'Funded' and partition_load_date=${PRCESS_DTE} AND load_country= ${P_LOAD_COUNTRY} )
funded_cust_delinquency on NVL(unfunded.customer_equation_id,funded.customer_equation_id)= funded_cust_delinquency.customer_equation_id
LEFT JOIN (SELECT * FROM IFRS9.interest_rate_v WHERE YR_MNTH IN (SELECT MAX(YR_MNTH) FROM IFRS9.interest_rate_v)) interest_rate_v on interest_rate_v.fact_deal_id=funded.deal_fact_key
) A
) ALL1 LEFT JOIN (SELECT * FROM ifrs9.mra_data join PREV_EXECUTION_DATE P on 1=1 WHERE REPLACE(PARTITION_LOAD_DATE,'-','')=REPLACE(P.LWD,'-','') AND load_country = ${P_LOAD_COUNTRY} ) mra
on mra.bsc=ALL1.customer_equation_id_new
LEFT JOIN
(select DISTINCT agreement_reference_id,customer_equation_id,full_name_in_arabic,full_name_in_english,customer_segment,economic_sector,economic_sector_description,
default_risk_rating, comprehensive_risk_rating from deals_stg where type= 'Unfunded' and
load_country= ${P_LOAD_COUNTRY} ) UNFUN_CUST ON UNFUN_CUST. customer_equation_id=ALL1.customer_equation_id_NEW AND
ALL1.funded_unfunded='funded' AND UNFUN_CUST.agreement_reference_id=ALL1.DEAL_KEY_UNFUNDED
LEFT JOIN (SELECT DISTINCT * FROM IFRS9.interest_rate_v
WHERE yr_mnth IN (SELECT MAX(YR_MNTH) FROM IFRS9.interest_rate_v) AND LOAD_COUNTRY = ${P_LOAD_COUNTRY} ) interest_rate_v on REPLACE(interest_rate_v.fact_deal_id ,' ','') =REPLACE(ALL1.deal_fact_key ,' ','')
LEFT JOIN
(select DISTINCT * from IFRS9.deals_suspended_interest join PREV_EXECUTION_DATE P on 1=1 WHERE yr_mnth=REPLACE(SUBSTR(CAST(P.LWD AS STRING),1,7),'-','') AND LOAD_COUNTRY=${P_LOAD_COUNTRY} ) si ON SI.DEAL_KEY=ALL1.facility_ref
LEFT JOIN (
select DISTINCT
fact_deal_id ,interestratetype, MAX( CASE WHEN NULLIF(TRIM(REPLACE(FIXEDRATE,'%','') ),'')='' THEN '0' ELSE REPLACE(FIXEDRATE,'%','') END ) OVER
(PARTITION BY fact_deal_id ) MAX_FIXEDRATE,
MAX(CASE WHEN NULLIF(TRIM(clientratespread ),'')='' THEN '0' ELSE REPLACE(clientratespread,'%','') END ) OVER (PARTITION BY fact_deal_id ) MAX_clientratespread
from IFRS9.interest_rate_v
where yr_mnth IN (SELECT MAX(YR_MNTH) FROM IFRS9.interest_rate_v)
AND LOAD_COUNTRY = ${P_LOAD_COUNTRY} ) INTEREST_RATE_SPREAD_fund
on REPLACE(INTEREST_RATE_SPREAD_fund.fact_deal_id ,' ','') =REPLACE(ALL1.deal_fact_key ,' ','')
LEFT JOIN (select * from raw_data_vault.CUSTOMERS WHERE gfpf_load_country= ${P_LOAD_COUNTRY} ) cust_corp on cust_corp.GFPF_CSTMR_ID=ALL1.bank_new
left join (SELECT CAST(limit_control_defaults.hppf_load_country AS VARCHAR(10)) hppf_load_country,
CAST(limit_control_defaults.hppf_cstmr_mnmnc AS VARCHAR(20)) HPPF_CSTMR_MNMNC,
CASE WHEN limit_control_defaults.hppf_dflt_lmt_expry_dte = 0 THEN NULL WHEN limit_control_defaults.hppf_dflt_lmt_expry_dte = 9999999 THEN NULL ELSE CAST(from_unixtime(unix_timestamp(SUBSTR(CAST(limit_control_defaults.hppf_dflt_lmt_expry_dte + 19000000 AS STRING),
1,
10),
'yyyyMMdd'),
'yyyy-MM-dd') AS TIMESTAMP) END HPPF_DFLT_LMT_EXPRY_DTE,
CAST(limit_control_defaults.hppf_year_mn AS VARCHAR(10)) hppf_year_mn,
CAST(limit_control_defaults.hppf_cstmr_lcton AS VARCHAR(100)) HPPF_CSTMR_LCTON FROM RDV_EOM.LIMIT_CONTROL_DEFAULTS join PREV_EXECUTION_DATE P on 1=1 WHERE
hppf_year_mn=REPLACE(SUBSTR(CAST(P.LWD AS STRING),1,7),'-','') AND hppf_load_country= ${P_LOAD_COUNTRY} AND
TRIM(limit_control_defaults.hppf_cstmr_mnmnc) != '' AND TRIM(limit_control_defaults.hppf_cstmr_mnmnc) IS NOT NULL AND
limit_control_defaults.hppf_dflt_lmt_expry_dte != 0 ) od_st_dt on
od_st_dt.hppf_cstmr_mnmnc=(CASE WHEN ALL1.LOAD_COUNTRY='JO' THEN ALL1.customer_equation_id_new ELSE ALL1.customer_id_new END )
LEFT JOIN (SELECT * FROM IFRS9.UNFUNDED_CASH_MARGIN join PREV_EXECUTION_DATE P on 1=1 WHERE LOAD_COUNTRY= ${P_LOAD_COUNTRY} AND PARTITION_LOAD_DATE= P.LWD ) CASH_MAR ON CASH_MAR.deal_key=
ALL1.facility_ref
left join ifrs9. rescheduling_restructuring_v res_restr on
res_restr.scpf_bsc =CASE WHEN ALL1.LOAD_COUNTRY='JO' THEN (NVL(UNFUN_CUST.customer_equation_id,ALL1.customer_equation_id_new)) ELSE ALL1.customer_equation_id_new END and
res_restr.load_country=ALL1.load_country
LEFT JOIN (SELECT DEAL_KEY,SQL_SEQ,deal_type DEAL_TYPE_ORIG FROM ifrs9.deals_v join PREV_EXECUTION_DATE P on 1=1 WHERE LOAD_COUNTRY=${P_LOAD_COUNTRY} AND partition_load_date = P.LWD ) FD
ON FD.DEAL_KEY=ALL1.facility_ref AND FD.SQL_SEQ=ALL1.DEAL_SEQ
left join IFRS9.deal_types_desc_v DEAL_DESC ON
REPLACE(UPPER(TRIM(DEAL_DESC.PRODUCT_NAME)),' ','')=REPLACE(UPPER(TRIM(ALL1.PRODUCT_NAME)),' ','') AND
TRIM(DEAL_DESC.field_VALUE )=TRIM(FD.DEAL_TYPE_ORIG)
left join (SELECT * FROM ifrs9.interest_rate WHERE yr_mnth IN (SELECT MAX(YR_MNTH) FROM IFRS9.interest_rate)
AND load_country= ${P_LOAD_COUNTRY} ) int_rate on int_rate.fact_deal_id=ALL1.deal_fact_key and int_rate.load_country = ALL1.load_country
left join (select CASE WHEN LPAD(TRIM(AB_RTNG.gfpf_ab_rating),3,'0') IN ('001','002','003','004','005','006','007','008','009','010') then cast(AB_RTNG.gfpf_ab_rating as integer) ELSE NULL END AS gfpf_ab_rating_int, * from ifrs9.ab_ratings_all_countries_temp AB_RTNG join PREV_EXECUTION_DATE P on 1=1 where
yr_mnth = REPLACE(SUBSTR(CAST(P.LWD AS STRING),1,7),'-','')
AND scpf_load_country= ${P_LOAD_COUNTRY} )
AB_RTNG on TRIM(AB_RTNG.deal_key)=TRIM(ALL1.facility_ref) AND AB_RTNG.DEAL_SEQ=ALL1.DEAL_SEQ
---LEFT JOIN
---(
---select facility_rating_at_origination,facility_reference from IFRS9.ifrs9_monthly_run_data WHERE
---LOAD_COUNTRY=${P_LOAD_COUNTRY} and load_date in (select max(a.load_date) from
--- IFRS9.ifrs9_monthly_run_data a WHERE
---a.LOAD_COUNTRY=${P_LOAD_COUNTRY} and substr( cast(a.load_date as string),1,10)< ${PRCESS_DTE} ) ) LAST_RUN ON
---REPLACE(LAST_RUN.facility_reference,' ','')=REPLACE(ALL1.deal_fact_key ,' ','')
left join
(select * from ifrs9.collaterals_final where yr_mnth=REPLACE(SUBSTR(CAST(${PRCESS_DTE} AS STRING),1,7),'-','') and load_country = ${P_LOAD_COUNTRY} ) col_f on
col_f.fact_deal_id= ALL1.deal_fact_key
left join (SELECT * FROM IFRS9.interestreceivedinadvanced_v join PREV_EXECUTION_DATE P on 1=1 WHERE LOAD_COUNTRY=${P_LOAD_COUNTRY} and partition_c8pf_load_date =P.LWD ) int_adv_v on
int_adv_v.fact_deal_id= ALL1.deal_fact_key
left join (SELECT * FROM ifrs9.previous_stage_details join PREV_EXECUTION_DATE P on 1=1 WHERE partition_load_date=P.LWD AND load_country= ${P_LOAD_COUNTRY} ) prev_stg_dtls on
REPLACE(prev_stg_dtls.facility_id,' ','') = REPLACE(ALL1.agreement_reference_id,' ','')
left join IFRS9.deal_types_desc_v deal_types on deal_types. field_value= ALL1.deal_type
left join ifrs9.orbis orbis on orbis.bankcode= ALL1.bank_new
left join
(select CASE WHEN LPAD(TRIM(CASE WHEN TRIM(external_rating)='' OR TRIM(external_rating) IS NULL THEN 0 ELSE TRIM(external_rating) END ),3,'0') IN ('001','002','003','004','005','006','007','008','009','010') then cast(external_rating as integer) ELSE NULL END AS external_rating_int ,
* from EXT_RATING where load_country=${P_LOAD_COUNTRY} and LPAD(TRIM(CASE WHEN TRIM(external_rating)='' OR TRIM(external_rating) IS NULL OR TRIM(EXTERNAL_RATINGS_ORIGIN)='NA' THEN 0 ELSE TRIM(external_rating) END ),3,'0') IN ('001','002','003','004','005','006','007','008','009','010','000') ) external_rating on
external_rating.deal_key=ALL1.facility_ref
left join
(select
CASE WHEN LPAD(TRIM(EXTERNAL_RATINGS_ORIGIN),3,'0') IN ('001','002','003','004','005','006','007','008','009','010') then cast(EXTERNAL_RATINGS_ORIGIN as integer) ELSE NULL END AS EXTERNAL_RATINGS_ORIGIN_int,
* from EXT_RATING where load_country=${P_LOAD_COUNTRY} AND LPAD(TRIM(CASE WHEN TRIM(EXTERNAL_RATINGS_ORIGIN)='' OR TRIM(EXTERNAL_RATINGS_ORIGIN) IS NULL OR TRIM(EXTERNAL_RATINGS_ORIGIN)='NA' THEN 0 ELSE TRIM(EXTERNAL_RATINGS_ORIGIN) END ),3,'0') IN ('001','002','003','004','005','006','007','008','009','010','000') ) external_rating_origin on
external_rating_origin.deal_key=ALL1.facility_ref
left join
(select
CASE WHEN LPAD(TRIM(EXTERNAL_RATINGS_ORIGIN),3,'0') IN ('001','002','003','004','005','006','007','008','009','010') then cast(EXTERNAL_RATINGS_ORIGIN as integer) ELSE NULL END AS EXTERNAL_RATINGS_ORIGIN_int,
* from EXT_RATING where load_country=${P_LOAD_COUNTRY} AND LPAD(TRIM(CASE WHEN TRIM(EXTERNAL_RATINGS_ORIGIN)='' OR TRIM(EXTERNAL_RATINGS_ORIGIN) IS NULL OR TRIM(EXTERNAL_RATINGS_ORIGIN)='NA' THEN 0 ELSE TRIM(EXTERNAL_RATINGS_ORIGIN) END ),3,'0') IN ('001','002','003','004','005','006','007','008','009','010') ) ero on
ero.deal_key=ALL1.facility_ref
left join
(select CASE WHEN LPAD(TRIM(CASE WHEN TRIM(external_rating)='' OR TRIM(external_rating) IS NULL THEN 0 ELSE TRIM(external_rating) END ),3,'0') IN ('001','002','003','004','005','006','007','008','009','010') then cast(external_rating as integer) ELSE NULL END AS external_rating_int ,
* from EXT_RATING where load_country=${P_LOAD_COUNTRY} and LPAD(TRIM(CASE WHEN TRIM(external_rating)='' OR TRIM(external_rating) IS NULL OR TRIM(EXTERNAL_RATINGS_ORIGIN)='NA' THEN 0 ELSE TRIM(external_rating) END ),3,'0') IN ('001','002','003','004','005','006','007','008','009','010') ) er on
er.deal_key=ALL1.facility_ref
left join (select * from ifrs9.ifrs9_customer_info where yyyymm = replace(SUBSTR( ${PRCESS_DTE},1,7),'-','') AND load_country= ${P_LOAD_COUNTRY} ) cust_info
on cust_info.customer_id= (CASE WHEN ALL1.LOAD_COUNTRY='JO' THEN ALL1.customer_equation_id_new ELSE ALL1.customer_id_new END )
left join (SELECT SUM(case when cc.COLLATERAL_TYPE not in ('CCI','CCO') and
not(cc.COLLATERAL_TYPE IN ('GTC','GGU') AND NVL(cc.FAC_CCY,'#') = NVL(cc.RSK_CCY,'#') and cc.LOAD_COUNTRY <> 'JO' )
then CAST( CC.FACILITY_COLLATERAL_COVER AS DOUBLE) else 0 end ) other_collateral,
SUM(case when cc.COLLATERAL_TYPE in ('CCI','CCO') or
(cc.COLLATERAL_TYPE IN ('GTC','GGU') AND NVL(cc.FAC_CCY,'#') = NVL(cc.RSK_CCY,'#') and cc.LOAD_COUNTRY <> 'JO' )
then CAST( CC.FACILITY_COLLATERAL_COVER AS DOUBLE) else 0 end ) cash_collateral ,deal_key
from ifrs9.collaterals_final CC where
yr_mnth=REPLACE(SUBSTR( ${PRCESS_DTE},1,7),'-','') AND LOAD_COUNTRY= ${P_LOAD_COUNTRY}
GROUP BY deal_key ) CC ON CC.deal_key= ALL1.facility_ref_new
left join (select distinct abre, moody ,fitch,sp ,bank from ifrs9.current_external_rating WHERE year_mn= replace(SUBSTR( ${PRCESS_DTE},1,7),'-','') ) curr_rating
on TRIM(ALL1.bank_new_code)= TRIM(curr_rating.bank )
) B
) P
) A
left join (select basic,previous_stage_2_, previous_stage_2_date previous_stage_2_date_,
previous_stage_2_reason previous_stage_2_reason_ from ifrs9.previous_stage join PREV_EXECUTION_DATE P on 1=1 where load_country= ${P_LOAD_COUNTRY} AND partition_load_date=P.LWD ) prev_stage
on prev_stage.basic=a.customer_id
where output of the table is
source_system facility_id customer_id branch basic suffix customer_group_name customer_name_arabic customer_name_english customer_segment industry_code industry_description country_of_risk ab_default_rating mra_rating mra_pd restructured_flag rescheduled_flag customer_delinquency_status classification classification_desc facility_reference product_segment facility_product_type facility_start_date facility_maturity_date facility_currency outstanding_balance_usd outstanding_balance_original_curreny outstanding_balance_base_currency limit_in_base_currency principal_repayment_frequency amortization_type interest_rate_type interest_rate_spread base_rate interest_rate_for_discounted_bills applied_interest_rate interest_frequency facility_rating_at_origination facility_rating_at_reporting_date facility_dpd limit_category collateral_cover cash_margin total_collateral_cover__usd_ coa_item stage staging_reason lgd moody_s_rating s_p_s_rating fitch_s_rating previous_effective_stage_reason previous_effective_stage_date previous_effective_stage effective_stage effective_staging_date effective_staging_reason funded_unfunded_flag ttc_pd ecl_1y ecl_maturity lossallowance cash_collaterals other_collaterals ead bank load_date load_country
CL 0855R220134L24004TLF-1600022-9070142038300 142038 9070 142038 300 LPSDWI XXXX XXXX XXX XXXXXXXX XXXXXX XXXXX XXXXXXXX XXX XXXXXXX XXXXXXX XXX XXXXXXXX CM 267 Cutting, shaping & finishing of stone PS 5 4+ 0.0051 N N 2 NULL NULL 0855R220134L24004TLF Corporate RECEIVABLE FINANCE 2024-05-28 00:00:00 2025-03-29 00:00:00 USD 173399.10000000 173399.10000000 122940.00000000 NULL NULL NULL Fixed 0% 0% 0% 0% NULL 5 5 2 LS023 NULL 0.00000000 18663.49649900 14231 1 0.40156507 NULL NULL NULL NULL 1 1 NULL NULL funded NULL NULL NULL NULL 18663.49649900 0.00000000 NULL NULL 2024-09-30 PS
CL 0857R230224L24023TLF-1600022-9090529648300 529648 9090 529648 300 LPMWSM XXXX XXXXXXX XXX XXX X XXXXXXXXX XXXXXX XXXX XX CL 157 Manufacture of prepared animal feeds PS 5 NULL NULL N N 3 NULL NULL 0857R230224L24023TLF Corporate RECEIVABLE FINANCE 2024-08-04 00:00:00 2025-04-05 00:00:00 NIS 5257.20000000 19469.00000000 3727.30000000 NULL NULL NULL Fixed 0% 0% 0% 0% NULL 5 5 NULL LS023 NULL 0.00000000 113.96270500 14231 1 0.44024515 NULL NULL NULL NULL 1 1 NULL NULL funded NULL NULL NULL NULL 113.96270500 0.00000000 NULL NULL 2024-09-30 PS
CL 0853R180199L23019TLF-1600022-9050721960301 721960 9050 721960 301 NULL XXXX XXXXXXX XXXXXXX XXXXXX XXXXXXX XXX XXXXXXX XXXXXXX XXXXXXX CL 517 PS 4 5+ 0.02184 N N NULL NULL NULL 0853R180199L23019TLF Corporate RECEIVABLE FINANCE 2023-05-24 00:00:00 2025-07-08 00:00:00 USD 4521.00000000 4521.00000000 3205.40000000 NULL NULL NULL Fixed 0% 0% 0% 0% NULL 4 4 NULL LS023 NULL 0.00000000 2135.75300900 14231 1 0.23741675 NULL NULL NULL NULL 1 1 NULL NULL funded NULL NULL NULL NULL 128.60903500 2007.14397400 NULL NULL 2024-09-30 PS
CL 0864R200032L24002TLF-1600022-9490341409301 341409 9490 341409 301 NULL XXXX XXXXXXX XXXXX XXXX XXXXXX XXXXXXXX XX XXXXXXXX XXX XXXXX XXX XXXXXXXX CL 012 Farming of animals PS 4 NULL NULL N N NULL NULL NULL 0864R200032L24002TLF Corporate RECEIVABLE FINANCE 2024-04-24 00:00:00 2025-01-25 00:00:00 NIS 90009.60000000 333334.00000000 63816.80000000 NULL NULL NULL Fixed 0% 0% 0% 0% NULL 4 4 NULL LS023 NULL 0.00000000 14646.13726000 14231 1 0.37677712 NULL NULL NULL NULL 1 1 NULL NULL funded NULL NULL NULL NULL 6188.23646000 8457.90080000 NULL NULL 2024-09-30 PS
CL 0870R170117L24004TLF-1600022-9420289413304 289413 9420 289413 304 NULL XXXXXXXXXX XXXXXXXX XXXXXXXXXXXXXXX XXXXXX XXXXXXX XXX XXXXXX CM 243 Manufacture of paints, varnishes & similar coatings, printing ink & mastics PS 5 NULL NULL N N NULL NULL NULL 0870R170117L24004TLF Corporate RECEIVABLE FINANCE 2024-09-10 00:00:00 2025-08-11 00:00:00 EUR 53736.30000000 48000.00000000 38099.00000000 NULL NULL NULL Fixed 0% 0% 0% 0% NULL 5 5 NULL LS023 NULL 0.00000000 9671.79612400 14231 1 0.36900618 NULL NULL NULL NULL NULL NULL 1 NULL NULL funded NULL NULL NULL NULL 5803.07767400 3868.71845000 NULL NULL 2024-09-30 PS
note: I want all the columns involved and none missed out. This is for data lineage so give accurate names.