Manual Source journal Query - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/manual-source-journal-query.html

Manual Source journal Query

select a.period_name,a.doc_sequence_value,a.je_category,a.je_source,b.accounted_dr,b.accounted_cr
from gl_je_headers a,
gl_je_lines b,
gl_code_combinations c

where a.je_header_id=b.je_header_id
and b.code_combination_id=c.code_combination_id
and c.segment4='248111100'
and a.je_source!='Payables'
and a.period_name='MAY-15'
---------------------------------------------------------------------------------------------

select sum(kk.ACCOUNTED_DR), sum(kk.ACCOUNTED_CR)
from gl_je_headers ll, gl_je_lines kk, gl_code_combinations gg

where ll.JE_HEADER_ID=kk.JE_HEADER_ID
and gg.CODE_COMBINATION_ID=kk.CODE_COMBINATION_ID
and kk.LEDGER_ID=ll.LEDGER_ID
and ll.LEDGER_ID=2021
and gg.SEGMENT4='248111100'
and ll.ACTUAL_FLAG='A'
and ll.JE_SOURCE !='Payables'

How to Assign an Accounting Sequence - Oracle EBS R12

https://generalledger-gl.blogspot.com/2016/01/how-to-assign-accounting-sequence.html

How to Assign an Accounting Sequence

Create Sequence

Assignments

Assign Sequences

GL Account Inquiry Query for Payables Reconciliation - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/gl-account-inquiry-query-for-payables.html

GL Account Inquiry Query for Payables Reconciliation

ON PKR CURRENCY & ENTRED DR/CR

select
--H.JE_CATEGORY,H.JE_SOURCE,
--h.je_header_id, h.name,
L.EFFECTIVE_DATE,
--L.ENTERED_DR, L.ENTERED_CR, (nvl(L.ENTERED_DR,0) - nvl(L.ENTERED_CR,0)) BAL
SUM(L.ENTERED_DR) DR, SUM(L.ENTERED_CR) CR, SUM(nvl(L.ENTERED_DR,0) - nvl(L.ENTERED_CR,0)) BAL
from
gl_je_headers h,
gl_je_lines l,
gl_code_combinations gcc
where 1=1
and h.je_header_id=l.je_header_id
and l.code_combination_id=gcc.code_combination_id
--and h.default_effective_date<='31-OCT-15'
--and h.period_name='OCT-15'
AND L.EFFECTIVE_DATE BETWEEN '01-OCT-15' AND '31-OCT-15'
--and h.je_category='Purchase Invoices'
and gcc.segment4='?'
AND H.CURRENCY_CODE='PKR'
AND H.LEDGER_ID=?
and H.STATUS='P'
and H.Actual_flag='A'
--AND H.JE_SOURCE='Payables'
--AND H.JE_CATEGORY='Purchase Invoices'
GROUP BY L.EFFECTIVE_DATE
--GROUP BY H.JE_CATEGORY,H.JE_SOURCE
ORDER BY L.EFFECTIVE_DATE

Automatic Reversal Setup - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/automatic-reversal-setup.html

Automatic Reversal Setup

Oracle Applications R12 (All about Multi Org) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/oracle-applications-r12-all-about-multi.html

Multi Organization Access Control

Please download the file from below:

http://www.erpstuff.com/forums/download/file.php?id=11

How to Change Journal Effective Date in GL for Auto Reversal "OR" How is the reversal date of a journal determined? - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/how-to-change-journal-effective-date-in.html

Q. When a journal is reversed, how is the Journal Default Effective Date derived into the Reversal Journal?

Q. How is the reversal date of a journal determined?

When creating automatically reversed journals via the Automatic reversal program, the journal entry created, may show an unexpected effective date, for example the last day of the month instead of the first day of the month.

SOLUTION

A. The effective date of the reversal journal is automatically derived in the following manner:

1. If the reversal period is the same as the original journal period, then the default date from the original journal is used.

Example (same period):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Jan-09
- then the effective date of reversal journal will be 20-JAN-2009 (the date of the original journal).

2. If current date is less than reversal period start date, then the period start date is used as the reversal date.

Example (period in the future):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Mar-09
- Current Date is 10-FEB-2009 which is less than reversal period start date i.e. 01-MAR-2009
- then the effective date of reversal journal will be the reversal period start date i.e. 01-MAR-2009

3. If current date is greater than reversal period end date, the period end date is used as the reversal date.

Example (period in the past):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Feb-09
- Current Date is 10-MAR-2009 which is greater than reversal period end date i.e. 28-FEB-2009
- then the effective date of reversal journal will be the reversal period end date i.e. 28-FEB-2009

4. If current date is in the reversal period, the system date is used as the reversal date.

Example (present period):
- Original Journal Effective Date is 20-JAN-2009
- Reversal Period is Feb-09
- Current Date is 10-FEB-2009 which falls in the reversal period date range
- then the effective date of reversal journal will be the system date i.e. 10-FEB-2009

The logic behind this functionality is to arrive the closest date to the current date.

The same defaults are also used by GLLEZL Journal Import when importing journals.

Note : This Note also holds true for journals that Auto Reversal reverses.

--------------------------------------------------------------------------------------

Query to change effective date from backend

SELECT * FROM GL_JE_BATCHES

--update gl_je_batches c set default_effective_date = to_date('01-MAY-15','dd-mon-rr')

WHERE je_batch_id IN

(

select JE_BATCH_ID from gl_je_headers gh

where gh.je_category='2'

and gh.period_name='MAY-15'

and gh.namelike'Rever%'

and gh.status='U'

)

select * from gl_je_headers gh

--update gl_je_headers gh set default_effective_date = to_date('01-MAY-15','dd-mon-rr')

where gh.je_category='2'

and gh.period_name='MAY-15'

and gh.namelike'Rever%'

and gh.status='U'

select * from gl_je_lines s

--update gl_je_lines s set effective_date = to_date('01-MAY-15','dd-mon-rr')

where s.je_header_id in

(select gh.je_header_id from gl_je_headers gh

where gh.je_category='2'

and gh.period_name='MAY-15'

and gh.namelike'Rever%'

and gh.status='U'

)

Oracle WEB ADI Settings of Excel 2013 - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/oracle-web-adi-settings-of-excel-2013.html

Oracle WEB ADI Settings of Excel 2013

Below Link will view the settings of Oracle WEB ADI Settings of Excel 2013. Excel sheet for ADI uploading and New Excel sheet not showing Adds In TAB therefore User are unable to Upload data from the sheet to Oracle GL. After apply the settings in Excel and Internet we are able to upload the sheet successfully with the help of ADI TAB.

http://appsr12help.blogspot.com/2013/06/web-adi-template-settings-for-microsoft.html

GL_Interface containing duplicate records which has already been posted in GL transferred from Payables (Query) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/glinterface-containing-duplicate.html

GL_Interface containing duplicate records which has already been posted in GL transferred from Payables (Query)

The below Query will return Data after giving Invoice_id. GL_Interface Table contains the Sub ledger Document Sequence Values through which we can get the Invoice Id's .

SELECT   distinct gll.*                  
FROM     GL_IMPORT_REFERENCES gir,
GL_JE_LINES   gll,                            
XLA_AE_LINES  ael,                            
XLA_AE_HEADERS   aeh,                            
XLA_EVENTS    aea                    
WHERE    aea.event_id IN ( SELECT  aid.accounting_event_id FROM    AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE   aid.invoice_id in (10448,
68267)
 )                  
AND      ael.gl_sl_link_id = gir.gl_sl_link_id                
AND      gir.gl_sl_link_table in ('APECL', 'XLAJEL')                
AND      aea.application_id = 200                
AND      aea.event_id = aeh.event_id                  
AND      aeh.ae_header_id = ael.ae_header_id                    
AND      gll.je_header_id = gir.je_header_id                  
AND      gll.je_line_num  = gir.je_line_num

----------------------------------------------------------------------------------------------------------------------

Raheem Bhai Query (Data Already Transferred to GL)

SELECT
H.NAME,
H.DEFAULT_EFFECTIVE_DATE,
H.DESCRIPTION,
H.JE_HEADER_ID,
A.JE_HEADER_ID,
H.JE_SOURCE,
H.JE_CATEGORY,
H.LEDGER_ID,
H.ACTUAL_FLAG,
H.PERIOD_NAME,
A.GL_SL_LINK_ID,
H.CODE_COMBINATION_ID,
H.ENTERED_DR,
H.ENTERED_CR,
A.ACCOUNTED_DR,
A.Accounted_Cr
FROM
(
SELECT * FROM GL_INTERFACE
WHERE USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices' AND LEDGER_ID=2021 AND ACTUAL_FLAG='A' -- 783
) A,
(
SELECT
H.JE_SOURCE,
H.JE_CATEGORY,
H.LEDGER_ID,
H.ACTUAL_FLAG,
H.PERIOD_NAME,
gll.CODE_COMBINATION_ID,
ael.ENTERED_DR, ael.ENTERED_CR,
ael.gl_sl_link_id,
H.NAME,
H.DEFAULT_EFFECTIVE_DATE,
H.JE_HEADER_ID,
H.DESCRIPTION
FROM
GL_JE_HEADERS H,
GL_JE_LINES gll,
XLA_AE_HEADERS aeh,
XLA_AE_LINES ael,
XLA_EVENTS aea,
GL_IMPORT_REFERENCES gir

WHERE 1=1
AND H.JE_HEADER_ID=gll.JE_HEADER_ID
   AND ael.gl_sl_link_id = gir.gl_sl_link_id
   AND gir.gl_sl_link_table in ('APECL', 'XLAJEL','XLAJEL')
   AND aea.application_id = 200
   AND aea.event_id = aeh.event_id
   AND aeh.ae_header_id = ael.ae_header_id
   AND gll.je_header_id = gir.je_header_id
   AND gll.je_line_num = gir.je_line_num

AND H.JE_SOURCE='Payables'
AND H.JE_CATEGORY='Purchase Invoices'
AND H.LEDGER_ID=2021
AND H.ACTUAL_FLAG='A'
--AND H.DEFAULT_EFFECTIVE_DATE='20-JUL-2009'
--AND H.PERIOD_NAME='JUL-09'
--AND H.DESCRIPTION LIKE '%200900160%'
) H
WHERE 1=1
AND H.JE_SOURCE=A.USER_JE_SOURCE_NAME
AND H.JE_CATEGORY=A.USER_JE_CATEGORY_NAME
AND H.LEDGER_ID=A.LEDGER_ID
AND H.ACTUAL_FLAG=A.ACTUAL_FLAG
AND H.PERIOD_NAME=A.PERIOD_NAME
--AND H.CODE_COMBINATION_ID=A.CODE_COMBINATION_ID
--AND (H.ENTERED_DR=A.ENTERED_DR or H.ENTERED_CR=A.ENTERED_CR)
AND H.GL_SL_LINK_ID=A.GL_SL_LINK_ID

-------------------------------------------------------------------------------------------------------------------------

Raheem Bhai Query (Data Not Transferred to GL)

SELECT * FROM GL_INTERFACE WHERE USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices' AND
(   USER_JE_SOURCE_NAME,
    USER_JE_CATEGORY_NAME,
    LEDGER_ID,
    ACTUAL_FLAG,
    PERIOD_NAME,
    GL_SL_LINK_ID
)
NOT IN -- 413
(
    SELECT
    --H.NAME,
    --H.DEFAULT_EFFECTIVE_DATE,
    --H.DESCRIPTION,
    --H.JE_HEADER_ID,
    --A.JE_HEADER_ID,
    H.JE_SOURCE,
    H.JE_CATEGORY,
    H.LEDGER_ID,
    H.ACTUAL_FLAG,
    H.PERIOD_NAME,
    A.GL_SL_LINK_ID
    --H.CODE_COMBINATION_ID,
    --H.ENTERED_DR,
    --H.ENTERED_CR
    FROM
    (
    SELECT * FROM GL_INTERFACE
    WHERE USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices' AND LEDGER_ID=2021 AND ACTUAL_FLAG='A' -- 783
    ) A,
    (
    SELECT
    H.JE_SOURCE,
    H.JE_CATEGORY,
    H.LEDGER_ID,
    H.ACTUAL_FLAG,
    H.PERIOD_NAME,
    gll.CODE_COMBINATION_ID,
    ael.ENTERED_DR, ael.ENTERED_CR,
    ael.gl_sl_link_id,
    H.NAME,
    H.DEFAULT_EFFECTIVE_DATE,
    H.JE_HEADER_ID,
    H.DESCRIPTION
    FROM
    GL_JE_HEADERS H,
    GL_JE_LINES gll,
    XLA_AE_HEADERS aeh,
    XLA_AE_LINES ael,
    XLA_EVENTS aea,
    GL_IMPORT_REFERENCES gir

    WHERE 1=1
    AND H.JE_HEADER_ID=gll.JE_HEADER_ID
       AND ael.gl_sl_link_id = gir.gl_sl_link_id
       AND gir.gl_sl_link_table in ('APECL', 'XLAJEL','XLAJEL')
       AND aea.application_id = 200
       AND aea.event_id = aeh.event_id
       AND aeh.ae_header_id = ael.ae_header_id
       AND gll.je_header_id = gir.je_header_id
       AND gll.je_line_num = gir.je_line_num

    AND H.JE_SOURCE='Payables'
    AND H.JE_CATEGORY='Purchase Invoices'
    AND H.LEDGER_ID=2021
    AND H.ACTUAL_FLAG='A'
    --AND H.DEFAULT_EFFECTIVE_DATE='20-JUL-2009'
    --AND H.PERIOD_NAME='JUL-09'
    --AND H.DESCRIPTION LIKE '%200900160%'
    ) H
    WHERE 1=1
    AND H.JE_SOURCE=A.USER_JE_SOURCE_NAME
    AND H.JE_CATEGORY=A.USER_JE_CATEGORY_NAME
    AND H.LEDGER_ID=A.LEDGER_ID
    AND H.ACTUAL_FLAG=A.ACTUAL_FLAG
    AND H.PERIOD_NAME=A.PERIOD_NAME
    --AND H.CODE_COMBINATION_ID=A.CODE_COMBINATION_ID
    --AND (H.ENTERED_DR=A.ENTERED_DR or H.ENTERED_CR=A.ENTERED_CR)
    AND H.GL_SL_LINK_ID=A.GL_SL_LINK_ID


)

--------------------------------------------------------------------------------------------------------------------------

How to verify Posted data in GL from GL_Interface Duplicate Records

select * from gl_je_headers ll, gl_je_lines kk
where ll.JE_HEADER_ID=kk.JE_HEADER_ID
and ll.STATUS='P'
and ll.ACTUAL_FLAG='A'
and kk.STATUS='P'
and kk.LEDGER_ID=2021
and ll.JE_SOURCE='Payables'
and ll.JE_CATEGORY='Purchase Invoices'
and (ll.JE_HEADER_ID, kk.JE_LINE_NUM) in
(select mm.JE_HEADER_ID, mm.JE_LINE_NUM
from gl_import_references mm, gl_interface jj
where mm.GL_SL_LINK_ID=jj.GL_SL_LINK_ID
)

Legal Entity Error - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/legal-entity-error.html

Legal Entity Error

Error:
ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1674 ORA-12545: Connect failed because target host or object does not exist in Package xle_legal_address_swi Procedure create_legal_address

Solution:
Set the profile option 'IRC: Geocode Host' to null at site level

WEBADI Common Issues - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/01/webadi-common-issues.html


WEBADI Common Issues

1. Download WebADI Document Stuck at "Do not close this window until processing completes" [ID 752760.1]

2. R12: WebADI Journal Wizard with Line DFF gets NullPointerException after Installing 7233317 [ID 786729.1]

3. Error :" Your document could not be created" When Creating Document using Launch Journal Wizard [ID 1310697.1] 

4. Web ADI And Vista: createPage() Exceeded Maximum Wait Time For Bootstrap [ID 784106.1]

5. Web Adi With Ms Office 2007 [ID 862046.1]

6. Web ADI Create error: No Components with the name FA_UOM exist. [ID 1298778.1]

7. Exception Name: Scripting Error Unknown runtime error: 'Sheets' [ID 299769.1]

8. Web Adi Cannot Create Journal Document In Excel 2007 [ID 1366138.1]

9. R12 Web ADI: Create Document Processing: Fatal Error java.lang.NoSuchMethodError: [ID 1349262.1]