FND Design Data [Home] [Help]

View: CE_801_TRANSACTIONS_V

Product: CE - Cash Management
Description: Available Payroll payments for reconciliation
Implementation/DBA Data: ViewAPPS.CE_801_TRANSACTIONS_V
View Text

SELECT PAA.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, ABB.BANK_NAME
, ABB.BANK_BRANCH_NAME
, PAA.ASSIGNMENT_ACTION_ID
, 'PAYMENT'
, L1.MEANING
, PAA.SERIAL_NUMBER
, TO_NUMBER(NULL)
, POPM.CURRENCY_CODE
, PPP.VALUE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, PPP.VALUE
, ABA.CURRENCY_CODE
, PPP.VALUE
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE))
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, L2.MEANING
, DECODE(PAA.ACTION_STATUS
, 'E'
, PCRP.STATUS_CODE
, PAA.ACTION_STATUS)
, NVL(PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PPA.CREATION_DATE
, PPA.CREATED_BY
, PPA.LAST_UPDATE_DATE
, PPA.LAST_UPDATED_BY
, NULL
, TO_NUMBER(NULL)
, 801
, 0
, ABA.CURRENCY_CODE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, ABA.ASSET_CODE_COMBINATION_ID
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'PAY'
, NULL
, PPP.BASE_CURRENCY_VALUE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, NULL
, TO_DATE(NULL)
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, PPA.BUSINESS_GROUP_ID
FROM CE_SYSTEM_PARAMETERS SYS
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, /*CE_GL_ACCOUNTS_CCID GAC
, */ CE_BANK_BRANCHES_V ABB
, GL_SETS_OF_BOOKS SOB
, CE_LOOKUPS L1
, FND_COMMON_LOOKUPS L2
, PAY_CE_RECONCILED_PAYMENTS PCRP
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ORG_PAYMENT_METHODS_F POPM
, PAY_PRE_PAYMENTS PPP
, PAY_PAYROLL_ACTIONS PPA
WHERE L1.LOOKUP_CODE = 'PAYMENT'
AND L1.LOOKUP_TYPE = 'TRX_TYPE'
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID /*AND SYS.ORG_ID = BAU.ORG_ID*/
AND CRE.REFERENCE_ID (+) = PAA.ASSIGNMENT_ACTION_ID
AND CRE.REFERENCE_TYPE (+) = 'PAY'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND L2.LOOKUP_TYPE = DECODE(NVL(PCRP.STATUS_CODE
, '#')
, 'E'
, 'RECON_STATUS'
, 'ACTION_STATUS')
AND L2.LOOKUP_CODE = DECODE(NVL(PCRP.STATUS_CODE
, '#')
, 'E'
, PCRP.STATUS_CODE
, PAA.ACTION_STATUS)
AND NVL(PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED) >= SYS.CASHBOOK_BEGIN_DATE
AND PCRP.ASSIGNMENT_ACTION_ID(+) = PAA.ASSIGNMENT_ACTION_ID
AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_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 BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID (+)*/
AND BAU.PAYROLL_BANK_ACCOUNT_ID = POPM.EXTERNAL_ACCOUNT_ID
AND POPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PPP.PRE_PAYMENT_ID = PAA.PRE_PAYMENT_ID
AND PPA.ACTION_TYPE IN ('P'
, 'H'
, 'E')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN POPM.EFFECTIVE_START_DATE
AND POPM.EFFECTIVE_END_DATE
AND PAA.ACTION_STATUS IN ('C'
, 'V')
AND NOT EXISTS (SELECT *
FROM PAY_ACTION_INTERLOCKS PAI
WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID) UNION ALL SELECT PAA.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, ABB.BANK_NAME
, ABB.BANK_BRANCH_NAME
, PAI.LOCKING_ACTION_ID
, 'PAYMENT'
, L1.MEANING
, PAA.SERIAL_NUMBER
, TO_NUMBER(NULL)
, POPM.CURRENCY_CODE
, PPP.VALUE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, PPP.VALUE
, ABA.CURRENCY_CODE
, PPP.VALUE
, NVL(PPP.BASE_CURRENCY_VALUE
, PPP.VALUE))
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, 'VOID'
, 'V'
, NVL(PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PPA.CREATION_DATE
, PPA.CREATED_BY
, PPA.LAST_UPDATE_DATE
, PPA.LAST_UPDATED_BY
, NULL
, TO_NUMBER(NULL)
, 801
, 0
, ABA.CURRENCY_CODE
, DECODE(POPM.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, ABA.ASSET_CODE_COMBINATION_ID
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'PAY'
, NULL
, PPP.BASE_CURRENCY_VALUE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, NULL
, TO_DATE(NULL)
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM CE_SYSTEM_PARAMETERS SYS
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_BANK_ACCOUNTS ABA
, CE_BANK_BRANCHES_V ABB
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, /*CE_GL_ACCOUNTS_CCID GAC
, */ GL_SETS_OF_BOOKS SOB
, CE_LOOKUPS L1
, PAY_CE_RECONCILED_PAYMENTS PCRP
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ORG_PAYMENT_METHODS_F POPM
, PAY_PRE_PAYMENTS PPP
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INTERLOCKS PAI
WHERE L1.LOOKUP_CODE = 'PAYMENT'
AND L1.LOOKUP_TYPE = 'TRX_TYPE'
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID /*AND SYS.ORG_ID = BAU.ORG_ID*/
AND CRE.REFERENCE_ID (+) = PAI.LOCKING_ACTION_ID
AND CRE.REFERENCE_TYPE (+) = 'PAY'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND NVL(PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED) >= SYS.CASHBOOK_BEGIN_DATE
AND PCRP.ASSIGNMENT_ACTION_ID(+) = PAA.ASSIGNMENT_ACTION_ID
AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_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 BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID (+)*/
AND BAU.PAYROLL_BANK_ACCOUNT_ID = POPM.EXTERNAL_ACCOUNT_ID
AND POPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PPP.PRE_PAYMENT_ID = PAA.PRE_PAYMENT_ID
AND PPA.ACTION_TYPE = 'H'
AND PPA.EFFECTIVE_DATE BETWEEN POPM.EFFECTIVE_START_DATE
AND POPM.EFFECTIVE_END_DATE
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID

Columns

Name
ROW_ID
MULTI_SELECT
BANK_ACCOUNT_ID
BANK_ACCOUNT_NAME
BANK_ACCOUNT_NUM
BANK_NAME
BANK_BRANCH_NAME
TRX_ID
TRX_TYPE
TYPE_MEANING
TRX_NUMBER
CHECK_NUMBER
CURRENCY_CODE
AMOUNT
BANK_ACCOUNT_AMOUNT
AMOUNT_CLEARED
GL_DATE
STATUS_DSP
STATUS
TRX_DATE
CLEARED_DATE
MATURITY_DATE
EXCHANGE_RATE_DATE
EXCHANGE_RATE_TYPE
USER_EXCHANGE_RATE_TYPE
EXCHANGE_RATE
BANK_CHARGES
BANK_ERRORS
BATCH_NAME
BATCH_ID
AGENT_NAME
CUSTOMER_NAME
PAYMENT_METHOD
VENDOR_NAME
CUSTOMER_ID
SUPPLIER_ID
REFERENCE_TYPE_DSP
REFERENCE_TYPE
REFERENCE_ID
ACTUAL_AMOUNT_CLEARED
CREATION_DATE
CREATED_BY
LAST_UPDATE_DATE
LAST_UPDATED_BY
REMITTANCE_NUMBER
CASH_RECEIPT_ID
APPLICATION_ID
COUNT_CLEARED
BANK_CURRENCY_CODE
TRX_CURRENCY_TYPE
CODE_COMBINATION_ID
PERIOD_NAME
JOURNAL_ENTRY_NAME
DOCUMENT_NUMBER
JOURNAL_ENTRY_LINE_NUMBER
CLEARING_TRX_TYPE
JOURNAL_CATEGORY
BASE_AMOUNT
RECEIPT_CLASS_ID
RECEIPT_METHOD_ID
RECEIPT_CLASS_NAME
DEPOSIT_DATE
REVERSED_RECEIPT_FLAG
VALUE_DATE
CE_BANK_ACCT_USE_ID
LEGAL_ENTITY_ID
ORG_ID