Oracle Apps Techno Functional

Thursday, November 7, 2013

Query 1: Reset User Password

Begin
fnd_user_pkg.updateuser(x_user_name => 'SKADIYALA',
x_owner => NULL,
x_unencrypted_password => 'welcome123');
COMMIT;
END;

Query 2:You know table get the package or any where

select * from ALL_DEPENDENCIES
where referenced_name = 'FPC_SPARES_INV_LOC_REP';

Query 3: Select responsibility name along with application name

SELECT application_short_name, frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt, fnd_application fa
WHERE fa.application_id = frt.application_id;

Get the Xml data file name
SELECT distinct LobsEO.LANGUAGE,
LobsEO.LOB_TYPE,
LobsEO.APPLICATION_SHORT_NAME,
LobsEO.LOB_CODE,
LobsEO.TERRITORY,
LobsEO.FILE_NAME,
LobsEO.FILE_STATUS,
header.TEMPLATE_ID,
header.TEMPLATE_CODE,
header.DS_APP_SHORT_NAME ,
header.DATA_SOURCE_CODE ,
header.TEMPLATE_TYPE_CODE AS TEMPLATE_TYPE,
language.NAME AS LANGUAGE_MEANING,
DECODE(LobsEO.Territory,'00','',territory.TERRITORY_SHORT_NAME) AS TERRITORY_MEANING,
(
CASE
WHEN header.DEPENDENCY_FLAG = 'C'
OR sample.FILE_NAME IS NULL
THEN 'MLSPreviewDisabled'
ELSE 'MLSPreviewEnabled'
END) AS PreviewImage,
(
CASE
WHEN LobsEO.TRANS_COMPLETE = 'Y'
THEN 'Complete'
ELSE 'Partial'
END) AS TRANSLATION_STATUS,
'N' AS SELECTED
FROM apps.xdo_templates_vl header,
apps.XDO_Lobs LobsEO,
apps.XDO_Lobs sample,
apps.FND_ISO_LANGUAGES_VL language,
apps.fnd_territories_vl territory
WHERE header.APPLICATION_SHORT_NAME = LobsEO.APPLICATION_SHORT_NAME
AND header.TEMPLATE_CODE =LobsEO.LOB_CODE
--- AND LobsEO.lob_type = 'MLS_TEMPLATE'
and header.DS_APP_SHORT_NAME =sample.APPLICATION_SHORT_NAME(+)
AND header.DATA_SOURCE_CODE = sample.LOB_CODE(+)
--AND sample.LOB_TYPE(+) ='XML_SAMPLE'
AND lower(LobsEO.LANGUAGE) =language.ISO_LANGUAGE_2(+)
AND territory.territory_code(+) = LobsEO.territory
and header.TEMPLATE_CODE like'%FPC_944_ITSG_CON_DTL_INVOICE%'

Query 4: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = &resp_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

Query 5: Get User name and related assigned responsibilities

SELECT DISTINCT u.user_id, u.user_name user_name,
r.responsibility_name responsiblity,
a.application_name application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY 1;

Query 6: Get Request Group associate with Responsibility Name

SELECT responsibility_name responsibility, request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

Query 7: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables
applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

Query 8: Query to view the patch level status of all modules

SELECT a.application_name,
DECODE (b.status, 'i', 'installed', 's', 'shared', 'n / a') status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;

Query 9: SQL to view all request who have attached to a responsibility

SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'p'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query 10: SQL to view all requests who have attached to a responsibility

SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'p'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
and fcpv.user_concurrent_program_name like :program_name
ORDER BY responsibility_name;14

Query 11: SQL to view all types of request Application wise

SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name,
minimum_width,
minimum_length, concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description

Query 10: SQL to view concurrent request processing time, quite useful

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.ac

Query 12: SQL to view concurrent program and executable ,executable method ,executable file name ,concurrent program short name

SELECT 'Program User Name: ' || fcpt.user_concurrent_program_name prog_name,
'Description: ' || fcpt.description description,
'Executable Name: ' || exe.executable_name exe_name,
'Executable File Nmae: ' || exe.EXECUTION_FILE_NAME,
'Executable File: ' || exe.execution_file_name exe_file_name,
'Execution Type: ' ||
DECODE(exe.execution_method_code,
'B',
'Request Set Stage Function',
'Q',
'SQL*Plus',
'H',
'Host',
'L',
'SQL*Loader',
'A',
'Spawned',
'I',
'PL/SQL Stored Procedure',
'P',
'Oracle Reports',
'S',
'Immediate',
exe.execution_method_code) exe_method,
--fnd.concurrent_program_id,
fnd.CONCURRENT_PROGRAM_NAME Short_name
FROM applsys.fnd_executables exe,
applsys.fnd_concurrent_programs fnd,
applsys.fnd_concurrent_programs_tl fcpt
WHERE exe.executable_id = fnd.executable_id
AND fnd.concurrent_program_id = fcpt.concurrent_program_id
AND fnd.application_id = fcpt.application_id
and exe.execution_method_code='I'
ORDER BY FND.CREATION_DATE DESC

Query 13: AR To GL

SELECT gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE gjh.period_name IN ('OCT-2008','NOV-2008')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id

Query 14: Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fcp.concurrent_program_id
, fav.APPLICATION_SHORT_NAME
, fav.APPLICATION_NAME
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_application_vl fav
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fav.application_id=fcp.application_id
AND fcpl.LANGUAGE = 'US'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;

Query 15: Query to find out responsibility and request group for concurrent program:
SELECT DISTINCT
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';
Posted by Subhash G at 1:58 AM 1 comments
Labels: AOL, CONCURRENT PROGRAM, FND

Query 16:Concurrent Program with request group details

SELECT frg.request_group_name
, fav1.APPLICATION_SHORT_NAME "RequestGroup Appln Shortname"
, fav1.APPLICATION_NAME "RequestGroup Application Name"
, fav1.application_id "RequestGroup Application ID"
, fcp.concurrent_program_name
, fcpl.user_concurrent_program_name
, fav.APPLICATION_SHORT_NAME "Con Prog Application Shortname"
, fav.APPLICATION_NAME "Con Prog Application Name"
, fav.application_id "Con Prog Application ID"
, fe.execution_file_name
, fe.executable_name
FROM fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_request_groups frg,
fnd_executables fe,
fnd_application_vl fav,
fnd_application_vl fav1
WHERE frgu.request_unit_id = fcp.concurrent_program_id
AND fav.application_id=fcp.application_id
AND fav1.application_id=frgu.application_id
AND frgu.request_group_id = frg.request_group_id
AND fe.executable_id = fcp.executable_id
AND fcp.concurrent_program_name = :conc_prg_name;

Query 17:Query to list concurrent program details with its parameter, values set and default value/type:

SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, fav.APPLICATION_SHORT_NAME
, fav.APPLICATION_NAME
, fav.application_id
, fdfcuv.end_user_column_name
, fdfcuv.form_left_prompt prompt
, fdfcuv.enabled_flag
, fdfcuv.required_flag
, fdfcuv.display_flag
, fdfcuv.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, fdfcuv.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl fdfcuv
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
, fnd_application_vl fav
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND fav.application_id=fcp.application_id
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG');

Query 18:
Select Distinct C.User_Concurrent_Program_Name, a.user_request_set_name
From
fnd_request_sets_vl a,
fnd_request_set_programs b,
fnd_req_set_stages_form_v d,
fnd_concurrent_programs_vl c
Where
a.Request_Set_Id = d.Request_Set_ID
And A.Request_Set_Id = b.Request_Set_Id
And A.Application_Id = b.Set_Application_Id
And A.Application_Id = d.Set_Application_Id
And d.Request_Set_Stage_Id = b.Request_Set_Stage_Id
--And a.user_request_set_name Like '11i AR Slow Start Reports'
and C.User_Concurrent_Program_Name like'MasTec (AT) CAR Transfer to AP Process WB'
And b.concurrent_program_id = c.concurrent_program_id
And b.Program_Application_Id = C.Application_Id

Query 19 : AR To GL

SELECT gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE gjh.period_name IN ('OCT-2008','NOV-2008')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id

Query 20:Iexpance with WF

SELECT to_char(aerh.report_header_id) expense_report
,aerh.invoice_num
,aerh.creation_date
,aerl.start_expense_date expense_date
,per.employee_number emp_number
,substr(last_name || ', ' || first_name, 1, 25) emp_name
,per.attribute8 pcc
,per.attribute9 dept
,substr(aerl.item_description,1,40) exp_type
,aerl.amount
,aerl.currency_code currency
,pp.segment1 project_num
,pp.NAME proj_name
,pt.task_number
,hau.NAME proj_org
,flv.meaning er_status
,wiav.text_value approver
,wias.end_date approval_date
,aerh.report_submitted_date date_submited
FROM ap_expense_report_headers aerh,
ap_expense_report_lines aerl,
ap_exp_report_dists aerd,
per_people_f per,
fnd_lookup_values_vl flv,
pa_projects_all pp,
pa_tasks pt,
hr_all_organization_units hau,
wf_item_activity_statuses wias,
wf_item_attribute_values wiav
WHERE 1 = 1
AND aerh.employee_id = per.person_id
AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date
AND aerh.expense_status_code NOT IN ('CANCELLED', 'PAID')
AND aerh.expense_status_code IS NOT NULL
AND flv.lookup_type = 'EXPENSE REPORT STATUS'
AND flv.lookup_code = aerh.expense_status_code
AND aerl.report_header_id = aerh.report_header_id
AND aerl.report_line_id = aerd.report_line_id
AND aerd.project_id = pp.project_id(+)
AND aerd.task_id = pt.task_id(+)
AND aerd.project_id = pt.project_id(+)
AND hau.organization_id(+) = pp.carrying_out_organization_id
AND wias.ACTIVITY_RESULT_CODE='APPROVED'
AND wias.item_type = 'APEXP'
AND wias.item_key = to_char(aerh.report_header_id)
AND wias.process_activity = (SELECT MIN(process_activity)
FROM wf_item_activity_statuses wis2
WHERE wis2.item_key = wias.item_key
AND wis2.item_type = wias.item_type
AND wis2.ACTIVITY_RESULT_CODE = wias.ACTIVITY_RESULT_CODE)
AND wiav.item_type = wias.item_type
AND wiav.item_key = wias.item_key
AND wiav.name = 'APPROVER_DISPLAY_NAME'
AND ROWNUM<=5

Query 20:Iexpance with AME

SELECT erh.invoice_num expense_report
,(SELECT full_name
FROM per_people_f
WHERE person_id = erh.employee_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date) employee_name
,ppf.full_name submitter_name
,erh.week_end_date week_end_date
,erh.description description
,erh.total amount
,(SELECT ppf.full_name
FROM per_all_people_f ppf
,fnd_user fu
WHERE fu.employee_id = ppf.person_id
AND SYSDATE BETWEEN ppf.effective_start_date AND nvl(ppf.effective_end_date, SYSDATE)
AND fu.user_name =
(SELECT ame.NAME
FROM ame_approvals_history ame
WHERE ame.transaction_id = erh.report_header_id
AND ame.approval_status = 'APPROVE'
AND ame.row_timestamp = (SELECT MAX(ame1.row_timestamp)
FROM ame_approvals_history ame1
WHERE ame1.transaction_id = ame.transaction_id))) approver
,apinv.creation_date invoice_imported_date
FROM ap_expense_report_headers_all erh
,fnd_user fndu
,per_all_people_f ppf
,ap_invoices_all apinv
WHERE erh.created_by = fndu.user_id
--and apinv.invoice_num = 'CAD-IE116487'
AND fndu.employee_id = ppf.person_id
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND erh.SOURCE = 'SelfService'
AND erh.workflow_approved_flag IN ('Y', 'A')
AND apinv.SOURCE = 'SelfService'
AND erh.invoice_num = apinv.invoice_num
AND apinv.payment_status_flag <> 'Y' -- invoices not paid
AND apinv.cancelled_amount IS NULL -- Exclude cancelled invoices
--and apinv.creation_date < ('11-JAN-2006')
AND trunc(apinv.creation_date) BETWEEN (trunc(p_start_date)) AND (trunc(p_end_date))
ORDER BY erh.invoice_num
,erh.week_end_date;

-------------
AR Invoice
-------------------
SELECT rt.name "Term Name" ,
rct.trx_number "Invoice Number",
RCT.ORG_ID,
rct.creation_date "Creation Date",
rct.trx_date "Invoice Date",
rac.customer_number "Customer Number",
rac.customer_name "Customer Name",
DECODE(aps.status,'OP','Open','CL','Close', aps.status)invoice_status,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1
FROM apps.ra_customer_trx_all rct,
apps.ar_payment_schedules_all aps,
apps.ra_terms_vl rt,
apps.ra_customers rac
WHERE rct.customer_trx_id = aps.customer_trx_id
AND rct.term_id = rt.term_id
AND rct.bill_to_customer_id = rac.customer_id
and aps.status = 'OP'
AND rt.name in ( 'ADVANCE', '1%10NET30','2%DIS-45','3% 18/NET 30','ARREARS-NET 30','CIT Leasing','COD','FPC LEASE',
'GE Finance1','GE-CDF','ML NET 30','NET 120 DAYS','NET 30 PROX (1)','NET 48','NET 65','NET 7 DAYS','PREPAID','PREPAYMENT',
'TCFC','TEXTRON')
order by rt.name desc

------------------.
SELECT rt.description,
rt.name terms_code,
discount_days,
discount_percent,
due_days "Grace Period Net Days" --, RELATIVE_AMOUNT
FROM apps.ra_terms_vl rt, ra_terms_lines_discounts rtld, ra_terms_lines rtl
WHERE rt.term_id = rtl.term_id(+)
AND rtl.term_id = rtld.term_id(+)
AND rt.end_date_active IS NULL

General Ledger Useful SQL Scripts – Oracle Applications 11i

General Ledger Useful SQL Scripts – Oracle Applications 11i

Contents
GL Set of Books Configuration Overview 1
GL Summary Account Template Definition Review 2
GL Segment Value Listing 3
GL Period Status 3
GL Chart of Accounts Structure 4
GL Chart of Accounts Structure Overview 4
GL Journal Header Summary 5
GL Journal Line Based Trial Balance Report 5
GL Journal Lines With AP Source Reference Fields 6
GL Mass Allocation Rule Migration Script in Dataload Classic Format 7
GL Balances and Movements 8
GL Chart of Account Segment Hierarchy Ranges 9
GL Code Combinations CCIDs 9
GL CVR Cross Validation Rule Detail Listing 10
GL CVR Cross Validation Rule Overview 11
GL Flexfield Security Rule Assignments 11
GL Flexfield Security Rule Definitions 11
GL FSG Report and Components Overview 12
GL Interface Details 13
GL Interface Summary 13
GL Mass Allocation Formula review script 14
GL Mass Allocation Migration Script in Dataload Professional FLD format 14
GL Mass Allocation Rule Migration Script in Dataload Classic Format 17
GL ADI Journal Balances script 18
GL Autopost Definitions 19
HR Operating Unit and Legal Entity Configuration 19
Dataload .dld GL Cross Validation Rules 20
Dataload Professional .fld -- Daily Rates load 21

GL Set of Books Configuration Overview

/* SET OF BOOKS CONFIGURATION OVERVIEW
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
THIS SQL GIVES AN OVERVIEW OF THE SET OF BOOK DEFINITIONS AND CAN BE USED WHEN IMPLEMENTING MULTIPLE SETS OF BOOKS
TO ENSURE CONSISTENT SETUP ACROSS COUNTRIES AND BETWEEN ENVIRONMENTS.
WHERE CLAUSE CAN BE ADDED OR COMMENTED OUT TO JUST LOOK AT SPECIFIC COUNTRIES. */

SELECT SOB.SET_OF_BOOKS_ID "ID"
, SOB.NAME
, SOB.SHORT_NAME
, SOB.DESCRIPTION
, SOB.CHART_OF_ACCOUNTS_ID "COA ID"
, FST.ID_FLEX_STRUCTURE_CODE "CHART OF ACCOUNTS"
, SOB.CURRENCY_CODE "CURR"
, PT.USER_PERIOD_TYPE "PERIOD"
, SOB.PERIOD_SET_NAME
, SOB.FUTURE_ENTERABLE_PERIODS_LIMIT "FUT. PER"
, SOB.LATEST_OPENED_PERIOD_NAME "LATEST OPEN"
, SOB.ATTRIBUTE1"OPERATIONAL BOOK"
, SOB.ATTRIBUTE2"PPL ?"
, SOB.ENABLE_REVAL_SS_TRACK_FLAG||'.'||ENABLE_SECONDARY_TRACK_FLAG"SEC SEG TRACK?"
, RET.SEGMENT1||'-'||RET.SEGMENT2||'-'||RET.SEGMENT3||'-'||RET.SEGMENT4||'-'||RET.SEGMENT5||'-'||RET.SEGMENT6 "RETAINED EARNINGS"
, TRAN.SEGMENT1||'-'||TRAN.SEGMENT2||'-'||TRAN.SEGMENT3||'-'||TRAN.SEGMENT4||'-'||TRAN.SEGMENT5||'-'||TRAN.SEGMENT6 "TRAN EARNINGS"
, '---JOURNALS---'
, SOB.ALLOW_INTERCOMPANY_POST_FLAG"INTERCO?"
, SOB.ENABLE_JE_APPROVAL_FLAG"JRNL APP?"
, SOB.ENABLE_AUTOMATIC_TAX_FLAG"AUTO TAX?"
, SOB.SUSPENSE_ALLOWED_FLAG"SUSP?"
, SOB.TRACK_ROUNDING_IMBALANCE_FLAG"TRK RND?"
, '---AV BAL---'
, SOB.ENABLE_AVERAGE_BALANCES_FLAG||SOB.CONSOLIDATION_SOB_FLAG||SOB.TRANSACTION_CALENDAR_ID||SOB.NET_INCOME_CODE_COMBINATION_ID
||SOB.DAILY_TRANSLATION_RATE_TYPE||SOB.TRANSLATE_EOD_FLAG||SOB.TRANSLATE_QATD_FLAG||SOB.TRANSLATE_YATD_FLAG "NOT USED"
, '---BUDGET CNTL---'
, SOB.ENABLE_BUDGETARY_CONTROL_FLAG||SOB.REQUIRE_BUDGET_JOURNALS_FLAG||SOB.RES_ENCUMB_CODE_COMBINATION_ID "NOT USED"
, '---MRC---'
, SOB.MRC_SOB_TYPE_CODE "NOT USED"
FROM GL_SETS_OF_BOOKS SOB, FND_ID_FLEX_STRUCTURES FST, GL_CODE_COMBINATIONS TRAN, GL_CODE_COMBINATIONS RET, GL_PERIOD_TYPES PT
WHERE FST.ID_FLEX_NUM = SOB.CHART_OF_ACCOUNTS_ID
AND RET.CODE_COMBINATION_ID(+) = SOB.RET_EARN_CODE_COMBINATION_ID
AND TRAN.CODE_COMBINATION_ID(+) = SOB.CUM_TRANS_CODE_COMBINATION_ID
AND PT.PERIOD_TYPE = SOB.ACCOUNTED_PERIOD_TYPE
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('BE','LU','ES','IT','HU','CZ','PL','RU')
ORDER BY 2
GL Summary Account Template Definition Review

/* GL SUMMARY TEMPLATE DEFINITIONS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
SMALL SCRIPT SHOWING SUMMARY TEMPLATE CONFIGURATION ACROSS MULTIPLE BOOKS,
(TESTED ON VISION 11.5.10.2 JUL-2007 ) */
SELECT SOB.NAME
, ST.TEMPLATE_NAME
, ST.CONCATENATED_DESCRIPTION
, ST.ACCOUNT_CATEGORY_CODE"CAT"
, ST.START_ACTUALS_PERIOD_NAME "FROM"
, ST.SEGMENT1_TYPE||'-'||ST.SEGMENT2_TYPE||'-'||ST.SEGMENT3_TYPE||'-'||ST.SEGMENT4_TYPE||'-'||ST.SEGMENT5_TYPE||'-'||
ST.SEGMENT6_TYPE||'-'||ST.SEGMENT7_TYPE||'-'||ST.SEGMENT8_TYPE||'-'||ST.SEGMENT9_TYPE||'-'||ST.SEGMENT10_TYPE "SEGMENT TYPE"
FROM GL_SUMMARY_TEMPLATES ST, GL_SETS_OF_BOOKS SOB
WHERE ST.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
--AND SUBSTR(SOB.NAME,1,2) IN ('ES','BE','LU')
GL Segment Value Listing

/* SEGMENT VALUE SET LISTINGS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
LISTS SINGLE OR MULTIPLE SEGMENT VALUE SETS. THIS IS USED TO PERFORM A QA ON CHART OF ACCOUNTS VALUES.
EXAMPLES OF OPTIONAL WHERE CLAUSES HAVE ALSO BEEN PROVIDED BELOW.
( TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT FFVS1.FLEX_VALUE_SET_NAME
--, FFVS1.FLEX_VALUE_SET_ID
, FFVAL1.FLEX_VALUE"VALUE"
, FFVAL1.SUMMARY_FLAG"PARENT ACC ?"
, FFVTL1.DESCRIPTION
, FFVAL1.ENABLED_FLAG
, FH.HIERARCHY_CODE
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),1,1)"BUDGET"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),3,1)"POST"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),5,1)"TYPE"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1)"CNTL"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),9,1)"RECON"
--SELECT DISTINCT FFVS1.FLEX_VALUE_SET_NAME
, FFVAL1.LAST_UPDATED_BY
, FFVAL1.LAST_UPDATE_DATE
FROM FND_FLEX_VALUES FFVAL1
, FND_FLEX_VALUES_TL FFVTL1
, FND_FLEX_VALUE_SETS FFVS1
, FND_ID_FLEX_SEGMENTS SEG
, FND_FLEX_HIERARCHIES_VL FH
WHERE FFVAL1.FLEX_VALUE_SET_ID(+) = FFVS1.FLEX_VALUE_SET_ID
AND SEG.FLEX_VALUE_SET_ID = FFVS1.FLEX_VALUE_SET_ID
AND SEG.ID_FLEX_NUM = 51974 /* COA ID IS NEEDED IF SEGMENT IS CHART IN MULTPLE COA. UPDATE FOR YOU CONFIGURATION OR REMOVE IF NOT APPLICABLE. */
AND FFVAL1.FLEX_VALUE_ID = FFVTL1.FLEX_VALUE_ID(+)
AND FFVS1.FLEX_VALUE_SET_NAME = 'OPERATIONS ACCOUNT'
AND FFVAL1.STRUCTURED_HIERARCHY_LEVEL = FH.HIERARCHY_ID(+)
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) != 'N' -- NON-CONTROL ACCOUNTS ONLY
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) = 'Y' -- CONTROL ACCOUNTS ONLY
--AND FFVAL1.SUMMARY_FLAG = 'Y'
--AND FFVAL1.FLEX_VALUE >= '8000'
--AND FFVAL1.FLEX_VALUE <= '99999'
--AND FFVTL1.DESCRIPTION LIKE '%FTE%'
--AND FFVAL1.FLEX_VALUE LIKE '16%'
ORDER BY FFVS1.FLEX_VALUE_SET_NAME, FFVAL1.FLEX_VALUE
GL Period Status

/* GL PERIOD STATUSES
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
TWO SMALL SCRIPTS FOR REVIEWING OPEN PERIODS ACROSS MULTIPEL BOOKS. ( MONTH END CLOSE CHECKING OR AUTOMATED ALERTS )
AND PERIOD STATUS FOR A GIVEN YEAR AND BOOK.
(TESTED ON VISION 11.5.10.2 JUL-2007 ) */
SELECT SOB.SHORT_NAME
, PS.PERIOD_NAME
, PS.SHOW_STATUS
, PS.START_DATE||' TO '||PS.END_DATE
, PS.PERIOD_YEAR
, PS.PERIOD_NUM
FROM GL_PERIOD_STATUSES_V PS, GL_SETS_OF_BOOKS SOB
WHERE PS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND APPLICATION_ID = 101
--AND PERIOD_YEAR = 2006
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('ES','LU','BE')
AND PS.SHOW_STATUS NOT IN ('NEVER OPENED')
ORDER BY 1,5,6 DESC

SELECT SOB.SHORT_NAME
, PS.PERIOD_NAME
, PS.START_DATE
, PS.END_DATE
, PS.PERIOD_YEAR
, PS.PERIOD_NUM
, PS.SHOW_STATUS
FROM GL_PERIOD_STATUSES_V PS, GL_SETS_OF_BOOKS SOB
WHERE PS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND APPLICATION_ID = 101
AND PERIOD_YEAR = 2006
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('GB')
ORDER BY 1,5,6 DESC
GL Chart of Accounts Structure

/* CHART OF ACCOUNTS STRUCTURE
Written by Daniel North, ORAFINAPPS Limited 2007
Gives an overview of the chart of accounts definitions and also status.
This is used when implementing multiple charts of accounts to ensure consistent setup across countries and between environments.
Where clause can be added or commented out to just look at specific countries. */
SELECT FST.ID_FLEX_STRUCTURE_NAME
--, FST.DESCRIPTION
--, FST.ID_FLEX_NUM
--, FST.ID_FLEX_STRUCTURE_CODE
, FST.CROSS_SEGMENT_VALIDATION_FLAG"X-VAL"
, FST.FREEZE_STRUCTURED_HIER_FLAG"FZ-HIER"
, FST.FREEZE_FLEX_DEFINITION_FLAG"FZ-DEFN"
, FSEG.SEGMENT_NUM "SEG#"
, FSEG.SEGMENT_NAME "SEG NAME"
, VS.FLEX_VALUE_SET_NAME "VALUE SET"
, FSEG.FLEX_VALUE_SET_ID"VAL_SET_ID"
, FSEG.DEFAULT_TYPE"DEF TYPE"
, FSEG.DEFAULT_VALUE"DEF. VALUE"
, FSEG.ENABLED_FLAG"ENBLD"
, FSEG.REQUIRED_FLAG"REQD"
FROM FND_ID_FLEX_STRUCTURES_VL FST, FND_ID_FLEX_SEGMENTS FSEG, FND_FLEX_VALUE_SETS VS
WHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM
AND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('BE','LU','ES','IT','HU','CZ','PL','RU')
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
ORDER BY 1, FSEG.SEGMENT_NUM
GL Chart of Accounts Structure Overview

/* CHART OF ACCOUNTS STRUCTURE
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
GIVES AN OVERVIEW OF THE CHART OF ACCOUNTS DEFINITIONS AND ALSO STATUS.
THIS IS USED WHEN IMPLEMENTING MULTIPLE CHARTS OF ACCOUNTS TO ENSURE CONSISTENT SETUP ACROSS COUNTRIES AND BETWEEN ENVIRONMENTS.
WHERE CLAUSE CAN BE ADDED OR COMMENTED OUT TO JUST LOOK AT SPECIFIC COUNTRIES. */
SELECT FST.ID_FLEX_STRUCTURE_NAME
--, FST.DESCRIPTION
--, FST.ID_FLEX_NUM
--, FST.ID_FLEX_STRUCTURE_CODE
, FST.CROSS_SEGMENT_VALIDATION_FLAG"X-VAL"
, FST.FREEZE_STRUCTURED_HIER_FLAG"FZ-HIER"
, FST.FREEZE_FLEX_DEFINITION_FLAG"FZ-DEFN"
, FSEG.SEGMENT_NUM "SEG#"
, FSEG.SEGMENT_NAME "SEG NAME"
, VS.FLEX_VALUE_SET_NAME "VALUE SET"
, FSEG.FLEX_VALUE_SET_ID"VAL_SET_ID"
, FSEG.DEFAULT_TYPE"DEF TYPE"
, FSEG.DEFAULT_VALUE"DEF. VALUE"
, FSEG.ENABLED_FLAG"ENBLD"
, FSEG.REQUIRED_FLAG"REQD"
FROM FND_ID_FLEX_STRUCTURES_VL FST, FND_ID_FLEX_SEGMENTS FSEG, FND_FLEX_VALUE_SETS VS
WHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM
AND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('BE','LU','ES','IT','HU','CZ','PL','RU')
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
ORDER BY 1, FSEG.SEGMENT_NUM
GL Journal Header Summary

/* GL JOURNAL HEADER SUMMARY
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
SUMMARY LISTING OF JOURNAL HEADER RECORDS BY CATEGORY AND SOURCE ACROSS MULTIPLE SETS OF BOOKS.
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */
SELECT SOB.SHORT_NAME"BOOK"
, GJH.STATUS
, GJH.POSTED_DATE
, GJH.CREATION_DATE
, GLS.USER_JE_SOURCE_NAME"SOURCE"
, GLC.USER_JE_CATEGORY_NAME"CATEGORY"
, GJH.PERIOD_NAME"PERIOD"
, GJB.NAME"BATCH NAME"
, GJH.NAME"JOURNAL NAME"
, GJH.CURRENCY_CODE"CURRENCY"
FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH,GL_SETS_OF_BOOKS SOB,
GL_JE_SOURCES GLS, GL_JE_CATEGORIES GLC
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
--AND GJH.NAME = 'QUV-DECLARATION TVA 11/04' -- JOURNAL NAME
--AND GLS.USER_JE_SOURCE_NAME LIKE '%MASS%' -- JOURNAL SOURCE
--AND GLC.USER_JE_CATEGORY_NAME= 'ADJUSTMENT' -- JOURNAL CATEGORY
--AND GJH.PERIOD_NAME IN ('MAY-06') -- JOURNAL PERIOD
AND (TRUNC(GJH.CREATION_DATE) >= TO_DATE('01/07/2002','DD/MM/YYYY')
OR TRUNC(GJH.POSTED_DATE) >= TO_DATE('01/07/2002','DD/MM/YYYY'))
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('DE')
ORDER BY 1,2 DESC,3,4,5,7
GL Journal Line Based Trial Balance Report

/* GL JOURNAL BASED TRIAL BALANCE
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
CREATES A TRIAL BALANCE BASED ON JOURNAL LINES. CAN BE USED FOR NERVOUS DATA CONVERSION MANAGERS AS YOU CAN SEE THE IMPACT
OF JOURNALS ON ACCOUNT BALANCES WITHOUT THE NEED TO POST THE JOURNALS.
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */
SELECT SOB.SHORT_NAME
, SOB.NAME
, GJH.NAME
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9 "ACCOUNT"
, GJH.CURRENCY_CODE
, SUM(GJL.ACCOUNTED_DR)"DR"
, SUM(GJL.ACCOUNTED_CR)"CR"
, SUM( NVL(GJL.ACCOUNTED_DR,0) - NVL(GJL.ACCOUNTED_CR,0))"END BALANCE"
, GJL.PERIOD_NAME
FROM GL_JE_LINES GJL
, GL_JE_HEADERS GJH
, GL_CODE_COMBINATIONS GCC
, GL_SETS_OF_BOOKS SOB
WHERE GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = GJL.SET_OF_BOOKS_ID
AND GJL.PERIOD_NAME = 'JUL-03'
--AND SOB.SHORT_NAME = 'GBMAN'
--AND GJH.NAME LIKE '%PPL%'
--AND GCC.SEGMENT1 = '85'
--AND GCC.SEGMENT2 = '70'
--AND GCC.SEGMENT3 = '0000'
--AND GCC.SEGMENT4 = '88165'
--AND GJH.STATUS = 'P'
--AND GJL.EFFECTIVE_DATE >= TO_DATE('06/04/2002','DD/MM/YYYY')
--AND GJL.EFFECTIVE_DATE <= TO_DATE('30/11/2002','DD/MM/YYYY')
GROUP BY SOB.SHORT_NAME, SOB.NAME, GJH.NAME
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9
,GJH.CURRENCY_CODE, GJL.PERIOD_NAME
GL Journal Lines With AP Source Reference Fields

/* GL JOURNAL LINES WITH AP SOURCE REFERENCE FIELDS
Written by Daniel North, ORAFINAPPS Limited 2007
(Tested on Vision 11.5.10.2 June 2007 ) */
SELECT SOB.SHORT_NAME"BOOK"
, GLS.USER_JE_SOURCE_NAME"SOURCE"
, GLC.USER_JE_CATEGORY_NAME"CATEGORY"
, GJB.NAME"BATCH NAME"
, GJH.NAME"JOURNAL NAME"
, GJH.CURRENCY_CODE"CURRENCY"
, GJL.JE_LINE_NUM"JRNL LINE#"
, GJL.EFFECTIVE_DATE"ACCOUNTING DATE"
, GJH.PERIOD_NAME"PERIOD"
, GJH.DATE_CREATED"CREATED DATE"
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 "ACCOUNT COMBINATION"
, GJL.ENTERED_DR
, GJL.ENTERED_CR
, GJL.ACCOUNTED_DR
, GJL.ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_RATE"CONV RATE"
, GJH.CURRENCY_CONVERSION_DATE"CONV DATE"
, GJH.CURRENCY_CONVERSION_TYPE"CONV TYPE"
, GJL.DESCRIPTION
, GJL.REFERENCE_1"AP VAND NAME"
, GJL.REFERENCE_2"AP INV_ID"
, GJL.REFERENCE_3"AP INV LINE#CHEQUEID"
, GJL.REFERENCE_4"AP PAYDOC#"
, GJL.REFERENCE_5"AP INVOICE #"
, GJL.REFERENCE_6"AP ACCOUNTING TYPE"
, GJL.REFERENCE_7"AP SOURCE ID"
, GJL.REFERENCE_8"AP NA"
, GJL.REFERENCE_9"AP DOCUMENT ID"
, GJL.REFERENCE_10"AP LINE TYPE"
FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH, GL_JE_LINES GJL,GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB,
GL_JE_SOURCES GLS, GL_JE_CATEGORIES GLC
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJH.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GLS.USER_JE_SOURCE_NAME = 'Payables'
and GJH.PERIOD_NAME = 'JUL-04'
--and sob.set_of_books_id = 87
order by 1,2,3,4,5,7

GL Mass Allocation Rule Migration Script in Dataload Classic Format

/* MASS ALLOCATION MIGRATION - DATALOAD CLASSIC LAYOUT
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
CREATES A PRE-FORMATED SPREADSHEET LAYOUT TO MIGRATE MASS ALLOCATIONS BETWEEN ENVIRONMENTS AND/OR BOOKS USING DATALOAD CLASSIC.
IT HAS BEEN WRITTEN FOR A 10 SEGMENT COA BUT CAN BE MODIFIED TO SUIT DIFFERENT STRUCTURES.
(TESTED ON VISION 11.5.9 JAN-2007 ) */
SELECT SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2)"BOOK"
, GAB.NAME "ALLOCATION NAME"
/*, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN GAB.NAME ELSE NULL END )"ALLOCATION NAME"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'A' ELSE NULL END )"A"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN GAB.DESCRIPTION ELSE NULL END )"ALLOC DESCRIPTION"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN '*AR' ELSE NULL END )"TAB"
,*/
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN '\'||GAF.NAME ELSE NULL END )"FORMULA NAME"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'ALLOCATION' ELSE NULL END )"ALLOCATION"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN GAF.DESCRIPTION ELSE NULL END )"FORMULA DESC"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN '*SB' ELSE NULL END )"FCP"
, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN (CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4) THEN 'TAB' ELSE NULL END )ELSE NULL END )"TAB"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT1 ELSE '\'||TO_CHAR(GAFL.AMOUNT) END )"1"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,0,1) ELSE NULL END )"1T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT2 ELSE NULL END )"2"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)ELSE NULL END )"2T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT3 ELSE NULL END )"3"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)ELSE NULL END )"3T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT4 ELSE NULL END )"4"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)ELSE NULL END )"4T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT5 ELSE NULL END )"5"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)ELSE NULL END )"5T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT6 ELSE NULL END )"6"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)ELSE NULL END )"6T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT7 ELSE NULL END )"7"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)ELSE NULL END )"7T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT8 ELSE NULL END )"8"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)ELSE NULL END )"8T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT9 ELSE NULL END )"9"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)ELSE NULL END )"9T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT10 ELSE NULL END )"10"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)ELSE NULL END )"10T"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4,5) THEN 'ENT' ELSE NULL END )ELSE NULL END )"TAB1"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4) THEN GAFL.CURRENCY_CODE ELSE NULL END )ELSE NULL END )"CURR"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4) THEN 'TAB' ELSE NULL END )ELSE NULL END)"TAB2"
, (CASE WHEN GAFL.AMOUNT IS NOT NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (2) THEN 'TAB' ELSE NULL END )ELSE NULL END)"TAB2"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3) THEN GAFL.AMOUNT_TYPE ELSE NULL END )ELSE NULL END )"PTD/YTD"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2) THEN '\{TAB 3}' ELSE (CASE WHEN GAFL.LINE_NUMBER IN (3) THEN '\{TAB 2}' ELSE NULL END) END)ELSE NULL END )"TAB3"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*SAVE' ELSE NULL END )ELSE NULL END )"*SAVE"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*PB' ELSE NULL END )ELSE NULL END )"*PB"
, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*NR' ELSE NULL END )ELSE NULL END )"*NR"
FROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL, FND_ID_FLEX_STRUCTURES_VL FST
WHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID
AND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM
AND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('DE')
ORDER BY 1,GAB.NAME, GAF.NAME, GAFL.LINE_NUMBER

GL Balances and Movements

/* GL BALANCES & MOVEMENTS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
GIVES A TRIAL BALANCE WITH OPENING, MOVEMENT AND CLOSING BALANCES FOR UPTO TEN SEGMENTS IN THE CHART OF ACCOUNTS BY CURRENCY.
THIS CAN BE USED TO AS A QUICK METHOD OF RUNNING A TRIAL BALANCE FOR DATA EXTRACT IN THE DESIRED FORMAT.
FOR EXAMPLE TO USE TO EXTRACT TO A THIRD PARTY REPORTING SYSTEM SUCH AS HYPERION
IT IS RECOMMENDED THAT THIS SCRIPT IS RUN FOR A SINGLE PERIOD AND BOOK FIRST TO GAUGE PERFORMANCE IN YOUR ENVIRONMENT.
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */
SELECT SOB.NAME
, GB.ACTUAL_FLAG
, GB.PERIOD_NAME
, GCC.CODE_COMBINATION_ID
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 "DISTRIBUTION"
,SUM( NVL(GB.BEGIN_BALANCE_DR,0) - NVL(GB.BEGIN_BALANCE_CR,0))"OPEN BAL"
,NVL(GB.PERIOD_NET_DR,0) "DEBIT"
,NVL(GB.PERIOD_NET_CR,0) "CREDIT"
,SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))"NET MOVEMENT"
,SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0))"CLOSE BAL"
, GB.CURRENCY_CODE
, GB.TRANSLATED_FLAG
, GB.TEMPLATE_ID
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
AND GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GB.PERIOD_NAME = 'APR-04'
AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('PR')
--AND GCC.SEGMENT1 = '85'
--AND GCC.SEGMENT2 = '70'
--AND GCC.SEGMENT3 = '0000'
--AND GCC.SEGMENT4 IN ('99659')
--AND GCC.SEGMENT7 = 'T'
--AND NVL(GB.TRANSLATED_FLAG,'X') != 'R'
GROUP BY SOB.NAME
, GB.ACTUAL_FLAG
, GB.PERIOD_NAME
, GCC.CODE_COMBINATION_ID
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10
, NVL(GB.PERIOD_NET_DR,0)
, NVL(GB.PERIOD_NET_CR,0)
, GB.CURRENCY_CODE
, GB.TRANSLATED_FLAG
, GB.TEMPLATE_ID
HAVING SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)) <> 0
GL Chart of Account Segment Hierarchy Ranges

/* GL : CCHART OF ACCOUNT SEGMENT HIERARCHY RANGES
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
CHART OF ACCOUNT SEGMENT HIERARCHY RANGES AND ATTRIBUTES FOR PARENT ACCOUNTS
*/
SELECT FVS.FLEX_VALUE_SET_NAME"VALUE SET"
, FV.FLEX_VALUE
, NH.PARENT_FLEX_VALUE "PARENT"
, FVT.DESCRIPTION
, NH.RANGE_ATTRIBUTE "INC C OR P?"
, NH.CHILD_FLEX_VALUE_LOW "FROM"
, NH.CHILD_FLEX_VALUE_HIGH "TO"
, NH.PARENT_FLEX_VALUE || ' : ' ||NH.RANGE_ATTRIBUTE || ' : ' ||
NH.CHILD_FLEX_VALUE_LOW || ' -> ' ||NH.CHILD_FLEX_VALUE_HIGH "HIERARCHY RANGE"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,1,1)"POSTING"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,3,1)"BUDGETING"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,5,1)"ACC TYPE"
, FV.ENABLED_FLAG"ENABLED"
, FV.SUMMARY_FLAG"PARENT?"
, NH.LAST_UPDATE_DATE
, FV.HIERARCHY_LEVEL"LEVEL"
FROM FND_FLEX_VALUE_NORM_HIERARCHY NH, FND_FLEX_VALUE_SETS FVS, FND_FLEX_VALUES_TL FVT, FND_FLEX_VALUES FV
WHERE FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
AND NH.PARENT_FLEX_VALUE(+) = FVT.FLEX_VALUE_MEANING
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_NAME LIKE '%ACCOUNT%' --- CHART OF ACCOUNTS SEGMENT NAME
-- AND SUBSTR(FVS.FLEX_VALUE_SET_NAME,4,2) IN ('BE','LU','ES')
AND FV.SUMMARY_FLAG = 'Y'
AND FV.FLEX_VALUE LIKE '%XYZ%' --- THIS IS THE PARENT SEGMENT VALUES
-- AND NH.PARENT_FLEX_VALUE = '%%'
-- AND FV.ENABLED_FLAG = 'Y'
-- AND FV.HIERARCHY_LEVEL = '2'
ORDER BY 1,3
GL Code Combinations CCIDs

/* GL CODE COMBINATIONS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
GL CODE COMBINATIONS EXTRACT. CAN BE SELECT BY CHART OF ACCOUNTS, SPECIFIC SEGMENT VALUES OR SPECIFIC CODE COMBINATION ATTRIBUTES.
THIS CAN BE USED FOR CHART OF ACCOUNTS MAINTENANCE AND REVIEW
(TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT FST.ID_FLEX_STRUCTURE_NAME
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
, GCC.CODE_COMBINATION_ID
, GCC.LAST_UPDATE_DATE
, GCC.JGZZ_RECON_FLAG
, GCC.START_DATE_ACTIVE
, GCC.END_DATE_ACTIVE
, GCC.DETAIL_POSTING_ALLOWED_FLAG
, GCC.ENABLED_FLAG
, GCC.SUMMARY_FLAG
, GCC.START_DATE_ACTIVE
FROM GL_CODE_COMBINATIONS GCC
, FND_ID_FLEX_STRUCTURES_VL FST
WHERE FST.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
--AND GCC.SEGMENT1 IN ('25','26','30')
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
--AND GCC.SEGMENT4 = '99901'
ORDER BY 1,2,3
GL CVR Cross Validation Rule Detail Listing

/* CVR CROSS VALIDATION RULE DETAIL LISTING
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
PROVIDES DETAIL VIEW OF CVR DEFINITION INCLUDING ACCOUNT RANGES.
(TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT FST.ID_FLEX_STRUCTURE_NAME
, R.FLEX_VALIDATION_RULE_NAME
, R.ENABLED_FLAG
--, R.ERROR_SEGMENT_COLUMN_NAME"ERR SEG"
--, TL.DESCRIPTION
--, TL.ERROR_MESSAGE_TEXT"ERROR MESSAGE"
, L.ENABLED_FLAG
, L.INCLUDE_EXCLUDE_INDICATOR"INC?"
, L.CONCATENATED_SEGMENTS_LOW "FROM"
, L.CONCATENATED_SEGMENTS_HIGH "TO"
, L.LAST_UPDATED_BY
, L.LAST_UPDATE_DATE
FROM FND_FLEX_VALIDATION_RULES R,
FND_FLEX_VDATION_RULES_TL TL,
FND_FLEX_VALIDATION_RULE_LINES L,
FND_ID_FLEX_STRUCTURES_VL FST
WHERE R.APPLICATION_ID = TL.APPLICATION_ID
AND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
AND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND R.APPLICATION_ID = L.APPLICATION_ID
AND R.ID_FLEX_CODE = L.ID_FLEX_CODE
AND R.ID_FLEX_NUM = L.ID_FLEX_NUM
AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND R.APPLICATION_ID = 101
-- OPTIONAL FILTERS BELOW TO LIMIT QUERY TO SPECIFIC CVR OR LINES
--AND R.ERROR_SEGMENT_COLUMN_NAME = 'SEGMENT5'
--AND TL.ERROR_MESSAGE_TEXT LIKE '%PLEASE USE A VALID R%'
--AND R.FLEX_VALIDATION_RULE_NAME LIKE 'BE GROUP ERROR%'
--AND TL.ERROR_MESSAGE_TEXT LIKE '%94005%'
--AND L.INCLUDE_EXCLUDE_INDICATOR = 'E'
ORDER BY 1,R.FLEX_VALIDATION_RULE_NAME, L.INCLUDE_EXCLUDE_INDICATOR DESC, L.CONCATENATED_SEGMENTS_LOW

GL CVR Cross Validation Rule Overview

/* CVR OVERVIEW (CROSS VALIDATION RULES )
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
PROVIDES VIEW OF HEADER LEVEL CROSS VALIDATION RULE DEFINITIONS TO
OBTAIN AN OVERVIEW OF RULES AND MESSAGES ACROSS MULTIPLE CHARTS OF ACCOUNTS
(TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT FST.ID_FLEX_STRUCTURE_NAME"COA"
, R.FLEX_VALIDATION_RULE_NAME"RULE NAME"
, R.ENABLED_FLAG"ENB?"
, R.ERROR_SEGMENT_COLUMN_NAME"ERROR SEG"
, LENGTH(TL.ERROR_MESSAGE_TEXT)"ERROR LENGTH"
, TL.ERROR_MESSAGE_TEXT"MESSAGE"
, TL.CREATION_DATE
--SELECT COUNT(*), FST.ID_FLEX_STRUCTURE_NAME
FROM FND_FLEX_VALIDATION_RULES R,
FND_FLEX_VDATION_RULES_TL TL,
FND_ID_FLEX_STRUCTURES_VL FST
WHERE R.APPLICATION_ID = TL.APPLICATION_ID
AND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
AND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND R.APPLICATION_ID = 101
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('BE','LU','ES') -- LIMITS RESULTS TO SPECIFIC CHARTS OF ACCOUNTS
--AND LENGTH(TL.ERROR_MESSAGE_TEXT) > 150 --- THIS IS USED FOR CHECK FOR MESSAGES OVER 150 CHARACTERS THAT CAN CAUSE SQL ERRORS IN I-EXPENSES
ORDER BY 1,2
GL Flexfield Security Rule Assignments

/* FLEXFIELD SECURITY RULE ASSIGNMENTS TO RESPONSIBILITIES
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
LISTS SECURITY RULE ASSIGNMENTS TO RESPONSIBILITIES
(TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT A.APPLICATION_NAME
, FVR.FLEX_VALUE_RULE_NAME
, R.RESPONSIBILITY_KEY
FROM FND_FLEX_VALUE_RULES FVR,
FND_FLEX_VALUE_RULE_USAGES RU, FND_RESPONSIBILITY R, FND_APPLICATION_TL A
WHERE FVR.FLEX_VALUE_RULE_ID = RU.FLEX_VALUE_RULE_ID
AND RU.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND RU.APPLICATION_ID = A.APPLICATION_ID
AND FVR.FLEX_VALUE_RULE_NAME LIKE '%'
ORDER BY FLEX_VALUE_RULE_NAME
GL Flexfield Security Rule Definitions

/* FLEXFIELD SECURITY RULE DEFINITIONS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
LISTS SECURITY RULE DEFINITIONS AND GL ACCOUNT RANGES
(TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT A.APPLICATION_NAME "APPS" ,
FS.SEGMENT_NAME ,
FVS.FLEX_VALUE_SET_NAME ,
FVR.FLEX_VALUE_RULE_NAME ,
FVR.PARENT_FLEX_VALUE_LOW "PRNT L" ,
FVR.PARENT_FLEX_VALUE_HIGH "PRNT H" ,
FVRL.INCLUDE_EXCLUDE_INDICATOR "INC/EXCL" ,
FVRL.FLEX_VALUE_LOW ,
FVRL.FLEX_VALUE_HIGH
FROM FND_FLEX_VALUE_RULES FVR, FND_FLEX_VALUE_SETS FVS,
FND_FLEX_VALUE_RULE_LINES FVRL, FND_ID_FLEX_SEGMENTS FS, FND_APPLICATION_TL A
WHERE FVR.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID
AND FVR.FLEX_VALUE_RULE_ID = FVRL.FLEX_VALUE_RULE_ID
AND FS.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID
AND FS.APPLICATION_ID = A.APPLICATION_ID
ORDER BY A.APPLICATION_NAME ,FS.SEGMENT_NAME, FVS.FLEX_VALUE_SET_NAME,FVR.FLEX_VALUE_RULE_NAME ,
FVR.PARENT_FLEX_VALUE_LOW ,FVR.PARENT_FLEX_VALUE_HIGH ,FVRL.FLEX_VALUE_LOW, FVRL.FLEX_VALUE_HIGH
GL FSG Report and Components Overview

/* FSG REPORTS AND COMPONENTS OVERVIEW
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
DETAILS DEFINITIONS OF FSG REPORTS BY COMPONENT, AND INCLUDES SEVERAL SMALL SCRIPTS FOR LISTING ALL COMPONENTS ACROSS DIFFERENT CHARTS OF ACCOUNTS.
CAN BE USED FOR SOX AND SYSTEM AUDITS.
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */

-- FSG REPORTS --------------
SELECT FST.ID_FLEX_STRUCTURE_NAME
, R.NAME
, R.REPORT_TITLE
, R.DESCRIPTION
, R.COLUMN_SET"COLUMN SET"
, RW2.STRUCTURE_ID
, RW2.DESCRIPTION"COL DESC"
, R.ROW_SET"ROW SET"
, RW.DESCRIPTION"ROW DESC"
, R.REPORT_DISPLAY_SET"DISPLAY SET"
, R.CONTENT_SET"CONTENT SET"
, R.ROW_ORDER"ROW ORDER"
, R.ROUNDING_OPTION "RND"
, U.USER_NAME
, U.DESCRIPTION
, R.CREATION_DATE
FROM RG_REPORTS_V R, FND_ID_FLEX_STRUCTURES_V FST, FND_USER U, RG_REPORT_AXIS_SETS_V RW, RG_REPORT_AXIS_SETS_V RW2
WHERE R.STRUCTURE_ID = FST.ID_FLEX_NUM
AND R.ROW_SET_ID = RW.AXIS_SET_ID
AND R.COLUMN_SET_ID = RW2.AXIS_SET_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
AND R.CREATED_BY = U.USER_ID
ORDER BY 1, 2

-- FSG ROW SETS AND COLUMN SETS ----------------
SELECT FST.ID_FLEX_STRUCTURE_NAME "COA"
, DECODE(RW.AXIS_SET_TYPE,'R','ROW SET','C','COLUMN SET','##') "ROW/COLUMN"
, RW.NAME"SET NAME"
, RW.AXIS_SET_ID
FROM RG_REPORT_AXIS_SETS_V RW, FND_ID_FLEX_STRUCTURES_V FST
WHERE RW.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
ORDER BY 1,2,3

---- CONTENT SETS ------------------------
SELECT FST.ID_FLEX_STRUCTURE_NAME "COA"
, CS.NAME
, CS.CONTENT_SET_ID
FROM RG_REPORT_CONTENT_SETS CS, FND_ID_FLEX_STRUCTURES_V FST
WHERE CS.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')

----- ROW ORDERS ---------------------------
SELECT FST.ID_FLEX_STRUCTURE_NAME "COA"
, RO.NAME "ROW ORDER"
, RO.DESCRIPTION "DESCRIPTION"
, RO.STRUCTURE_ID
, RO.ROW_ORDER_ID
FROM RG_ROW_ORDERS RO, FND_ID_FLEX_STRUCTURES_V FST
WHERE RO.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
ORDER BY RO.NAME

GL Interface Details

/* GL INTERFACE DETAIL
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
SHOWS TRANSACTIONS LEVEL DETAIL WITH FULL ACCOUNTING AND STATUS INFORMATION FOR EACH LINE IN THE GL INTERFACE ACROSS MULTIPLE SETS OF BOOKS
CAN BE USED FOR SOX AND SYSTEM AUDITS.
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */
SELECT SOB.SHORT_NAME "BOOK"
, GLI.SET_OF_BOOKS_ID "SOB ID"
, TRUNC(GLI.ACCOUNTING_DATE) "GL DATE"
, GLI.CURRENCY_CODE "CUR"
, GLI.USER_JE_CATEGORY_NAME "JE CATEGOTY"
, GLI.USER_JE_SOURCE_NAME "JE SOURCE"
, GLI.ENTERED_DR "ENT DR"
, GLI.ENTERED_CR "ENT CR"
, GLI.ACCOUNTED_DR "ACC DR"
, GLI.ACCOUNTED_CR "ACC CR"
, GLI.SEGMENT1||'.'||GLI.SEGMENT2||'.'||GLI.SEGMENT3||'.'||GLI.SEGMENT4||'.'||GLI.SEGMENT5
||'.'||GLI.SEGMENT6||'.'||GLI.SEGMENT7||'.'||GLI.SEGMENT8||'.'||GLI.SEGMENT9||'.'||GLI.SEGMENT10 "ACCOUNT COMB."
, GLI.REFERENCE1 "REF 1"
, GLI.REFERENCE2 "REF 2"
, GLI.REFERENCE4 "REF 4"
, GLI.REFERENCE7 "REF 7"
, GLI.REFERENCE10 "REF 10"
, GLI.WARNING_CODE
, GLI.STATUS_DESCRIPTION
, GLI.STATUS
--SELECT GLI.REFERENCE10 "REF 10"
--SELECT DISTINCT GLI.SEGMENT4--,GLI.SEGMENT2, GLI.SEGMENT3, SOB.SHORT_NAME, GLI.SET_OF_BOOKS_ID
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID
--AND GLI.WARNING_CODE IS NOT NULL
--AND GLI.STATUS <> 'P'
AND GLI.USER_JE_SOURCE_NAME = 'PAYABLES'
--AND TRUNC(GLI.DATE_CREATED) > '01-DEC-2005'
--AND GLI.CURRENCY_CODE = 'GBP'
--AND (GLI.ENTERED_DR <> GLI.ACCOUNTED_DR
-- OR GLI.ENTERED_CR <> GLI.ACCOUNTED_CR)
--AND GLI.USER_JE_CATEGORY_NAME = 'BILL'
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('BE')
--AND GLI.SEGMENT3 = '8181'
--AND GLI.STATUS_DESCRIPTION IS NOT NULL
ORDER BY 3
GL Interface Summary

/* GL INTERFACE SUMMARY
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
SHOWS SUMMARY BY SOURCE, BOOK, REQUEST_ID AND GROUP_ID OF THE TRANSACTIONS IN THE GL INTERFACE ACROSS MULTIPLE SETS OF BOOKS
THIS CAN BE USED FOR AD-HOC QUERIES SUCH AS MONTH AND OR TO INCLUDE IN AUTOMATED ORACLE ALERTS
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */
SELECT SOB.SHORT_NAME"BOOK NAME"
, GLI.USER_JE_SOURCE_NAME "JRNL SOURCE"
, GLI.SET_OF_BOOKS_ID "BOOKS ID"
--, TRUNC(ACCOUNTING_DATE)"GL DATE"
, PERIOD_NAME"PERIOD"
, GLI.STATUS
, GLI.GROUP_ID
, GLI.REQUEST_ID
, TRUNC(GLI.DATE_CREATED) "CREATED DATE"
--,GLI.DATE_CREATED
, TRUNC(GLI.ACCOUNTING_DATE)"GL DATE"
, COUNT(*)
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID
--AND GLI.USER_JE_SOURCE_NAME = 'PEOPLESOFT HR'
AND GLI.USER_JE_SOURCE_NAME = 'RECEIVABLES'
--AND TRUNC(GLI.DATE_CREATED) > '01-DEC-2005'
--AND GLI.USER_JE_SOURCE_NAME = 'PAYROLL'
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('ES','BE','LU')
GROUP BY SOB.SHORT_NAME,GLI.USER_JE_SOURCE_NAME, GLI.SET_OF_BOOKS_ID,
PERIOD_NAME, GLI.STATUS, GLI.GROUP_ID, TRUNC(GLI.DATE_CREATED),TRUNC(ACCOUNTING_DATE)--,GLI.DATE_CREATED
, GLI.REQUEST_ID
--,GLI.DATE_CREATED
--ORDER BY GLI.DATE_CREATED
GL Mass Allocation Formula review script

/* MASS ALLOCATION FORMULA REVIEW SCRIPT
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
WILL SHOW THE DEFINITION OF MASS ALLOCATION BATCHES AND LINES ACROSS MULTIPLE BOOKS IN AN EASY TO READ FORMAT FOR REVIEW IN EXCEL
IT HAS BEEN WRITTEN FOR A 10 SEGMENT COA BUT CAN BE MODIFIED TO SUIT DIFFERENT STRUCTURES.
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */
SELECT FST.ID_FLEX_STRUCTURE_NAME"CHART OF ACCOUNTS"
, GAB.VALIDATION_STATUS "VALID?"
, GAB.NAME "ALLOCATION NAME"
, GAF.NAME "FORMULA NAME"
, GAF.FULL_ALLOCATION_FLAG"FULL?"
--, GAF.VALIDATION_STATUS"VALID?"
, GAFL.LINE_NUMBER"LINE #"
, DECODE(GAFL.LINE_NUMBER,1,'A',2,'B',3,'C',4,'T',5,'O','XXX')"LINE"
, GAFL.AMOUNT"AMOUNT"
, GAFL.CURRENCY_CODE "CURR"
, GAFL.SEGMENT1||'-'||GAFL.SEGMENT2||'-'||GAFL.SEGMENT3||'-'||GAFL.SEGMENT4||'-'||GAFL.SEGMENT5||'-'||GAFL.SEGMENT6
||'-'||GAFL.SEGMENT7||'-'||GAFL.SEGMENT8||'-'||GAFL.SEGMENT9||'-'||GAFL.SEGMENT10 "ACCOUNT"
, GAFL.SEGMENT_TYPES_KEY "SEGMENT"
, GAFL.RELATIVE_PERIOD"PERIOD"
--, GAFL.TRANSACTION_CURRENCY"CURR"
, GAFL.ACTUAL_FLAG"ACTUAL?"
, GAFL.AMOUNT_TYPE"AMT TYPE"
FROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL
, FND_ID_FLEX_STRUCTURES_VL FST
WHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID
AND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM
AND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('BE','LU','ES')
ORDER BY 1,3,4,6
GL Mass Allocation Migration Script in Dataload Professional FLD format

/* EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
THIS IS DESIGNED TO WORK WITH A 10 SEGMENT CHART OF ACCOUNTS, SO WILL NEED TO BE MODIFIED TO SUIT YOUR STRUCTURE
THIS EXTRACT WILL ONLY WORK WITH THE FOLLOWING CONDITIONS
1- THAT LINES B&C ARE ACCOUNTS RATHER THAN VALUES. IF VALUES ARE USED THEN USE THE SECOND EXTRACT BELOW.
2- THAT RELATIVE PERIOD IS CURRENT
3- THAT AMOUNT TYPE IS ACTUAL
*/
SELECT GAB.NAME
, GAF.NAME "FORMULA NAME"
, 'ALLOCATION'
, GAF.DESCRIPTION "FORMULA DESC"
, GAFL.SEGMENT1"S11"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL.SEGMENT2"S12"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL.SEGMENT3"S13"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL.SEGMENT4"S14"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL.SEGMENT5"S15"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL.SEGMENT6"S16"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL.SEGMENT7"S17"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL.SEGMENT8"S18"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL.SEGMENT9"S19"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL.SEGMENT10"S110"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)"T"
, GAFL.CURRENCY_CODE"CURR"
, GAFL.AMOUNT_TYPE"AMT TYPE"
, GAFL2.SEGMENT1"S21"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL2.SEGMENT2"S22"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL2.SEGMENT3"S23"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL2.SEGMENT4"S24"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL2.SEGMENT5"S25"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL2.SEGMENT6"S26"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL2.SEGMENT7"S27"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL2.SEGMENT8"S28"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL2.SEGMENT9"S29"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL2.SEGMENT10"S210"
, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,19,1)"T"
, GAFL2.CURRENCY_CODE"CURR"
, GAFL2.AMOUNT_TYPE"AMT TYPE"
, GAFL3.SEGMENT1"S31"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL3.SEGMENT2"S32"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL3.SEGMENT3"S33"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL3.SEGMENT4"S34"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL3.SEGMENT5"S35"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL3.SEGMENT6"S36"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL3.SEGMENT7"S37"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL3.SEGMENT8"S38"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL3.SEGMENT9"S39"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL3.SEGMENT10"S310"
, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,19,1)"T"
, GAFL3.CURRENCY_CODE"CURR"
, GAFL3.AMOUNT_TYPE"AMT TYPE"
, GAFL4.SEGMENT1"S41"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL4.SEGMENT2"S42"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL4.SEGMENT3"S43"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL4.SEGMENT4"S44"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL4.SEGMENT5"S45"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL4.SEGMENT6"S46"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL4.SEGMENT7"S47"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL4.SEGMENT8"S48"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL4.SEGMENT9"S49"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL4.SEGMENT10"S410"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,19,1)"T"
, GAFL4.CURRENCY_CODE"CURR"
, GAFL5.SEGMENT1"S51"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL5.SEGMENT2"S52"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL5.SEGMENT3"S53"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL5.SEGMENT4"S54"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL5.SEGMENT5"S55"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL5.SEGMENT6"S56"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL5.SEGMENT7"S57"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL5.SEGMENT8"S58"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL5.SEGMENT9"S59"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL5.SEGMENT10"S510"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,19,1)"T"
FROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL,GL_ALLOC_FORMULA_LINES GAFL2,GL_ALLOC_FORMULA_LINES GAFL3
,GL_ALLOC_FORMULA_LINES GAFL4,GL_ALLOC_FORMULA_LINES GAFL5
, FND_ID_FLEX_STRUCTURES_VL FST
WHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID
AND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM
AND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL2.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL3.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL4.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL5.ALLOCATION_FORMULA_ID
AND GAFL.LINE_NUMBER =1
AND GAFL2.LINE_NUMBER =2
AND GAFL3.LINE_NUMBER =3
AND GAFL4.LINE_NUMBER =4
AND GAFL5.LINE_NUMBER =5
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('DE')
AND GAFL2.AMOUNT IS NULL
--AND GAB.NAME LIKE 'DE MAIN%'
ORDER BY 1,2

---==========================================================================================================================
GL Mass Allocation Rule Migration Script in Dataload Classic Format

/* EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
IT IS DESIGNED TO WORK WITH A 10 SEGMENT CHART OF ACCOUNTS, SO WILL NEED TO BE MODIFIED TO SUIT YOUR STRUCTURE
THIS EXTRACT WILL ONLY WORK WITH THE FOLLOWING CONDITIONS
1- THAT LINES B&C ARE VALUES NOT ACCOUNTS
2- THAT RELATIVE PERIOD IS CURRENT
3- THAT AMOUNT TYPE IS ACTUAL
*/
SELECT GAB.NAME
, GAF.NAME "FORMULA NAME"
, 'ALLOCATION'
, GAF.DESCRIPTION "FORMULA DESC"
, GAFL.SEGMENT1"S11"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL.SEGMENT2"S12"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL.SEGMENT3"S13"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL.SEGMENT4"S14"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL.SEGMENT5"S15"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL.SEGMENT6"S16"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL.SEGMENT7"S17"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL.SEGMENT8"S18"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL.SEGMENT9"S19"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL.SEGMENT10"S110"
, SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)"T"
, GAFL.CURRENCY_CODE"CURR"
, GAFL.AMOUNT_TYPE"AMT TYPE"
, GAFL2.AMOUNT "B-AMT"
, GAFL3.AMOUNT "C-AMT"
, GAFL4.SEGMENT1"S41"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL4.SEGMENT2"S42"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL4.SEGMENT3"S43"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL4.SEGMENT4"S44"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL4.SEGMENT5"S45"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL4.SEGMENT6"S46"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL4.SEGMENT7"S47"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL4.SEGMENT8"S48"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL4.SEGMENT9"S49"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL4.SEGMENT10"S410"
, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,19,1)"T"
, GAFL4.CURRENCY_CODE"CURR"
, GAFL5.SEGMENT1"S51"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,1,1)"T"
, GAFL5.SEGMENT2"S52"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,3,1)"T"
, GAFL5.SEGMENT3"S53"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,5,1)"T"
, GAFL5.SEGMENT4"S54"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,7,1)"T"
, GAFL5.SEGMENT5"S55"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,9,1)"T"
, GAFL5.SEGMENT6"S56"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,11,1)"T"
, GAFL5.SEGMENT7"S57"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,13,1)"T"
, GAFL5.SEGMENT8"S58"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,15,1)"T"
, GAFL5.SEGMENT9"S59"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,17,1)"T"
, GAFL5.SEGMENT10"S510"
, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,19,1)"T"
FROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL,GL_ALLOC_FORMULA_LINES GAFL2,GL_ALLOC_FORMULA_LINES GAFL3
,GL_ALLOC_FORMULA_LINES GAFL4,GL_ALLOC_FORMULA_LINES GAFL5
, FND_ID_FLEX_STRUCTURES_VL FST
WHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID
AND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM
AND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL2.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL3.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL4.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL5.ALLOCATION_FORMULA_ID
AND GAFL.LINE_NUMBER =1
AND GAFL2.LINE_NUMBER =2
AND GAFL3.LINE_NUMBER =3
AND GAFL4.LINE_NUMBER =4
AND GAFL5.LINE_NUMBER =5
AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('DE')
AND GAFL2.AMOUNT IS NOT NULL
--AND GAB.NAME LIKE 'DE MAIN%'
ORDER BY 1

GL ADI Journal Balances script

/* GL ADI JOURNAL OF OPENING BALANCES & MOVEMENTS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
GIVES A TRIAL BALANCE IN ADI FORMAT FOR UPTO TEN SEGMENTS IN THE CHART OF ACCOUNTS WITH DEBIT AND CREDIT BALANCE.
THIS CAN BE USED TO EXTRACT GL BALANCES DATA FROM ONE ENVIRONMENT IN AND ADI JOURNAL FORMAT TO LOAD INTO ANOTHER ENVIRONMENT.
IT IS RECOMMENDED THAT THIS SCRIPT IS RUN FOR A SINGLE PERIOD AND BOOK FIRST TO GAUGE PERFORMANCE IN YOUR ENVIRONMENT.
(TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT SOB.NAME
, GB.PERIOD_NAME
, GCC.SEGMENT1
, GCC.SEGMENT2
, GCC.SEGMENT3
, GCC.SEGMENT4
, GCC.SEGMENT5
, GCC.SEGMENT6
, GCC.SEGMENT7
, GCC.SEGMENT8
, GCC.SEGMENT9
, GCC.SEGMENT10
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) >= 0
THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)))
ELSE 0 END ) "DEBIT"
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <= 0
THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))*-1)
ELSE 0 END ) "CREDIT"
FROM GL_BALANCES GB
, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
--AND GB.PERIOD_NAME = 'DEC-05'
AND GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HK','JP','TH','SG','CN')
AND GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
GROUP BY SOB.NAME
, GB.ACTUAL_FLAG
, GB.PERIOD_NAME
, GCC.SEGMENT1
, GCC.SEGMENT2
, GCC.SEGMENT3
, GCC.SEGMENT4
, GCC.SEGMENT5
, GCC.SEGMENT6
, GCC.SEGMENT7
, GCC.SEGMENT8
, GCC.SEGMENT9
, GCC.SEGMENT10
, NVL(GB.PERIOD_NET_DR,0)
, NVL(GB.PERIOD_NET_CR,0)
HAVING SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <> 0
ORDER BY 1,2,3,4,5,6,7,8,9
GL Autopost Definitions

/* GL AUTOPOST DEFINITIONS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
LISTS THE AUTOPOST DEFINITIONS BY BOOK SHOW THE JOURNAL SOURCE AND CATEGORY
TESTED ON VISION 11.5.10.2 JUNE 2007 */
SELECT SOB.NAME "BOOK"
, APS.AUTOPOST_SET_NAME "SET NAME"
, APS.DESCRIPTION
, APS.ENABLED_FLAG
, APS.SUBMIT_ALL_PRIORITIES_FLAG "SUBMIT ALL?"
, APO.ACTUAL_FLAG "ACTUAL FLAG"
, APO.PERIOD_NAME "PERIOD"
, APO.JE_SOURCE_NAME "SOURCE"
, APO.USER_JE_CATEGORY_NAME "CATEGORY"
FROM GL_AUTOMATIC_POSTING_SETS_V APS, GL_AUTOMATIC_POSTING_OPTIONS_V APO, GL_SETS_OF_BOOKS SOB
WHERE APO.AUTOPOST_SET_ID = APS.AUTOPOST_SET_ID
AND APS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
--AND SUBSTR(SOB.NAME,1,2) IN ('ES','LU','BE')
ORDER BY 1
HR Operating Unit and Legal Entity Configuration

/* LEGAL ENTITIERS & ORGANIZATIONS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
GIVES AN OVERVIEW OF THE LEGAL ENTITY AND OPERATING UNIT CONFIGURATION ACROSS MULTIPLE OU
THIS IS USED WHEN IMPLEMENTING MULTIPLE OFFICES TO ENSURE CONSISTENT SETUP ACROSS COUNTRIES AND BETWEEN ENVIRONMENTS.
WHERE CLAUSE CAN BE ADDED OR COMMENTED OUT TO JUST LOOK AT SPECIFIC COUNTRIES.IF CONSISTENT NAMING CONVENTIONS HAVE BEEN USED.
( TESTED ON VISION 11.5.10.2 JUNE 2007 )*/
SELECT HRO.ORGANIZATION_ID
, HRO.NAME
, HOI.ORG_INFORMATION_CONTEXT
, SOB2.NAME "LE SET OF BOOKS"
--, HOI.ORG_INFORMATION1
, HRO_LE.NAME "OU LEGAL ENT"
, HOI.ORG_INFORMATION2 "LE VAT CODES"
--, HOI.ORG_INFORMATION3
, SOB.NAME "OU SET OF BOOKS"
FROM HR_ALL_ORGANIZATION_UNITS_TL HRO, HR_ORGANIZATION_INFORMATION_V HOI,
GL_SETS_OF_BOOKS SOB,GL_SETS_OF_BOOKS SOB2, HR_ALL_ORGANIZATION_UNITS_TL HRO_LE
WHERE HOI.ORG_INFORMATION_CONTEXT IN ('LEGAL ENTITY ACCOUNTING','OPERATING UNIT INFORMATION')
AND HRO.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND TO_CHAR(SOB.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION3
AND TO_CHAR(SOB2.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION1
AND TO_CHAR(HRO_LE.ORGANIZATION_ID(+)) = HOI.ORG_INFORMATION2
--AND SUBSTR(HRO.NAME,1,2) IN ('BE','LU','ES')
ORDER BY 2,3
Dataload .dld GL Cross Validation Rules

/* DATALOAD (DLD) FORMAT SQL EXTRACT OF CVR CROSS VALIDATION RULES
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
EXTRACTS CVR'S FROM ONE ENVIRONMENT IN A DATALOAD FORMAT READY TO LOAD INTO THE NEXT ENVIRONMENT USING DATALOAD CLASSIC.
NOTE : THE SEGMENTS LOW&HIGH SUBSTINGS WILL NEED UPDATING TO MATCH YOUR SPECIFIC CHART OF ACCOUNTS DEFINITIONS
(TESTED ON 11.5.9 MAR 2007 )*/
SELECT FST.ID_FLEX_STRUCTURE_NAME "BOOKS"
, L.INCLUDE_EXCLUDE_INDICATOR"INC?"
, R.FLEX_VALIDATION_RULE_NAME"NAME"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I'THEN R.FLEX_VALIDATION_RULE_NAME ELSE NULL END )"NAME"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN TL.DESCRIPTION ELSE NULL END )"DESCRIPTION"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN TL.ERROR_MESSAGE_TEXT ELSE NULL END )"MESSAGE"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN DECODE(R.ERROR_SEGMENT_COLUMN_NAME,'SEGMENT1','ENTITY','SEGMENT2','OFFICE','SEGMENT3','GROUP','SEGMENT4','ACCOUNT','SEGMENT5','LOCAL','SEGMENT6','PARTNER','SEGMENT7','PROJECT','SEGMENT8','YEAR','XXXXX') ELSE NULL END )"SEGMENT"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,0,2)"1L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,0,2)"1H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,4,2)"2L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,4,2)"2H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,7,4)"3L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,7,4)"3H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,12,5)"4L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,12,5)"4H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,18,6)"5L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,18,6)"5H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,25,4)"6L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,25,4)"6H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,30,5)"7L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,30,5)"7H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,36,4)"8L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,36,4)"8H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,41,4)"9L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,41,4)"9H"
, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,46,4)"10L"
, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,46,4)"10H"
, 'ENT'
, '*SL3'
, '*DN'
, 'TAB'
, '*SL1'
FROM FND_FLEX_VALIDATION_RULES R,
FND_FLEX_VDATION_RULES_TL TL,
FND_FLEX_VALIDATION_RULE_LINES L,
FND_ID_FLEX_STRUCTURES_VL FST
WHERE R.APPLICATION_ID = TL.APPLICATION_ID
AND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
AND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND R.APPLICATION_ID = L.APPLICATION_ID
AND R.ID_FLEX_CODE = L.ID_FLEX_CODE
AND R.ID_FLEX_NUM = L.ID_FLEX_NUM
AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND R.APPLICATION_ID = 101
AND R.ID_FLEX_CODE = 'GL#'
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('BE','LU')
--AND R.ERROR_SEGMENT_COLUMN_NAME = 'SEGMENT5'
--AND TL.ERROR_MESSAGE_TEXT LIKE '%LOCAL%'
--AND SUBSTR(L.CONCATENATED_SEGMENTS_LOW,1,2)='ZZ'
ORDER BY 1,3,2 DESC, 12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

Dataload Professional .fld -- Daily Rates load

/* DAILY RATES DLD PROFESSIONAL LOAD (DATE RANGES )
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
EXTRACTS SPECIFIC DAILY RATES IN A DATALOAD PROFESSIONAL FILE FORMAT .FLD
TO BE USED IN CONJUNCTION WITH A PREDEFINED .FLD FILE
(TESTED ON VISION 11.5.10.2 JUNE 2007 ) */
SELECT
GLR.FROM_CURRENCY
, GLR.TO_CURRENCY
, GLR.CONVERSION_DATE"FROM"
, GLR.CONVERSION_DATE"TO"
, RT.USER_CONVERSION_TYPE
, GLR.SHOW_CONVERSION_RATE "FROM > TO"
, GLR.SHOW_INVERSE_CON_RATE " TO > FROM(INVERSE)"
FROM GL_DAILY_RATES_V GLR, FND_CURRENCIES C, GL_DAILY_CONVERSION_TYPES RT
WHERE GLR.FROM_CURRENCY = C.CURRENCY_CODE
AND GLR.CONVERSION_TYPE = RT.CONVERSION_TYPE
AND RT.USER_CONVERSION_TYPE LIKE 'CORPORATE'
--AND CONVERSION_DATE > TO_DATE('30-NOV-2002','DD-MON-YYYY')
--AND CONVERSION_DATE > TO_DATE('31-AUG-2006','DD-MON-YYYY')
--AND SUBSTR(GLR.CONVERSION_DATE,1,2) = '01'
--AND FROM_CURRENCY IN ('GBP')
--AND TO_CURRENCY NOT IN ('GBP')
--AND RT.USER_CONVERSION_TYPE LIKE 'THAI%'
ORDER BY 1,GLR.CONVERSION_DATE

Some Useful Queries in Oracle Apps R12

How to initialize session in Oracle ?

FND_GLOBAL.APPS_INITIALIZE is used
for initializing the session before calling any
public or private API's in Oracle Ebusiness
suite. Its not required for all the API's but its
recommended that you set this profile
before making any calls to either private or
public API.
Mentioned below is a sample call to
FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);

l_user_id is the fnd user ID which will be utilized during the call.
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.

You can use either sysadmin or use some user who has all the above listed responsibilities.
For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = 'SYSADMIN'
and fresp.responsibility_name = 'Order Management Super User';

Another option is Help > Diagnostics > Examine and get the values from $profile session values.
How to Find PO Requisitions associated with PO Orders?

By using below query , we can find out the all the requisitions (Purchasing Requisitions) which are
associated with what all Purchasing orders.
They are 2 types of requisitions.
1) Purchasing Requisition
2) Internal Requisition.
Note:- Only Purchase Requisitions will be converted to the Purchase orders.
SELECT prha.segment1 "Requisition Number",
prha.type_lookup_code "Requisition Type",
pha.segment1 "Purchase Order Number",
pha.type_lookup_code "Purchase Order Type"
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.
SELECT
prha.requisition_header_id "requisition_header_id"
,prha.segment1 "Requisition Number"
,prha.preparer_id "preparer_id"
,TRUNC(prha.creation_date) "creation_date"
,prha.description "description"
,prha.note_to_authorizer "note_to_authorizer"
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code='CANCEL'
AND pah.object_type_code='
REQUISITION'
AND pah.object_id=prha.requisition_header_id

How to find On-hand inventory information?

Execute below query to see the onhand inventory information in oracle applications.
SELECT
NVL(substr(org.organization_code, 1,3), ' ') orgcode
,NVL(substr(msi.segment1, 1, 8), ' ') seg11
,NVL(substr(msi.segment1, 9, 8), ' ') seg12
,NVL(substr(msi.segment1, 17, 4), ' ') seg13
,NVL(moq.subinventory_code, ' ') sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ' ') trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

How to know the version in oracle apps?
select * from v$version;

With the help of above query we can find out the oracle apps version.
How to know the application is multi-org or not?
By using below query we can get the multi org status.

SELECT multi_org_flag
FROM fnd_product_groups

How to know Which User is Locked the table?
This Query will list the name of user who locked the table table. The object name is taken as an
input parameter.
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name = :P_TABLE_NAME
AND nvl(vs.status,'XX') != 'KILLED';

How to get Customer Address Details in Oracle apps?

The following Query will provide the customer address details in oracle applications.
SELECT hca.account_number customer_number,
hp.party_name customer_name,
hps.party_site_number site_number, hl.address1 address1,
hl.address2 address2, hl.address3 address3,
hl.address4 address4, hl.city city,
hl.postal_code postal_code, hl.state state,
ftt.territory_short_name country,
hcsua1.LOCATION bill_to_location,
hcsua2.LOCATION ship_to_location
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua1,
hz_locations hl,
fnd_territories_tl ftt,
hz_cust_acct_sites_all hcasa2,
hz_cust_site_uses_all hcsua2
WHERE hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hcasa1.party_site_id(+) = hps.party_site_id
AND hcasa2.party_site_id(+) = hps.party_site_id
AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
AND hcsua1.site_use_code(+) = 'bill_to'
AND hcsua2.site_use_code(+) = 'ship_to'
AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code
AND ftt.LANGUAGE = USERENV ('lang')
ORDER BY customer_number;

Script to find Oracle API’s for any module

With the help of below query we can find the API for a module in Oracle Apps.
select substr(a.OWNER,1,20) , substr(a.NAME,1,30) , substr(a.TYPE,1,20) ,
substr(u.status,1,10) Stat , u.last_ddl_time , substr(text,1,80) Description from dba_source a,
dba_objects u WHERE 2=2 and u.object_name = a.name and a.text like ‘%Header%’ and
a.type = u.object_type and a.name like ‘AR_%API%’ –- Checking for AR Related APIs order
by a.owner, a.name