VIEW: APPS.CE_801_TRANSACTIONS_V

Source

View Text - Preformatted

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), ppa.effective_date, 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 ppa.effective_date >= 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', ppa.effective_date, 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 ppa.effective_date >= 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

View Text - HTML Formatted

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)
, PPA.EFFECTIVE_DATE
, 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 PPA.EFFECTIVE_DATE >= 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'
, PPA.EFFECTIVE_DATE
, 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 PPA.EFFECTIVE_DATE >= 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