Payment : Query to Fetch 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(+)
   AND ppp.system_profile_code LIKE 'XX%';

Read More

Payment : QUery to get 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 
   AND payment_document_name LIKE '%' 
ORDER BY
   acct.bank_account_name,
   pmtdoc.payment_document_name;

Read More

Payment : Query to get 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';

Read More

Payment : To Pull PPR and Source XML

You can pull PPR and source XML details , which got 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);

Read More

Payment :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(+)
   AND apt.TEMPLATE_NAME LIKE 'XX%'
ORDER BY
   apt.template_name,
   ipmv.payment_method_name;

Read More