To Know GAIN/LOSS for Vendors in Oracle Apps R12.2.5

Creating User and Adding Responsibility in Oracle Apps

SELECT * FROM ICX_PARAMETERS;

DECLARE
  v_user_name  VARCHAR2(30):=UPPER('NAGESH');
  v_password   VARCHAR2(30):='welcome123';
  v_session_id INTEGER     := USERENV('sessionid');
BEGIN
  fnd_user_pkg.createuser (
    x_user_name => v_user_name,
    x_owner => NULL,
    x_unencrypted_password => v_password,
    x_session_number => v_session_id,
    x_start_date => SYSDATE,
    x_end_date => NULL
  );
  COMMIT;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
END;


BEGIN
fnd_user_pkg.addresp ('NAGESH','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
        WHEN others THEN
                dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));
                ROLLBACK;
END;

R12 SLA Tables connection to AP, AR, INV,Payments, Receiving -- R12 SLA (Sub ledger Accounting)

R12 SLA Tables connection to AP, AR, INV,Payments, Receiving

R12 SLA (Sub ledger Accounting)

1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL

2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process

– Applies accounting rules

– Loads SLA tables, GL tables

– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah

XLA_AE_LINES xal

XLA_TRANSACTION_ENTITIES xte

XLA_DISTRIBUTION_LINKS xdl

GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xah.entity_id = xte.entity_id

xah.ae_header_id = xdl.ae_header_id

xah.event_id = xdl.event_id

xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = (Different value based on Module)

xte.entity_code =

'TRANSACTIONS' or

'RECEIPTS' or

'ADJUSTMENTS' or

'PURCHASE_ORDER' or

'AP_INVOICES' or

'AP_PAYMENTS' or

'MTL_ACCOUNTING_EVENTS' or

'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 =

'INVOICE_ID' or

'CHECK_ID' or

'TRX_NUMBER'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types

xdl.source_distribution_type = 'AP_PMT_DIST'

and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id

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

xdl.source_distribution_type = 'AP_INV_DIST'

and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id

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

xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'

and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id

and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id

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

xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id

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

xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id

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

xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id

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

xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'

and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

Queries:

In this post, we will check the Data related to the   Payable INVOICE ( Invoice_id = 166014 ) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

XLA_EVENTS

SELECT DISTINCT xe.*

FROM   ap_invoices_all ai,

       xla_events xe,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xte.application_id   = xe.application_id

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xe.entity_id

ORDER BY

       xe.entity_id,

       xe.event_number;

XLA_AE_HEADERS 

 

SELECT DISTINCT xeh.*

FROM   xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xeh.event_id,

       xeh.ae_header_id ASC; 

XLA_AE_LINES

SELECT DISTINCT xel.*,

       fnd_flex_ext.get_segs('SQLGL','GL#', '50577' , xel.code_combination_id) "Account"

FROM   xla_ae_lines xel,

       xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xel.application_id   = xeh.application_id

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xel.ae_header_id     = xeh.ae_header_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xel.ae_header_id,

       xel.ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl.*

FROM   xla_distribution_links xdl,

       xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xdl.application_id   = xeh.application_id

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xdl.ae_header_id     = xeh.ae_header_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xdl.event_id,

       xdl.a_header_id,

       xdl.ae_line_num ASC;

XLA_TRANSACTION_ENTITIES 

SELECT DISTINCT xte.*

FROM   ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id;

XLA_ACCOUNTING_ERRORS

SELECT DISTINCT xae.*

FROM   ap_invoices_all ai,

       xla_events xe,

       xla.xla_transaction_entities xte,

       xla_accounting_errors xae

WHERE  xte.application_id = 200

AND    xae.application_id   = xte.application_id

AND    xte.application_id   = xe.application_id

AND    ai.invoice_id        = '166014'

AND    xe.event_id          = xae.event_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xe.entity_id;

oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException Errors In Oracle Apps R12.2.5

For this error need to Check RTF template there should be tags are not correctly and if you are developing new  RTF latest version. Just select there  Backward Compatible  option and do new RTF with new tags. You will get resolve the error. Please follow screen shots for reference.

+--------- 1) POST-PROCESSING  ---------+
Beginning post-processing of request 1544251 on node  at 13-JUL-2016 18:26:30.
-- Publishing output

 
Post-processing of request 1544251 failed at 13-JUL-2016 18:26:31 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
 
CONC-POST-PROCESSING RESULTS

-- PUBLISH: 
oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException
+---------------------------------------+

oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException Errors In Oracle Apps R12.2.5

For this error need to Check RTF template there should be tags are not correctly and if you are developing new  RTF latest version. Just select there  Backward Compatible  option and do new RTF with new tags. You will get resolve the error. Please follow screen shots for reference.

+--------- 1) POST-PROCESSING  ---------+
Beginning post-processing of request 1544251 on node  at 13-JUL-2016 18:26:30.
-- Publishing output

 
Post-processing of request 1544251 failed at 13-JUL-2016 18:26:31 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
 
CONC-POST-PROCESSING RESULTS

-- PUBLISH: 
oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException
+---------------------------------------+

Executable location in Oracle apps server

SELECT
fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
 AND fcpp.action_type = 6
 AND fcpp.concurrent_request_id = &request_id;


 SELECT
APPLICATION_NAME,'$'||BASEPATH||'/'||'reports/US'
Reports_Path,EXECUTION_FILE_NAME
FROM APPS.FND_EXECUTABLES_VL A,
APPS.FND_APPLICATION_VL B
WHERE EXECUTION_METHOD_CODE='P'
AND A.APPLICATION_ID=B.APPLICATION_ID
AND EXECUTION_FILE_NAME ='XXU_CUSTOMER_DETAIL'

Enable Flexfield Value Set Security in Oracle R12.2.3

How to enable Value Set Security in Oracle Apps R12.2.3

Go through this video link

https://www.youtube.com/watch?v=drgRjiW0URQ

Enable Flexfield Value Set Security in Oracle R12.2.3

How to enable Value Set Security in Oracle Apps R12.2.3

Go through this video link

https://www.youtube.com/watch?v=drgRjiW0URQ