VIEW: APPS.PAY_JP_SOE_PREPAY_PAYMENTS_V

Source

View Text - Preformatted

SELECT v.assignment_action_id, v.pre_payment_id, v.m_assignment_action_id, v.m_object_version_number, v.m_payroll_action_id, v.m_action_type, substrb(hr_general.decode_lookup('ACTION_TYPE',v.m_action_type),1,80), v.m_action_status, decode(v.m_action_status,'M','Y','N'), v.category, v.payment_type_name, v.org_payment_method_name, v.priority, v.value, substrb(to_char(v.value,fnd_currency.get_format_mask(v.currency_code,40)),1,40), v.currency_code, v.external_account_id, substrb(pay_jp_soe_pkg.messages_exist_flag(v.assignment_action_id,'A'),1,1) from ( select paa.assignment_action_id, ppp.pre_payment_id, ppt.category, ppttl.payment_type_name, opm.currency_code, opmtl.org_payment_method_name, ppm.priority, ppp.value, ppm.external_account_id, ( select pay_jp_soe_pkg.get_lock_action_num(ppp.pre_payment_id,'M','assignment_action_id') from dual ) m_assignment_action_id, ( select pay_jp_soe_pkg.get_lock_action_num(ppp.pre_payment_id,'M','object_version_number') from dual ) m_object_version_number, ( select pay_jp_soe_pkg.get_lock_action_num(ppp.pre_payment_id,'M','payroll_action_id') from dual ) m_payroll_action_id, ( select pay_jp_soe_pkg.get_lock_action_val(ppp.pre_payment_id,'M','action_type') from dual ) m_action_type, ( select pay_jp_soe_pkg.get_lock_action_val(ppp.pre_payment_id,'M','action_status') from dual ) m_action_status from pay_personal_payment_methods_f ppm, pay_payment_types_tl ppttl, pay_payment_types ppt, pay_org_payment_methods_f_tl opmtl, pay_org_payment_methods_f opm, pay_pre_payments ppp, pay_payroll_actions ppa, pay_assignment_actions paa where ppa.payroll_action_id = paa.payroll_action_id and ppp.assignment_action_id = paa.assignment_action_id and opm.org_payment_method_id = ppp.org_payment_method_id and ppa.effective_date between opm.effective_start_date and opm.effective_end_date and opmtl.org_payment_method_id = opm.org_payment_method_id and opmtl.language = userenv('LANG') and ppt.payment_type_id = opm.payment_type_id and ppttl.payment_type_id = ppt.payment_type_id and ppttl.language = userenv('LANG') and ppm.personal_payment_method_id(+) = ppp.personal_payment_method_id and ppa.effective_date between nvl(ppm.effective_start_date,ppa.effective_date) and nvl(ppm.effective_end_date,ppa.effective_date) ) v

View Text - HTML Formatted

SELECT V.ASSIGNMENT_ACTION_ID
, V.PRE_PAYMENT_ID
, V.M_ASSIGNMENT_ACTION_ID
, V.M_OBJECT_VERSION_NUMBER
, V.M_PAYROLL_ACTION_ID
, V.M_ACTION_TYPE
, SUBSTRB(HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, V.M_ACTION_TYPE)
, 1
, 80)
, V.M_ACTION_STATUS
, DECODE(V.M_ACTION_STATUS
, 'M'
, 'Y'
, 'N')
, V.CATEGORY
, V.PAYMENT_TYPE_NAME
, V.ORG_PAYMENT_METHOD_NAME
, V.PRIORITY
, V.VALUE
, SUBSTRB(TO_CHAR(V.VALUE
, FND_CURRENCY.GET_FORMAT_MASK(V.CURRENCY_CODE
, 40))
, 1
, 40)
, V.CURRENCY_CODE
, V.EXTERNAL_ACCOUNT_ID
, SUBSTRB(PAY_JP_SOE_PKG.MESSAGES_EXIST_FLAG(V.ASSIGNMENT_ACTION_ID
, 'A')
, 1
, 1)
FROM ( SELECT PAA.ASSIGNMENT_ACTION_ID
, PPP.PRE_PAYMENT_ID
, PPT.CATEGORY
, PPTTL.PAYMENT_TYPE_NAME
, OPM.CURRENCY_CODE
, OPMTL.ORG_PAYMENT_METHOD_NAME
, PPM.PRIORITY
, PPP.VALUE
, PPM.EXTERNAL_ACCOUNT_ID
, ( SELECT PAY_JP_SOE_PKG.GET_LOCK_ACTION_NUM(PPP.PRE_PAYMENT_ID
, 'M'
, 'ASSIGNMENT_ACTION_ID')
FROM DUAL ) M_ASSIGNMENT_ACTION_ID
, ( SELECT PAY_JP_SOE_PKG.GET_LOCK_ACTION_NUM(PPP.PRE_PAYMENT_ID
, 'M'
, 'OBJECT_VERSION_NUMBER')
FROM DUAL ) M_OBJECT_VERSION_NUMBER
, ( SELECT PAY_JP_SOE_PKG.GET_LOCK_ACTION_NUM(PPP.PRE_PAYMENT_ID
, 'M'
, 'PAYROLL_ACTION_ID')
FROM DUAL ) M_PAYROLL_ACTION_ID
, ( SELECT PAY_JP_SOE_PKG.GET_LOCK_ACTION_VAL(PPP.PRE_PAYMENT_ID
, 'M'
, 'ACTION_TYPE')
FROM DUAL ) M_ACTION_TYPE
, ( SELECT PAY_JP_SOE_PKG.GET_LOCK_ACTION_VAL(PPP.PRE_PAYMENT_ID
, 'M'
, 'ACTION_STATUS')
FROM DUAL ) M_ACTION_STATUS
FROM PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_PAYMENT_TYPES_TL PPTTL
, PAY_PAYMENT_TYPES PPT
, PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_ORG_PAYMENT_METHODS_F OPM
, PAY_PRE_PAYMENTS PPP
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPP.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PPA.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE
AND OPM.EFFECTIVE_END_DATE
AND OPMTL.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE = USERENV('LANG')
AND PPT.PAYMENT_TYPE_ID = OPM.PAYMENT_TYPE_ID
AND PPTTL.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID
AND PPTTL.LANGUAGE = USERENV('LANG')
AND PPM.PERSONAL_PAYMENT_METHOD_ID(+) = PPP.PERSONAL_PAYMENT_METHOD_ID
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PPM.EFFECTIVE_START_DATE
, PPA.EFFECTIVE_DATE)
AND NVL(PPM.EFFECTIVE_END_DATE
, PPA.EFFECTIVE_DATE) ) V