eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_PRE_PAYMENTS_V

Source

View Text - Preformatted


SELECT PPP.PRE_PAYMENT_ID , PPP.ASSIGNMENT_ACTION_ID , OPMTL.ORG_PAYMENT_METHOD_NAME , PPM.PRIORITY , PPTTL.PAYMENT_TYPE_NAME , LO1.MEANING SOURCE , OPM.CURRENCY_CODE , PPP.VALUE , PAY_ASSIGNMENT_ACTIONS_PKG.GET_PAYMENT_STATUS( AAC1.ASSIGNMENT_ACTION_ID, PPP.PRE_PAYMENT_ID) STATUS , OPM.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID, PPP.prepayment_action_id, PPP.ASSIGNMENT_ACTION_ID PREPAY_ACTION_ID FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL, PAY_PAYMENT_TYPES_TL PPTTL, PAY_ORG_PAYMENT_METHODS_F OPM, PAY_PRE_PAYMENTS PPP, PAY_PERSONAL_PAYMENT_METHODS_F PPM, HR_LOOKUPS LO1, PAY_PAYMENT_TYPES PPT, PAY_ASSIGNMENT_ACTIONS AAC1, PAY_PAYROLL_ACTIONS PAC1 WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID AND OPMTL.LANGUAGE = userenv('LANG') AND PPT.PAYMENT_TYPE_ID = PPTTL.PAYMENT_TYPE_ID AND PPTTL.LANGUAGE = userenv('LANG') AND PPP.ASSIGNMENT_ACTION_ID = AAC1.ASSIGNMENT_ACTION_ID AND PAC1.PAYROLL_ACTION_ID = AAC1.PAYROLL_ACTION_ID AND LO1.LOOKUP_TYPE = 'PAYMENT_SOURCE' AND LO1.LOOKUP_CODE = DECODE( PAC1.ORG_PAYMENT_METHOD_ID, NULL, DECODE( PPM.PERSONAL_PAYMENT_METHOD_ID, NULL,'D','P'), 'O' ) AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID AND PAC1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE AND PPT.PAYMENT_TYPE_ID = OPM.PAYMENT_TYPE_ID AND PPP.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID (+) AND ( PPP.PERSONAL_PAYMENT_METHOD_ID IS NULL OR PAC1.EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE AND PPM.EFFECTIVE_END_DATE ) union all SELECT PPP.PRE_PAYMENT_ID , PPP.ASSIGNMENT_ACTION_ID , OPMTL.ORG_PAYMENT_METHOD_NAME , PPM.PRIORITY , PPTTL.PAYMENT_TYPE_NAME , LO1.MEANING SOURCE , OPM.CURRENCY_CODE , PPP.VALUE , PAY_ASSIGNMENT_ACTIONS_PKG.GET_PAYMENT_STATUS( AAC1.ASSIGNMENT_ACTION_ID, PPP.PRE_PAYMENT_ID) STATUS , OPM.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID, PPP.prepayment_action_id, aac1.source_action_id PREPAY_ACTION_ID FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL, PAY_PAYMENT_TYPES_TL PPTTL, PAY_ORG_PAYMENT_METHODS_F OPM, PAY_PRE_PAYMENTS PPP, PAY_PERSONAL_PAYMENT_METHODS_F PPM, HR_LOOKUPS LO1, PAY_PAYMENT_TYPES PPT, PAY_ASSIGNMENT_ACTIONS AAC1, PAY_PAYROLL_ACTIONS PAC1 WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID AND OPMTL.LANGUAGE = userenv('LANG') AND PPT.PAYMENT_TYPE_ID = PPTTL.PAYMENT_TYPE_ID AND PPTTL.LANGUAGE = userenv('LANG') AND PPP.ASSIGNMENT_ACTION_ID = AAC1.ASSIGNMENT_ACTION_ID AND PAC1.PAYROLL_ACTION_ID = AAC1.PAYROLL_ACTION_ID AND LO1.LOOKUP_TYPE = 'PAYMENT_SOURCE' AND LO1.LOOKUP_CODE = DECODE( PAC1.ORG_PAYMENT_METHOD_ID, NULL, DECODE( PPM.PERSONAL_PAYMENT_METHOD_ID, NULL,'D','P'), 'O' ) AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID AND PAC1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE AND PPT.PAYMENT_TYPE_ID = OPM.PAYMENT_TYPE_ID AND PPP.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID (+) AND ( PPP.PERSONAL_PAYMENT_METHOD_ID IS NULL OR PAC1.EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE AND PPM.EFFECTIVE_END_DATE ) and aac1.source_action_id is not null and aac1.source_action_id <> aac1.assignment_action_id

View Text - HTML Formatted

SELECT PPP.PRE_PAYMENT_ID
, PPP.ASSIGNMENT_ACTION_ID
, OPMTL.ORG_PAYMENT_METHOD_NAME
, PPM.PRIORITY
, PPTTL.PAYMENT_TYPE_NAME
, LO1.MEANING SOURCE
, OPM.CURRENCY_CODE
, PPP.VALUE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_PAYMENT_STATUS( AAC1.ASSIGNMENT_ACTION_ID
, PPP.PRE_PAYMENT_ID) STATUS
, OPM.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PPP.PREPAYMENT_ACTION_ID
, PPP.ASSIGNMENT_ACTION_ID PREPAY_ACTION_ID
FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_PAYMENT_TYPES_TL PPTTL
, PAY_ORG_PAYMENT_METHODS_F OPM
, PAY_PRE_PAYMENTS PPP
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, HR_LOOKUPS LO1
, PAY_PAYMENT_TYPES PPT
, PAY_ASSIGNMENT_ACTIONS AAC1
, PAY_PAYROLL_ACTIONS PAC1
WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE = USERENV('LANG')
AND PPT.PAYMENT_TYPE_ID = PPTTL.PAYMENT_TYPE_ID
AND PPTTL.LANGUAGE = USERENV('LANG')
AND PPP.ASSIGNMENT_ACTION_ID = AAC1.ASSIGNMENT_ACTION_ID
AND PAC1.PAYROLL_ACTION_ID = AAC1.PAYROLL_ACTION_ID
AND LO1.LOOKUP_TYPE = 'PAYMENT_SOURCE'
AND LO1.LOOKUP_CODE = DECODE( PAC1.ORG_PAYMENT_METHOD_ID
, NULL
, DECODE( PPM.PERSONAL_PAYMENT_METHOD_ID
, NULL
, 'D'
, 'P')
, 'O' )
AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PAC1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE
AND OPM.EFFECTIVE_END_DATE
AND PPT.PAYMENT_TYPE_ID = OPM.PAYMENT_TYPE_ID
AND PPP.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID (+)
AND ( PPP.PERSONAL_PAYMENT_METHOD_ID IS NULL OR PAC1.EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE
AND PPM.EFFECTIVE_END_DATE ) UNION ALL SELECT PPP.PRE_PAYMENT_ID
, PPP.ASSIGNMENT_ACTION_ID
, OPMTL.ORG_PAYMENT_METHOD_NAME
, PPM.PRIORITY
, PPTTL.PAYMENT_TYPE_NAME
, LO1.MEANING SOURCE
, OPM.CURRENCY_CODE
, PPP.VALUE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_PAYMENT_STATUS( AAC1.ASSIGNMENT_ACTION_ID
, PPP.PRE_PAYMENT_ID) STATUS
, OPM.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PPP.PREPAYMENT_ACTION_ID
, AAC1.SOURCE_ACTION_ID PREPAY_ACTION_ID
FROM PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_PAYMENT_TYPES_TL PPTTL
, PAY_ORG_PAYMENT_METHODS_F OPM
, PAY_PRE_PAYMENTS PPP
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, HR_LOOKUPS LO1
, PAY_PAYMENT_TYPES PPT
, PAY_ASSIGNMENT_ACTIONS AAC1
, PAY_PAYROLL_ACTIONS PAC1
WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE = USERENV('LANG')
AND PPT.PAYMENT_TYPE_ID = PPTTL.PAYMENT_TYPE_ID
AND PPTTL.LANGUAGE = USERENV('LANG')
AND PPP.ASSIGNMENT_ACTION_ID = AAC1.ASSIGNMENT_ACTION_ID
AND PAC1.PAYROLL_ACTION_ID = AAC1.PAYROLL_ACTION_ID
AND LO1.LOOKUP_TYPE = 'PAYMENT_SOURCE'
AND LO1.LOOKUP_CODE = DECODE( PAC1.ORG_PAYMENT_METHOD_ID
, NULL
, DECODE( PPM.PERSONAL_PAYMENT_METHOD_ID
, NULL
, 'D'
, 'P')
, 'O' )
AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PAC1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE
AND OPM.EFFECTIVE_END_DATE
AND PPT.PAYMENT_TYPE_ID = OPM.PAYMENT_TYPE_ID
AND PPP.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID (+)
AND ( PPP.PERSONAL_PAYMENT_METHOD_ID IS NULL OR PAC1.EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE
AND PPM.EFFECTIVE_END_DATE )
AND AAC1.SOURCE_ACTION_ID IS NOT NULL
AND AAC1.SOURCE_ACTION_ID <> AAC1.ASSIGNMENT_ACTION_ID