Charge Account
===============
Accounts Payable Trial Balance Overview
=======================================
ACH & Wire Transfer
===================
EXTERNAL_BANK_ACCOUNT_ID
========================
Pay Alone
=========
RELEASE_DATE
============
Pay Site
========
ACH - CTX(Corporate Trade Exchange) Format
==========================================
Remittance Advice
=================
OIE:CC Payment Notify
=====================
AP Recurring Invoice
====================
To download XML Payment Instructions
====================================
==========================
IBY_FD_EXTRACT_EXT_PUB===============
An account where a specified amount of funds are made available for one to use in purchasing various items on credit. A 'line of credit' is generally issued and cannot be exceeded under normal circumstances. in other words,an account, especially in retailing, that permits a customer to buy merchandise and be billed for it at a later date.
Accounts Payable Trial Balance Overview
=======================================
The Accounts Payable Trial Balance Report is used to verify that the total accounts payable liabilities in Oracle Payables is equal to those in the General Ledger. Before closing a period, you can compare the cumulative total liability provided by this report with the total liability provided by General Ledger to reconcile these balances. In Oracle General Ledger, you can retrieve account liability information from the Account Inquiry form, the General Ledger Detail report, or from the Account Analysis Report with Payables Detail. Since this Trial Balance report lists your outstanding accounts payable liability information, it is only valid for an accrual set of books. The report lists and subtotals by supplier all unpaid and partially paid invoices with a GL date on or before the “As of Date” parameter. The report also lists and subtotals the invoices by the Accounts Payable Liability account. When you run the Payables Transfer to General Ledger process, the Trial Balance report will reflect the transactions that you have transferred to GL. It is not until you have successfully run the Journal Import and posted the resulting Journal Entry that General Ledger will also reflect the transactions that you have transferred from Payables. The Trial Balance report prints the difference between the sum of an invoice's distributions and the sum of the posted payment distribution, as expressed in your functional currency. A positive amount indicates an outstanding accounts payable liability in the General Ledger if you have posted the invoice (i.e. run AP Transfer to GL and Journal Import, posting the resulting Journal Entry). This can result from nonpayment, partial payment, or not creating a journal entry for the payment.A negative amount can result from transferring a payment to your General Ledger without transferring the invoice. The Posted Invoice Register, the Posted Payment Register, and the current and last period's Accounts Payable Trial Balance reports are used to reconcile your posted invoices and payments to your Accounts Payable Trial Balance to ensure that your Trial Balance accurately reflects your accounts payable liability. To calculate this, you add the current period's posted invoices (total invoice amount from the Posted Invoice Register) and subtract the current period's posted payments (total cash plus discounts taken from the Posted Payments Register) from the prior period's Accounts Payable Trial Balance. This amount should equal the balance for the current period's Accounts Payable Trial Balance. For example, you are closing your accounting period for April and you have just posted your final invoice and payment batches to your general ledger system. To reconcile your accounts payable activity for April,make the following calculation: Accounts Payable Trial Balance as of March 31 +Posted Invoice Register for the period between April 1 and April 30 -Posted Payment Register for the period between April 1 and April 30 = Accounts Payable Trial Balance as of April 30
ACH & Wire Transfer
===================
ACH transactions and wire transfers are often confused with each other. They are both electronic transfers of money, but they are not the same types of transactions. Wire transfers are typically used for transferring large dollar amounts of funds between banks very quickly. In the United States, wire transfers are processed through the Federal Reserve Wire Network. The acronym ACH can stand for “Automated Check Handling” or “Automated Clearinghouse.” ACH transactions are electronic checks, and are used for processing lower dollar amount payments. They are processed in a manner similar to that of paper checks, and do not travel directly from one bank to another. ACH transactions are processed through a clearing house divides that handles crediting and deducting accounts of the originating and paying banks. It typically takes one or two days to settle ACH transactions. Automated Clearing House (ACH): A domestic electronic funds transfer system. The National Automated Clearing House (NACHA) is responsible for maintaining and policing the electronic payments network. Electronic Funds Transfer (EFT): The movement of funds from one bank account directly into another without the use of cash or paper checks. ACH and wire transfers are two methods of EFT’s. Wire Transfer: A same day transfer of funds between financial institutions processed through the Federal Reserve’s Fedline system. International wires may be processed through the Society for Worldwide Interbank Financial Telecommuications S.W.I.F.T.).
EXTERNAL_BANK_ACCOUNT_ID
========================
For ACH,the bank accounts should be setup before the invoice created,because when invoice created it creates record in AP_PAYMENT_SCHEDULES_ALL and puts the supplier bank account id in EXTERNAL_BANK_ACCOUNT_ID column in AP_PAYMENT_SCHEDULES_ALL.
Pay Alone
=========
Pay Alone = AP_INVOICES_ALL.EXCLUSIVE_PAYMENT_FLAG
RELEASE_DATE
============
AP_HOLDS_ALL.LAST_UPDATE_DATE = RELEASE_DATE
Pay Site
========
A supplier site that is designated to receive payments. One cannot enter an invoice for a supplier site that is not defined as a pay site.
ACH - CTX(Corporate Trade Exchange) Format
==========================================
ACH CTX format should include record type 7 which will have payment information.
Remittance Advice
=================
A remittance advice is simply a notification of payment, often sent along with a cheque to show which invoices are being paid. It is just as a courtesy to help accounts departments tie up the payment with any outstanding invoices. They're not mandatory and not all companies use them.
Remittance struck in WF_DEFERRED table and delay in sending the notification Executed the below script to fix the issue. exec wf_event.listen(p_agent_name => 'WF_DEFERRED', p_correlation=>'oracle.apps.ap.payment%');
OIE:CC Payment Notify
=====================
This profile is for enable/disable iexpense notification to employee when the payments are made.
AP Recurring Invoice
====================
Recurring Invoice can be created for other than closed or permantly closed period.
To download XML Payment Instructions
====================================
set echo off accept instruction_id prompt "Instruction ID: " set termout off set heading off set pagesize 0 set linesize 32767 set verify off set trimspool on set feedback off set long 320000000 set longchunksize 320000000 COLUMN x HEADING OFF format A32000 spool pi_&instruction_id..xml SELECT xmlserialize(content xmltype(document) indent) from IBY_TRXN_DOCUMENTS pi where pi.payment_instruction_id = &instruction_id ; spool off set feedback on undefine instruction_id set echo on set verify on set termout onTo query remaining amounts
==========================
Select
v.segment1 vendor_number,
v.vendor_name VENDOR_NAME,
i.invoice_num INVOICE_NUMBER,
ps.due_date Due_Date,
i.invoice_date Invoice_date,
i.invoice_currency_code CURRENCY_CODE,
i.exchange_rate EXCHANGE_RATE,
ps.amount_remaining,
i.payment_cross_rate
FROM
APPS.ap_payment_schedules_all ps,
APPS.ap_invoices_all i,
APPS.po_vendors v,
APPS.po_vendor_sites_all vs
Where i.invoice_id = ps.invoice_id
AND i.vendor_id = v.vendor_id
AND i.vendor_site_id = vs.vendor_site_id
AND i.cancelled_date IS NULL
AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) != 0
AND i.payment_status_flag in ('N','P')
AND i.org_id=ps.org_id
AND i.org_id=vs.org_id
AND i.org_id='1'
To extract vendor from 11i==========================
SELECT *
FROM (SELECT distinct pv.vendor_id V_VENDOR_ID,
pv.segment1 supplier_no,
pv.vendor_name V_VENDOR_NAME,
pv.vendor_name_alt alias,
pv.vendor_type_lookup_code supplier_type,
(SELECT name
FROM apps.AP_TERMS AT
WHERE at.term_id = pv.terms_id)
payment_terms,
pv.vat_registration_num tax_reg_no,
decode(pv.num_1099,NULL,decode(pv.INDIVIDUAL_1099,NULL,NULL,pv.INDIVIDUAL_1099),pv.num_1099) taxpayer_id,
pv.tax_reporting_name,
pv.small_business_flag,
pv.enforce_ship_to_location_code,
(SELECT routing_name
FROM apps.RCV_ROUTING_HEADERS RH
WHERE rh.routing_header_id = pv.receiving_routing_id)
receipt_routing,
DECODE(pv.inspection_required_flag || pv.receipt_required_flag,'NN', '2-Way', 'NY', '3-Way', 'YY', '4-Way', pv.inspection_required_flag ||
pv.receipt_required_flag) match_approval,
pv.qty_rcv_tolerance,
pv.qty_rcv_exception_code,
pv.days_early_receipt_allowed,
pv.days_late_receipt_allowed,
pv.allow_substitute_receipts_flag,
pv.allow_unordered_receipts_flag,
pv.receipt_days_exception_code,
pvs.payment_method_lookup_code payment_method,
pv.invoice_currency_code,
pv.invoice_amount_limit,
(SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'INVOICE MATCH OPTION'
AND lookup_code = pvs.match_option)
inv_match_option,
pv.hold_all_payments_flag,
pv.payment_currency_code,
pv.payment_priority,
(SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'TERMS DATE BASIS'
AND lookup_code = pv.terms_date_basis)
term_date_basis ,
upper(pv.pay_date_basis_lookup_code)
pay_date_basis,
(SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'PAY GROUP'
AND upper(lookup_code) = upper(pv.pay_group_lookup_code)) pay_group,
--AND lookup_code = pv.pay_group_lookup_code)
pv.always_take_disc_flag,
pv.exclude_freight_from_discount,
pv.auto_calculate_interest_flag,
pv.organization_type_lookup_code,
pv.type_1099,
pv.start_date_active,
pv.end_date_active,
PV.STATE_REPORTABLE_FLAG,
PV.FEDERAL_REPORTABLE_FLAG,
PV.PAYMENT_METHOD_LOOKUP_CODE,
PV.EXCLUSIVE_PAYMENT_FLAG,
PV.NUM_1099,
PV.ALLOW_AWT_FLAG,
PV.AWT_GROUP_ID,
PV.TAX_VERIFICATION_DATE,
pv.inspection_required_flag,--n
pv.receipt_required_flag, --n
(SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'INVOICE MATCH OPTION'
AND lookup_code = pv.match_option) v_invoice_match_option,
pv.attribute_category V_ATTRIBUTE_CATEGORY,
pv.attribute1 V_ATTRIBUTE1,
pv.attribute2 V_ATTRIBUTE2,
pv.attribute3 V_ATTRIBUTE3,
pv.attribute4 V_ATTRIBUTE4,
pv.attribute5 V_ATTRIBUTE5,
pv.attribute6 V_ATTRIBUTE6,
pv.attribute7 V_ATTRIBUTE7,
pv.attribute8 V_ATTRIBUTE8,
pv.attribute9 V_ATTRIBUTE9,
pv.attribute10 V_ATTRIBUTE10,
pv.attribute11 V_ATTRIBUTE11,
pv.attribute12 V_ATTRIBUTE12,
pv.attribute13 V_ATTRIBUTE13,
pv.attribute14 V_ATTRIBUTE14,
pv.attribute15 V_ATTRIBUTE15,
pvs.vendor_site_id,
81 VS_ORG_ID,
pvs.address_line1,
pvs.address_line2,
pvs.address_line3,
pvs.address_line4,
pvs.city,
pvs.state,
pvs.zip,
PVS.INVOICE_CURRENCY_CODE VS_INVOICE_CURRENCY_CODE,
PVS.PAYMENT_CURRENCY_CODE VS_PAYMENT_CURRENCY_CODE,
PVS.HOLD_ALL_PAYMENTS_FLAG VS_HOLD_ALL_PAYMENTS_FLAG,
PVS.TERMS_DATE_BASIS,
upper(PVS.PAY_DATE_BASIS_LOOKUP_CODE),
PVS.EXCLUSIVE_PAYMENT_FLAG VS_EXCLUSIVE_PAYMENT_FLAGE,
PVS.REMITTANCE_EMAIL,
(SELECT name
FROM apps.AP_TERMS AT
WHERE at.term_id = pvs.terms_id)
vs_payment_terms,
PVS.PAY_GROUP_LOOKUP_CODE VS_PAY_GROUP_LOOKUP_CODE,
'SUPPLIER-INVOICE-TOLERANCE' vs_goods_tolerance_name,
PVS.ALLOW_AWT_FLAG VS_ALLOW_AWT_FLAG,
( SELECT name
FROM ap_awt_groups
WHERE group_id= pvs.awt_group_id)
VS_AWT_GROUP_ID,
(SELECT segment1
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.accts_pay_code_combination_id)
vs_liablity_seg1,
(SELECT segment2
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.accts_pay_code_combination_id)
vs_liablity_seg2,
(SELECT segment3
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.accts_pay_code_combination_id)
vs_liablity_seg3,
(SELECT segment4
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.accts_pay_code_combination_id)
vs_liablity_seg4,
(SELECT segment5
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.accts_pay_code_combination_id)
vs_liablity_seg5,
(SELECT segment6
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.accts_pay_code_combination_id)
vs_liablity_seg6,
(SELECT segment1
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.prepay_code_combination_id)
vs_prepay_seg1,
(SELECT segment2
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.prepay_code_combination_id)
vs_prepay_seg2,
(SELECT segment3
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.prepay_code_combination_id)
vs_prepay_seg3,
(SELECT segment4
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.prepay_code_combination_id)
vs_prepay_seg4,
(SELECT segment5
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.prepay_code_combination_id)
vs_prepay_seg5,
(SELECT segment6
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.prepay_code_combination_id)
vs_prepay_seg6,
(SELECT segment1
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.future_dated_payment_ccid)
vs_bills_pay_seg1,
(SELECT segment2
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.future_dated_payment_ccid)
vs_bills_pay_seg2,
(SELECT segment3
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.future_dated_payment_ccid)
vs_bills_pay_seg3,
(SELECT segment4
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.future_dated_payment_ccid)
vs_bills_pay_seg4,
(SELECT segment5
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.future_dated_payment_ccid)
vs_bills_pay_seg5,
(SELECT segment6
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id =
pvs.future_dated_payment_ccid)
vs_bills_pay_seg6,
(SELECT location_code
FROM HR_LOCATIONS
WHERE location_id = pvs.bill_to_location_id)
bill_to_loc,
(SELECT location_code
FROM HR_LOCATIONS
WHERE location_id = pvs.ship_to_location_id)
ship_to_loc,
pvs.pay_on_code
pay_on,
(SELECT pvs1.vendor_site_code
FROM PO_VENDOR_SITES_ALL PVS1
WHERE pvs1.vendor_site_id = pvs.default_pay_site_id)
alt_pay_site,
pvs.pay_on_receipt_summary_code
inv_summary_level,
pvs.create_debit_memo_flag,
pvs.gapless_inv_num_flag gapless_inv_num,
(SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_TYPE = 'FOB'
AND lookup_code = pvs.fob_lookup_code)
fob ,
(SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_TYPE = 'FREIGHT TERMS'
AND lookup_code = pvs.freight_terms_lookup_code)
freight_terms ,
pvs.attribute_category VS_ATTRIBUTE_CATEGORY,
pvs.attribute1 VS_ATTRIBUTE1,
pvs.attribute2 VS_ATTRIBUTE2,
pvs.attribute3 VS_ATTRIBUTE3,
pvs.attribute4 VS_ATTRIBUTE4,
pvs.attribute5 VS_ATTRIBUTE5,
pvs.attribute6 VS_ATTRIBUTE6,
pvs.attribute7 VS_ATTRIBUTE7,
pvs.attribute8 VS_ATTRIBUTE8,
pvs.attribute9 VS_ATTRIBUTE9,
pvs.attribute10 VS_ATTRIBUTE10,
pvs.attribute11 VS_ATTRIBUTE11,
pvs.attribute12 VS_ATTRIBUTE12,
pvs.attribute13 VS_ATTRIBUTE13,
pvs.attribute14 VS_ATTRIBUTE14,
pvs.attribute15 VS_ATTRIBUTE15,
(SELECT distribution_set_name
FROM AP_DISTRIBUTION_SETS_ALL AD
WHERE ad.distribution_set_id = pvs.distribution_set_id)
distribution_set_name,
pvs.country_of_origin_code country_of_origin,
pvs.duns_number,
pvs.country,
pvs.vendor_site_code address_name,
pvs.language,
pvs.supplier_notif_method notif_method,
pvs.area_code,
pvs.phone,
pvs.fax_area_code,
pvs.fax,
pvs.telex,
pvs.email_address,
pvs.purchasing_site_flag purchasing_site,
pvs.pay_site_flag pay_site,
pvs.primary_pay_site_flag primary_pay_site,
pvs.rfq_only_site_flag rfq_only_site,
pvs.customer_num,
pvs.ece_tp_location_code edi_location,
--decode(pvs.tax_reporting_site_flag,'Y',pvs.vendor_site_code,NULL) reporting_site,
pvs.tax_reporting_site_flaG,
pvc.url,
pvc.prefix,
pvc.first_name,
pvc.middle_name,
pvc.last_name,
pvc.title,
pvc.email_address c_email_address,
pvc.area_code c_area_code,
pvc.phone c_phone,
pvc.fax_area_code c_fax_area_code,
pvc.fax c_fax,
(select employee_number from per_all_people_f where person_id=pv.employee_id AND rownum=1) employee_number ,PV.CUSTOMER_NUM V_CUST_NUM
FROM PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC
WHERE
(pv.END_DATE_ACTIVE is null or pv.END_DATE_ACTIVE > sysdate)
and (pvs.inactive_date is null or pvs.inactive_date > sysdate)
and pv.vendor_id = pvs.vendor_id
AND pvs.vendor_site_id = pvc.vendor_site_id(+)
and ( pv.vendor_type_lookup_code <>'EMPLOYEE' OR pv.vendor_type_lookup_code IS NULL)
AND pvs.org_id IN
(1)
) vendors,
(SELECT bbnch.bank_number,
bbnch.bank_name,
bbnch.bank_branch_name,
bbnch.bank_num,
bbnch.description,
bacct.bank_account_name,
bacct.bank_account_num,
bacct.currency_code curr,
bacct.check_digits,
bbnch.country ctry,
bauses.vendor_site_id vsi,
bauses.primary_flag primary,
bacct.attribute_category,--n
bacct.attribute1,bacct.attribute2,bacct.attribute3,bacct.attribute4,bacct.attribute5,bacct.attribute6,bacct.attribute7,bacct.attribute8,
bacct.attribute9,bacct.attribute10,bacct.attribute11,bacct.attribute12,bacct.attribute13,bacct.attribute14,bacct.attribute15
,bauses.start_date --n
FROM AP_BANK_ACCOUNT_USES_ALL BAUSES,
AP_BANK_BRANCHES BBNCH,
AP_BANK_ACCOUNTS BACCT
WHERE bauses.external_bank_account_id = bacct.bank_account_id
AND bacct.bank_branch_id = bbnch.bank_branch_id
AND (Bauses.end_date IS NULL or Bauses.end_date > sysdate)
) BANKS
WHERE VENDORS.vENDOR_SITE_ID = banks.vsi(+)
begin
dbms_application_info.set_client_info(1);
end;
======================
Oracle has provided an extensible user hook “IBY_FD_EXTRACT_EXT_PUB” to add custom tags as part of seeded payment XML extract generated by Oracle Payment process. This hook allows custom elements to be created at following levels. Instruction Payment Document Payable Document Payable Line Payment Process Request You cannot customize the package specification, but package body contains stubbed functions that you can customize.
No comments:
Post a Comment