VIEW: APPS.CE_801_RECONCILED_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)