Oracle Apps Techno Functional Info

Thursday, August 3, 2017

How to Find the Correct Version of JDeveloper for OA Extensions to Use with E-Business Suite

OA Framework - How to Find the Correct Version of JDeveloper for OA Extensions to Use with E-Business Suite 11i or Release 12.x (Doc ID 416708.1)

This document lists Oracle JDeveloper patches that are needed for the corresponding Oracle E-Business Suite releases. To use Oracle JDeveloper with Oracle E-Business Suite, you must apply any patches that are relevant.

In This Document

The most current version of this document can be obtained in My Oracle Support Knowledge Document 416708.1. Refer to Section 4 for availability of translated versions.

There is a change log at the end of this document.

Section 1: Using This Document

When you create extensions to OA Framework-based pages in Oracle E-Business Suite, you must use the version of Oracle JDeveloper shipped by the Oracle E-Business Suite product development team. The version of Oracle JDeveloper is specific to the Oracle E-Business Suite Applications Technology patch level, so there is a new version of Oracle JDeveloper with each new release of the Oracle E-Business Suite Applications Technology patchset.

To find the correct Oracle JDeveloper patches for creating OA Extensions with Oracle E-Business Suite Release 12.x or Release 11i, follow these steps:

Identify the OA Framework version in your instance by setting the FND: Diagnostics / FND_DIAGNOSTICS profile option to activate Diagnostics
Select the "About This Page" link from any OA Framework page.
Select the "Technology Components" tab and make a note of the OA Framework version in the first row of the table, matching it to the Oracle JDeveloper Patch shown in the appropriate table from those listed in Section 2 below.

Note: The Oracle JDeveloper patches described in this document are intended for creating OA Extensions to OA Framework-based pages in Oracle E-Business Suite Release 11i and Release 12.x. If you are building other types of application extensions, such as web services, refer to the documentation resources of those technologies to identify the correct version of JDeveloper to use.

Section 2: Finding Oracle JDeveloper Patches For Oracle E-Business Suite

The following tables list the Oracle JDeveloper patches required for use with the corresponding versions of Oracle E-Business Suite.

Release 12.2

ATG Release 12.2 Version	Oracle JDeveloper 10g Patches
12.2.6	Patch 24611686 Oracle JDeveloper 10g with OA Extension ARU for R12.2.6 Bundle 1, certified on Windows 7, Windows 8.1, Windows 2012 Server, and Linux. Preferred web browser is Microsoft Internet Explorer 11.0 or above. Patch 24555396 Oracle JDeveloper 10g with OA Extension ARU for R12.2.6, certified on Windows 7, Windows 8.1, Windows 2012 Server, and Linux. Preferred web browser is Microsoft Internet Explorer 11.0 or above. Known issues: Bug 24707646 (Linux)
12.2.5	Patch 22064122 Oracle JDeveloper 10g with OA Extension ARU for R12.2.5 Consolidated Bundle 1, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 9.0 or above. Patch 21662342 Oracle JDeveloper 10g with OA Extension ARU for R12.2.5, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 9.0 or above.
12.2.4	Patch 19170592 Oracle JDeveloper 10g with OA Extension ARU for R12.2.4, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 8.0 or above.
12.2.3	Patch 17888411 Oracle JDeveloper 10g with OA Extension ARU for R12.2.3, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 8.0 or above for Skyros Look-and-Feel, or Internet Explorer 6.0 or above for Swan Look-and-Feel.
12.2	Patch 17513160 Oracle JDeveloper 10g with OA Extension ARU for R12.2, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 6.0 or above.

Release 12.1

ATG Release 12.1 Version	Oracle JDeveloper 10g Patch
12.1 (Controlled Release - only included for completeness)	Patch 7315332 JDeveloper 10g with OA Extension ARU for R12.1 (Controlled Release)
12.1.1 (Rapid Install, or Patch 7303030)	Patch 8431482 JDeveloper 10g with OA Extension ARU for R12.1.1
12.1.2 (Patch 7303033 or 7651091)	Patch 9172975 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.2
12.1.3 (Patch 9239090 or 8919491)	Patch 9879989 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.3
12.1.3.1 (Patch 11894708)	Patch 9879989 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.3
12.1.3.2 (Patch 15880118)	Patch 9879989 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1.3
12.1 RUP4 (Patch 18936791)	Patch 22501192 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1 RUP4
12.1 RUP5 (Patch 22284589)	Patch 23111832 JDeveloper 10g WITH OA EXTENSION ARU FOR R12.1 RUP5

Release 12.0

ATG Release 12 Version	Oracle JDeveloper 10g Patch
12.0.0	Patch 5856648 JDeveloper 10g with OA Extension
12.0.1 (Patch 5907545)	Patch 5856648 JDeveloper 10g with OA Extension
12.0.2 (Patch 5484000 or 5917344)	Patch 6491398 JDeveloper 10g with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3 (Patch 6141000 or 6077669)	Patch 6509325 JDeveloper 10g with OA Extension ARU for R12 RUP3
12.0.4 (Patch 6435000 or 6272680)	Patch 6908968 JDeveloper 10g WITH OA EXTENSION ARU FOR R12 RUP4
12.0.5 (No new ATG code released)	(No new Oracle JDeveloper patch required)
12.0.6 (Patch 6728000 or 7237006)	Patch 7523554 JDeveloper 10g With OA Extension ARU for R12 RUP6

Release 11i

Date	Description
OA Framework 5.10 patch	Oracle JDeveloper 9i Patch
ATG.PF.H (Patch 3438354 or Oracle Applications 11.5.10)	Patch 4045639 JDeveloper 9i WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (Patch 4017300)	Patch 4141787 JDeveloper 9i WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (Patch 4125550)	Patch 4573517 JDeveloper 9i with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (Patch 4334965)	Patch 4725670 JDeveloper 9i WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (Patch 4676589)	Patch 5455514 JDeveloper 9i WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (Patch 5473858)	Patch 6012619 JDeveloper 9i With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (Patch 5903765)	Patch 6739235 JDeveloper 9i With OA Extension ARU FOR 11i10 RUP6 ~~Patch 6469392 JDeveloper 9i WITH OA EXTENSION ARU FOR 11I10 RUP6~~
11i.ATG_PF.H.delta.7 (Patch 6241631)	Patch 8751878 JDeveloper 9i WITH OA EXTENSION ARU FOR 11I RUP7

Monday, July 31, 2017

Journal Import - Oracle GL

Interface Tables :
------------------------------------------------
gl_interface
gl_interface_control

Sequence :
------------------------------------------------
Group Id : (Table : gl_interface)
SELECT gl_journal_import_s.NEXTVAL INTO x_group_id FROM DUAL;

Interface Run Id : (Table : gl_interface_control)
SELECT gl_journal_import_s.NEXTVAL INTO x_run_id FROM DUAL;

Program Name :
------------------------------------------------
Journal Import

fnd_request.submit_request ('SQLGL',
'GLLEZL',
'',
'',
FALSE,
p_run_id,
p_set_of_books_id,
'N',
'',
'',
'N',
'N');

Key Fields:
------------------------------------------------
SELECT b.user_je_source_name FROM gl_je_sources;
user_je_category_name

Insert Statements :
------------------------------------------------
INSERT INTO gl_interface (STATUS,
LEDGER_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
CURRENCY_CONVERSION_DATE,
ENCUMBRANCE_TYPE_ID,
BUDGET_VERSION_ID,
USER_CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
AVERAGE_JOURNAL_FLAG,
ORIGINATING_BAL_SEG_VALUE,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
SEGMENT21,
SEGMENT22,
SEGMENT23,
SEGMENT24,
SEGMENT25,
SEGMENT26,
SEGMENT27,
SEGMENT28,
SEGMENT29,
SEGMENT30,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
TRANSACTION_DATE,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
REFERENCE5,
REFERENCE6,
REFERENCE7,
REFERENCE8,
REFERENCE9,
REFERENCE10,
REFERENCE11,
REFERENCE12,
REFERENCE13,
REFERENCE14,
REFERENCE15,
REFERENCE16,
REFERENCE17,
REFERENCE18,
REFERENCE19,
REFERENCE20,
REFERENCE21,
REFERENCE22,
REFERENCE23,
REFERENCE24,
REFERENCE25,
REFERENCE26,
REFERENCE27,
REFERENCE28,
REFERENCE29,
REFERENCE30,
JE_BATCH_ID,
PERIOD_NAME,
JE_HEADER_ID,
JE_LINE_NUM,
CHART_OF_ACCOUNTS_ID,
FUNCTIONAL_CURRENCY_CODE,
CODE_COMBINATION_ID,
DATE_CREATED_IN_GL,
WARNING_CODE,
STATUS_DESCRIPTION,
STAT_AMOUNT,
GROUP_ID,
REQUEST_ID,
SUBLEDGER_DOC_SEQUENCE_ID,
SUBLEDGER_DOC_SEQUENCE_VALUE,
ATTRIBUTE1,
ATTRIBUTE2,
GL_SL_LINK_ID,
GL_SL_LINK_TABLE,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTEXT,
CONTEXT2,
INVOICE_DATE,
TAX_CODE,
INVOICE_IDENTIFIER,
INVOICE_AMOUNT,
CONTEXT3,
USSGL_TRANSACTION_CODE,
DESCR_FLEX_ERROR_MESSAGE,
JGZZ_RECON_REF,
REFERENCE_DATE,
SET_OF_BOOKS_ID,
BALANCING_SEGMENT_VALUE,
MANAGEMENT_SEGMENT_VALUE,
FUNDS_RESERVED_FLAG--, CODE_COMBINATION_ID_INTERIM
)
(SELECT status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
currency_conversion_date,
encumbrance_type_id,
budget_version_id,
user_currency_conversion_type,
currency_conversion_rate,
average_journal_flag,
originating_bal_seg_value,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
transaction_date,
reference1,
reference2,
reference3,
reference4,
reference5,
reference6,
reference7,
reference8,
reference9,
reference10,
reference11,
reference12,
reference13,
reference14,
reference15,
reference16,
reference17,
reference18,
reference19,
reference20,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30,
je_batch_id,
period_name,
je_header_id,
je_line_num,
chart_of_accounts_id,
functional_currency_code,
code_combination_id,
date_created_in_gl,
warning_code,
status_description,
stat_amount,
GROUP_ID,
request_id,
subledger_doc_sequence_id,
subledger_doc_sequence_value,
attribute1,
attribute2,
gl_sl_link_id,
gl_sl_link_table,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
CONTEXT,
context2,
invoice_date,
tax_code,
invoice_identifier,
invoice_amount,
context3,
ussgl_transaction_code,
descr_flex_error_message,
jgzz_recon_ref,
reference_date,
-- start of R12 upgrade changes
set_of_books_id,
NULL,
NULL,
NULL
--,null --End of R12 upgrade changes
FROM xxt_gl_interface_gbl);
------------
INSERT INTO gl_interface_control (je_source_name,
status,
interface_run_id,
GROUP_ID,
set_of_books_id)
SELECT DISTINCT b.je_source_name,
'S',
x_run_id,
a.GROUP_ID,
set_of_books_id
FROM gl_interface a, gl_je_sources b
WHERE a.GROUP_ID = grpid_type_tbl (grp_id.set_of_books_id)
AND a.user_je_source_name = b.user_je_source_name;

Tuesday, July 18, 2017

Type of Accounts

Types of accounts:
Accounts can be broadly classified under either one of the following three heads:
1. Real
2. Personal
3. Nominal

Real accounts are your tangible and intangible assets like machinery, land, furniture, cash, etc.
Personal accounts are all assets and liabilities which represent some "person" like debtors, creditors, banks, owners, etc.
Nominal accounts are accounts of your incomes and expenses.

The three golden rules:
Let us have a look at the three rules. Each of these rules applies to one of the aforesaid type of account.
For Real: Debit was comes in, credit what goes out.
For Personal: Debit the receiver, credit the giver.
For Nominal: Debit all expenses and losses, credit all incomes and gains.

Friday, June 2, 2017

Block to get the XML file under the docment/page

declare
begin
jdr_utils.printdocument('/oracle/apps/fnd/cp/viewreq/webui/CPReqDetailsStopPG');
end;

Tuesday, February 21, 2017

External Bank Account Assigned to Suppliers

SELECT APS.vendor_name,
APS.segment1 vendor_num,
APSS.vendor_site_code vendor_Site,
HZP.party_name Bank_Name,
IEBA.bank_Account_name,
IEBA.bank_account_num,
IEBA.IBAN,
IPUA.ORDER_OF_PREFERENCE Priority,
IPUA.START_DATE,
IPUA.END_DATE,
BranchOrgProfile.bank_or_branch_number Sort_Code
FROM apps.IBY_EXT_BANK_ACCOUNTS IEBA,
apps.IBY_PMT_INSTR_USES_ALL IPUA,
apps.IBY_EXTERNAL_PAYEES_ALL IEPA,
apps.AP_SUPPLIERS APS,
apps.AP_SUPPLIER_SITES_ALL APSS,
apps.HZ_PARTIES HZP,
apps.HZ_ORGANIZATION_PROFILES BranchOrgProfile
--CE_BANK_BRANCHES_V
WHERE 1 = 1
AND IEBA.ext_bank_account_id = IPUA.instrument_id
AND IPUA.instrument_type = 'BANKACCOUNT'
AND IPUA.ext_pmt_party_id = IEPA.ext_payee_id
AND IEPA.payee_party_id = APS.party_id
AND IEPA.party_site_id = APSS.party_site_id(+)
AND IEPA.payment_function = 'PAYABLES_DISB'
AND IEPA.supplier_site_id = APSS.vendor_site_id(+)
--Bank as Party
AND IEBA.bank_id = HZP.party_id(+)
-- AND IEBA.bank_id = HZ_ORGANIZATION_PROFILES.party_id(+)
-- AND IEBA.branch_id = CE_BANK_BRANCHES_V.branch_party_id(+)
-- AND IEBA.BANK_ACCOUNT_NUM = '02249990'
-- AND APS.segment1 = '95921'
AND apss.org_id = 84
AND payment_flow = 'DISBURSEMENTS'
AND IEBA.branch_id = BranchOrgProfile.PARTY_ID(+)
ORDER BY aps.segment1, apss.vendor_site_Code, ipua.order_of_preference

Friday, February 10, 2017

AR Lockbox Queries

select * from apps.ar_transmissions_all
where org_id = XX
and transmission_name = 'BANK_XXXX_14290534'

select * from apps.ar_payments_interface_all
where transmission_request_id = XXX --- Get the transmission_request_id from above query
--- Need to check the status column for errors

select * from apps.AR_BATCHES_ALL
where TRANSMISSION_REQUEST_ID = XXX
-- Get the batchid here using transmission_request_id

select * from apps.ar_interim_cash_receipts_all
where batch_id = XXX
-- Check for any stuck receipts, normally this table should be empty otherwise period will not be closed

select * from apps.ar_cash_Receipt_history_all
where batch_id = XXX
-- Get the batch_id from AR_BATCHES_ALL table
-- To get all the receipts created under this batch
-- Take the event_id to get the GL Posting details

select * from apps.ar_cash_Receipts_all
where cash_Receipt_id = XXX
-- Get the receipt details, based on the cash_Receipt_id from history table

select * from apps.ar_receivable_applications_all
where cash_Receipt_id = XXX
-- To get the receipt application details

Journal Related to AP Invoice

SELECT DISTINCT
aia.invoice_num,
--eve_line.ENTERED_DR "Entered DR in SLA",
-- eve_line.ENTERED_CR "Entered CR in SLA",
-- eve_line.ACCOUNTED_DR "Accounted DR in SLA",
-- eve_line.ACCOUNTED_CR "Accounted CR in SLA",
-- je_lin.ENTERED_DR "Entered DR in GL",
-- je_lin.ACCOUNTED_DR "Accounted DR in GL",
-- eve_line.ACCOUNTING_CLASS_CODE "Accounting Class",
eve_hed.JE_CATEGORY_NAME "JE Category Name",
je_bat.NAME "Journal Batch Name",
je_hed.NAME "Journal Header Name",
je_lin.JE_LINE_NUM "Journal Line Number",
je_lin.Description "Journal Line Desc",
gcc.SEGMENT1
|| '.'
|| gcc.SEGMENT2
|| '.'
|| gcc.SEGMENT3
|| '.'
|| gcc.SEGMENT4
|| '.'
|| gcc.SEGMENT5
"Code Combination"
FROM apps.ap_invoices_all aia,
apps.ap_invoice_distributions_all aida,
apps.xla_events eve,
apps.xla_ae_headers eve_hed,
apps.xla_ae_lines eve_line,
apps.GL_IMPORT_REFERENCES imp_ref,
apps.gl_code_combinations gcc,
apps.gl_je_batches je_bat,
apps.gl_je_headers je_hed,
apps.gl_je_lines je_lin
WHERE 1 = 1
AND aia.invoice_Id = aida.invoice_id
AND aida.accounting_event_id = eve.event_id
AND eve.event_Id = eve_hed.event_id
AND aida.accounting_event_id = eve_hed.event_id
AND eve_hed.ae_header_id = eve_line.ae_header_id
AND imp_ref.GL_SL_LINK_ID = eve_line.GL_SL_LINK_ID
AND imp_ref.GL_SL_LINK_TABLE = eve_line.GL_SL_LINK_TABLE
AND imp_ref.je_header_id = je_hed.je_header_Id
AND imp_ref.je_header_id = je_lin.je_header_Id
AND imp_ref.je_batch_Id = je_bat.je_batch_Id
AND je_bat.je_batch_Id = je_hed.je_batch_id
AND je_hed.je_header_id = je_lin.je_header_Id
AND eve_line.DESCRIPTION = je_lin.DESCRIPTION
AND gcc.CODE_COMBINATION_ID = eve_line.CODE_COMBINATION_ID
AND gcc.CODE_COMBINATION_ID = je_lin.CODE_COMBINATION_ID
AND aia.invoice_Id = XXXXXX