Employee Bank Account Script oracle R12

SELECT

aps.vendor_id,

apss.vendor_site_id,

aps.vendor_name,

apss.vendor_site_code,

ieb.bank_name,

ieb.country,

iebb.bank_branch_name,

iebb.eft_swift_code,

iebb.branch_number,

ieba.bank_account_num,

ieba.bank_account_name

FROM ap.ap_suppliers aps,

per_all_people_f papf,

ap.ap_supplier_sites_all apss,

apps.iby_ext_bank_accounts ieba,

apps.iby_account_owners iao,

apps.iby_ext_banks_v ieb,

apps.iby_ext_bank_branches_v iebb

WHERE aps.vendor_id = apss.vendor_id

AND iao.account_owner_party_id = aps.party_id

AND ieba.ext_bank_account_id = iao.ext_bank_account_id

AND ieb.bank_party_id = iebb.bank_party_id

AND ieba.branch_id = iebb.branch_party_id

AND ieba.bank_id = ieb.bank_party_id

AND aps.employee_id = papf.person_id

AND TRUNC(sysdate) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;

How to set PO Match Options in Oracle Apps?

 [PO] Matching Setting in Purchase Order Shipment Line

We can Set Invoice matching in five different areas of Oracle Purchasing:
In the list below, a setting at any level will override the settings above it.

1. Oracle Purchasing Options
a. Navigate to: Setup > Organizations > Purchasing Options
b. Select Default Alternative Region

2. Supplier Information
a. Navigate to: Supply Base > Suppliers
b. Query on specific supplier
c. Click on Open
d. Select Receiving Alternative Region

3. Line Types
a. Navigate to: Setup > Purchasing > Line Types
b. In the Receipt Required field: Yes = 3-way, No = 2-way

4. Items
a. Navigate to: Items > Master Items
b. Query on specific item
c. Select Purchasing Alternative Region
d. In the Invoice Matching section: Yes = 3-way, No = 2-way

5. Purchase Order Shipments
a. Navigate to: Purchase Orders > Purchase Orders
b. Enter (header and) line information
c. Click on Shipments button
d. Select More Alternative Region

  Invoice Match to PO and Invoice Match to Receipt

Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and/or received. If you are billed for an item over the amount and quantity tolerances you define in the Invoice Tolerance window, during Approval, Oracle Payables applies a hold to the invoice, which prevents payment.
Oracle Payables supports three levels of matching which verify that purchase order and
invoice information match within defined tolerances.

Match Approval Level: 2-Way , 3-Way, 4-Way.

In 2-way: what ever you have ordered for the PO you will make the payment for the suppliers in 2- way i.e we will compare two documents PO and Invoice.
2-way matching verifies that Purchase order and invoice information match within your tolerances:
Quantity billed <= Quantity Ordered
Invoice price <= Purchase order price
Eg:Suppose we Had given PO for 100 items ,for that we will receive invoice for 100 items. so that we will make payment for that 100 items.

In 3-Way you will compare 3 documents i.e PO+reciept+Invoice.
3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:
Quantity billed <= Quantity received.
Eg:Suppose we have ordered 100 items in PO. But we had received only 75 items ,But we had received invoice for 100 items. so, we will make payment for only 75 items.

In 4-Way you will compare 4 documents i.e PO+Receipt+Invoice+Inspection.
4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Quantity billed <= Quantity accepted.
Eg:Suppose we have 100 items in PO. Suppers send us 75 items We will do inspection on those items what ever we have received, If 15items got damaged. finally, we are going to make payment to the 60 items only.

When you match to a purchase order, Payables automatically checks that the total of PO_DISTRIBUTIONS.QUANTITY_

ORDERED = AP_INVOICE_DISTRIBUTIONS.QUANTITY_INVOICED (2-way matching). Payables only checks QUANTITY_RECEIVED (3-way matching) if the RECEIPT_REQUIRED_FLAG is set to Y and only checks QUANTITY_ACCEPTED (4-way matching) if the INSPECTION_REQUIRED_FLAG is set to Y.
Invoice Match Option:
The Invoice Match Option determines whether or not you intend to match invoices for this supplier against purchase orders or receipts.
Invoice Match option to PO Match:
--------------------------------------------
•Payables must match the invoice to the purchase order.
•If the Invoice is matched to a PO rather than to the Receipt when the AP team do a match they have the full PO available to match rather than just the specific lines on the PO that were received.
•There is a possibility someone in payables matching to the wrong distribution if they use Match to PO.

•The accrual is valued at the PO exchange rate date.

Invoice Match option to PO Match:

----------------------------------------------
•Payables must match the invoice to the receipt.
•Receipt Match Option is recommended if you want accounting to use exchange rate information based on the receipt date or if you want to update exchange rate information on the receipt.
•If you use the match to receipt option AP team can't match until the goods are received. If the receiving doesn't happen there is no way to associate the invoice with the PO.
•Invoice processing will be on hold till a receipt is entered into the system.
•Receipt Match option determines the cost with more accuracy i.e. Match to Material item receipt and link other invoice charges to receipt and You can now also associate freight, tax, and miscellaneous charges from invoices to the related receipt.
•Run the Matching Detail Report from Other -> Request -> Run. This report will show you detail of how an invoice, purchase order, or receipt was matched. This report is especially helpful when an invoice is on hold and you are trying determine why the hold was placed.

The Invoice Match Option defaults from the Supplier Sites window. You can change the Invoice Match Option on the shipment until you receive against the shipment.

Financial Options - Invoice Match Option:
For purchase order shipments, indicate whether you want to match invoices to purchase orders or to purchase order receipts. If the supplier was created automatically during Expense Report Import, the default value is Purchase Order, and you can change it to Receipts as appropriate.

The Business Needs To Take decision to set the Invoice match option to Receipt or Purchase.
•The main advantage is the Exchange Rate factor i.e. Receipt/Current Date instead of Purchaser Order date.
•If you are using Multi Currency Functionality for Purchase Order and if like to have the rate based on       Receipt date you need to go with Receipt Match at the invoice match option.
•Receipt Match gives much closer control of the matching process,Particularly where you have multiple receipts of large purchase order.

If a Business is using Invoice Match option Purhcase Order and like to change from Purchase Order to Receipt Match, then what would be the impact?

•All Supplier sites need to be updated with Invoice Match option to Receipt.
•Purchase orders which are already approved with Invoice Match Option of Purchase order should continue with the same existing process.
•All existing Open PO, which need a change from Purchase Order to Receipt, should be corrected by changing the invoice match option in the purchase order shipment to Receipt. Payables team should match invoices to Receipt for all Purchase Orders created after the cut-off date.
•Training to AP/PO users.

In order for the receipt to use the current exchange rate and not the PO exchange rate, the Invoice Match Option at the system level and on the suppliers should be Receipt, not Purchase Order.

Supplier and Site bank detail query in R12

/* Formatted on 2/15/2018 2:46:46 PM (QP5 v5.114.809.3010) */
SELECT   /*Supplier Information*/
         aps.segment1 oracle_supplier_number,
         aps.vendor_id,
         aps.vendor_name supplier_name,
         aps.party_id supplier_party_id,
         iepa.remit_advice_fax remit_advice_fax,
         iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
         ,
         assa.vendor_site_id,
         assa.party_site_id supplier_party_site_id,
         assa.vendor_site_code vendor_site_code,
         assa.pay_site_flag pay_site_flag,
         assa.purchasing_site_flag purchasing_site_flag,
         assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
         ,
         ieba.ext_bank_account_id,
         hp.party_name Bank_party_name,
         ieba.bank_account_num bank_account_num,
         ieba.bank_account_name bank_account_name,
         ieba.country_code bank_acct_country_code,
         ieba.currency_code bank_acct_currency_code/* Bank Address */
         ,
         hp.address1 bank_address_line1,
         hp.address2 bank_address_line2,
         hp.address3 bank_address_line3,
         hp.city bank_address_city,
         hp.state bank_address_state,
         hp.postal_code bank_address_zip,
         hp.country bank_address_country/* Bank Branch Address */
         ,
         hp1.address1 branch_address_line1,
         hp1.address2 branch_address_line2,
         hp1.address3 branch_address_line3,
         hp1.city branch_address_city,
         hp1.state branch_address_state,
         hp1.postal_code branch_address_zip,
         hp1.country branch_address_country
  FROM   ap_supplier_sites_all assa,
         hz_parties hp,
         iby_ext_bank_accounts ieba,
         iby_external_payees_all iepa,
         iby_pmt_instr_uses_all ipiua,
         ap_suppliers aps,
         hz_parties hp1
 WHERE       assa.vendor_site_id = iepa.supplier_site_id
         AND hp.party_id = ieba.bank_id
         AND ipiua.instrument_id = ieba.ext_bank_account_id
         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
         AND assa.vendor_id = aps.vendor_id
         AND ieba.branch_id = hp1.party_id
         AND ipiua.instrument_type = 'BANKACCOUNT'
         AND aps.vendor_name LIKE '3M%'
         AND assa.vendor_site_id = 6916
         AND ipiua.payment_flow = 'DISBURSEMENTS'
         AND ipiua.order_of_preference = 1;

Supplier / Supplier Site Details:-

SELECT   assa.vendor_site_id
                  FROM   ap_supplier_sites_all assa,
                         hz_parties hp,
                         iby_ext_bank_accounts ieba,
                         iby_external_payees_all iepa,
                         iby_pmt_instr_uses_all ipiua,
                         ap_suppliers aps,
                         hz_parties hp1
                 WHERE       assa.vendor_site_id = nvl(iepa.supplier_site_id,assa.vendor_site_id)
                         AND hp.party_id = ieba.bank_id
                         AND ipiua.instrument_id = ieba.ext_bank_account_id
                         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
                         AND assa.vendor_id = aps.vendor_id
                         AND ieba.branch_id = hp1.party_id
                         AND ipiua.instrument_type = ''BANKACCOUNT''
                         --and assa.vendor_site_id = 6916
                         AND ieba.ext_bank_account_id = : Bank Id
                         AND assa.org_id = fnd_profile.VALUE (''org_id'')


        ********************  ##################  ********************

SELECT  aps.vendor_name "VERDOR NAME",

        apss.vendor_site_code "VENDOR SITE CODE",

        ieb.bank_name "BANK NAME",

        iebb.bank_branch_name "BANK BRANCH NAME",

        iebb.branch_number "BRANCH NUMBER",

        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",

        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"

FROM    ap.ap_suppliers aps,

        ap.ap_supplier_sites_all apss,

        apps.iby_ext_bank_accounts ieba,

        apps.iby_account_owners iao,

        apps.iby_ext_banks_v ieb,

        apps.iby_ext_bank_branches_v iebb

WHERE   aps.vendor_id = apss.vendor_id

        and iao.account_owner_party_id = aps.party_id

        and ieba.ext_bank_account_id = iao.ext_bank_account_id

        and ieb.bank_party_id = iebb.bank_party_id

        and ieba.branch_id = iebb.branch_party_id

        and ieba.bank_id = ieb.bank_party_id;

Query to get the Profile Option Configurations

/* Formatted on 1/15/2018 9:40:58 AM (QP5 v5.114.809.3010) */
  SELECT   ot.user_profile_option_name,
           TO_CHAR (v.level_id) level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, 'Responsibility',
                   10004, 'User ID',
                   v.level_id)
              level_meaning,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, apl.application_name,
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id)
              level_name,
           v.profile_option_value,
           o.profile_option_name,
           v.creation_date value_creation_date,
           v.created_by value_created_by,
           v.last_update_date value_last_updated_date,
           v.last_updated_by value_last_updated_by
    FROM   applsys.fnd_profile_options_tl ot,
           applsys.fnd_profile_options o,
           applsys.fnd_profile_option_values v,
           applsys.fnd_responsibility_tl frt,
           apps.fnd_application_vl apl,
           fnd_user u
   WHERE       v.level_value = frt.responsibility_id(+)
           AND v.profile_option_id = o.profile_option_id
           AND o.profile_option_name = ot.profile_option_name
           AND ot.language = 'US'
           AND NVL (frt.language, 'US') = 'US'
           AND v.level_value = apl.application_id(+)
           AND u.user_id(+) = v.level_value
ORDER BY   ot.user_profile_option_name,
           v.level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id);