Archive for the ‘Oracle’ Category

Payables: Payments by Method by Supplier (& Site)

Tuesday, 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

Wednesday, 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

Friday, 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

Monday, June 6th, 2016

AIM to OUM

Implementation Question: Should I turn on Dynamic combination creation?

Wednesday, 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.

Oracle Check for Applied Patches

Friday, 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

Friday, 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

Friday, 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;

Project Accounting – Example AA Rule for Classifications when you have more than 1 AA Classification

Friday, March 4th, 2016

Example Auto Accounting Lookup Rule. Used in the event that you have more than 1 classification setup to be used in auto accounting. Dont forget you’ll need to modify this for your own uses.
SELECT ppc.CLASS_CODE
FROM pa_projects_all ppa,
PA_PROJECT_CLASSES ppc,
PA_CLASS_CODES PCC
WHERE ppa.project_id = ppc.project_id
AND ppa.project_id = :ProjectID
AND PPC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
AND PPC.CLASS_CATEGORY = :CategoryName
AND PPC.CLASS_CODE = PCC.CLASS_CODE
AND TRUNC(SYSDATE) BETWEEN PCC.START_DATE_ACTIVE AND NVL(PCC.END_DATE_ACTIVE,SYSDATE+1) ;

Oracle Projects: Resources – SQL Auto-Accounting Validation Scripts

Friday, March 4th, 2016

Having setup a number of operating units extremely quickly, and finding the standard Oracle Reports layout difficult to make easy comparisons, the enclosed SQL statement will help you to extract the configuration for all OU and accounting rules.

This script was tested and confirmed working in R12.2.4.

SELECT ho.name "OU",
pf.function_name,
pf.description,
pft.function_transaction_name,
pft.enabled_flag,
psr.segment_num,
pr.rule_name
FROM PA_FUNCTION_TRANSACTIONS_ALL pft,
hr_all_organization_units ho,
PA_SEGMENT_RULE_PAIRINGS_ALL psr,
PA_RULES pr,
PA_FUNCTIONS pf
WHERE pft.org_id = ho.organization_id
AND pft.org_id = psr.org_id
AND psr.function_code = pft.function_code
AND psr.function_transaction_code = pft.function_transaction_code
AND pr.rule_id = psr.rule_id
AND pf.function_code = pft.function_code
ORDER BY
pf.function_name,
function_transaction_Name,
segment_num;