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;