[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CSH.STATEMENT_HEADER_ID
FROM
CE_STATEMENT_HEADERS CSH
WHERE CSH.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND CSH.BANK_ACCOUNT_ID IN (
SELECT
BANK_ACCOUNT_ID
FROM
CE_BANK_ACCTS_GT_V BA
WHERE BA.BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BA.BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE))
AND not exists (
SELECT
1
FROM
CE_STATEMENT_HEADERS SH,
CE_STATEMENT_LINES SL,
CE_STATEMENT_RECONCILS_ALL SR
WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
AND SH.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND SH.BANK_ACCOUNT_ID IN (
SELECT
BANK_ACCOUNT_ID
FROM
CE_BANK_ACCOUNTS BA
WHERE BA.BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BA.BANK_BRANCH_ID) )
AND TRUNC(SH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(SH.STATEMENT_DATE))
AND TRUNC(SH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(SH.STATEMENT_DATE))
AND SR.CURRENT_RECORD_FLAG = 'Y'
AND SR.STATUS_FLAG = 'M'
AND ( SR.ORG_ID is not null
OR SR.LEGAL_ENTITY_ID is not null )
AND not exists (
SELECT
1
FROM
CE_SECURITY_PROFILES_GT LBG
WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
SELECT
CSL.STATEMENT_LINE_ID
FROM
CE_STATEMENT_LINES CSL
WHERE CSL.STATEMENT_HEADER_ID = X_HEADER_ID;
SELECT
CSH.STATEMENT_HEADER_ID
FROM
CE_INTRA_STMT_HEADERS CSH
WHERE CSH.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND CSH.BANK_ACCOUNT_ID IN (
SELECT
BANK_ACCOUNT_ID
FROM
CE_BANK_ACCTS_GT_V BA
WHERE BA.BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BA.BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE));
SELECT
CSL.STATEMENT_LINE_ID
FROM
CE_INTRA_STMT_LINES CSL
WHERE CSL.STATEMENT_HEADER_ID = X_HEADER_ID;
SELECT
BB.BANK_NAME,
BB.BANK_BRANCH_NAME,
BA.BANK_ACCOUNT_NAME,
BA.BANK_ACCOUNT_NUM,
BA.CURRENCY_CODE
INTO
C_BANK_NAME
,C_BANK_BRANCH_NAME
,C_BANK_ACCOUNT_NAME
,C_BANK_ACCOUNT_NUM
,C_CURRENCY_CODE
FROM
CE_BANK_BRANCHES_V BB,
CE_BANK_ACCTS_GT_V BA
WHERE BA.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
AND BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID;
SELECT
BB.BANK_NAME,
BB.BANK_BRANCH_NAME
INTO
C_BANK_NAME
,C_BANK_BRANCH_NAME
FROM
CE_BANK_BRANCHES_V BB
WHERE BB.BRANCH_PARTY_ID = P_BANK_BRANCH;
SELECT
L.MEANING
INTO
C_ALL_TRANSLATION
FROM
GL_SETS_OF_BOOKS GL,
CE_SYSTEM_PARAMETERS CB,
CE_LOOKUPS L
WHERE GL.SET_OF_BOOKS_ID = CB.SET_OF_BOOKS_ID
AND L.LOOKUP_TYPE = 'LITERAL'
AND L.LOOKUP_CODE = 'ALL'
AND ROWNUM = 1;
SELECT
BANK_BRANCH_ID
INTO
L_BANK_BRANCH_ID
FROM
CE_BANK_ACCTS_GT_V
WHERE BANK_ACCOUNT_ID = P_BANK_ACCOUNT;
,'DELETE') = 'DELETE') THEN
IF (P_STATEMENT_TYPE in ('BOTH','PREVIOUS')) THEN
IF (P_OBJECTS in ('BOTH','STATEMENT')) THEN
SELECT
COUNT(*)
INTO
COUNT_HEADERS
FROM
CE_ARCH_HEADERS;
SELECT
COUNT(*)
INTO
COUNT_LINES
FROM
CE_ARCH_LINES;
SELECT
COUNT(*)
INTO
COUNT_RECONS
FROM
CE_ARCH_RECONCILIATIONS;
SELECT
COUNT(*)
INTO
COUNT_ERRORS
FROM
CE_ARCH_RECON_ERRORS;
SELECT
COUNT(*)
INTO
COUNT_INT_HEADERS
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'N';
SELECT
COUNT(*)
INTO
COUNT_INT_LINES
FROM
CE_ARCH_INTERFACE_LINES
WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
SELECT
BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'N' );
SELECT
COUNT(*)
INTO
COUNT_INTRA_HEADERS
FROM
CE_ARCH_INTRA_HEADERS;
SELECT
COUNT(*)
INTO
COUNT_INTRA_LINES
FROM
CE_ARCH_INTRA_LINES;
SELECT
COUNT(*)
INTO
COUNT_INTRA_INT_HEADERS
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'Y';
SELECT
COUNT(*)
INTO
COUNT_INTRA_INT_LINES
FROM
CE_ARCH_INTERFACE_LINES
WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
SELECT
BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'Y' );
DELETE FROM CE_ARCH_HEADERS;
DELETE FROM CE_ARCH_LINES;
DELETE FROM CE_ARCH_RECONCILIATIONS_ALL
WHERE ORG_ID in (
SELECT
ORG_ID
FROM
CE_SECURITY_PROFILES_GT )
OR LEGAL_ENTITY_ID in (
SELECT
ORG_ID
FROM
CE_SECURITY_PROFILES_GT )
OR REFERENCE_TYPE in ( 'JE_LINE' , 'ROI_LINE' , 'STATEMENT' );
DELETE FROM CE_ARCH_RECON_ERRORS;
DELETE FROM CE_ARCH_INTERFACE_LINES
WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
SELECT
BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'N' );
DELETE FROM CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'N';
DELETE FROM CE_ARCH_INTRA_HEADERS;
DELETE FROM CE_ARCH_INTRA_LINES;
DELETE FROM CE_ARCH_INTERFACE_LINES
WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN (
SELECT
BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'Y' );
DELETE FROM CE_ARCH_INTERFACE_HEADERS
WHERE NVL(INTRA_DAY_FLAG
,'N') = 'Y';
INSERT INTO CE_ARCH_HEADERS
(STATEMENT_COMPLETE_FLAG
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,STATEMENT_HEADER_ID
,BANK_ACCOUNT_ID
,STATEMENT_NUMBER
,STATEMENT_DATE
,AUTO_LOADED_FLAG
,GL_DATE
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
STATEMENT_COMPLETE_FLAG,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
STATEMENT_HEADER_ID,
BANK_ACCOUNT_ID,
STATEMENT_NUMBER,
STATEMENT_DATE,
AUTO_LOADED_FLAG,
GL_DATE,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM
CE_STATEMENT_HEADERS CSH
WHERE CSH.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
AND CSH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
,CSH.STATEMENT_DATE)
AND CSH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
,CSH.STATEMENT_DATE)
AND not exists (
SELECT
1
FROM
CE_STATEMENT_HEADERS SH,
CE_STATEMENT_LINES SL,
CE_STATEMENT_RECONCILS_ALL SR
WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
AND SH.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
AND SH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
,SH.STATEMENT_DATE)
AND SH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
,SH.STATEMENT_DATE)
AND SR.CURRENT_RECORD_FLAG = 'Y'
AND SR.STATUS_FLAG = 'M'
AND ( SR.ORG_ID is not null
OR SR.LEGAL_ENTITY_ID is not null )
AND not exists (
SELECT
1
FROM
CE_SECURITY_PROFILES_GT LBG
WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
INSERT INTO CE_ARCH_HEADERS
(STATEMENT_COMPLETE_FLAG
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,STATEMENT_HEADER_ID
,BANK_ACCOUNT_ID
,STATEMENT_NUMBER
,STATEMENT_DATE
,AUTO_LOADED_FLAG
,GL_DATE
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
STATEMENT_COMPLETE_FLAG,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
STATEMENT_HEADER_ID,
BANK_ACCOUNT_ID,
STATEMENT_NUMBER,
STATEMENT_DATE,
AUTO_LOADED_FLAG,
GL_DATE,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM
CE_STATEMENT_HEADERS CSH
WHERE CSH.BANK_ACCOUNT_ID IN (
SELECT
ABA.BANK_ACCOUNT_ID
FROM
CE_BANK_ACCTS_GT_V ABA
WHERE ABA.BANK_BRANCH_ID = P_BANK_BRANCH )
AND CSH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
,CSH.STATEMENT_DATE)
AND CSH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
,CSH.STATEMENT_DATE)
AND not exists (
SELECT
1
FROM
CE_STATEMENT_HEADERS SH,
CE_STATEMENT_LINES SL,
CE_STATEMENT_RECONCILS_ALL SR
WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
AND SH.BANK_ACCOUNT_ID IN (
SELECT
ABA.BANK_ACCOUNT_ID
FROM
CE_BANK_ACCOUNTS ABA
WHERE ABA.BANK_BRANCH_ID = P_BANK_BRANCH )
AND SH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
,SH.STATEMENT_DATE)
AND SH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
,SH.STATEMENT_DATE)
AND SR.CURRENT_RECORD_FLAG = 'Y'
AND SR.STATUS_FLAG = 'M'
AND ( SR.ORG_ID is not null
OR SR.LEGAL_ENTITY_ID is not null )
AND not exists (
SELECT
1
FROM
CE_SECURITY_PROFILES_GT LBG
WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
INSERT INTO CE_ARCH_HEADERS
(STATEMENT_COMPLETE_FLAG
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,STATEMENT_HEADER_ID
,BANK_ACCOUNT_ID
,STATEMENT_NUMBER
,STATEMENT_DATE
,AUTO_LOADED_FLAG
,GL_DATE
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
STATEMENT_COMPLETE_FLAG,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
STATEMENT_HEADER_ID,
BANK_ACCOUNT_ID,
STATEMENT_NUMBER,
STATEMENT_DATE,
AUTO_LOADED_FLAG,
GL_DATE,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM
CE_STATEMENT_HEADERS CSH
WHERE CSH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
,CSH.STATEMENT_DATE)
AND CSH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
,CSH.STATEMENT_DATE)
AND not exists (
SELECT
1
FROM
CE_STATEMENT_HEADERS SH,
CE_STATEMENT_LINES SL,
CE_STATEMENT_RECONCILS_ALL SR
WHERE SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = SR.STATEMENT_LINE_ID
AND SH.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
AND SH.STATEMENT_DATE >= NVL(P_STATEMENT_DATE_FROM
,SH.STATEMENT_DATE)
AND SH.STATEMENT_DATE <= NVL(P_STATEMENT_DATE_TO1
,SH.STATEMENT_DATE)
AND SR.CURRENT_RECORD_FLAG = 'Y'
AND SR.STATUS_FLAG = 'M'
AND ( SR.ORG_ID is not null
OR SR.LEGAL_ENTITY_ID is not null )
AND not exists (
SELECT
1
FROM
CE_SECURITY_PROFILES_GT LBG
WHERE LBG.ORGANIZATION_ID = SR.ORG_ID
OR SR.LEGAL_ENTITY_ID = LBG.ORGANIZATION_ID ) );
INSERT INTO CE_ARCH_LINES
(STATEMENT_LINE_ID
,STATEMENT_HEADER_ID
,LINE_NUMBER
,TRX_DATE
,TRX_TYPE
,AMOUNT
,CHARGES_AMOUNT
,STATUS
,TRX_CODE_ID
,EFFECTIVE_DATE
,BANK_TRX_NUMBER
,TRX_TEXT
,CUSTOMER_TEXT
,INVOICE_TEXT
,CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_RATE_DATE
,ORIGINAL_AMOUNT
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECONCILE_TO_STATEMENT_FLAG)
SELECT
STATEMENT_LINE_ID,
STATEMENT_HEADER_ID,
LINE_NUMBER,
TRX_DATE,
TRX_TYPE,
AMOUNT,
CHARGES_AMOUNT,
STATUS,
TRX_CODE_ID,
EFFECTIVE_DATE,
BANK_TRX_NUMBER,
TRX_TEXT,
CUSTOMER_TEXT,
INVOICE_TEXT,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
EXCHANGE_RATE_DATE,
ORIGINAL_AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
RECONCILE_TO_STATEMENT_FLAG
FROM
CE_STATEMENT_LINES
WHERE STATEMENT_HEADER_ID IN (
SELECT
STATEMENT_HEADER_ID
FROM
CE_ARCH_HEADERS );
INSERT INTO CE_ARCH_RECONCILIATIONS_ALL
(STATEMENT_LINE_ID
,REFERENCE_TYPE
,REFERENCE_ID
,JE_HEADER_ID
,ORG_ID
,LEGAL_ENTITY_ID
,REFERENCE_STATUS
,STATUS_FLAG
,ACTION_FLAG
,CURRENT_RECORD_FLAG
,AUTO_RECONCILED_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,AMOUNT)
SELECT
STATEMENT_LINE_ID,
REFERENCE_TYPE,
REFERENCE_ID,
JE_HEADER_ID,
ORG_ID,
LEGAL_ENTITY_ID,
REFERENCE_STATUS,
STATUS_FLAG,
ACTION_FLAG,
CURRENT_RECORD_FLAG,
AUTO_RECONCILED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
AMOUNT
FROM
CE_STATEMENT_RECON_GT_V
WHERE STATEMENT_LINE_ID IN (
SELECT
STATEMENT_LINE_ID
FROM
CE_ARCH_LINES );
INSERT INTO CE_ARCH_RECON_ERRORS
(STATEMENT_LINE_ID
,MESSAGE_NAME
,CREATION_DATE
,CREATED_BY
,APPLICATION_SHORT_NAME
,STATEMENT_HEADER_ID)
SELECT
STATEMENT_LINE_ID,
MESSAGE_NAME,
CREATION_DATE,
CREATED_BY,
APPLICATION_SHORT_NAME,
STATEMENT_HEADER_ID
FROM
CE_RECONCILIATION_ERRORS
WHERE STATEMENT_LINE_ID IN (
SELECT
STATEMENT_LINE_ID
FROM
CE_ARCH_LINES );
DELETE FROM CE_STATEMENT_HEADERS
WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
DELETE FROM CE_STATEMENT_LINES
WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
DELETE FROM CE_STATEMENT_RECONCILS_ALL
WHERE STATEMENT_LINE_ID = X_LINE_ID
AND ( ORG_ID in (
SELECT
ORGANIZATION_ID
FROM
CE_SECURITY_PROFILES_GT )
OR LEGAL_ENTITY_ID in (
SELECT
ORGANIZATION_ID
FROM
CE_SECURITY_PROFILES_GT )
OR REFERENCE_TYPE in ( 'JE_LINE' , 'ROI_LINE' , 'STATEMENT' ) );
DELETE FROM CE_RECONCILIATION_ERRORS
WHERE STATEMENT_LINE_ID = X_LINE_ID;
INSERT INTO CE_ARCH_INTERFACE_HEADERS
(STATEMENT_NUMBER
,BANK_ACCOUNT_NUM
,STATEMENT_DATE
,BANK_NAME
,BANK_BRANCH_NAME
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CONTROL_LINE_COUNT
,RECORD_STATUS_FLAG
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
STATEMENT_NUMBER,
BANK_ACCOUNT_NUM,
STATEMENT_DATE,
BANK_NAME,
BANK_BRANCH_NAME,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CONTROL_LINE_COUNT,
RECORD_STATUS_FLAG,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM
CE_STATEMENT_HEADERS_INT CSH
WHERE NVL(CSH.BANK_BRANCH_NAME
,C_BANK_BRANCH_NAME) IN (
SELECT
BB.BANK_BRANCH_NAME
FROM
CE_BANK_BRANCHES_V BB,
CE_BANK_ACCTS_GT_V BA
WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BA.BANK_ACCOUNT_ID)
AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
,BB.BRANCH_PARTY_ID) )
AND CSH.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_BANK_ACCTS_GT_V
WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE))
AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
,'C'
,'C'
,'E'
,'E'
,'N'
,'N'
,'T'
,'T'
,'A'
,RECORD_STATUS_FLAG
,'T') )
AND NVL(CSH.INTRA_DAY_FLAG
,'N') = 'N';
INSERT INTO CE_ARCH_INTERFACE_LINES
(EXCHANGE_RATE_DATE
,EXCHANGE_RATE
,BANK_TRX_NUMBER
,CUSTOMER_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORIGINAL_AMOUNT
,BANK_ACCOUNT_NUM
,STATEMENT_NUMBER
,LINE_NUMBER
,TRX_DATE
,TRX_CODE
,EFFECTIVE_DATE
,TRX_TEXT
,INVOICE_TEXT
,AMOUNT
,CHARGES_AMOUNT
,CURRENCY_CODE
,USER_EXCHANGE_RATE_TYPE)
SELECT
EXCHANGE_RATE_DATE,
EXCHANGE_RATE,
BANK_TRX_NUMBER,
CUSTOMER_TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_AMOUNT,
BANK_ACCOUNT_NUM,
STATEMENT_NUMBER,
LINE_NUMBER,
TRX_DATE,
TRX_CODE,
EFFECTIVE_DATE,
TRX_TEXT,
INVOICE_TEXT,
AMOUNT,
CHARGES_AMOUNT,
CURRENCY_CODE,
USER_EXCHANGE_RATE_TYPE
FROM
CE_STATEMENT_LINES_INTERFACE CSL
WHERE CSL.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_ARCH_INTERFACE_HEADERS )
AND STATEMENT_NUMBER IN (
SELECT
STATEMENT_NUMBER
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE CSL.BANK_ACCOUNT_NUM = BANK_ACCOUNT_NUM
AND NVL(INTRA_DAY_FLAG
,'N') = 'N' );
DELETE FROM CE_STATEMENT_LINES_INTERFACE CSL
WHERE CSL.STATEMENT_NUMBER || '-' || CSL.BANK_ACCOUNT_NUM IN (
SELECT
CSH.STATEMENT_NUMBER || '-' || CSH.BANK_ACCOUNT_NUM
FROM
CE_STATEMENT_HEADERS_INT CSH
WHERE NVL(CSH.BANK_BRANCH_NAME
,C_BANK_BRANCH_NAME) IN (
SELECT
BB.BANK_BRANCH_NAME
FROM
CE_BANK_BRANCHES_V BB,
CE_BANK_ACCTS_GT_V BA
WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BA.BANK_ACCOUNT_ID)
AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
,BB.BRANCH_PARTY_ID) )
AND CSH.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_BANK_ACCTS_GT_V
WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE))
AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
,'C'
,'C'
,'E'
,'E'
,'N'
,'N'
,'T'
,'T'
,'A'
,RECORD_STATUS_FLAG
,'T') )
AND NVL(CSH.INTRA_DAY_FLAG
,'N') = 'N' );
DELETE FROM CE_STATEMENT_HEADERS_INT CSH
WHERE NVL(CSH.BANK_BRANCH_NAME
,C_BANK_BRANCH_NAME) IN (
SELECT
BB.BANK_BRANCH_NAME
FROM
CE_BANK_BRANCHES_V BB,
CE_BANK_ACCTS_GT_V BA
WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BA.BANK_ACCOUNT_ID)
AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
,BB.BRANCH_PARTY_ID) )
AND CSH.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_BANK_ACCTS_GT_V
WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE))
AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
,'C'
,'C'
,'E'
,'E'
,'N'
,'N'
,'T'
,'T'
,'A'
,RECORD_STATUS_FLAG
,'T') )
AND NVL(CSH.INTRA_DAY_FLAG
,'N') = 'N';
INSERT INTO CE_ARCH_INTRA_HEADERS
(STATEMENT_COMPLETE_FLAG
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,STATEMENT_HEADER_ID
,BANK_ACCOUNT_ID
,STATEMENT_NUMBER
,STATEMENT_DATE
,AUTO_LOADED_FLAG
,GL_DATE
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
STATEMENT_COMPLETE_FLAG,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
STATEMENT_HEADER_ID,
BANK_ACCOUNT_ID,
STATEMENT_NUMBER,
STATEMENT_DATE,
AUTO_LOADED_FLAG,
GL_DATE,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM
CE_INTRA_STMT_HEADERS CSH
WHERE CSH.BANK_ACCOUNT_ID = P_BANK_ACCOUNT
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE));
INSERT INTO CE_ARCH_INTRA_HEADERS
(STATEMENT_COMPLETE_FLAG
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,STATEMENT_HEADER_ID
,BANK_ACCOUNT_ID
,STATEMENT_NUMBER
,STATEMENT_DATE
,AUTO_LOADED_FLAG
,GL_DATE
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
STATEMENT_COMPLETE_FLAG,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
STATEMENT_HEADER_ID,
BANK_ACCOUNT_ID,
STATEMENT_NUMBER,
STATEMENT_DATE,
AUTO_LOADED_FLAG,
GL_DATE,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM
CE_INTRA_STMT_HEADERS CSH
WHERE CSH.BANK_ACCOUNT_ID IN (
SELECT
ABA.BANK_ACCOUNT_ID
FROM
CE_BANK_ACCOUNTS ABA
WHERE ABA.BANK_BRANCH_ID = P_BANK_BRANCH )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE));
INSERT INTO CE_ARCH_INTRA_HEADERS
(STATEMENT_COMPLETE_FLAG
,DOC_SEQUENCE_ID
,DOC_SEQUENCE_VALUE
,STATEMENT_HEADER_ID
,BANK_ACCOUNT_ID
,STATEMENT_NUMBER
,STATEMENT_DATE
,AUTO_LOADED_FLAG
,GL_DATE
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
STATEMENT_COMPLETE_FLAG,
DOC_SEQUENCE_ID,
DOC_SEQUENCE_VALUE,
STATEMENT_HEADER_ID,
BANK_ACCOUNT_ID,
STATEMENT_NUMBER,
STATEMENT_DATE,
AUTO_LOADED_FLAG,
GL_DATE,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
FROM
CE_INTRA_STMT_HEADERS CSH
WHERE TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE));
INSERT INTO CE_ARCH_INTRA_LINES
(STATEMENT_LINE_ID
,STATEMENT_HEADER_ID
,LINE_NUMBER
,TRX_DATE
,TRX_TYPE
,AMOUNT
,CHARGES_AMOUNT
,STATUS
,TRX_CODE_ID
,EFFECTIVE_DATE
,BANK_TRX_NUMBER
,TRX_TEXT
,CUSTOMER_TEXT
,INVOICE_TEXT
,CURRENCY_CODE
,EXCHANGE_RATE_TYPE
,EXCHANGE_RATE
,EXCHANGE_RATE_DATE
,ORIGINAL_AMOUNT
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,RECONCILE_TO_STATEMENT_FLAG)
SELECT
STATEMENT_LINE_ID,
STATEMENT_HEADER_ID,
LINE_NUMBER,
TRX_DATE,
TRX_TYPE,
AMOUNT,
CHARGES_AMOUNT,
STATUS,
TRX_CODE_ID,
EFFECTIVE_DATE,
BANK_TRX_NUMBER,
TRX_TEXT,
CUSTOMER_TEXT,
INVOICE_TEXT,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
EXCHANGE_RATE_DATE,
ORIGINAL_AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
RECONCILE_TO_STATEMENT_FLAG
FROM
CE_INTRA_STMT_LINES
WHERE STATEMENT_HEADER_ID IN (
SELECT
STATEMENT_HEADER_ID
FROM
CE_ARCH_INTRA_HEADERS );
DELETE FROM CE_INTRA_STMT_HEADERS
WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
DELETE FROM CE_INTRA_STMT_LINES
WHERE STATEMENT_HEADER_ID = X_HEADER_ID;
INSERT INTO CE_ARCH_INTERFACE_HEADERS
(STATEMENT_NUMBER
,BANK_ACCOUNT_NUM
,STATEMENT_DATE
,BANK_NAME
,BANK_BRANCH_NAME
,CHECK_DIGITS
,CONTROL_BEGIN_BALANCE
,CONTROL_TOTAL_DR
,CONTROL_TOTAL_CR
,CONTROL_END_BALANCE
,CASHFLOW_BALANCE
,INT_CALC_BALANCE
,ONE_DAY_FLOAT
,TWO_DAY_FLOAT
,CONTROL_DR_LINE_COUNT
,CONTROL_CR_LINE_COUNT
,CONTROL_LINE_COUNT
,RECORD_STATUS_FLAG
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INTRA_DAY_FLAG)
SELECT
STATEMENT_NUMBER,
BANK_ACCOUNT_NUM,
STATEMENT_DATE,
BANK_NAME,
BANK_BRANCH_NAME,
CHECK_DIGITS,
CONTROL_BEGIN_BALANCE,
CONTROL_TOTAL_DR,
CONTROL_TOTAL_CR,
CONTROL_END_BALANCE,
CASHFLOW_BALANCE,
INT_CALC_BALANCE,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
CONTROL_DR_LINE_COUNT,
CONTROL_CR_LINE_COUNT,
CONTROL_LINE_COUNT,
RECORD_STATUS_FLAG,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
INTRA_DAY_FLAG
FROM
CE_STATEMENT_HEADERS_INT CSH
WHERE NVL(CSH.BANK_BRANCH_NAME
,C_BANK_BRANCH_NAME) IN (
SELECT
BB.BANK_BRANCH_NAME
FROM
CE_BANK_BRANCHES_V BB,
CE_BANK_ACCTS_GT_V BA
WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BA.BANK_ACCOUNT_ID)
AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
,BB.BRANCH_PARTY_ID) )
AND CSH.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_BANK_ACCTS_GT_V
WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE))
AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
,'C'
,'C'
,'E'
,'E'
,'N'
,'N'
,'T'
,'T'
,'A'
,RECORD_STATUS_FLAG
,'T') )
AND CSH.INTRA_DAY_FLAG = 'Y';
INSERT INTO CE_ARCH_INTERFACE_LINES
(EXCHANGE_RATE_DATE
,EXCHANGE_RATE
,BANK_TRX_NUMBER
,CUSTOMER_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORIGINAL_AMOUNT
,BANK_ACCOUNT_NUM
,STATEMENT_NUMBER
,LINE_NUMBER
,TRX_DATE
,TRX_CODE
,EFFECTIVE_DATE
,TRX_TEXT
,INVOICE_TEXT
,AMOUNT
,CHARGES_AMOUNT
,CURRENCY_CODE
,USER_EXCHANGE_RATE_TYPE)
SELECT
EXCHANGE_RATE_DATE,
EXCHANGE_RATE,
BANK_TRX_NUMBER,
CUSTOMER_TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_AMOUNT,
BANK_ACCOUNT_NUM,
STATEMENT_NUMBER,
LINE_NUMBER,
TRX_DATE,
TRX_CODE,
EFFECTIVE_DATE,
TRX_TEXT,
INVOICE_TEXT,
AMOUNT,
CHARGES_AMOUNT,
CURRENCY_CODE,
USER_EXCHANGE_RATE_TYPE
FROM
CE_STATEMENT_LINES_INTERFACE CSL
WHERE CSL.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_ARCH_INTERFACE_HEADERS )
AND STATEMENT_NUMBER IN (
SELECT
STATEMENT_NUMBER
FROM
CE_ARCH_INTERFACE_HEADERS
WHERE CSL.BANK_ACCOUNT_NUM = BANK_ACCOUNT_NUM
AND INTRA_DAY_FLAG = 'Y' );
DELETE FROM CE_STATEMENT_LINES_INTERFACE CSL
WHERE CSL.STATEMENT_NUMBER || '-' || CSL.BANK_ACCOUNT_NUM IN (
SELECT
CSH.STATEMENT_NUMBER || '-' || CSH.BANK_ACCOUNT_NUM
FROM
CE_STATEMENT_HEADERS_INT CSH
WHERE NVL(CSH.BANK_BRANCH_NAME
,C_BANK_BRANCH_NAME) IN (
SELECT
BB.BANK_BRANCH_NAME
FROM
CE_BANK_BRANCHES_V BB,
CE_BANK_ACCTS_GT_V BA
WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BA.BANK_ACCOUNT_ID)
AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
,BB.BRANCH_PARTY_ID) )
AND CSH.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_BANK_ACCTS_GT_V
WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE))
AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
,'C'
,'C'
,'E'
,'E'
,'N'
,'N'
,'T'
,'T'
,'A'
,RECORD_STATUS_FLAG
,'T') )
AND CSH.INTRA_DAY_FLAG = 'Y' );
DELETE FROM CE_STATEMENT_HEADERS_INT CSH
WHERE NVL(CSH.BANK_BRANCH_NAME
,C_BANK_BRANCH_NAME) IN (
SELECT
BB.BANK_BRANCH_NAME
FROM
CE_BANK_BRANCHES_V BB,
CE_BANK_ACCTS_GT_V BA
WHERE BB.BRANCH_PARTY_ID = BA.BANK_BRANCH_ID
AND BA.BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BA.BANK_ACCOUNT_ID)
AND BB.BRANCH_PARTY_ID = NVL(P_BANK_BRANCH
,BB.BRANCH_PARTY_ID) )
AND CSH.BANK_ACCOUNT_NUM IN (
SELECT
BANK_ACCOUNT_NUM
FROM
CE_BANK_ACCTS_GT_V
WHERE BANK_ACCOUNT_ID = NVL(P_BANK_ACCOUNT
,BANK_ACCOUNT_ID)
AND BANK_BRANCH_ID = NVL(P_BANK_BRANCH
,BANK_BRANCH_ID) )
AND TRUNC(CSH.STATEMENT_DATE) >= NVL(P_STATEMENT_DATE_FROM
,TRUNC(CSH.STATEMENT_DATE))
AND TRUNC(CSH.STATEMENT_DATE) <= NVL(P_STATEMENT_DATE_TO1
,TRUNC(CSH.STATEMENT_DATE))
AND CSH.RECORD_STATUS_FLAG in ( DECODE(P_HDR_INT_STATUS
,'C'
,'C'
,'E'
,'E'
,'N'
,'N'
,'T'
,'T'
,'A'
,RECORD_STATUS_FLAG
,'T') )
AND CSH.INTRA_DAY_FLAG = 'Y';