Payment Templates – Details of
Payment Templates defined:
SELECT apt.template_id, apt.template_name,
ipmv.payment_method_name
pmt_method_name,
cba.bank_account_name bank_acct_name,
ipp.payment_profile_name
pmt_profile_name,
cpd.payment_document_name
pmt_doc_name, apt.pay_group_option pay_grp_optn,
apt.ou_group_option ou_grp_optn,
apt.currency_group_option curr_grp_optn,
apt.description ppp_description,
apt.inactive_date, apt.addl_pay_thru_days,
apt.addl_pay_from_days,
apt.low_payment_priority, apt.hi_payment_priority,
apt.vendor_id,
apt.pay_only_when_due_flag,
apt.vendor_type_lookup_code
vdr_type_lcode, apt.bank_account_id,
apt.payment_profile_id,
apt.zero_inv_allowed_flag, apt.payment_method_code,
apt.inv_exchange_rate_type,
apt.payment_date_option, apt.addl_payment_days,
apt.payment_exchange_rate_type,
apt.zero_amounts_allowed,
apt.payables_review_settings,
apt.calc_awt_int_flag, apt.payments_review_settings,
apt.document_rejection_level_code
doc_reject_lvl, apt.create_instrs_flag,
apt.payment_rejection_level_code
pmt_reject_lvl, apt.payment_document_id,
plc.displayed_field
supplier_type, pv.vendor_name payee,
alc1.displayed_field
template_type_name, gdct.user_conversion_type user_rate_type,
fu.user_name
FROM ap_payment_templates apt,
po_lookup_codes plc, iby_payment_methods_vl ipmv,
iby_payment_profiles ipp,
ce_bank_accounts cba, ap_lookup_codes alc1,
gl_daily_conversion_types gdct,
po_vendors pv, fnd_user fu, ce_payment_documents cpd
WHERE fu.user_id =
apt.last_updated_by
AND plc.lookup_code(+) =
apt.vendor_type_lookup_code
AND plc.lookup_type(+) = 'VENDOR
TYPE'
AND cba.bank_account_id(+) =
apt.bank_Account_id
AND ipmv.payment_method_code(+)
= apt.payment_method_code
AND alc1.lookup_type(+) =
'PAYMENT_TEMPLATE_TYPE'
AND alc1.lookup_code(+) =
apt.template_type
AND gdct.conversion_type(+) =
apt.payment_exchange_rate_type
AND ipp.payment_profile_id(+) =
apt.payment_profile_id
AND pv.party_id(+) =
apt.party_id
AND apt.payment_document_id =
cpd.payment_document_id(+)
-- Uncomment to limit to
specific Payment Templates
-- AND apt.TEMPLATE_NAME LIKE
'J%'
ORDER BY apt.template_name, ipmv.payment_method_name;
Payment Process Profiles –
Verification of your PPP setup:
SELECT app.payment_profile_name, ppp.system_profile_code, ppp.payment_format_code,
pm.payment_method_name,
ppp.periodic_sequence_name_1 seq_name,
app.payment_profile_id pmt_prof,
app.reset_value_1 reset_val,
app.last_used_number_1
last_used, ppp.processing_type,
ppp.mark_complete_event
mrk_cmplt, ppp.outbound_pmt_file_directory,
ppp.outbound_pmt_file_prefix,
ppp.outbound_pmt_file_extension ext,
ppp.positive_pay_file_directory,
ppp.positive_pay_file_prefix,
ppp.positive_pay_file_extension
pp_ext, ppp.default_payment_document_id def_pmt_docid,
ppp.last_update_date,
sra.sra_override_payee_flag sra_ovrd_payee,
fmt.format_name sra_fmt,
sra.automatic_sra_submit_flag sra_autosub
FROM iby_sys_pmt_profiles_b ppp,
iby_acct_pmt_profiles_vl app,
iby_applicable_pmt_profs
appl_prof, iby_payment_methods_vl pm,
iby_remit_advice_setup sra,
iby_formats_vl fmt
WHERE 1 = 1
AND ppp.system_profile_code =
app.system_profile_code
AND ppp.system_profile_code =
appl_prof.system_profile_code(+)
AND
appl_prof.applicable_value_to = pm.payment_method_code(+)
AND NVL
(appl_prof.applicable_type_code, 'PAYMENT_METHOD') = 'PAYMENT_METHOD'
AND ppp.system_profile_code =
sra.system_profile_code(+)
AND
sra.remittance_advice_format_code = fmt.format_code(+)
-- Only custom PPP’s, if this is
your naming convention
AND ppp.system_profile_code LIKE
'XX%';
Payment Documents – Setup data, by internal disbursing Bank Account:
SELECT acct.bank_account_name, acct.bank_account_num, acct.currency_code,
acct.multi_currency_allowed_flag
multi_curr, acct.payment_multi_currency_flag pmt_mc,
pmtdoc.payment_document_name
pmt_doc_name, fmt.format_name,
pmtdoc.first_available_document_num
first_avail_docno,
pmtdoc.last_available_document_number
last_avail_docno,
pmtdoc.last_issued_document_number
last_issued_docno,
pmtdoc.payment_document_id
pmt_doc_id, pmtdoc.internal_bank_account_id,
pmtdoc.paper_stock_type,
pmtdoc.attached_remittance_stub_flag attchd_stub,
pmtdoc.number_of_lines_per_remit_stub
lines_per_stub, pmtdoc.format_code,
pmtdoc.inactive_date,
fu1.user_name created_by, pmtdoc.creation_date,
fu2.user_name last_updated_by,
pmtdoc.last_update_date,
DECODE (inactive_date, NULL,
'Y', 'N') status
FROM ce_payment_documents
pmtdoc, iby_formats_vl fmt, ce_bank_accounts acct,
fnd_user fu1, fnd_user fu2
WHERE pmtdoc.format_code =
fmt.format_code
AND
pmtdoc.internal_bank_account_id = acct.bank_account_id
AND pmtdoc.created_by =
fu1.user_id
AND pmtdoc.last_updated_by =
fu2.user_id
-- Uncomment to limit to a
certain document name
-- AND payment_document_name
LIKE '%'
ORDER BY acct.bank_account_name, pmtdoc.payment_document_name;
Layout Templates - Metadata and
RTF/eText files for all Outbound Payment Instruction Layout Templates:
SELECT xtv.application_short_name tmpl_app, xtv.template_code,
xtv.ds_app_short_name ds_app,
xtv.data_source_code, xtv.template_type_code tmpl_type,
xtv.default_language def_lang,
xtv.default_territory def_terr, xtv.template_status,
xtv.start_date, xtv.end_date,
xtv.template_name, xtv.description, xtv.created_by,
xtv.creation_date,
xtv.last_updated_by, xtv.last_update_date, xtv.last_update_login,
(SELECT application_name
FROM fnd_application_vl
WHERE application_short_name =
xtv.application_short_name) application_name,
(SELECT meaning
FROM fnd_lookups
WHERE lookup_type =
'XDO_TEMPLATE_TYPE'
AND lookup_code =
xtv.template_type_code) template_type,
(SELECT data_source_name
FROM xdo_ds_definitions_vl
WHERE data_source_code =
xtv.data_source_code
AND application_short_name =
xtv.ds_app_short_name) data_source_name,
(SELECT file_name
FROM xdo_lobs
WHERE ( ( lob_type = 'TEMPLATE'
AND xdo_file_type != 'RTF'
AND xdo_file_type =
xtv.template_type_code
AND xdo_lobs.LANGUAGE =
xtv.default_language
AND xdo_lobs.territory =
xtv.default_territory)
OR ( lob_type =
'TEMPLATE_SOURCE'
AND xdo_file_type IN ('RTF',
'RTF-ETEXT')
AND xdo_lobs.LANGUAGE =
xtv.default_language
AND xdo_lobs.territory =
xtv.default_territory)
OR ( xdo_file_type = 'RTF'
AND lob_type = 'TEMPLATE_SOURCE'
AND LANGUAGE = xtv.mls_language
AND territory =
xtv.mls_territory
AND EXISTS
(SELECT mls.lob_code
FROM xdo_lobs mls
WHERE mls.lob_type =
'MLS_TEMPLATE'
AND mls.lob_code =
xtv.template_code
AND mls.application_short_name =
xtv.application_short_name
AND mls.LANGUAGE =
xtv.default_language
AND mls.territory =
xtv.default_territory)
AND NOT EXISTS
(SELECT LOCAL.lob_code
FROM xdo_lobs LOCAL
WHERE LOCAL.lob_type =
'TEMPLATE_SOURCE'
AND LOCAL.lob_code =
xtv.template_code
AND LOCAL.application_short_name
= xtv.application_short_name
AND LOCAL.LANGUAGE =
xtv.default_language
AND LOCAL.territory =
xtv.default_territory)))
AND lob_code = xtv.template_code
AND
xdo_lobs.application_short_name = xtv.application_short_name)
AS default_template_file,
(SELECT file_name FROM xdo_lobs
WHERE lob_type =
'TEMPLATE_SOURCE' AND lob_code = xtv.template_code
AND xdo_lobs.application_short_name
=xtv.application_short_name
AND xdo_lobs.LANGUAGE =
xtv.mls_language
AND xdo_lobs.territory =
xtv.mls_territory) AS mls_template_file,
(SELECT NAME
FROM fnd_iso_languages_vl
WHERE iso_language_2 =
xtv.default_language) AS default_file_lang,
DECODE (xtv.default_territory,
'00', '', ftv.territory_short_name) AS default_file_terr,
xtv.mls_language,
xtv.mls_territory, xtv.default_output_type
FROM xdo_templates_vl xtv,
fnd_application_vl fav, fnd_territories_vl ftv
WHERE 1 = 1
AND fav.application_short_name =
xtv.application_short_name
AND ftv.territory_code(+) =
xtv.default_territory
-- Only Outbound Payment
Instructions
AND xtv.application_short_name =
'IBY'
AND data_source_code =
'IBY_FD_INSTRUCTION_1_0';
PPR and Source XML – Payment
Process Requests formatted today, with the source XML:
SELECT trxnmid,
EXTRACTVALUE (
XMLType (document),
'/OutboundPaymentInstruction/PaymentInstructionInfo/UserAssignedRefCode')
ppr_name,
EXTRACTVALUE (
XMLType (document),
'/OutboundPaymentInstruction/PaymentInstructionInfo/FormatProgramRequestID')
fmt_request_id,
EXTRACTVALUE (
XMLType (document),
'/OutboundPaymentInstruction/PaymentProcessProfile/PaymentProcessProfileName')
ppp_name,
EXTRACTVALUE (
XMLType (document),
'/OutboundPaymentInstruction/CheckFormatInfo/PaymentDocumentName')
pmt_doc_name,
EXTRACTVALUE (
XMLType (document),
'/OutboundPaymentInstruction/InstructionTotals/PaymentCount')
pmt_cnt,
document raw_xml
FROM iby_trxn_documents itd
WHERE 1 = 1
AND itd.creation_date > TRUNC
(SYSDATE);
No comments:
Post a Comment