How to create a history table

CREATE TABLE TABLE1
(
  ASSIGNMENT_ID     NUMBER                      NOT NULL,
  CREATION_DATE     DATE                        NOT NULL,
  CREATED_BY        VARCHAR2(30 BYTE)           NOT NULL,
  LAST_UPDATE_DATE  DATE                        NOT NULL,
  LAST_UPDATED_BY   VARCHAR2(30 BYTE)           NOT NULL,
  EMPLOYEE_ID       NUMBER                      NOT NULL,
  START_DATE        DATE                        NOT NULL,
  END_DATE          DATE,
  PRIMARY_FLAG      VARCHAR2(1 BYTE)            NOT NULL,
  DESCRIPTION       VARCHAR2(240 BYTE),
  PERF_MGR_ID       NUMBER,
  PERF_MRG_START_DATE   DATE,
  PERF_MRG_END_DATE     DATE,
  WORK_LOCATION     VARCHAR2(240 BYTE),
  JOB_TITLE         VARCHAR2(30 BYTE),
  ORG_ID            NUMBER                      NOT NULL,
  HR_MGR_ID         NUMBER,
  WORK_SHIFT        VARCHAR2(30 BYTE),
  PROJECT_MGR_ID    NUMBER,
  PROJECT_MRG_START_DATE    DATE,
  PROJECT_MRG_END_DATE      DATE
)

=======================================================

CREATE TABLE HISTORYTABLE
(
  ASSIGNMENT_ID     NUMBER                      NOT NULL,
  CREATION_DATE     DATE                        NOT NULL,
  CREATED_BY        VARCHAR2(30 BYTE)           NOT NULL,
  LAST_UPDATE_DATE  DATE                        NOT NULL,
  LAST_UPDATED_BY   VARCHAR2(30 BYTE)           NOT NULL,
  EMPLOYEE_ID       NUMBER                      NOT NULL,
  START_DATE        DATE                        NOT NULL,
  END_DATE          DATE,
  PRIMARY_FLAG      VARCHAR2(1 BYTE)            NOT NULL,
  DESCRIPTION       VARCHAR2(240 BYTE),
  PERF_MGR_ID       NUMBER,
  PERF_MRG_START_DATE   DATE,
  PERF_MRG_END_DATE     DATE,
  WORK_LOCATION     VARCHAR2(240 BYTE),
  JOB_TITLE         VARCHAR2(30 BYTE),
  HR_MGR_ID         NUMBER,
  WORK_SHIFT        VARCHAR2(30 BYTE),
  PROJECT_MGR_ID    NUMBER,
  PROJECT_MRG_START_DATE    DATE,
  PROJECT_MRG_END_DATE      DATE,
  ORG_ID            NUMBER                      NOT NULL,
  REVISION_NUM        NUMBER
)





===================================================

CREATE OR REPLACE TRIGGER <TRIGGER_NAME>
  AFTER UPDATE ON TABLE1
  FOR EACH ROW
DECLARE

  l_revision_num    NUMBER;

BEGIN

  SELECT MAX(revision_num)
    INTO l_revision_num
    FROM HISTORYTABLE
   WHERE ASSIGNMENT_ID = :new.ASSIGNMENT_ID;


   IF nvl(l_revision_num, 0) = 0 then
      l_revision_num := 1;
   ELSE
      l_revision_num := l_revision_num + 1;
   END IF;

   INSERT INTO OGS_HR_ASSIGNMENTS_H_ALL (
                                    REVISION_NUM
                                  , ASSIGNMENT_ID
                                  , CREATION_DATE
                                  , CREATED_BY
                                  , LAST_UPDATE_DATE
                                  , LAST_UPDATED_BY
                                  , EMPLOYEE_ID
                                  , START_DATE
                                  , END_DATE
                                  , PRIMARY_FLAG
                                  , DESCRIPTION
                                  , PERF_MGR_ID
                                  , PERF_MRG_START_DATE
                                  , PERF_MRG_END_DATE
                                  , WORK_LOCATION
                                  , JOB_TITLE
                                  , HR_MGR_ID
                                  , PROJECT_MGR_ID
                                  , PROJECT_MRG_START_DATE
                                  , PROJECT_MRG_END_DATE
                                  , ORG_ID
                                )
                     VALUES  (
                                    l_revision_num
                                  , :old.assignment_id
                                  , :old.creation_date
                                  , :old.created_by
                                  , :old.last_update_date
                                  , :old.last_updated_by
                                  , :old.employee_id
                                  , :old.start_date
                                  , :old.end_date
                                  , :old.primary_flag
                                  , :old.description
                                  , :old.perf_mgr_id
                                  , :old.perf_mrg_start_date
                                  , :old.perf_mrg_end_date
                                  , :old.work_location
                                  , :old.job_title
                                  , :old.hr_mgr_id
                                  , :old.project_mgr_id
                                  , :old.project_mrg_start_date
                                  , :old.project_mrg_end_date
                                  , :old.org_id                            
                             );
EXCEPTION
  WHEN OTHERS
  THEN
    raise_application_error (-20100, SQLERRM);
END OGS_HR_ASSIGNMENTS_ALL_AU;
/

Ger the financial year in pl/sql

function GET_FISCAL_YR
return number
is
    v_date    date;
    v_month   varchar2(2);
    v_year    varchar2(4);
   
begin

    v_date := sysdate;

    select to_char(v_date, 'MM'), to_char(v_date, 'YYYY') 
    into   v_month, v_year
    from   dual;
   

    if v_month > '03' then
       return (v_year+1);
    else
       return (v_year);
    end if;
   
end GET_FISCAL_YR;

Oracle Concurrent Request Tuning

-- List of concurrent request which ran more than given number of minutes.
-- Useful to point the high load request and get it tunes.
-- Give minimum number of minutes as parameter.
select  fcr.request_id request_id,
           trunc(((fcr.actual_completion_date - fcr.actual_start_date)
           /(1 / 24)) * 60) exec_time,
           fcr.actual_start_date start_date,
           fcp.concurrent_program_name conc_prog,
           fcpt.user_concurrent_program_name user_conc_prog
from    fnd_concurrent_programs fcp,
           fnd_concurrent_programs_tl fcpt,
           fnd_concurrent_requests fcr
where  trunc(((fcr.actual_completion_date - fcr.actual_start_date)
            /(1 / 24)) * 60) > nvl('&min', 45)
and     fcr.request_id = &request_id
and     fcr.concurrent_program_id = fcp.concurrent_program_id
and     fcr.program_application_id = fcp.application_id
and     fcr.concurrent_program_id = fcpt.concurrent_program_id
and     fcr.program_application_id = fcpt.application_id
and     fcpt.language = userenv('lang')
order by trunc(((fcr.actual_completion_date - fcr.actual_start_date) /(1 / 24)) * 60) desc;

Deleting Concurrent Program in Oracle

To Delete a concurrent program and executable, use the below script:
Begin
  fnd_program.delete_program('program short name','schema');
  fnd_program.delete_executable('program short name','schema');
commit;
End;

Party Merge in r12

Receivable Manager > Customer > Party Merge

 


As per TCA model,each party can have multiple customer accounts and you might chose to merge two of those. This is customer (account) merge as discussed in last post.
Within TCA model, the concept of “Customer” is separated into two layers: the Party layer and the Account layer

• CRM applications are referring to the Party layer when they refer to “Customer”
• Other applications, on the other hand, are considering to the Account layer, when they refer to “Customer”

Party Merge

This utilities can provide these functionality in EBS:

• Consolidate Duplicate parties or party sites
• Merge an acquired party into the acquiring party
• Combine duplicate party site for a party

If you have multiple party like ABC in the system and you determine that there has to be only one, you can use party merge to merge the duplicate parties into the one that you think is the original.

When you create a merge batch with parties to merge, you can also define the merge of entities from the merge-from and merge-to parties, including:

• Party sites
• Party relationships
• Organization contacts

You can also view party profile attributes of the merge-from and merge-to parties.

Party Merge or Account Merge - which one to pick
Party and Account merge are two distinct processes. As such, merging an Account does not automatically merge the parties and vice versa.
There is not a specific rule (Account first or Party first). This depends upon the situation.Here are few cases:

• If want to merge some accounts (Only the accounts), will not have to run Party merge
• If want to merge installed based records (CRM application) can only merge account if they are under the same party. If this case, needs to run party merge first, then all the account will be under the same party. Afterwards, can run party merge.
• If are using only party information and want to merge those, will not run account merge
• If want to move some accounts (belonging to a party) to another party then need to run only party merge

What is the difference between Transfer and Merge function in Party Merge?
Here is what Metalink Note:270032.1 documented.
Lets assume there are parties A and B. They have sites (addresses) AS and BS respectively. And there are transactions (invoices and orders) hanging from AS as well as BS. We are merging A into B. (A is the From and B is the To.)
A -> AS -> AST
B -> BS -> BST
TRANSFER - AS (address) will now point to B instead of A. The transactions from AS stay as is, so now they are eventually part of B. Party B now has 2 sites (addresses). The transactions (invoices and orders etc) remain attached to each site as they were before the merge.
B -> BS -> BST
-> AS -> AST
MERGE- AS will be marked with status M (address is deleted). Transactions from AS will be repointed to BS. Party B still has only 1 address. This (BS) address has all of the transactions (invoices and orders etc) that belonged to both addresses before the merge.
B -> BS -> BST
| -------> AST

• A TRANSFER allows you to keep an existing SITE (address) on the FROM party.
• A MERGE deletes the SITE and moves the transactions (invoices, orders, etc) to an existing site of the TO party.

New Features in BI Publisher 11gR1

With all the noise and publicity around the OBIEE 11gR1 launch, it’s easy to miss the fact that BI Publisher has also had a major revamp as part of the 11gR1 release. We saw a bit of a preview of this earlier in the year with the online template builder that was available in preview form for 10.1.3.4, but the 11gR1 release takes this further and adds a lot of new visualization features to the product.
BI Publisher 11gR1, like the 10g release, comes as either a standalone install or as an integrated part of OBIEE 11gR1. If you’re working with OBIEE 11gR1, you can get to BI Publisher either through the Published Reporting links on the left-hand side of the home page, like this:

or you can create the various elements of a BI Publisher report directly from the New… menu in the common header area of the OBIEE 11gR1 user interface, like this:

If you’ve used BI Publisher in its previous releases, you’ll probably know that a BI Publisher report consists of three things:

1. The data query, which can typically be an SQL query, an MDX query, a web service call an HTTP call
2. One or more data templates, which lay out the results of the query on the page, and
3. The report definition (stored as an .XDO file), which references the query and the templates together with parameters, LOVs etc

These elements are combined at runtime with the data itself, and the template is used to output results as a PDF, RTF file, Excel output, HTML and so on. BI Publisher complements Oracle Answers and Oracle Discoverer as it gives you more control of the format of the output, lets you combine queries and formats on a single page, handles things like bursting, page breaks and mass distribution, and is generally used when you want to “publish” something for use outside of the BI tool environment rather than query the data interactively on screen. Being candid, probably one of the weaknesses of BI Publisher in the 10g release was its reliance on using Microsoft Word as the template editor, and the limited functionality you got when you viewed the report output on screen using a web browser, and these are in fact two of the main areas addressed in the 11gR1 release.
BI Publisher 11gR1 amends this three-element report model to add a bit more granularity and functionality to the report definition. In 10g, the data model was defined as part of the report definition, whilst in 11gR1 it’s a separate object that can be re-used across multiple report definitions. If you’re running BI Publisher 11gR1 integrated with OBIEE 11gR1, report definitions (including the data model, templates and the definition itself) are stored in the web catalog along with Answers analyses, Agent definitions, dashboards, KPIs and scorecards, keeping everything in one place. There’s also now the concept of style templates and sub-templates, something that’ll be useful for organizations churning out lots of reports with a similar look and feel and with re-usable, modular elements.
It’s in the web area though that the most significant improvements have been made in 11gR1. Templates can now be created and edited online, removing the dependency on Microsoft Word or other offline template editors. There’s a new Interactive Viewer that allows you to view and interact with reports online, in an environment that has echos of BI tools like Qlikview. Templates created with the Online Template Builder and viewed using the Interactive Viewer can take advantage of the same ADF DVT graphing and visualization engine used by OBIEE 11gR1, giving a consistent look and feel to reports across both products. There’s also a new data model editor that makes it easy to create federated queries across multiple data sources, making BI Publisher 11gR1 standalone an interesting alternative to OBIEE 11gR1, especially considering the new interactive web features. So how does this all work when you want to create a new report?
In this example, I have some sales data from our stores that details revenue and activity across products and time periods. I would like to use BI Publisher to create a report that has a single query against this dataset, and uses three templates to show

• Product revenue and sales counts in the form of a crosstab and a graph
• Sales and profit breakdown in a graphical form, with a way for the user to interactively “drill-into” the data
• A formal document I can print-off and send to store managers, in the form of a PDF

The data I’m looking to report against is stored in an Oracle database, and so after setting up the data source connection in the BI Publisher 11gR1 Administration screen, I can start by creating a new data model. Now if you’ve used the 10g release of BI Publisher then this is quite a different approach to before, as firstly the data model is a separate object and secondly, the UI has changed considerably.
I start by creating a new data model, and then selecting SQL Query as the query type. Note also that I can use an Answers analysis as a data source, an MDX query (against Essbase, MS AS etc), an ADF view object, and Excel file and so on.

You then put the SQL query together in the same way as with 10g, with a visual query builder or the option to type in the SQL directly.

Once the query has been defined though, this is where things start to get different than BI Publisher 10g. The Data Model editor has three tabs, called Diagram, Structure and Code, that lets you fine-tune the output of the data model query and also combine data from multiple sources. The Diagram tab below gives me a visual view of the dataset, which at the moment consists of just the query I’ve just defined.

If I switch to the Structure tab, I can see the XML structure of the dataset, and can amend the “business” names of the output columns that will be displayed in the reports, as well as assign default values for NULLs, change datatypes, preview the output and so on.

The Code tab shows me the data model definition in terms of an XML document.
Now my data model is fairly simple and straightforward, but developers who’ve worked with the 10g release will know that it’s possible to merge multiple queries into one data model, something that you can do either by just concatenating datasets or in a more sophisticated form through something called a data template. Data templates in particular were tricky to work with though as they had to be manually created in the form of an XML document, but the 11gR1 release makes this easier through the Diagram tab. Now the example I’m using has just a single query, but in the screenshot below (taken from the product documentation), we can see a data model that has multiple queries with joins created between them. You can take this further to define nested groups, aggregations that apply at various group levels, and derived columns based on fields in the dataset.

I’m going to use this diagram editor now, to add a derived “profit” field to the dataset based two of the other fields within it. I select the Add Element by Expression menu option for the query like this:

This then brings up an expression editor, where I can derive profit based on revenue less costs. After validating the expression, the new field is added to the query as a derived (function) field.

So now we’re at the point where the data model query is defined. One final step thought that I need to perform, is to generate some sample XML output that can then be used by the Online Template Builder. This is the same as with the Microsoft Word Template Builder that came with 10g (and is still available with this release), that would extract a small sample dataset when you created the report template, so that you could see data in the template when you went to preview it. For this new data model editor, you create the sample XML dataset yourself, which gives you a good chance to sanity check the query output before going on to lay out the various templates.

Now that I’ve defined the data model, I can get onto designing the templates. I select New > Report from the common header menu, and the first thing I’m prompted to do is to select an existing data model, like this:

I select the data model I defined a moment ago, and then I’m prompted to select a layout base template. Now in 11gR1, there’s a distinction between the RTF templates that you got with 10g and are usually edited using the Microsoft Word Template Builder, and the new-style templates you get with 11gR1 that are edited using the Online Template Builder (at the moment, there’s no way to upgrade RTF templates to the online-style ones). I select the Blank (Landscape) basic template as my first reports are going to be viewed online, using the new Interactive Viewer.

This then brings up the Online Template Builder. I start by adding a page layout grid with a single row and two columns, and then I add a data table object, and a graph object, to the template so that it looks like this:

The 11gR1 release of the Online Template Builder gives us a few more visualizations, including gauges.

The Page Layout tab lets me set the page orientation, together with page breaks, headers and footers. The Chart tab is contextual and changes depending on the object you’ve got selected in the template. There’s also a concertina-style menu on the left-hand side, which lets you see the available fields, like this:

a list of all the available template objects, or the properties of the object should you wish to fine-tune it. The screenshot below shows the properties for the graph object in my template.

Once this template is complete, I give it a name and then save it to the report definition.
For the next template, I want to really try out the features in the new Web-based Interactive Viewer. I start by adding a pie chart, that shows the share of product sales profit broken down by region, like this:

I then add a pivot table to the right of the pie chart, with a repeating group at the top that repeats on month, and the pivot table itself breaking sales revenue down by product category and type, and by store across the top.

Now what I’d like to happen now, is for the user to be able to click on regions in the pie chart, and for this to filter the values in the pivot table I’ve just created. To do this, I switch to the Page Layout tab and press the Configure Events button.

Using this feature, I can “wire” the output of the pie chart to the pivot table that I’ve just created, so that clicks on the pie chart will trigger filtering on the pivot table, in a similar way to master-detail linking in Answers 11g and of course, Qlikview.

Now when I run this report and template, the user can filter the entire report by clicking on segments in the pie chart, like this:

The pivot table is also dynamic, so that I can click on the Drinks category, for example, and see sales broken down by the various drink types.

I can then go back to the template and add more visualizations, and wire all of these together so that changes in one act as filters on the others.

Bringing it all together and viewing it online, I’ve got an interactive web report that lets users drill-in to, and explore, their dataset.

Of course, it being BI Publisher, I’ve still got all the standard stuff such as printing to PDF, scheduling reports, bursting to separate files and so on, but these new features certainly add a new dimension to the product.

Coupled with the visual editor for federated datasets, BI Publisher 11g stands up in its own right as a reporting tool and would be an interesting option for customers on a more limited budget, but who want to buy into the whole Oracle Business Intelligence / Fusion Middleware reporting stack. No doubt we’ll post more on the product as we start to roll it out on customer projects.

GL to FA link in r12

SELECT *
  FROM gl_je_headers
 WHERE NAME = <JOURNAL_NAME>;

SELECT *
  FROM gl_je_lines
 WHERE je_header_id = <JE_HEADER_ID>
   AND code_combination_iD = <CCID>;

SELECT *
  FROM gl_import_references
 WHERE je_header_id = <JE_HEADER_ID>
   AND je_line_num IN (1,2);

SELECT *
  FROM xla_ae_lines
 WHERE gl_sl_link_id IN (SELECT gl_sl_link_id
                           FROM gl_import_references
                          WHERE je_header_id = <JE_HEADER_ID>
                            AND je_line_num IN (1,2));

SELECT *
  FROM xla_distribution_links
 WHERE ae_header_id IN (SELECT ae_header_id
                          FROM xla_ae_lines
                         WHERE gl_sl_link_id IN
                                 (SELECT gl_sl_link_id
                                    FROM gl_import_references
                                   WHERE je_header_id = <JE_HEADER_ID>
                                     AND je_line_num IN (1,2)));


SELECT *
  FROM fa_deprn_detail
 WHERE event_id IN
       (  SELECT event_id
            FROM xla_distribution_links
           WHERE ae_header_id IN (SELECT ae_header_id
                                    FROM xla_ae_lines
                                   WHERE gl_sl_link_id IN
                                           (SELECT gl_sl_link_id
                                              FROM gl_import_references
                                             WHERE je_header_id =  <JE_HEADER_ID>
                                               AND je_line_num IN (1,2)))
       );

Ap to FA link in r12

Select Query :-

select * from ap_invoices_all where invoice_num like '25-apr-2012';

select * from AP_INVOICE_LINES_ALL where invoice_id=1181871;

select * from AP_INVOICE_DISTRIBUTIONS_ALL where invoice_id=1181871;

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=1181871;

select * from AP_PAYMENT_SCHEDULES_ALL where INVOICE_ID=1181871;

--SELECT * FROM AP_ACCOUNTING_EVENTS_ALL WHERE SOURCE_TABLE='AP_INVOICES' AND SOURCE_ID=1181871;

SELECT * FROM XLA.XLA_TRANSACTION_ENTITIES WHERE SOURCE_ID_INT_1='1181871';

SELECT * FROM XLA.XLA_TRANSACTION_ENTITIES WHERE SOURCE_ID_INT_1='1181871' AND ENTITY_CODE='AP_INVOICES';

--TRANSACTION_NUMBER => INVOICE_NUMBER

-- LEDGER_ID

--GET THE EVENT_ID= 4590221

SELECT * FROM XLA.XLA_EVENTS WHERE ENTITY_ID=4590221;

--=> AFTER DRAFT ACCOUNTING

--GET THE EVENT_ID- 4623169

--EVENT_STATUS_CODE U => UNPOSTED

--PROCESS_STATUS_CODE - D => DRAFT

--EVENT_TYPE_CODE

--=> AFTER FINAL ACCOUNTING

--EVENT_STATUS_CODE U => P

--PROCESS_STATUS_CODE - P

SELECT * FROM XLA.XLA_AE_HEADERS WHERE ENTITY_ID=4590221 AND EVENT_ID=4623169;

--=> AFTER DRAFT ACCOUNTING

--ACCOUNTING_ENTRY_STATUS_CODE F

--=> AFTER FINAL ACCOUNTING

--ACCOUNTING_ENTRY_STATUS_CODE F

SELECT * FROM XLA.XLA_AE_LINES WHERE AE_HEADER_ID=8489753;

-- AE LINES WHEN DRAFT

SELECT * FROM XLA.XLA_AE_LINES WHERE AE_HEADER_ID=8489758;

-- AE LINES WHEN FINAL

--AE HEADER ID IS CHANGED AFTER ACCOUTNING TO FINAL

Link between AP and GL in R/12

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

AP_INVOICES_ALL --> Invoice_id = APPLIED_TO_SOURCE_ID_NUM_1 <-- XLA_DISTRIBUTION_LINKS

Also

AP_INVOICE_DISTRIBUTIONS_ALL -->INVOICE_DISTRIBUTION_ID = SOURCE_DISTRIBUTION_ID_NUM_1 <-- XLA_DISTRIBUTION_LINKS

 

SELECT invoice_id

  FROM ap_invoices_all 

WHERE invoice_num = <invoice_number>;

SELECT amount,

               period_name,

              accounting_date,

              posted_flag,

              accrual_posted_flag,

              accounting_event_id,

              dist_code_combination_id,

               invoice_distribution_id

  FROM ap_invoice_distributions_all

WHERE invoice_id = < invoice_id >;

SELECT entity_id

   FROM xla_events

 WHERE event_id = <accounting_event_id >;

SELECT ae_header_id

   FROM xla_ae_headers

 WHERE entity_id = <entity_id>;

SELECT gl_sl_link_id

   FROM xla_ae_lines

 WHERE ae_header_id = < ae_header_id >

     AND code_combination_id = < dist_code_combination_id >;

SELECT je_batch_id,

               je_header_id

   FROM gl_import_references

 WHERE gl_sl_link_id = <gl_sl_link_id>;

By using the below query we can view journal entry batches from which the invoice is posted.

SELECT name Batch_name, Status, Default_period_name,

               Posted_date, Posting_run_id

   FROM gl_je_batches

 WHERE je_batch_id = < je_batch_id >;

By using the below query we can view the Journal entry headers.

SELECT name Journal_name, Je_category, Je_source, Ledger_id, Period_name, Je_from_SLA_flag,

               Status

  FROM gl_je_headers

WHERE je_header_id = < je_header_id >;

By using the below query we can view the Journal entry lines of a particular Journal entry header

SELECT Je_line_num, code_combination_id, Period_name,

               Entered_dr, Entered_cr, Ledger_id

   FROM gl_je_lines

 WHERE je_header_id = < je_header_id >;

 If we want to see the Journal entry lines of a particular invoice distribution line, use the below query as shown:

SELECT Je_line_num, code_combination_id, Period_name,

               Entered_dr, Entered_cr, Ledger_id

   FROM gl_je_lines

 WHERE je_header_id = < je_header_id >

      AND code_combination_id = < dist_code_combination_id from Ap_invoice_distributions_all >                                        

      AND Period_name = < Period_name from Ap_invoice_distributions_all >;

SELECT Source_distribution_id_num_1,

               Source_distribution_type,

               Applied_to_entity_id,

               Applied_to_source_id_num_1,

               Applied_to_dist_id_num_1,

               Ref_ae_header_id,

               Ref_temp_line_num,

               Ref_event_id

  FROM xla_distribution_links

WHERE ae_header_id = < ae_header_id >;

The tables’ xla_distribution_links and Ap_invoice_distributions_all are linked by

Source_distribution_id_num_1 = Invoice_distribution_id

Source_distribution_type = ‘AP_INV_DIST’

SELECT Source_distribution_id_num_1,

               Source_distribution_type,

               Applied_to_entity_id,

               Applied_to_source_id_num_1,

               Applied_to_dist_id_num_1,

               Ref_ae_header_id,

               Ref_temp_line_num,

               Ref_event_id

   FROM xla_distribution_links

 WHERE ae_header_id = < ae_header_id >

      AND source_distribution_type = 'AP_INV_DIST'

      AND source_distribution_id_num_1 = (SELECT invoice_distribution_id

                                                                     FROM Ap_invoice_distributions_all

                                                                   WHERE invoice_id = < Invoice_id >);

 

query

1.  SELECT

2.      aia.INVOICE_ID "Invoice Id",

3.      aia.INVOICE_NUM "Invoice Number",

4.      aia.INVOICE_DATE "Invoice Date",

5.      aia.INVOICE_AMOUNT "Amount",

6.      xal.ENTERED_DR "Entered DR in SLA",

7.      xal.ENTERED_CR "Entered CR in SLA",

8.      xal.ACCOUNTED_DR "Accounted DR in SLA",

9.      xal.ACCOUNTED_CR "Accounted CR in SLA",

10.    gjl.ENTERED_DR "Entered DR in GL",

11.    gjl.ACCOUNTED_DR "Accounted DR in GL",

12.    xal.ACCOUNTING_CLASS_CODE "Accounting Class",

13.    gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'

14.        ||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'

15.        ||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'

16.        ||gcc.SEGMENT7 "Code Combination",

17.    aia.INVOICE_CURRENCY_CODE "Inv Curr Code",

18.    aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",

19.    aia.GL_DATE "GL Date",

20.    xah.PERIOD_NAME "Period",

21.    aia.PAYMENT_METHOD_CODE "Payment Method",

22.    aia.VENDOR_ID "Vendor Id",

23.    aps.VENDOR_NAME "Vendor Name",

24.    xah.JE_CATEGORY_NAME "JE Category Name"

25.FROM

26.    ap.ap_invoices_all aia,

27.    xla.xla_transaction_entities XTE,

28.    xla.xla_events xev,

29.    xla.xla_ae_headers XAH,

30.    xla.xla_ae_lines XAL,

31.    GL_IMPORT_REFERENCES gir,

32.    gl_je_headers gjh,

33.    gl_je_lines  gjl,

34.    gl_code_combinations gcc,

35.    ap.ap_suppliers aps,

36.    (SELECT aid1.invoice_id,

37.            pa.project_id,

38.            nvl(pa.segment1,'NO PROJECT') Project

39.    FROM    ap_invoice_distributions_all aid1,

40.            PA_PROJECTS_ALL pa

41.    WHERE aid1.rowid IN

42.        (SELECT MAx(rowid)

43.        FROM ap_invoice_distributions_all aid2

44.        WHERE aid1.INvoice_ID=aid2.INvoice_ID

45.        GROUP BY aid1.invoice_id)

46.    AND aid1.project_id=pa.project_id(+)) sql1,

47.    (SELECT aid1.invoice_id,

48.            pt.task_id,

49.            nvl(pt.task_number,'NO TASK') Task

50.    FROM    ap_invoice_distributions_all aid1,

51.            PA_TASKS pt

52.    WHERE aid1.rowid IN

53.        (SELECT MAx(rowid)

54.        FROM ap_invoice_distributions_all aid2

55.        WHERE aid1.INvoice_ID=aid2.INvoice_ID

56.        GROUP BY aid1.invoice_id)

57.    AND aid1.task_id=pt.task_id(+)) sql2

58.WHERE

59.    aia.INVOICE_ID = xte.source_id_int_1

60.    AND aia.INVOICE_ID=sql1.Invoice_ID

61.    AND aia.INVOICE_ID=sql2.Invoice_ID

62.    AND xev.entity_id= xte.entity_id

63.    AND xah.entity_id= xte.entity_id

64.    AND xah.event_id= xev.event_id

65.    AND XAH.ae_header_id = XAL.ae_header_id

66.    AND XAH.je_category_name = 'Purchase Invoices'

67.    AND XAH.gl_transfer_status_code= 'Y'

68.    AND XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID

69.    AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

70.    AND gjl.JE_HEADER_ID=gjh.JE_HEADER_ID

71.    AND gjh.JE_HEADER_ID=gir.JE_HEADER_ID

72.    AND gjl.JE_HEADER_ID=gir.JE_HEADER_ID

73.    AND gir.JE_LINE_NUM=gjl.JE_LINE_NUM

74.    AND gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID

75.    AND gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID

76.    AND aia.VENDOR_ID=aps.VENDOR_ID

77.    AND gjh.STATUS='P'

78.    AND gjh.Actual_flag='A'

79.    AND gjh.CURRENCY_CODE='USD'

80.    AND aia.Invoice_id=&Invoice_Id;