[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT delivery_channel_code,
meaning,
description,
territory_code
FROM IBY_DELIVERY_CHANNELS_VL ibydlv
WHERE (ibydlv.territory_code = p_payer_country OR ibydlv.territory_code is NULL)
AND (ibydlv.inactive_date is NULL OR ibydlv.inactive_date >= trunc(sysdate));
SELECT xle.country
FROM XLE_FIRSTPARTY_INFORMATION_V xle
WHERE xle.legal_entity_id = p_payer_le_id;
SELECT DISTINCT b.bank_account_name,
b.ext_bank_account_id,
b.bank_account_number,
b.currency_code,
b.iban_number,
b.bank_name,
b.bank_number,
b.bank_branch_name,
b.branch_number,
b.country_code,
b.alternate_account_name,
b.bank_account_type,
b.account_suffix,
b.description,
b.foreign_payment_use_flag,
b.payment_factor_flag,
b.eft_swift_code
FROM IBY_PMT_INSTR_USES_ALL ibyu,
IBY_EXT_BANK_ACCOUNTS_V b,
IBY_EXTERNAL_PAYEES_ALL ibypayee
WHERE ibyu.instrument_id = b.ext_bank_account_id
AND ibyu.instrument_type = 'BANKACCOUNT'
AND (b.currency_code = p_payment_currency OR b.currency_code is null)
AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
AND ibypayee.payment_function = p_payment_function
AND ibypayee.payee_party_id = p_payee_party_id
AND trunc(sysdate) between
NVL(ibyu.start_date,trunc(sysdate)) AND NVL(ibyu.end_date-1,trunc(sysdate))
AND trunc(sysdate) between
NVL(b.start_date,trunc(sysdate)) AND NVL(b.end_date-1,trunc(sysdate))
AND (ibypayee.party_site_id is null OR ibypayee.party_site_id = p_payee_party_site_id)
AND (ibypayee.supplier_site_id is null OR ibypayee.supplier_site_id = p_supplier_site_id)
AND (ibypayee.org_id is null OR
(ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type));
SELECT f.format_name,
f.format_code
FROM IBY_PAYMENT_PROFILES p,
IBY_FORMATS_VL f,
IBY_APPLICABLE_PMT_PROFS apf,
IBY_APPLICABLE_PMT_PROFS apm,
IBY_PAYMENT_METHODS_B m
WHERE f.format_code = p.payment_format_code
AND apf.system_profile_code = p.system_profile_code
AND (apf.applicable_type_code = APL_TYPE_PMT_FORMAT AND
(apf.applicable_value_to = f.format_code OR
apf.applicable_value_to IS NULL))
AND apm.system_profile_code = p.system_profile_code
AND (m.inactive_date is null OR m.inactive_date >= trunc(sysdate))
AND apm.applicable_type_code = APL_TYPE_PMT_METHOD
AND apm.applicable_value_to = m.payment_method_code;
SELECT m.Payment_Method_Name,
am.PAYMENT_METHOD_CODE,
m.SUPPORT_BILLS_PAYABLE_FLAG,
m.MATURITY_DATE_OFFSET_DAYS,
m.DESCRIPTION
FROM IBY_APPLICABLE_PMT_MTHDS am,
IBY_PAYMENT_METHODS_VL m
WHERE am.PAYMENT_FLOW = 'DISBURSEMENTS'
AND am.APPLICABLE_TYPE_CODE = 'PAYEE'
AND am.APPLICABLE_VALUE_TO is null
AND (am.APPLICATION_ID is null OR am.APPLICATION_ID = p_application_id)
AND (m.INACTIVE_DATE is null OR m.INACTIVE_DATE >= trunc(sysdate))
AND (am.INACTIVE_DATE is null OR am.INACTIVE_DATE >= trunc(sysdate))
AND am.PAYMENT_METHOD_CODE = m.PAYMENT_METHOD_CODE
AND NOT EXISTS (select 1
from IBY_EXT_PARTY_PMT_MTHDS ppm,
IBY_EXTERNAL_PAYEES_ALL payee
where ppm.PAYMENT_FLOW = 'DISBURSEMENTS'
and ppm.PAYMENT_METHOD_CODE = am.PAYMENT_METHOD_CODE
and ppm.PAYMENT_FUNCTION = p_payment_function
and ppm.INACTIVE_DATE < trunc(sysdate)
and ppm.EXT_PMT_PARTY_ID = payee.EXT_PAYEE_ID
and payee.PAYEE_PARTY_ID = p_payee_party_id
AND (payee.PARTY_SITE_ID is null OR payee.PARTY_SITE_ID = p_payee_psite_id)
AND (payee.SUPPLIER_SITE_ID is null OR payee.SUPPLIER_SITE_ID = p_supplier_site_id)
AND (payee.ORG_ID is null OR (payee.ORG_ID = p_org_id AND payee.ORG_TYPE = p_org_type)))
AND p_ignore_flag = 'N'
UNION
SELECT m.Payment_Method_Name,
ppm.PAYMENT_METHOD_CODE,
m.SUPPORT_BILLS_PAYABLE_FLAG,
m.MATURITY_DATE_OFFSET_DAYS,
m.DESCRIPTION
FROM IBY_EXT_PARTY_PMT_MTHDS ppm,
IBY_EXTERNAL_PAYEES_ALL payee,
IBY_PAYMENT_METHODS_VL m
WHERE ppm.PAYMENT_FLOW = 'DISBURSEMENTS'
AND ppm.PAYMENT_FUNCTION = p_payment_function
AND (m.INACTIVE_DATE is null OR m.INACTIVE_DATE >= trunc(sysdate))
AND (ppm.INACTIVE_DATE is null OR ppm.INACTIVE_DATE >= trunc(sysdate))
AND ppm.PAYMENT_METHOD_CODE = m.PAYMENT_METHOD_CODE
AND ppm.EXT_PMT_PARTY_ID = payee.EXT_PAYEE_ID
AND payee.PAYEE_PARTY_ID = p_payee_party_id
AND (payee.PARTY_SITE_ID is null OR payee.PARTY_SITE_ID = p_payee_psite_id)
AND (payee.SUPPLIER_SITE_ID is null OR payee.SUPPLIER_SITE_ID = p_supplier_site_id)
AND (payee.ORG_ID is null OR (payee.ORG_ID = p_org_id AND payee.ORG_TYPE = p_org_type))
AND p_ignore_flag = 'N'
UNION
SELECT pmthds.Payment_Method_Name,
pmthds.Payment_Method_Code,
pmthds.SUPPORT_BILLS_PAYABLE_FLAG,
pmthds.MATURITY_DATE_OFFSET_DAYS,
pmthds.DESCRIPTION
FROM IBY_PAYMENT_METHODS_VL pmthds
WHERE (pmthds.inactive_date is NULL OR pmthds.inactive_date >= trunc(sysdate))
AND NOT EXISTS (select 1
from IBY_EXT_PARTY_PMT_MTHDS ppm,
IBY_EXTERNAL_PAYEES_ALL payee
where ppm.PAYMENT_FLOW = 'DISBURSEMENTS'
and PAYMENT_METHOD_CODE = pmthds.PAYMENT_METHOD_CODE
and ppm.PAYMENT_FUNCTION = p_payment_function
and ppm.INACTIVE_DATE < trunc(sysdate)
and ppm.EXT_PMT_PARTY_ID = payee.EXT_PAYEE_ID
and payee.PAYEE_PARTY_ID = p_payee_party_id
AND (payee.PARTY_SITE_ID is null OR payee.PARTY_SITE_ID = p_payee_psite_id)
AND (payee.SUPPLIER_SITE_ID is null OR payee.SUPPLIER_SITE_ID = p_supplier_site_id)
AND (payee.ORG_ID is null OR (payee.ORG_ID = p_org_id AND payee.ORG_TYPE = p_org_type)))
AND p_ignore_flag = 'Y';
SELECT Payment_Method_Code,
Applicable_Type_Code,
Applicable_Value_From,
Applicable_Value_To
FROM IBY_APPLICABLE_PMT_MTHDS apmthds
WHERE apmthds.Payment_method_code = p_payment_method_code
AND (apmthds.application_id = p_application_id
OR apmthds.application_id is NULL);
SELECT xlev.country,
glv.currency_code
FROM XLE_FIRSTPARTY_INFORMATION_V xlev,
GL_LEDGER_LE_V glv
WHERE xlev.legal_entity_id = glv.legal_entity_id
AND glv.ledger_category_code = 'PRIMARY'
AND xlev.legal_entity_id = p_payer_le_id;
SELECT country
FROM HZ_PARTIES
WHERE party_id = p_payee_id;
SELECT locs.country
FROM HZ_PARTY_SITES sites,
HZ_LOCATIONS locs
WHERE sites.party_id = p_payee_id
AND sites.party_site_id = p_payee_site_id
AND sites.location_id = locs.location_id;
* We need to select payment profiles that are applicable to
* given (payment method, org, format, currency, int bank account).
*
*
* | Profiles |
* | applicable to |
* | given pmt | Profiles applicable to
* | method | given payment currency
* | | /
* | | | /
* | V | L
* | |
* |----------------|--------------------------
* |/ \| Profiles
* | | applicable to
* | Intersection | <-- given
* | | org
* |\ /|
* |----------------|--------------------------
* | |
* | | .__
* | ^ | |\
* | | | \
* | | |
* | | Profiles applicable to
* | Profiles | given internal bank
* | applicable to | account
* | given format |
* | |
*
* We need the intersection of (profiles applicable to
* a given payment method) and (profiles applicable to
* a given org) and (profiles applicable to a given
* format) and (profiles applicable to given payment
* currency) and (profiles applicable to given internal
* bank account) as shown in the graphic.
*
* Therefore, we need to join with the IBY_APPLICABLE_PMT_PROFS
* five times - once to get the profiles for the method, once to get
* the profiles for the org, and once to get the profiles for the
* format etc. If we are able to get a non-null intersect for these
* five queries, it means that there is a profile that matches the
* (org, method, format, currency, bank acct) combination.
*
* If the 'applicable_value_to' is set to NULL, it means that the
* profile is applicable to 'all orgs' | 'all methods' |
* 'all formats' etc., depending upon the applicable_type_code.
* Therefore, we need to factor this condition in the join.
*
* Payment format is not a driving item for payment profile.
*/
CURSOR c_profiles(
p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.
payment_method_code%TYPE,
p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.
payment_currency_code%TYPE,
p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.
internal_bank_account_id%TYPE
)
IS
SELECT
prof.payment_profile_id,
prof.payment_profile_name,
prof.processing_type
FROM
IBY_APPLICABLE_PMT_PROFS app1,
IBY_APPLICABLE_PMT_PROFS app2,
IBY_APPLICABLE_PMT_PROFS app3,
IBY_APPLICABLE_PMT_PROFS app4,
IBY_PAYMENT_PROFILES prof
WHERE
(app1.applicable_type_code=APL_TYPE_PAYER_ORG AND
((app1.applicable_value_to=TO_CHAR(p_org_id) AND
app1.applicable_value_from=p_org_type) OR
(app1.applicable_value_to IS NULL AND
app1.applicable_value_from IS NULL)) )
AND (app2.applicable_type_code=APL_TYPE_PMT_METHOD AND
(app2.applicable_value_to=p_pmt_method_cd OR
app2.applicable_value_to IS NULL))
AND (app3.applicable_type_code=APL_TYPE_PMT_CURRENCY AND
(app3.applicable_value_to=p_pmt_currency OR
app3.applicable_value_to IS NULL))
AND (app4.applicable_type_code=APL_TYPE_INT_BANK_ACCT AND
(app4.applicable_value_to=TO_CHAR(p_int_bank_acct_id) OR
app4.applicable_value_to IS NULL))
AND app1.system_profile_code=app2.system_profile_code
AND app2.system_profile_code=app3.system_profile_code
AND app3.system_profile_code=app4.system_profile_code
AND app4.system_profile_code=app1.system_profile_code
AND app1.system_profile_code=prof.system_profile_code
/*
* Fix for bug 5929889:
*
* Filter profiles by inactive date so that we do not
* pick up end-dated profiles.
*/
AND NVL(prof.inactive_date, SYSDATE + 1) > SYSDATE
;
l_prof_intsct_tab.DELETE(k);
SELECT payment_reason_code,
description,
meaning,
territory_code
FROM IBY_PAYMENT_REASONS_VL ibypr
WHERE (ibypr.territory_code = p_payer_country OR ibypr.territory_code is NULL)
AND (ibypr.inactive_date is NULL OR ibypr.inactive_date >= trunc(sysdate));
SELECT xle.country
FROM XLE_FIRSTPARTY_INFORMATION_V xle
WHERE xle.legal_entity_id = p_payer_le_id;
SELECT payment_method_at_payee_flag
FROM IBY_INTERNAL_PAYERS_ALL
WHERE org_id is null;
SELECT payment_method_at_payee_flag
FROM IBY_INTERNAL_PAYERS_ALL
WHERE org_id = p_org_id
AND org_type = p_org_type;
SELECT pm.payment_method_code,
m.payment_method_name,
m.SUPPORT_BILLS_PAYABLE_FLAG,
m.MATURITY_DATE_OFFSET_DAYS,
payee.payment_format_code,
f.format_name,
payee.bank_charge_bearer,
payee.delivery_channel_code,
d.meaning delivery_channel_meaning,
d.description delivery_channel_description,
payee.payment_reason_code,
r.meaning payment_reason,
r.description payment_reason_description,
payee.payment_reason_comments,
payee.exclusive_payment_flag,
payee.settlement_priority,
d.territory_code delivery_channel_country,
r.territory_code payment_reason_country
FROM IBY_EXTERNAL_PAYEES_ALL payee,
IBY_EXT_PARTY_PMT_MTHDS pm,
IBY_PAYMENT_METHODS_VL m,
IBY_FORMATS_VL f,
IBY_DELIVERY_CHANNELS_VL d,
IBY_PAYMENT_REASONS_VL r,
IBY_APPLICABLE_PMT_MTHDS am1,
IBY_APPLICABLE_PMT_MTHDS am2,
IBY_APPLICABLE_PMT_MTHDS am3
WHERE payee.payee_party_id = p_payee_party_id
AND am1.PAYMENT_METHOD_CODE(+) = m.PAYMENT_METHOD_CODE
AND am1.PAYMENT_FLOW(+) = 'DISBURSEMENTS'
AND am1.APPLICABLE_TYPE_CODE(+) = 'PAY_PROC_TRXN_TYPE'
AND (am1.APPLICABLE_VALUE_TO is null OR
am1.APPLICABLE_VALUE_TO=p_Pay_Proc_Trxn_Type_Code )
AND (am1.INACTIVE_DATE is null OR am1.INACTIVE_DATE >= trunc(sysdate))
AND am2.PAYMENT_METHOD_CODE(+) = am1.PAYMENT_METHOD_CODE
AND am2.APPLICABLE_TYPE_CODE(+) = 'PAYER_LE'
AND (am2.APPLICABLE_VALUE_TO is null OR am2.APPLICABLE_VALUE_TO=p_payer_le_id )
AND (am2.INACTIVE_DATE is null OR am2.INACTIVE_DATE >= trunc(sysdate))
AND am3.PAYMENT_METHOD_CODE(+) = am2.PAYMENT_METHOD_CODE
AND am3.APPLICABLE_TYPE_CODE(+) = 'PAYER_ORG'
AND (am3.APPLICABLE_VALUE_TO is null OR am3.APPLICABLE_VALUE_TO=p_org_id )
AND (am3.INACTIVE_DATE is null OR am3.INACTIVE_DATE >= trunc(sysdate))
AND payee.payment_function = p_payment_function
AND payee.ext_payee_id = pm.ext_pmt_party_id(+)
AND pm.payment_method_code = m.payment_method_code(+)
AND pm.payment_function(+) = p_payment_function
AND pm.primary_flag(+) = 'Y'
AND (pm.inactive_date is null OR pm.inactive_date >= trunc(sysdate))
AND (m.inactive_date is null OR m.inactive_date >= trunc(sysdate))
AND payee.payment_format_code = f.format_code(+)
AND payee.delivery_channel_code = d.delivery_channel_code(+)
AND payee.payment_reason_code = r.payment_reason_code(+)
AND (payee.org_id is NULL
OR (payee.org_id = p_org_id AND payee.org_type = p_org_type))
AND (payee.party_site_id is NULL OR payee.party_site_id = p_payee_party_site_id)
AND (payee.supplier_site_id is NULL OR payee.supplier_site_id = p_supplier_site_id)
ORDER by payee.supplier_site_id,
payee.party_site_id,
payee.org_id;
SELECT
payee.payment_format_code,
f.format_name,
payee.bank_charge_bearer,
payee.delivery_channel_code,
d.meaning delivery_channel_meaning,
d.description delivery_channel_description,
payee.payment_reason_code,
r.meaning payment_reason,
r.description payment_reason_description,
payee.payment_reason_comments,
payee.exclusive_payment_flag,
payee.settlement_priority,
d.territory_code delivery_channel_country,
r.territory_code payment_reason_country
FROM
IBY_EXTERNAL_PAYEES_ALL payee,
IBY_FORMATS_VL f,
IBY_DELIVERY_CHANNELS_VL d,
IBY_PAYMENT_REASONS_VL r
WHERE
payee.ext_payee_id IN
(
p_payee1,
p_payee2,
p_payee3,
p_payee4
)
AND payee.payment_format_code = f.format_code(+)
AND payee.delivery_channel_code = d.delivery_channel_code(+)
AND payee.payment_reason_code = r.payment_reason_code(+)
ORDER BY
payee.supplier_site_id,
payee.party_site_id,
payee.org_id
;
SELECT
pm.payment_method_code,
m.payment_method_name,
m.support_bills_payable_flag,
m.maturity_date_offset_days
FROM
IBY_EXTERNAL_PAYEES_ALL payee,
IBY_EXT_PARTY_PMT_MTHDS pm,
IBY_PAYMENT_METHODS_VL m
WHERE
payee.ext_payee_id IN (
p_payee1,
p_payee2,
p_payee3,
p_payee4
)
AND payee.payment_function = p_payment_function
AND payee.ext_payee_id = pm.ext_pmt_party_id
AND pm.payment_method_code = m.payment_method_code
AND pm.payment_function = p_payment_function
AND pm.primary_flag = 'Y'
AND (pm.inactive_date IS NULL OR pm.inactive_date >= trunc(sysdate))
AND (m.inactive_date IS NULL OR m.inactive_date >= trunc(sysdate))
AND EXISTS (SELECT 1 FROM IBY_APPLICABLE_PMT_MTHDS am1
WHERE am1.payment_method_code = m.payment_method_code
AND am1.payment_flow(+) = 'DISBURSEMENTS'
AND am1.applicable_type_code = 'PAY_PROC_TRXN_TYPE'
AND (am1.applicable_value_to IS NULL OR
am1.applicable_value_to=p_pay_proc_trxn_type_code)
AND (am1.inactive_date IS null OR
am1.inactive_date >= trunc(sysdate)))
AND EXISTS (SELECT 1 FROM IBY_APPLICABLE_PMT_MTHDS am2
WHERE am2.payment_method_code = m.payment_method_code
AND am2.applicable_type_code = 'PAYER_LE'
AND (am2.applicable_value_to IS NULL OR
am2.applicable_value_to=p_payer_le_id)
AND (am2.inactive_date IS NULL OR
am2.inactive_date >= trunc(sysdate)))
AND EXISTS (SELECT 1 FROM IBY_APPLICABLE_PMT_MTHDS am3
WHERE am3.payment_method_code = m.payment_method_code
AND am3.applicable_type_code = 'PAYER_ORG'
AND (am3.applicable_value_to IS NULL OR
am3.applicable_value_to=p_org_id)
AND (am3.inactive_date IS NULL OR
am3.inactive_date >= trunc(sysdate))
)
ORDER BY
payee.supplier_site_id,
payee.party_site_id,
payee.org_id
;
SELECT b.bank_account_name,
b.ext_bank_account_id,
b.bank_account_number,
b.currency_code,
b.iban_number,
b.bank_name,
b.bank_number,
b.bank_branch_name,
b.branch_number,
b.country_code,
b.alternate_account_name,
b.bank_account_type,
b.account_suffix,
b.description,
b.foreign_payment_use_flag,
b.payment_factor_flag,
b.eft_swift_code
FROM IBY_PMT_INSTR_USES_ALL ibyu,
IBY_EXT_BANK_ACCOUNTS_V b,
IBY_EXTERNAL_PAYEES_ALL ibypayee
WHERE ibyu.instrument_id = b.ext_bank_account_id
AND ibyu.instrument_type = 'BANKACCOUNT'
AND (b.currency_code = p_payment_currency
OR b.currency_code is null)
AND ibyu.payment_function = p_payment_function
AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
AND ibypayee.payee_party_id = p_payee_party_id
AND trunc(sysdate) between NVL(trunc(ibyu.start_date),trunc(sysdate)) AND
NVL(trunc(ibyu.end_date-1),trunc(sysdate))
AND trunc(sysdate) between NVL(trunc(b.start_date),trunc(sysdate)) AND
NVL(trunc(b.end_date-1),trunc(sysdate))
AND (ibypayee.party_site_id is NULL OR ibypayee.party_site_id = p_payee_party_site_id)
AND (ibypayee.supplier_site_id is NULL OR ibypayee.supplier_site_id = p_supplier_site_id)
AND (ibypayee.org_id is null
OR (ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type) )
/*
* Fix for bug 5505267:
*
* Add payment flow predicate to improve
* performance.
*/
AND ibyu.payment_flow='DISBURSEMENTS'
ORDER by ibypayee.supplier_site_id,
ibypayee.party_site_id,
ibypayee.org_id,
ibyu.order_of_preference;
SELECT lookup_code,
meaning,
description
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND (trunc(sysdate) BETWEEN NVL(start_date_active,trunc(sysdate)) AND
NVL(end_date_active,trunc(sysdate)));
* Update:
*
* We need to pick up all possible ext payee ids based on the
* following chart. This means that there can be upto 4 possible
* ext payee ids in the select statement.
*
* EXT PARTY ID PRECEDENCE CHART
* -------------------------------------------------------------
* | | | | |
* | supp | org | party | party |
* | site | | site | |
* -------------------------------------------------------------
* | | | | |
* supp site | Y | Y | Y | Y |
* | | | | |
* -------------------------------------------------------------
* | | | | |
* party site (org) | null | Y | Y | Y |
* | | | | |
* -------------------------------------------------------------
* | | | | |
* party site (no org) | null | null | Y | Y |
* | | | | |
* -------------------------------------------------------------
* | | | | |
* party | null | null | null | Y |
* | | | | |
* --------------------------------------------------------------
*
* Y = value provided, null = no value provided
*
* Matching by supp site has the highest precedence
* Matching by party alone has the lowest precedence
*
*/
/*
* exact context:
* supplier site level
*/
l_payee1 := IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext(
p_trxn_attributes_rec.Payee_Party_Id,
p_trxn_attributes_rec.Payee_Party_Site_Id,
p_trxn_attributes_rec.Supplier_Site_Id,
p_trxn_attributes_rec.Payer_Org_Id,
p_trxn_attributes_rec.Payer_Org_Type,
p_trxn_attributes_rec.Payment_Function
);
SELECT b.bank_account_name,
b.ext_bank_account_id,
b.bank_account_number,
b.currency_code,
b.iban_number,
b.bank_name,
b.bank_number,
b.bank_branch_name,
b.branch_number,
b.country_code,
b.alternate_account_name,
b.bank_account_type,
b.account_suffix,
b.description,
b.foreign_payment_use_flag,
b.payment_factor_flag,
b.eft_swift_code
FROM IBY_PMT_INSTR_USES_ALL ibyu,
IBY_EXT_BANK_ACCOUNTS_V b,
IBY_EXTERNAL_PAYEES_ALL ibypayee
WHERE ibyu.instrument_id = b.ext_bank_account_id
AND ibyu.instrument_type = 'BANKACCOUNT'
AND (b.currency_code = p_payment_currency
OR b.currency_code is null)
AND ibyu.payment_function = p_payment_function
AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
AND ibypayee.payee_party_id = p_payee_party_id
AND trunc(sysdate) between NVL(ibyu.start_date,trunc(sysdate)) AND
NVL(ibyu.end_date-1,trunc(sysdate))
AND trunc(sysdate) between NVL(b.start_date,trunc(sysdate)) AND
NVL(b.end_date-1,trunc(sysdate))
AND (ibypayee.party_site_id is null
OR ibypayee.party_site_id = p_payee_party_site_id)
AND (ibypayee.supplier_site_id is NULL OR ibypayee.supplier_site_id = p_supplier_site_id) --Bug 9194531
AND (ibypayee.org_id is null
OR (ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type) )
AND ibyu.payment_flow='DISBURSEMENTS'
ORDER by ibypayee.supplier_site_id,
ibypayee.party_site_id,
ibypayee.org_id,
ibyu.order_of_preference;
SELECT payment_reason_comnt_apl_flag,
remittance_message1_apl_flag,
remittance_message2_apl_flag,
remittance_message3_apl_flag,
unique_remittance_id_apl_flag,
uri_check_digit_apl_flag,
payment_format_apl_flag,
delivery_channel_apl_flag,
bank_charge_bearer_apl_flag,
settlement_priority_apl_flag,
payment_reason_apl_flag,
external_bank_acct_apl_flag,
exclusive_pmt_apl_flag,
inactive_date
FROM IBY_PAYMENT_METHODS_B
WHERE payment_method_code = p_payment_method_code;
SELECT ibypmtrules.payment_rule_id,
ibypmtmthds.payment_method_code,
ibypmtmthds.payment_method_name,
ibypmtmthds.support_bills_payable_flag,
ibypmtmthds.maturity_date_offset_days
FROM IBY_PAYMENT_RULES ibypmtrules,
IBY_PAYMENT_METHODS_VL ibypmtmthds,
IBY_APPLICABLE_PMT_MTHDS am1,
IBY_APPLICABLE_PMT_MTHDS am2,
IBY_APPLICABLE_PMT_MTHDS am3
WHERE ibypmtrules.payment_method_code = ibypmtmthds.payment_method_code
AND ibypmtrules.application_id = p_application_id
AND NVL(ibypmtmthds.inactive_date,trunc(sysdate)) >= trunc(sysdate)
AND am1.payment_method_code=ibypmtmthds.payment_method_code
AND am1.applicable_type_code='PAY_PROC_TRXN_TYPE'
AND am1.application_id= p_application_id
AND (am1.applicable_value_to is null or
am1.applicable_value_to=p_pay_proc_trxn_type)
AND am2.payment_method_code=am1.payment_method_code
AND am2.applicable_type_code='PAYER_LE'
AND am2.application_id= p_application_id
AND (am2.applicable_value_to is null or
am2.applicable_value_to=p_payer_le_id)
AND am3.payment_method_code=am2.payment_method_code
AND am3.applicable_type_code='PAYER_ORG'
AND am3.application_id= p_application_id
AND (am3.applicable_value_to is null or
am3.applicable_value_to=p_payer_org_id)
ORDER BY ibypmtrules.payment_rule_priority;
SELECT rule_condition_type_code,
operator_code,
rule_condition_value
FROM IBY_RULE_CONDITIONS ibyruleconds
WHERE ibyruleconds.payment_rule_id = p_payment_rule_id;
SELECT xlev.country,
glv.currency_code
FROM XLE_FIRSTPARTY_INFORMATION_V xlev,
GL_LEDGER_LE_V glv
WHERE xlev.legal_entity_id = glv.legal_entity_id
AND glv.ledger_category_code = 'PRIMARY'
AND xlev.legal_entity_id = p_le_id;
SELECT country
FROM HZ_PARTIES
WHERE party_id = p_payee_id;
SELECT locs.country
FROM HZ_PARTY_SITES sites,
HZ_LOCATIONS locs
WHERE sites.party_id = p_payee_id
AND sites.party_site_id = p_payee_site_id
AND sites.location_id = locs.location_id;
SELECT b.bank_account_name,
b.ext_bank_account_id,
b.bank_account_number,
b.currency_code,
b.iban_number,
b.bank_name,
b.bank_number,
b.bank_branch_name,
b.branch_number,
b.country_code,
b.alternate_account_name,
b.bank_account_type,
b.account_suffix,
b.description,
b.foreign_payment_use_flag,
b.payment_factor_flag,
b.eft_swift_code
FROM IBY_PMT_INSTR_USES_ALL ibyu,
IBY_EXT_BANK_ACCOUNTS_V b,
IBY_EXTERNAL_PAYEES_ALL ibypayee
WHERE ibyu.instrument_id = b.ext_bank_account_id
AND ibyu.instrument_type = 'BANKACCOUNT'
AND (b.currency_code = p_payment_currency OR b.currency_code is null)
AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
AND ibypayee.payment_function = p_payment_function
AND ibypayee.payee_party_id = p_payee_party_id
AND trunc(sysdate) between
trunc(NVL(ibyu.start_date,sysdate)) AND trunc(NVL(ibyu.end_date-1,sysdate))
AND trunc(sysdate) between
trunc(NVL(b.start_date,sysdate)) AND trunc(NVL(b.end_date-1,sysdate))
AND (ibypayee.party_site_id is null OR ibypayee.party_site_id = p_payee_party_site_id)
AND (ibypayee.supplier_site_id is null OR ibypayee.supplier_site_id = p_supplier_site_id)
AND (ibypayee.org_id is null OR
(ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type))
AND ibyu.payment_flow='DISBURSEMENTS'
ORDER BY
ibypayee.supplier_site_id,
ibypayee.party_site_id,
ibypayee.org_id,
ibyu.order_of_preference;