SELECT tab.card_no,tab.stmt_end_date,tab.stmt_begin_date,tab.card_type
,tab.bill_type,tab.transfer_card_no,tab.mask_pdf_acc_no
,tab.pk_tb_company_details,tab.sys_default_date,tab.paper_stmt_opt_out,tab.country
,tab.country_code,tab.is_ipc_country
,tab.currency_format,tab.despatch_type_flag,tab.email,tab.sort_ord_one
,tab.sort_ord_two,tab.single_payment,tab.coll_1,tab.coll_2,tab.cycle_day
FROM (select /*+ PARALLEL */ card.card_no,
TO_CHAR(stmt_end_date,NVL((SELECT syspar1.param_value
FROM tb_system_parameters syspar1
WHERE syspar1.param_name = 'SYS_DEFAULT_DATE'
AND syspar1.company_id = companydetails.pk_tb_company_details
AND syspar1.rec_status = 'AC'), 'DD/MM/YYYY')) stmt_end_date,
TO_CHAR(stmt_begin_date,NVL((SELECT syspar1.param_value
FROM tb_system_parameters syspar1
WHERE syspar1.param_name = 'SYS_DEFAULT_DATE'
AND syspar1.company_id = companydetails.pk_tb_company_details
AND syspar1.rec_status = 'AC'), 'DD/MM/YYYY')) stmt_begin_date,
card.card_type,
card.bill_type,
(SELECT SYS_CONNECT_BY_PATH(mis.card_no,',')
FROM tb_AP_card_mis_det mis, tb_AP_card_demographic_det det
WHERE mis.card_no = det.card_no
AND det.block_cd in ('L', 'F')
AND connect_by_isleaf = 1
START WITH mis.transfer_card_no = card.card_no
CONNECT BY NOCYCLE PRIOR mis.card_no = mis.transfer_card_no) transfer_card_no,
companydetails.mask_pdf_acc_no,
companydetails.pk_tb_company_details,
NVL((SELECT syspar1.param_value
FROM tb_system_parameters syspar1
WHERE syspar1.param_name = 'SYS_DEFAULT_DATE'
AND syspar1.company_id = pk_tb_company_details
AND syspar1.rec_status = 'AC'), 'DD/MM/YYYY') sys_default_date,
'0' paper_stmt_opt_out,
card.country,
country.country_code,
country.is_ipc_country,
'' currency_format,
'' despatch_type_flag,
DECODE(country.COUNTRY_CODE,'BR',
(NVL(TO_CHAR(card.birth_date,'ddmmyy'),'000000')||
LPAD(NVL(SUBSTR(card.HOME_POSTAL_CDE,1,5),'00000'),5,'0')||
SUBSTR(card.card_no,-4,4)||
'~CARD_EMBOSSED_NAME_DOB~'||
card.email||'%~@~%'||
card.cardhldr_first_name||' '||
card.cardhldr_last_name),
(CASE LENGTH(card.cardhldr_first_name)
WHEN 0 THEN ''
WHEN 1 THEN card.cardhldr_first_name
WHEN 2 THEN card.cardhldr_first_name
WHEN 3 THEN card.cardhldr_first_name
ELSE SUBSTR(card.cardhldr_first_name,1,4)
END) ||
DECODE(card.birth_date,null,'',TO_CHAR(card.birth_date,'MMYYYY'))||
'~CARD_EMBOSSED_NAME_DOB~'||
card.email ||'%~@~%'||
card.cardhldr_first_name||' '||
card.cardhldr_last_name) email,
'' sort_ord_one,
'' sort_ord_two,
DECODE('','Y','S','') single_payment,
'' coll_1,
'' coll_2,
card.cycle_day,
ad.pk_tb_alert_details,
cardHier.Node_Id
FROM tb_AP_card_demographic_det card,
tb_AP_card_statement statements,
tb_company_details companydetails,
tb_AP_Card_Hier cardHier,
tb_country country,
tb_alert_master am,
tb_alert_details ad
WHERE card.card_no = statements.card_no
AND card.rec_status = 'AC'
AND statements.rec_status = 'AC'
AND cardHier.Grp_Acc_No = companydetails.site_org_id
AND cardHier.Card_No = card.card_no
AND country.pk_tb_country(+) = companydetails.fk_tb_country
AND country.rec_status(+) = 'AC'
and '480414,464558,540531,552790,547019,461155' like '%' || substr(card.card_no,0,6) || '%'
AND companydetails.rec_status = 'AC'
AND upper(am.alert_name) = 'EMAIL STATEMENT'
AND ad.fk_tb_alert_master = am.pk_tb_alert_master
AND ad.fk_tb_company_details = companydetails.pk_tb_company_details
AND am.rec_status = 'AC'
AND ad.rec_status = 'AC' and statements.upload_id = 575 and trunc(statements.created_on) >= trunc(to_date('08-SEP-16','DD-MON-YY')) AND card.card_no NOT IN (SELECT DISTINCT look.cardnumber FROM tb_AP_mcl_card_lookup look)) tab
LEFT OUTER JOIN tb_user_cards tuc ON (tuc.card_no = tab.card_no AND tuc.sso_login_id IS NULL AND tuc.rec_status = 'AC')
LEFT OUTER JOIN tb_alert_hierarchy ah ON (ah.fk_tb_alert_details = tab.pk_tb_alert_details AND ah.fk_tb_unit = tab.Node_Id AND ah.email_alert_status = 'N' AND ah.rec_status = 'AC')
WHERE ah.fk_tb_alert_details IS NULL AND ah.fk_tb_unit IS NULL AND tuc.card_no IS NULL
UNION
SELECT /*+ PARALLEL */ card.card_no,
TO_CHAR(statements.stmt_end_date, NVL((SELECT syspar1.param_value
FROM tb_system_parameters syspar1
WHERE syspar1.param_name = 'SYS_DEFAULT_DATE'
AND syspar1.company_id = companydetails.pk_tb_company_details
AND syspar1.rec_status = 'AC'), 'DD/MM/YYYY')),
TO_CHAR(statements.stmt_begin_date, NVL((SELECT syspar1.param_value
FROM tb_system_parameters syspar1
WHERE syspar1.param_name = 'SYS_DEFAULT_DATE'
AND syspar1.company_id = companydetails.pk_tb_company_details
AND syspar1.rec_status = 'AC'), 'DD/MM/YYYY')),
card.card_type,
card.bill_type,
(SELECT SYS_CONNECT_BY_PATH(mis.card_no,',')
FROM tb_AP_card_mis_det mis, tb_AP_card_demographic_det det
WHERE mis.card_no=det.card_no AND det.block_cd in ('L','F') AND CONNECT_BY_ISLEAF = 1
START WITH mis.transfer_card_no =card.card_no
CONNECT BY NOCYCLE PRIOR mis.card_no = mis.transfer_card_no), companydetails.MASK_PDF_ACC_NO,
companydetails.pk_tb_company_details,
NVL((SELECT syspar1.param_value
FROM tb_system_parameters syspar1
WHERE syspar1.param_name = 'SYS_DEFAULT_DATE'
AND syspar1.company_id = companydetails.pk_tb_company_details
AND syspar1.rec_status = 'AC'), 'DD/MM/YYYY'),
usercard.paper_stmt_opt_out,
card.country,
country.country_code,
country.is_ipc_country,
userdetails.currency_format,
statements.despatch_type_flag,
DECODE(country.country_code,'BR',
(NVL(TO_CHAR(card.birth_date,'ddmmyy'),'000000')
||LPAD(NVL(substr(card.home_postal_cde,1,5),'00000'),5,'0')||
SUBSTR(card.card_no,-4,4)||
'~CARD_EMBOSSED_NAME_DOB~'||
card.email ||'%~@~%'||
userdetails.first_name||' '||
userdetails.last_name),
((CASE LENGTH(card.cardhldr_first_name)
WHEN 0 THEN ''
WHEN 1 THEN card.cardhldr_first_name
WHEN 2 THEN card.cardhldr_first_name
WHEN 3 THEN card.cardhldr_first_name
ELSE substr(card.cardhldr_first_name,1,4)
END)||
DECODE(card.BIRTH_DATE,null,'',TO_CHAR(card.BIRTH_DATE,'MMYYYY'))||
'~CARD_EMBOSSED_NAME_DOB~'||
card.email||'%~@~%'||
userdetails.first_name||' '||
userdetails.last_name)) ,'' as sort_ord_one, '' as sort_ord_two ,'' as curr_type , '', '' , card.cycle_day FROM
tb_AP_card_statement statements,
tb_AP_card_demographic_det card,
tb_company_details companydetails,
tb_AP_card_hier cardHier,
tb_country country,
tb_user_details userdetails,
tb_AP_company_org org, tb_login_master tlm,
tb_user_alerts tua,
tb_alert_master tam,
tb_user_cards usercard WHERE card.card_no = statements.card_no AND usercard.card_no = card.card_no(+) AND card.card_no NOT IN (SELECT distinct look.cardnumber FROM tb_AP_mcl_card_lookup look) AND org.grp_acc_no=companydetails.site_org_id AND org.rec_status='AC' and card.rec_status = 'AC' and usercard.rec_status(+)='AC'
and usercard.fk_tb_login_master = userdetails.fk_tb_login_master(+)
and usercard.sso_login_id(+) is null
and country.pk_tb_country(+) = companydetails.fk_tb_country
and country.rec_status(+) = 'AC'
and cardHier.Grp_Acc_No=companydetails.site_org_id
and companydetails.rec_status='AC' AND cardHier.card_no = usercard.card_no
AND statements.card_no = usercard.card_no
AND cardHier.card_no = statements.card_no
AND tlm.pk_tb_login_master = usercard.fk_tb_login_master
AND tua.fk_tb_login_master = usercard.fk_tb_login_master
AND tua.fk_tb_alert_master = tam.pk_tb_alert_master
AND tua.fk_tb_company_details = companydetails.pk_tb_company_details
AND usercard.fk_tb_company_details = tua.fk_tb_company_details
AND tlm.rec_status = 'AC'
AND usercard.rec_status = 'AC'
AND statements.rec_status = 'AC'
AND tua.rec_status = 'AC'
AND tam.rec_status = 'AC'
AND UPPER(tam.alert_name) = 'EMAIL STATEMENT'
AND (SELECT fn_alert_chk_avail('EMAIL STATEMENT',tlm.user_id,companydetails.pk_tb_company_details,'EMAIL') FROM DUAL) > 0
and '480414,464558,540531,552790,547019,461155' like '%' || substr(card.card_no,0,6) || '%'
and statements.upload_id = 575
and trunc(statements.created_on) >= trunc(to_date('08-SEP-16','DD-MON-YY'))