Payables: Payments by Method by Supplier (& Site)

April 4th, 2017

You can use this SQL statement to review the setup of the supplier payment options by site, and the remittance preference and if specified email address.

This SQL statement could be useful if you ware looking to validate or extract lists of contacts and look for opportunities to streamline processes.

  SELECT COUNT(aca.check_number)            “Number of Payments”,
asa.segment1                       “Supplier Number”,
asa.vendor_name                    “Supplier Name”,
st.vendor_site_code                “Supplier Site Name”,
aca.PAYMENT_METHOD_CODE            “Payment Method (At Payment)”,
ieppm.payment_method_code          “Site Payment Method (Current)”,
iepa.remit_advice_delivery_method  “Site Remit Delivery Method”,
iepa.remit_advice_email            “Site Remit Advice Email”
FROM ap_checks_all aca,
ap_suppliers asa,
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds      ieppm,
ap.ap_supplier_sites_all     st
WHERE     1=1
AND aca.vendor_id = asa.vendor_id
and iepa.payee_party_id = asa.party_id
and iepa.supplier_site_id = aca.vendor_site_id
and iepa.ext_payee_id = ieppm.ext_pmt_party_id
AND iepa.supplier_site_id = st.vendor_site_id
–and iepa.supplier_site_id = aca.vendor_site_id
group by asa.segment1, asa.vendor_name, aca.PAYMENT_METHOD_CODE, remit_advice_delivery_method, remit_advice_email, ieppm.payment_method_code, st.vendor_site_code
ORDER BY COUNT(aca.check_number) desc;

11.0.3. Extract GL Budgets

December 7th, 2016

SQL Statement to extract GL Budget STAT amounts.

 

select /*+ index(gcc code_combination_id) */
bal.period_num,
bal.period_net_dr,
bal.period_net_cr,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7
from apps.gl_balances bal
, apps.gl_sets_of_books bsk
, apps.gl_code_combinations gcc
, apps.gl_periods pd
where bal.period_year = 2016
and bal.period_num = 1
and bal.actual_flag = ‘B’
and ‘STAT’ =  bal.currency_code
and bsk.set_of_books_id = bal.set_of_books_id
and pd.period_set_name = bsk.period_set_name
and pd.period_name = bal.period_name
and gcc.code_combination_id = bal.code_combination_id
and gcc.summary_flag = ‘N’

R12 SQL Bank Accounts Key Setup and Usage

August 12th, 2016
 SELECT
 bb.bank_name                     "Bank Name",
 bb.bank_branch_type              "Bank Branch Type",
 bb.bank_branch_name              "Bank Branch Name",
 bb.bank_branch_number            "Bank Branch Number",
 cba.bank_account_name            "Bank Account Name",
 cba.bank_account_num             "Bank Account Number",
 cba.multi_currency_allowed_flag  "Multi Currency Flag",
 cba.zero_amount_allowed          "Zero Amount Flag",
 cba.account_classification       "Account Classification",
 bb.bank_name                     "Bank Name",
 bb.bank_branch_type              "Bank Branch Type",
 bb.bank_branch_name              "Bank Branch Name",
 bb.bank_branch_number            "Bank Branch Number",
 bb.eft_swift_code                "Swift Code",
 -- bb.description                   "Description",
 ou.name                          "Operating Unit",
 gcf.concatenated_segments        "GL Code Combination",
 bau.ar_use_enable_flag           "Enabled for AR",
 bau.ap_use_enable_flag           "Enabled for AP"
 FROM ce_bank_accounts          cba,
 ce_bank_acct_uses_all     bau,
 cefv_bank_branches        bb,
 HR_ALL_ORGANIZATION_UNITS ou,
 gl_code_combinations_kfv  gcf
 WHERE 1=1
 AND cba.bank_account_id = bau.bank_account_id
 AND cba.bank_branch_id  = bb.bank_branch_id
 AND ou.organization_id  = bau.org_id
 AND cba.asset_code_combination_id = gcf.code_combination_id
 AND sysdate between nvl(cba.start_date,sysdate-1) and nvl(cba.end_date,sysdate+1)
 ORDER BY BANK_NAME, BANK_BRANCH_NAME,cba.bank_account_num;

AIM to OUM Mapping

June 6th, 2016

AIM to OUM

Implementation Question: Should I turn on Dynamic combination creation?

June 1st, 2016

Oracle recommend turning on the setting for dynamic combinations creation (cloud) or dynamic inserts (11i/R12) at least until all setup related activities are finalised. Turning off dynamic creation / inserts limits the code combinations allows and can hinder implementations.

Project Accounting – Validation of the Project Starting Organisation per OU

March 4th, 2016

Used to quickly validate the setup and configuration of a implementation.

Select o.name "Operating Unit", o1.name "Project Start Org" 
from PA_IMPLEMENTATIONS_ALL pi, hr_all_organization_units o, hr_all_organization_units o1
where pi.org_id = o.organization_id
and proj_start_org_id = o1.organization_id
and o.name <> o1.name;

Oracle WebADI (R12) Examine Configuration of WebADI Layouts and Codes

March 4th, 2016

This SQL statement was used to support the creation of custom menu items for custom WebADI layouts.

SELECT biv.integrator_code,
  biv.user_name,
  fa.application_id,
  fa.application_short_name
  ,biv.language
  , bl.layout_code
FROM bne_integrators_tl biv,
  fnd_application fa,
  fnd_application_tl fat,
  BNE_LAYOUTS_B bl
WHERE biv.application_id = fa.application_id
AND fa.application_id    = fat.application_id
  /* The next few line can be commented or changed to suit*/
AND fa.application_short_name = 'PA'
and biv.integrator_code = 'PAXTTRXB'
and biv.integrator_code = bl.integrator_code
AND fat.language              = 'US'            
and biv.language = fat.language
ORDER BY fa.application_short_name,
  biv.user_name;

Oracle Check for Applied Patches

March 4th, 2016
select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE,  AD.PLATFORM,AL.LANGUAGE
from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID

Project Accounting – Auto Accounting Rule – Extend Labour Control Account

March 4th, 2016

We had a requirement to extend the labour control accounts to use different accounts depending on the capitalisation status of the transaction. This enclosed SQL statement is an example of how the issues was resolved. This allowed us to specify a different account depending on the type of project.

SELECT DECODE(project_type_class_code,'CAPITAL', DECODE(billable_flag, 'Y','CAPITAL CoA Segement Value', 'NON-CAPITAL CoA Segment Value') ,'NON-CAPITAL CoA Segment Value') "Value"
FROM pa_expenditure_items_all pei,
  pa_projects_all ppa,
  PA_PROJECT_TYPES_ALL ppt
WHERE 1                 =1
AND expenditure_item_id = :1
AND pei.project_id      = ppa.project_id
AND ppa.project_type    = ppt.project_type;

Oracle AP – Establish the Intercompany Account from a Supplier Site

March 4th, 2016

SELECT gcc.segment5 "Intercompany" ,
vendor_name,
vendor_site_code ,
hou.name ,
apss.accts_pay_code_combination_id ,
gcc.segment1 ,
gcc.segment2 ,
gcc.segment3 ,
gcc.segment4 ,
gcc.segment5 ,
gcc.segment6 ,
gcc.segment7 ,
gcc.segment8 ,
gcc.segment9 ,
gcc.segment10 ,
gcc.segment11 ,
apss.*
FROM AP_SUPPLIERS aps,
AP_SUPPLIER_SITES_ALL apss,
HR_ALL_ORGANIZATION_UNITS hou,
gl_code_combinations gcc
WHERE aps.vendor_id = apss.vendor_id
AND apss.org_id = hou.organization_id
AND gcc.code_combination_id = apss.accts_pay_code_combination_id
ORDER BY 1,2,3;