CREATE OR REPLACE PROCEDURE Qsfw_Adv_Search_Data_Dump_Prc(
pSuccessFailure  OUT VARCHAR2,
pErrorCode       OUT VARCHAR2,
pErrorMsg   OUT VARCHAR2
)
AS

v_seperator CONSTANT VARCHAR2(2) := ':';
v_details CLOB;
i NUMBER:=1;


-- Base Cursor for getting all the case numbers which are not in adv_search table.

CURSOR cur_mantas_case IS
SELECT case_nbr FROM AML_CASE_MASTER 
WHERE case_nbr NOT IN (SELECT CONTROL_NBR FROM QSFW_ADV_SEARCH_DATA) 
and POST_PRC_FLG = 'I' 
AND CASE_SOURCE = 'MANTAS';

-- Cursor for getting the MI_TRXN details

CURSOR cur_mantas_mi_trxn(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS SELECT  TRXN_INTRL_REF_ID||v_seperator||
INSTR_NM||v_seperator||
AUG_INSTR_NB||v_seperator||
REM_NM||v_seperator||
REM_AUG_NM||v_seperator||
REM_ACCT_ID||v_seperator||
BENEF_NM||v_seperator||
BENEF_AUG_NM||v_seperator||
BENEF_ACCT_ID||v_seperator||
DEP_INSTN_NM||v_seperator||
DEP_INSTN_AUG_NM||v_seperator||
DEP_INSTN_ACCT_ID||v_seperator||
CLR_INSTN_NM||v_seperator||
CLR_INSTN_AUG_NM||v_seperator||
CLR_INSTN_ACCT_ID||v_seperator||
ISSUE_INSTN_ID||v_seperator||
ISSUE_INSTN_BRNCH_ID||v_seperator||
ISSUE_INSTN_NM||v_seperator||
ISSUE_INSTN_AUG_NM||v_seperator||
ISSUE_INSTN_ACCT_ID||v_seperator||
SCND_BENEF_NM||v_seperator||
SCND_BENEF_AUG_NM||v_seperator||
SCND_BENEF_ACCT_ID||v_seperator||
MRKNG_TX||v_seperator||
CMMNT_TX||v_seperator||
CSTM_1_TX||v_seperator||
CSTM_2_TX||v_seperator||
CSTM_3_TX||v_seperator||
CSTM_4_TX||v_seperator||
CSTM_5_TX||v_seperator||
TRXN_GRP_TX_ID||v_seperator||
CNDTR_NM||v_seperator||
CNDTR_AUG_NM||v_seperator||
CNDTR_ACCT_ID||v_seperator||
REM_NTITY_RISK_FACTR_TX||v_seperator||
REM_NTITY_LIST_MATCH_TX||v_seperator||
BENEF_NTITY_RISK_FACTR_TX||v_seperator||
BENEF_NTITY_LIST_MATCH_TX||v_seperator||
DEP_INSTN_NTITY_RISK_FACTR_TX||v_seperator||
DEP_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
CLR_INSTN_NTITY_RISK_FACTR_TX||v_seperator||
CLR_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
ISSUE_INST_NTITY_RISK_FACTR_TX||v_seperator||
ISSUE_INST_NTITY_LIST_MATCH_TX||v_seperator||
SCND_BENEF_NTITY_LIST_MATCH_TX||v_seperator||
CNDTR_NTITY_RISK_FACTR_TX||v_seperator||
CNDTR_NTITY_LIST_MATCH_TX details
FROM MANTAS_MI_TRXN WHERE case_nbr=v_case_nbr;

-- Cursor for getting the WIRE_TRXN details

CURSOR cur_mantas_wire_trxn(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS SELECT
TRXN_INTRL_REF_ID||v_seperator||
ORIG_NM||v_seperator||
ORIG_AUG_NM||v_seperator||
ORIG_ACCT_ID||v_seperator||
BENEF_NM||v_seperator||
BENEF_AUG_NM||v_seperator||
BENEF_ACCT_ID||v_seperator||
ORIG_TO_BENEF_INSTR_TX||v_seperator||
SEND_INSTN_ID||v_seperator||
SEND_INSTN_NM||v_seperator||
SEND_INSTN_AUG_NM||v_seperator||
SEND_INSTN_ACCT_ID||v_seperator||
RCV_INSTN_ID||v_seperator||
RCV_INSTN_NM||v_seperator||
RCV_INSTN_AUG_NM||v_seperator||
RCV_INSTN_ACCT_ID||v_seperator||
BANK_TO_BANK_INSTR_TX||v_seperator||
SCND_BENEF_NM||v_seperator||
SCND_BENEF_AUG_NM||v_seperator||
SCND_BENEF_ACCT_ID||v_seperator||
CSTM_1_TX||v_seperator||
CSTM_2_TX||v_seperator||
CSTM_3_TX||v_seperator||
CSTM_4_TX||v_seperator||
CSTM_5_TX||v_seperator||
ORIG_NTITY_RISK_FACTR_TX||v_seperator||
ORIG_NTITY_LIST_MATCH_TX||v_seperator||
BENEF_NTITY_RISK_FACTR_TX||v_seperator||
BENEF_NTITY_LIST_MATCH_TX||v_seperator||
BENEF_NTITY_LIST_MATCH_TX||v_seperator||
SEND_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
RCV_INSTN_NTITY_RISK_FACTR_TX||v_seperator||
RCV_INSTN_NTITY_LIST_MATCH_TX||v_seperator||
SCND_ORIG_NTITY_RISK_FACTR_TX||v_seperator||
SCND_ORIG_NTITY_LIST_MATCH_TX||v_seperator||
SCND_BENEF_NTITY_RISK_FACTR_TX||v_seperator||
SCND_BENEF_NTITY_LIST_MATCH_TX details
FROM MANTAS_WIRE_TRXN
WHERE case_nbr=v_case_nbr;


-- Cursor for getting the Account details


CURSOR cur_mantas_acct_smry(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
A.ARC_BATCH_NM||v_seperator||
A.ACCT_INTRL_ID||v_seperator||
A.ACCT_TYPE1_CD||v_seperator||
A.ACCT_TYPE2_CD||v_seperator||
A.ACCT_TAX_ID||v_seperator||
A.TITL_LINE1_TX||v_seperator||
A.TITL_LINE2_TX||v_seperator||
A.TITL_LINE3_TX||v_seperator||
A.HH_ACCT_GRP_ID||v_seperator||
A.ALT_ACCT_ID||v_seperator||
A.ACCT_DSPLY_NM||v_seperator||
A.PRMRY_PRDCT_TYPE_CD||v_seperator||
A.LEGAL_NTITY_ID||v_seperator||
A.PRMRY_CUST_INTRL_ID||v_seperator||
A.ACCT_MATCH_TX||v_seperator||
A.TAX_PAYR_CUST_INTRL_ID||v_seperator||
A.CSTM_1_TX||v_seperator||
A.CSTM_2_TX||v_seperator||
A.CSTM_3_TX||v_seperator||
A.CSTM_4_TX||v_seperator||
A.CSTM_5_TX||v_seperator||
A.DAY_TRD_APRVL_UPDT_U_INTRL_ID||v_seperator||
A.ULTMT_INSTL_CUST_INTRL_ID||v_seperator||
A.PRCSNG_BATCH_NM||v_seperator||
A.DMCLD_BRCH_CD||v_seperator||
A.BUS_DMN_LIST_TX||v_seperator||
A.FIRM_ACCT_ORG_INTRL_ID||v_seperator||
A.ACCT_EFCTV_RISK_FACTR_TX||v_seperator||
A.SRVC_TEAM_INTRL_ID details
FROM MANTAS_ACCT a
WHERE a.case_nbr=v_case_nbr;

CURSOR Cur_mantas_acct_addr(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
B.ADDR_RGN_NM||v_seperator||
B.ADDR_STRT_LINE6_TX||v_seperator||
B.ADDR_STRT_LINE5_TX||v_seperator||
B.ADDR_STRT_LINE4_TX||v_seperator||
B.ADDR_STRT_LINE3_TX||v_seperator||
B.ADDR_STRT_LINE2_TX||v_seperator||
B.ADDR_STRT_LINE1_TX||v_seperator||
B.ADDR_POSTL_CD||v_seperator||
B.ADDR_STATE_CD||v_seperator||
B.ADDR_CITY_NM||v_seperator||
B.ADDR_USAGE_CD||v_seperator||
B.PRCSNG_BATCH_NM details
FROM
MANTAS_ACCT_ADDR b
WHERE b.case_nbr=v_case_nbr;

CURSOR Cur_mantas_acct_bal_posn_smry(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
C.ARC_BATCH_NM||v_seperator||
C.ACCT_INTRL_ID||v_seperator||
C.HI_CNC_SCRTY_INTRL_ID||v_seperator||
C.HI_CNC_SHRT_SCRTY_INTRL_ID||v_seperator||
C.HI_CNC_SCTR_NM||v_seperator||
C.HI_CNC_NKD_OPTN_UND_SCRTY_ID||v_seperator||
C.HI_CNC_LNG_OPTN_UND_SCRTY_ID||v_seperator||
C.HH_ACCT_GRP_ID||v_seperator||
C.CSTM_1_TX||v_seperator||
C.CSTM_2_TX||v_seperator||
C.CSTM_3_TX||v_seperator||
C.CSTM_4_TX||v_seperator||
C.CSTM_5_TX||v_seperator details
FROM
MANTAS_ACCT_BAL_POSN_SMRY c
WHERE c.case_nbr=v_case_nbr;

CURSOR Cur_mantas_acct_grp(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
D.ACCT_GRP_NM||v_seperator||
D.ROUTG_ORG_INTRL_ID||v_seperator||
D.PRCSNG_BATCH_NM||v_seperator||
D.BUS_DMN_LIST_TX||v_seperator||
D.PRMRY_ACCT_INTRL_ID||v_seperator details
FROM
MANTAS_ACCT_GRP d
WHERE d.case_nbr=v_case_nbr;

CURSOR Cur_mantas_mangd_acct(v_case_nbr AML_CASE_MASTER.case_nbr%TYPE)
IS
SELECT
E.ARC_BATCH_NM||v_seperator||
E.ACCT_INTRL_ID||v_seperator||
E.NVSMT_MGR_INTRL_ID||v_seperator||
E.NVSMT_MGR_NM||v_seperator||
E.MSTR_ACCT_INTRL_ID||v_seperator||
E.MSTR_ACCT_NM||v_seperator||
E.PRCSNG_BATCH_NM details
FROM
MANTAS_MANGD_ACCT e
WHERE e.case_nbr=v_case_nbr;


BEGIN

 FOR cur_case IN cur_mantas_case
  LOOP
    FOR cur_mi_trxn IN cur_mantas_mi_trxn(cur_case.case_nbr)
     LOOP
     v_details := v_details ||v_seperator || cur_mi_trxn.details;
     END LOOP;

     FOR cur_cash_trxn IN cur_mantas_cash_trxn(cur_case.case_nbr)
     LOOP
     v_details := v_details ||v_seperator || cur_cash_trxn.details;
     END LOOP;

    IF LENGTH(v_details)>1
     THEN
        i:=i +1;
       INSERT INTO QSFW_ADV_SEARCH_DATA VALUES (cur_case.case_nbr,'TRXN_DATA',v_details);
      v_details :='';
     END IF;

     FOR cur_acct IN cur_mantas_acct_smry(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||cur_acct.details;
     END LOOP;

      FOR cur_acct_addr IN Cur_mantas_acct_addr(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||cur_acct_addr.details;
     END LOOP;

      FOR Cur_acct_bal_posn_smry IN Cur_mantas_acct_bal_posn_smry(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||Cur_acct_bal_posn_smry.details;
     END LOOP;

      FOR Cur_acct_grp IN Cur_mantas_acct_grp(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||Cur_acct_grp.details;
     END LOOP;

      FOR Cur_mangd_acct IN Cur_mantas_mangd_acct(cur_case.case_nbr)
     LOOP
       v_details := v_details ||v_seperator ||Cur_mangd_acct.details;
     END LOOP;

    
     IF LENGTH(v_details)>1
      THEN
         i:=i +1;
         INSERT INTO QSFW_ADV_SEARCH_DATA VALUES (cur_case.case_nbr,'FOCUS_DATA',v_details);
         v_details :='';
     END IF;
  
  IF i>10 THEN
    COMMIT;
    i:=1;
  END IF;

  END LOOP;

  COMMIT;

END;