FND Design Data [Home] [Help]

View: CE_801_RECONCILED_V

Product: CE - Cash Management
Description: Reconciled Payroll payments for unreconciling
Implementation/DBA Data: ViewAPPS.CE_801_RECONCILED_V
View Text

SELECT PAA.ROWID
, 'N'
, SL.STATEMENT_LINE_ID
, SH.BANK_ACCOUNT_ID
, PAI.LOCKING_ACTION_ID
, 'PAYMENT'
, L.MEANING
, PAA.SERIAL_NUMBER
, POPM.CURRENCY_CODE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, PPP.VALUE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, PPP.VALUE
, ABA.CURRENCY_CODE
, PPP.VALUE
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE))
, DECODE( POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, PPP.VALUE
, ABA.CURRENCY_CODE
, PPP.VALUE
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE) )
, TO_DATE(NULL)
, (SELECT L2.MEANING
FROM FND_COMMON_LOOKUPS L2
WHERE L2. LOOKUP_CODE = 'V'
AND L2.LOOKUP_TYPE = 'ACTION_STATUS')
, 'V'
, PPA.EFFECTIVE_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, CRE.CREATION_DATE
, CRE.CREATED_BY
, CRE.LAST_UPDATE_DATE
, CRE.LAST_UPDATED_BY
, SL.STATEMENT_HEADER_ID
, TO_NUMBER(NULL)
, ABA.BANK_BRANCH_ID
, PPA.EFFECTIVE_DATE
, NULL
, TO_DATE(NULL)
, L1.MEANING
, SL.LINE_NUMBER
, SL.TRX_DATE
, SL.AMOUNT
, SL.STATUS
, TC.DESCRIPTION
, SH.STATEMENT_NUMBER
, SH.STATEMENT_DATE
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, ABA.CURRENCY_CODE
, SH.DOC_SEQUENCE_VALUE
, SL.TRX_TYPE
, NULL
, SH.CONTROL_END_BALANCE
, 801
, 1
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, PPP.VALUE
, ABA.CURRENCY_CODE
, PPP.VALUE
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE))
, 'PAY'
, CRE.REQUEST_ID
, BAU.BANK_ACCT_USE_ID
, PPA.BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
FROM CE_STATEMENT_HEADERS SH
, CE_STATEMENT_LINES SL
, CE_TRANSACTION_CODES TC
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_SYSTEM_PARAMETERS SYS
, CE_LOOKUPS L
, CE_LOOKUPS L1
, FND_COMMON_LOOKUPS L2
, GL_SETS_OF_BOOKS SOB
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, PAY_CE_RECONCILED_PAYMENTS PCRP
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ORG_PAYMENT_METHODS_F POPM
, PAY_PAYROLL_ACTIONS PPA
, PAY_PRE_PAYMENTS PPP
, PAY_ACTION_INTERLOCKS PAI
WHERE L.LOOKUP_CODE = 'PAYMENT'
AND L.LOOKUP_TYPE = 'TRX_TYPE'
AND L1.LOOKUP_CODE = SL.TRX_TYPE
AND L1.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND L2.LOOKUP_TYPE = 'RECON_STATUS'
AND L2.LOOKUP_CODE = PCRP.STATUS_CODE
AND PCRP.ASSIGNMENT_ACTION_ID = PAI.LOCKING_ACTION_ID
AND TC.TRANSACTION_CODE_ID (+) = SL.TRX_CODE_ID
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BAU.PAY_USE_ENABLE_FLAG = 'Y'
AND BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'BUSINESS_GROUP'
AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID
AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID
AND CRE.REFERENCE_TYPE = 'PAY'
AND CRE.STATUS_FLAG = 'M'
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND CRE.REFERENCE_ID = PAI.LOCKING_ACTION_ID
AND PPP.PRE_PAYMENT_ID = PAA.PRE_PAYMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPP.ORG_PAYMENT_METHOD_ID = POPM.ORG_PAYMENT_METHOD_ID
AND PPA.EFFECTIVE_DATE BETWEEN POPM.EFFECTIVE_START_DATE
AND POPM.EFFECTIVE_END_DATE /*AND BAU.ORG_ID = SYS.ORG_ID
AND CRE.ORG_ID = SYS.ORG_ID*/
AND BAU.ORG_ID = CRE.ORG_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID UNION ALL SELECT PAA.ROWID
, 'N'
, SL.STATEMENT_LINE_ID
, SH.BANK_ACCOUNT_ID
, PAA.ASSIGNMENT_ACTION_ID
, 'PAYMENT'
, L.MEANING
, PAA.SERIAL_NUMBER
, POPM.CURRENCY_CODE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, PPP.VALUE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, PPP.VALUE
, ABA.CURRENCY_CODE
, PPP.VALUE
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE))
, DECODE( POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(PCRP.STATUS_CODE
, 'C'
, PCRP.CLEARED_AMOUNT
, 'V'
, PPP.VALUE)
, ABA.CURRENCY_CODE
, DECODE(PCRP.STATUS_CODE
, 'C'
, PCRP.CLEARED_AMOUNT
, 'V'
, PPP.VALUE)
, DECODE(PCRP.STATUS_CODE
, 'C'
, NVL(PCRP.CLEARED_BASE_AMOUNT
, PCRP.CLEARED_AMOUNT)
, 'V'
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE)) )
, TO_DATE(NULL)
, L2.MEANING
, PCRP.STATUS_CODE
, PPA.EFFECTIVE_DATE
, PCRP.CLEARED_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, CRE.CREATION_DATE
, CRE.CREATED_BY
, CRE.LAST_UPDATE_DATE
, CRE.LAST_UPDATED_BY
, SL.STATEMENT_HEADER_ID
, TO_NUMBER(NULL)
, ABA.BANK_BRANCH_ID
, PPA.EFFECTIVE_DATE
, NULL
, TO_DATE(NULL)
, L1.MEANING
, SL.LINE_NUMBER
, SL.TRX_DATE
, SL.AMOUNT
, SL.STATUS
, TC.DESCRIPTION
, SH.STATEMENT_NUMBER
, SH.STATEMENT_DATE
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, ABA.CURRENCY_CODE
, SH.DOC_SEQUENCE_VALUE
, SL.TRX_TYPE
, NULL
, SH.CONTROL_END_BALANCE
, 801
, 1
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(PCRP.STATUS_CODE
, 'C'
, PCRP.CLEARED_AMOUNT
, 'V'
, PPP.VALUE)
, ABA.CURRENCY_CODE
, DECODE(PCRP.STATUS_CODE
, 'C'
, PCRP.CLEARED_AMOUNT
, 'V'
, PPP.VALUE)
, DECODE(PCRP.STATUS_CODE
, 'C'
, NVL(PCRP.CLEARED_BASE_AMOUNT
, PCRP.CLEARED_AMOUNT)
, 'V'
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE)))
, 'PAY'
, CRE.REQUEST_ID
, BAU.BANK_ACCT_USE_ID
, PPA.BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
FROM CE_STATEMENT_HEADERS SH
, CE_STATEMENT_LINES SL
, CE_TRANSACTION_CODES TC
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_SYSTEM_PARAMETERS SYS
, CE_LOOKUPS L
, CE_LOOKUPS L1
, FND_COMMON_LOOKUPS L2
, GL_SETS_OF_BOOKS SOB
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, PAY_CE_RECONCILED_PAYMENTS PCRP
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ORG_PAYMENT_METHODS_F POPM
, PAY_PAYROLL_ACTIONS PPA
, PAY_PRE_PAYMENTS PPP
WHERE L.LOOKUP_CODE = 'PAYMENT'
AND L.LOOKUP_TYPE = 'TRX_TYPE'
AND L1.LOOKUP_CODE = SL.TRX_TYPE
AND L1.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND L2.LOOKUP_TYPE = 'RECON_STATUS'
AND L2.LOOKUP_CODE = PCRP.STATUS_CODE
AND PCRP.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND TC.TRANSACTION_CODE_ID (+) = SL.TRX_CODE_ID
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BAU.PAY_USE_ENABLE_FLAG = 'Y'
AND BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'BUSINESS_GROUP'
AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID
AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID
AND CRE.REFERENCE_TYPE = 'PAY'
AND CRE.STATUS_FLAG = 'M'
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND CRE.REFERENCE_ID = PAA.ASSIGNMENT_ACTION_ID
AND PPP.PRE_PAYMENT_ID = PAA.PRE_PAYMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPP.ORG_PAYMENT_METHOD_ID = POPM.ORG_PAYMENT_METHOD_ID
AND PPA.EFFECTIVE_DATE BETWEEN POPM.EFFECTIVE_START_DATE
AND POPM.EFFECTIVE_END_DATE
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.ORG_ID = CRE.ORG_ID /*AND BAU.ORG_ID = SYS.ORG_ID
AND CRE.ORG_ID = SYS.ORG_ID*/
AND NOT EXISTS (SELECT NULL
FROM PAY_ACTION_INTERLOCKS PAI
WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID)

Columns

Name
ROW_ID
MULTI_SELECT
STATEMENT_LINE_ID
BANK_ACCOUNT_ID
TRX_ID
TRX_TYPE
TYPE_MEANING
TRX_NUMBER
CURRENCY_CODE
TRX_CURRENCY_TYPE
AMOUNT
BANK_ACCOUNT_AMOUNT
AMOUNT_CLEARED
GL_DATE
STATUS_DSP
STATUS
TRX_DATE
CLEARED_DATE
MATURITY_DATE
EXCHANGE_RATE_DATE
EXCHANGE_RATE_TYPE
EXCHANGE_RATE
BANK_CHARGES
BANK_ERRORS
BATCH_NAME
BATCH_ID
AGENT_NAME
CUSTOMER_NAME
VENDOR_NAME
CUSTOMER_ID
SUPPLIER_ID
REFERENCE_TYPE_DSP
REFERENCE_TYPE
REFERENCE_ID
CREATION_DATE
CREATED_BY
LAST_UPDATE_DATE
LAST_UPDATED_BY
STATEMENT_HEADER_ID
CASH_RECEIPT_ID
BANK_BRANCH_ID
RECEIPT_DATE
POSTED_FLAG
POSTED_DATE
LINE_TYPE_MEANING
STATEMENT_LINE_NUMBER
STATEMENT_LINE_DATE
STATEMENT_LINE_AMOUNT
STATEMENT_LINE_STATUS_MEANING
STATEMENT_LINE_TYPE_MEANING
STATEMENT_HEADER_NUMBER
STATEMENT_HEADER_DATE
BANK_ACCOUNT_NAME
BANK_ACCOUNT_NUM
BANK_CURRENCY_CODE
STATEMENT_DOC_SEQUENCE_VALUE
LINE_TRX_TYPE
REMITTANCE_NUMBER
CONTROL_END_BALANCE
APPLICATION_ID
COUNT_CLEARED
ACTUAL_AMOUNT_CLEARED
CLEARING_TRX_TYPE
REQUST_ID
CE_BANK_ACCT_USE_ID
ORG_ID
LEGAL_ENTITY_ID