Product: | IBY - Payments |
---|---|
Description: | |
Implementation/DBA Data: | APPS.IBY_EXT_FD_INS_1_0_V |
SELECT INS.PAYMENT_INSTRUCTION_ID
, INS.CREATION_DATE
, INS.PAYMENT_INSTRUCTION_STATUS
, PI_STATUS_LOOKUP.MEANING
, PP.PROCESSING_TYPE
, INS.PAY_ADMIN_ASSIGNED_REF_CODE
, INS.BANK_ASSIGNED_REF_CODE
, PP.PERIODIC_SEQUENCE_NAME_1
, PP.LAST_USED_NUMBER_1
, PP.PERIODIC_SEQUENCE_NAME_2
, PP.LAST_USED_NUMBER_2
, PP.PERIODIC_SEQUENCE_NAME_3
, PP.LAST_USED_NUMBER_3
, PP.BANK_INSTRUCTION1_CODE
, BI1.MEANING BANK_INSTRUCTION1_MEANING
, BI1.FORMAT_VALUE BANK_INSTRUCTION1_FORMAT_VALUE
, PP.BANK_INSTRUCTION2_CODE
, BI2.MEANING BANK_INSTRUCTION2_MEANING
, BI2.FORMAT_VALUE BANK_INSTRUCTION2_FORMAT_VALUE
, PP.BANK_INSTRUCTION_DETAILS
, PP.PAYMENT_TEXT_MESSAGE1
, PP.PAYMENT_TEXT_MESSAGE2
, PP.DECLARATION_OPTION
, PAYSYS_ACCT.KEY
, PP.BEP_ACCOUNT_ID
, PAYSYS_ACCT.ATTRIBUTE_CATEGORY
, INS.COMMENTS
, INS.ATTRIBUTE_CATEGORY
, PP.PAYMENT_PROFILE_ID
, PP.SYSTEM_PROFILE_CODE
, PP.PAYMENT_PROFILE_NAME
, PP.ATTRIBUTE_CATEGORY
, PF.FORMAT_CODE
, PF.FORMAT_NAME
, PF.ATTRIBUTE_CATEGORY
, 1
, INS.PAYMENT_COUNT
, INS.PAYMENT_DATE
, INS.PAYMENT_CURRENCY_CODE
, INS.PAYMENT_SERVICE_REQUEST_ID
, INS.ORG_ID
, INS.PAYMENT_REASON_CODE
, PAY_REASON.MEANING
, PAY_REASON.FORMAT_VALUE
, INS.PAYMENT_REASON_COMMENTS
, PI_RULES.GROUP_BY_PAYMENT_DATE
, PI_RULES.GROUP_BY_PAYMENT_CURRENCY
, PI_RULES.GROUP_BY_PAY_SERVICE_REQUEST
, PI_RULES.GROUP_BY_LEGAL_ENTITY
, PI_RULES.GROUP_BY_ORGANIZATION
, PI_RULES.GROUP_BY_PAYMENT_REASON
, INS.LEGAL_ENTITY_ID
FROM IBY_PAY_INSTRUCTIONS_ALL INS
, FND_LOOKUPS PI_STATUS_LOOKUP
, IBY_PAYMENT_PROFILES PP
, IBY_INSTR_CREATION_RULES PI_RULES
, IBY_BEPKEYS PAYSYS_ACCT
, IBY_FORMATS_VL PF
, IBY_BANK_INSTRUCTIONS_VL BI1
, IBY_BANK_INSTRUCTIONS_VL BI2
, IBY_PAYMENT_REASONS_VL PAY_REASON
WHERE PP.PAYMENT_PROFILE_ID = INS.PAYMENT_PROFILE_ID
AND PP.SYSTEM_PROFILE_CODE = PI_RULES.SYSTEM_PROFILE_CODE(+)
AND PP.BEP_ACCOUNT_ID = PAYSYS_ACCT.BEP_ACCOUNT_ID(+)
AND PP.PAYMENT_FORMAT_CODE = PF.FORMAT_CODE
AND INS.PAYMENT_INSTRUCTION_STATUS = PI_STATUS_LOOKUP.LOOKUP_CODE
AND PI_STATUS_LOOKUP.LOOKUP_TYPE = 'IBY_PAY_INSTRUCTION_STATUSES'
AND INS.PAYMENT_REASON_CODE = PAY_REASON.PAYMENT_REASON_CODE(+)
AND PP.BANK_INSTRUCTION1_CODE = BI1.BANK_INSTRUCTION_CODE(+)
AND PP.BANK_INSTRUCTION2_CODE = BI2.BANK_INSTRUCTION_CODE(+)