How to Implement Bar Code in XML Publisher Report

Bar Code Local Setup:

1) Copy Barcode font in C:\Windows\Fonts directory.
2) Copy xdo.cfg in C:\Program Files\Oracle\XML Publisher Desktop\Template Builder for Word\config directory. The sample file is already available when BI Publisher desktop is installed.
3) Right click the font file and go to properties. Copy the .ttf file name. Double click the font file to get the family name.
4) Open the xdo.cfg file and paste as follows (My filename is w39elc.ttf and font family is WASP 39 ELC)

<font family="WASP 39 ELC" style="normal" weight="normal">  <truetype path="C:\WINDOWS\Fonts\w39elc.ttf" /> </font>

Bar Code Application Setup:

Basically this post refers to any font that you want to embed into you output but I was working specifically on demand to insert the barcode.

So start with something simple. Obtain the font file. In my case I posses “WASP 39 ELC" font. Font file name is w39elc.ttf. Install the font into Windows by double clicking on it and choosing “install”

Open your RTF template and place the bar code in the location you want.

So far so good and if you run this template locally on your workstation you will see the bar code. But not in EBS.

  This is what you need to do in order to make it work in EBS

Open “XML Publisher Administrator” responsibility

Go to “Administration”-> “Font Files”

Click on “Create Font File”

In “Font Name” put the name of the font as it appears in Word. Choose the font file and click “Apply”

Go to “Administrator”-> “Font Mapping”

Click on “Create Font Mapping Set”

Put into “Mapping Name” – Bar Code Fonts, into “Mapping Code” – BAR_CODE_FONTS, into “Type” –FO To PDF and click “Apply”

Click on the Bar Code Fonts link

Click on “Create Font Mapping”

Define Font Mapping as appears in the print screen bellow and click “Continue”

Ok, now you need  template to “know” the bar code font we just created.

Open template definition and go to “Edit Configuration”

Expand “FO Processing” and put into “Font Mapping Set” – Bar Code Fonts and click “Apply”.

Now you can run your template and it will display the bar code properly.

Some Useful Queries in Oracle Apps R12

How to initialize session in Oracle ?
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.

Mentioned below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
                           resp_id=>l_resp_id,
                           resp_appl_id=>l_resp_appl_id);

l_user_id is the fnd user ID which will be utilized during the call.
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.
You can use either sysadmin or use some user who has all the above listed responsibilities.

For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id ,
       fresp.responsibility_id,
       fresp.application_id
from   fnd_user fnd
,      fnd_responsibility_tl fresp
where  fnd.user_name = 'SYSADMIN'
and    fresp.responsibility_name = 'Order Management Super User';

Another option is Help > Diagnostics > Examine and get the values from $profile session values.

How to Find PO Requisitions associated with PO Orders?
By using below query , we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.
1) Purchasing Requisition
2) Internal Requisition.
Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 "Requisition Number",
prha.type_lookup_code "Requisition Type",
pha.segment1 "Purchase Order Number",
pha.type_lookup_code "Purchase Order Type"
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id "requisition_header_id"
,prha.segment1 "Requisition Number"
,prha.preparer_id "preparer_id"
,TRUNC(prha.creation_date) "creation_date"
,prha.description "description"
,prha.note_to_authorizer "note_to_authorizer"
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code='CANCEL'
AND pah.object_type_code='

REQUISITION'
AND pah.object_id=prha.requisition_header_id



How to find On-hand inventory information?

Execute below query to see the onhand inventory information in oracle applications.

SELECT
NVL(substr(org.organization_code, 1,3), ' ') orgcode
,NVL(substr(msi.segment1, 1, 8), ' ') seg11
,NVL(substr(msi.segment1, 9, 8), ' ') seg12
,NVL(substr(msi.segment1, 17, 4), ' ') seg13
,NVL(moq.subinventory_code, ' ') sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ' ') trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;



How to know the version in oracle apps?

select * from v$version;



With the help of above query we can find out the oracle apps version.

How to know the application is multi-org or not?

By using below query we can get the multi org status.







SELECT multi_org_flag

FROM   fnd_product_groups





How to know Which User is Locked the table?



This Query will list the name of user who locked the table table. The object name is taken as an input parameter.



SELECT c.owner

      ,c.object_name

      ,c.object_type

      ,fu.user_name locking_fnd_user_name

      ,fl.start_time locking_fnd_user_login_time

      ,vs.module

      ,vs.machine

      ,vs.osuser

      ,vlocked.oracle_username

      ,vs.sid

      ,vp.pid

      ,vp.spid AS os_process

      ,vs.serial#

      ,vs.status

      ,vs.saddr

      ,vs.audsid

      ,vs.process

FROM fnd_logins      fl

    ,fnd_user        fu

    ,v$locked_object vlocked

    ,v$process       vp

    ,v$session       vs

    ,dba_objects     c

WHERE vs.sid = vlocked.session_id

AND vlocked.object_id = c.object_id

AND vs.paddr = vp.addr

AND vp.spid = fl.process_spid(+)

AND vp.pid = fl.pid(+)

AND fl.user_id = fu.user_id(+)

AND c.object_name = :P_TABLE_NAME

AND nvl(vs.status,'XX') != 'KILLED';





How to get Customer Address Details in Oracle apps?







The following Query will provide the customer address details in oracle applications.



SELECT  hca.account_number customer_number,

                hp.party_name customer_name,

                hps.party_site_number site_number, hl.address1 address1,

                hl.address2 address2, hl.address3 address3,

                hl.address4 address4, hl.city city,

                hl.postal_code postal_code, hl.state state,

                ftt.territory_short_name country,

                hcsua1.LOCATION bill_to_location,

                hcsua2.LOCATION ship_to_location

  FROM hz_parties hp,

                hz_party_sites hps,

                hz_cust_accounts hca,

                hz_cust_acct_sites_all hcasa1,

                hz_cust_site_uses_all hcsua1,

                hz_locations hl,

                fnd_territories_tl ftt,

                hz_cust_acct_sites_all hcasa2,

                hz_cust_site_uses_all hcsua2

  WHERE hp.party_id = hps.party_id(+)

       AND hp.party_id = hca.party_id(+)

       AND hcasa1.party_site_id(+) = hps.party_site_id

       AND hcasa2.party_site_id(+) = hps.party_site_id

       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id

       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id

       AND hcsua1.site_use_code(+) = 'bill_to'

       AND hcsua2.site_use_code(+) = 'ship_to'

       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')

       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')

       AND hps.location_id = hl.location_id

       AND hl.country = ftt.territory_code

       AND ftt.LANGUAGE = USERENV ('lang')

  ORDER BY customer_number;





Script to find Oracle API’s for any module



With the help of below query we can find the API for a module in Oracle Apps.



 select substr(a.OWNER,1,20) , substr(a.NAME,1,30) , substr(a.TYPE,1,20) , substr(u.status,1,10) Stat , u.last_ddl_time , substr(text,1,80) Description from dba_source a, dba_objects u WHERE 2=2 and u.object_name = a.name and a.text like ‘%Header%’ and a.type = u.object_type and a.name like ‘AR_%API%’ –- Checking for AR Related APIs order by a.owner, a.name

R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving

Here I am trying to describe R12 SLA(Sub Ledger Accounting) Procedure.

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.

Hope this will help you.

Bulk Collect in Oracle PL/SQL

Below are some examples:

create table BULK_COLLECT_TEST as select * from PER_ALL_PEOPLE_F;

Table created.

insert into BULK_COLLECT_TEST

select * from BULK_COLLECT_TEST;

20000 rows created.

--BLOCK1:Using Loops
declare
 cursor c1
 is select object_name from BULK_COLLECT_TEST;
 rec1 c1%rowtype;
 begin
      open c1;
    loop
       fetch c1 into rec1;
    exit when c1%notfound;
    null;
    end loop;
 end;

total Elapsed Time is : 45 Secs

--BLOCK2: Using Bulk Collecting
declare
  cursor c1 is select object_name from BULK_COLLECT_TEST;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
begin
open c1;
   fetch c1 bulk collect into rec1;
end;

total Elapsed Time is : 5 Sec

So bulk collecting the rows shows a huge performance improvement over fetching row by row.

Some cases there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again.
Otherwise process memory gets bigger and bigger as you fetch the rows.

--Bulk Collect Example using LIMIT :
declare
 cursor c1 is select object_name from BULK_COLLECT_TEST;
 type c1_type is  table of c1%rowtype;
 rec1 c1_type;
begin
    open c1;
    loop
    fetch c1 bulk collect into rec1 limit 200;
    for i in 1..rec1.count loop
    null;
    end loop;
    exit when c1%notfound;
    end loop;
end;


Example:-
-----------

declare
type array is table of number index by binary_integer;
l_data array;
cursor c is select empno from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
if ( c%notfound )
then
dbms_output.put_line
( 'Cursor returned NOT FOUND but array has ' || l_data.count
|| ' left to process' );
else
dbms_output.put_line
( 'We have ' || l_data.count
|| ' to process' );
end if;
exit when c%notfound;
end loop;
close c;
end;

PO TO GL LINK IN R12

  PO TO GL LINK IN R12( THROUGH XLA) or ( WITH XLA )

SELECT   prh.segment1 "PO Requisition Number",
         pha.segment1 "PO Number",
         aps.SEGMENT1 "Supplier Number",
         aps.vendor_name,
         apss.vendor_site_code,
         apsc.first_name,
         apsc.last_name,
         pla.item_id,
         plla.ship_to_organization_id,
         plla.ship_to_location_id,
         rt.transaction_type,
         rt.destination_type_code,
         rsh.receipt_num "PO Receipt Number",
         aia.invoice_num,
         aida.dist_code_combination_id,
         aca.check_number,
         gjh.ledger_id,
         gjh.name
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         po_headers_all pha,
         po_lines_all pla,
         po_distributions_all pda,
         po_line_locations_all plla,
         ap_suppliers aps,
         ap_supplier_sites_all apss,
         ap_supplier_contacts apsc,
         rcv_transactions rt,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         ap_invoices_all aia,
         ap_invoice_lines_all aila,
         ap_invoice_distributions_all aida,
         ap_invoice_payments_all aipa,
         ap_checks_all aca,
         xla.xla_transaction_entities xte,
         xla_events xe,
         xla_ae_headers xah,
         xla_ae_lines xal,
         xla_distribution_links xdl,
         gl_import_references gir,
         gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl
 WHERE       prh.segment1 = :RequitionNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
         AND aps.vendor_id = pha.vendor_id
         AND apss.vendor_id = aps.vendor_id
         AND apss.vendor_site_id(+) = pha.vendor_site_id
         AND apss.vendor_site_id = aca.vendor_site_id
         AND apsc.vendor_site_id = apss.vendor_site_id
         AND apsc.vendor_contact_id = pha.vendor_contact_id
         AND prl.requisition_header_id = prh.requisition_header_id
         AND prd.requisition_line_id = prl.requisition_line_id
         AND pda.req_distribution_id = prd.distribution_id
         AND pla.po_header_id = pda.po_header_id
         AND pla.po_line_id = pda.po_line_id
         AND pha.po_header_id = pla.po_header_id
         AND pha.org_id = 204
         AND plla.po_header_id = pla.po_header_id
         AND plla.po_line_id = pla.po_line_id
         AND rt.transaction_type = 'DELIVER'
         AND rt.po_header_id = pha.po_header_id
         AND rt.po_line_id = pla.po_line_id
         AND rsh.shipment_header_id = rt.shipment_header_id
         AND rsl.shipment_header_id = rsh.shipment_header_id
         AND rsl.shipment_line_id = rt.shipment_line_id
         AND aila.po_header_id = pha.po_header_id
         AND aila.po_line_id = pla.po_line_id
         AND aia.invoice_id = aila.invoice_id
         AND aida.invoice_id = aila.invoice_id
         AND aida.invoice_line_number = aila.line_number
         AND aipa.invoice_id = aia.invoice_id
         AND aca.check_id = aipa.check_id
         AND xte.entity_code = 'AP_PAYMENTS'
         AND xte.transaction_number = aca.check_number
         AND xte.source_id_int_1 = aipa.check_id
         AND xte.security_id_int_1 = aia.org_id
         AND xe.entity_id = xte.entity_id
         AND xah.event_id = xe.event_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xal.ae_line_num = aida.invoice_line_number
         AND xdl.ae_header_id = xah.ae_header_id
         AND xdl.ae_line_num = xal.ae_line_num
         AND xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
         AND gir.reference_5 = xte.entity_id                      -- Entity Id
         AND gir.reference_6 = TO_CHAR (xe.event_id)                --Event Id
         AND gir.reference_7 = TO_CHAR (xah.ae_header_id)      -- AE Header Id
         AND gir.gl_sl_link_id = xal.gl_sl_link_id
         --and gir.created_by = 1318
         AND gjb.je_batch_id = gir.je_batch_id
         AND gjh.je_batch_id = gjb.je_batch_id
         AND gjh.je_header_id = gir.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gir.je_line_num

AR TO GL LINK IN R12

 AR TO GL LINK IN R12( THROUGH XLA) or ( WITH XLA )

/* Formatted on 11/29/2013 4:46:26 PM (QP5 v5.114.809.3010) */
SELECT   ooha.order_number,
         ooha.org_id,
         hca.account_name,
         hp.party_name "Customer Name",
         hcasab.orig_system_reference BILL_TO_ORIG_REF,
         hpsb.status BILL_TO_STATUS,
            'ADDRESS1 - '
         || bill_loc.address1
         || ','
         || CHR (10)
         || 'ADDRESS2 - '
         || bill_loc.address2
         || ','
         || CHR (10)
         || 'ADDRESS3 - '
         || bill_loc.address3
         || ','
         || CHR (10)
         || 'CITY     - '
         || bill_loc.city
         || ','
         || CHR (10)
         || 'POSTAL CD- '
         || bill_loc.postal_code
         || ','
         || CHR (10)
         || 'COUNTRY  - '
         || bill_loc.country
            BILL_TO_ADDRESS,
         hcasas.orig_system_reference SHIP_TO_ORIG_REF,
         hpss.status SHIP_TO_STATUS,
            'ADDRESS1 - '
         || ship_loc.address1
         || ','
         || CHR (10)
         || 'ADDRESS2 - '
         || ship_loc.address2
         || ','
         || CHR (10)
         || 'ADDRESS3 - '
         || ship_loc.address3
         || ','
         || CHR (10)
         || 'CITY     - '
         || ship_loc.city
         || ','
         || CHR (10)
         || 'POSTAL CD- '
         || ship_loc.postal_code
         || ','
         || CHR (10)
         || 'COUNTRY  - '
         || ship_loc.country
            SHIP_TO_ADDRESS,
         oola.inventory_item_id,
         oola.ordered_item,
         msib.description item_description,
         wnd.name delivery_number,
         rct.trx_number "AR Invoice Number",
         acr.receipt_number "AR Receipt Number",
         gjh.ledger_id,
         gjh.name
  FROM   oe_order_headers_all ooha,
         oe_order_lines_all oola,
         hz_parties hp,
         hz_cust_accounts hca,
         hz_party_sites hpss,
         hz_party_sites hpsb,
         hz_locations bill_loc,
         hz_locations ship_loc,
         hz_cust_acct_sites_all hcasab,
         hz_cust_acct_sites_all hcasas,
         hz_cust_site_uses_all hzsuab,
         hz_cust_site_uses_all hzsuas,
         mtl_system_items_b msib,
         wsh_delivery_details wdd,
         wsh_new_deliveries wnd,
         wsh_delivery_assignments wda,
         ra_customer_trx_all rct,
         ra_customer_trx_lines_all rctl,
         ra_cust_trx_line_gl_dist_all rctld,
         ar_cash_receipts_all acr,
         xla.xla_transaction_entities xte,
         xla_events xe,
         xla_ae_headers xah,
         xla_ae_lines xal,
         xla_distribution_links xdl,
         gl_import_references gir,
         gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl
 WHERE       ooha.order_number = :SalesOrderNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
         AND ooha.org_id = 204
         AND hca.cust_account_id = ooha.sold_to_org_id
         AND hp.party_id = hca.party_id
         AND hpss.party_id = hca.party_id
         AND hpsb.party_id = hca.party_id
         AND bill_loc.location_id = hpss.location_id
         AND ship_loc.location_id = hpsb.location_id
         AND hcasas.cust_account_id = hca.cust_account_id
         AND hcasab.cust_account_id = hca.cust_account_id
         AND hcasas.party_site_id = hpss.party_site_id
         AND hcasab.party_site_id = hpsb.party_site_id
         AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
         AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
         AND hzsuas.site_use_id = ooha.ship_to_org_id
         AND hzsuab.site_use_id = ooha.invoice_to_org_id
         AND wda.delivery_id = wnd.delivery_id(+)
         AND wdd.delivery_detail_id = wda.delivery_detail_id
         AND wdd.source_header_id = ooha.header_id
         AND wdd.source_line_id = oola.line_id
         AND wdd.organization_id = msib.organization_id(+)
         AND wdd.inventory_item_id = msib.inventory_item_id(+)
         AND rct.interface_header_attribute1 = TO_CHAR (ooha.order_number)
         AND rct.org_id = ooha.org_id
         AND rctl.customer_trx_id = rct.customer_trx_id
         AND rctl.sales_order = TO_CHAR (ooha.order_number)
         AND rctld.customer_trx_id = rct.customer_trx_id
         AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
         AND acr.receipt_number = 'G-1001'
         AND acr.pay_from_customer = rct.sold_to_customer_id
         AND acr.org_id = ooha.org_id
         AND acr.customer_site_use_id = rct.bill_to_site_use_id
         AND xte.transaction_number = acr.receipt_number
         AND xte.entity_code = 'RECEIPTS'
         AND xe.entity_id = xte.entity_id
         AND xah.event_id = xe.event_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xal.accounting_class_code = 'CASH'
         AND xdl.ae_header_id = xah.ae_header_id
         AND xdl.ae_line_num = xal.ae_line_num
         --and xdl.source_distribution_id_num_1
         AND gir.reference_5 = xte.entity_id                      -- Entity Id
         AND gir.reference_6 = TO_CHAR (xe.event_id)                --Event Id
         AND gir.reference_7 = TO_CHAR (xah.ae_header_id)      -- AE Header Id
         AND gir.gl_sl_link_id = xal.gl_sl_link_id
         AND gir.created_by = 1318
         AND gjb.je_batch_id = gir.je_batch_id
         AND gjh.je_batch_id = gjb.je_batch_id
         AND gjh.je_header_id = gir.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gir.je_line_num

Apex 4.0.2 Configured with Oracle EBS 12.1.2

This post is about the integration of Oracle Application Express 4.0.2 with Oracle EBS 12.1.2 with oracle Application server 10.1.3.1 in the middle.

Operating System: Redhat - Linux 4 update 5
Database : 10.2.0.4
Application Server 10g: 10.1.3.1.0

Oracle E Business Suite: 12.1.2 with 11g Database (11.1.0.7)

The steps to be done:

1. Install oracle Database (Installed 10.2.0.1 and upgraded to 10.2.0.4)

Oracle Database to be installed with enterprise option.

Oracle Database upgraded to 10.2.0.4.
Detailed Upgrade steps can be obtained from (http://balajiabhi.blogspot.com/2009/02/install-soa-and-bpel-part-1.html) or click here

2. Install and configure Oracle SOA Suite 10g (10.1.3.1)
Installation and configuration Steps are detailed here


3. Oracle Application Express 4.0.2 can be obtained here (download)

4. Unzip the Apex402.zip into a location in the server.

5. Login to the EBS Database owner and connect as sys user.
[oracle@fserver apex]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Dec 1 13:17:13 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace APEX402 datafile '/oracle2/db/apps_st/data/apex402.dbf' size 1G autoextend on;

Tablespace Created.

After creating the tablespace, exit from the sqlplus window, navigate to the folder where we unzipped the Apex402 Zip file.

from that location, again connect as sys user

SQL> @apexins.sql apex402 apex402 temp /i/

here apex402 is the tablespace
apex402 is the datafile,
temp is temporary tablespace by default
/i/ is the location of the image files. (simply specify /i/ at this time)

Once the process started, keep watching the logfile for the errors. Log file will be created at the base location of apex source files (Unzipped location)


...Adjust Oracle APEX Application owner to install owner

13 rows updated.


Commit complete.

...Adjust Application Express (APEX) version

FLOW_VERSION
--------------------------------------------------------------------------------
4.0.2.00.06

1 row selected.


12 rows updated.


1 row updated.


Commit complete.


User altered.


User altered.


PL/SQL procedure successfully completed.

-- Now beginning upgrade. This will take several minutes.-------
-- Ensuring template names are unique -------
-- Migrating metadata to new schema -------
-- Switching builder to new schema -------
-- Migrating SQL Workshop metadata -------
-- Upgrading new schema. -------


PL/SQL procedure successfully completed.

-- Now beginning upgrade. This will take several minutes.-------
-- Ensuring template names are unique -------
-- Migrating metadata to new schema -------
-- Switching builder to new schema -------
-- Migrating SQL Workshop metadata -------
-- Upgrading new schema. -------
-- Copying preferences to new schema. -------
Upgrade completed successfully no errors encountered.
-- Upgrade is complete -----------------------------------------
timing for: Upgrade
Elapsed: 00:01:14.28



Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the APEX_040000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

JOB_QUEUE_PROCESSES: 2

Performing Application Express component validation - please wait...

Completing registration process. 13:35:57
Validating installation. 13:35:57
...Database user "SYS", database schema "APEX_040000", user# "587" 13:35:57

Once you see this message, the Apex installation to the Oracle EBS database is completed.

unlock the APEX_PUBLIC_USER account.
Change the password using @apxchpwd.sql from apex directory.



6. Navigate to the Application server installed directory.

Copy the image files:

Copy the image files from the Apex Base directory to someother directory in the Application server installed directory.

In my case it is APPLICATION_SERVER_BASE/images



7. Move to Apache/modplsql/conf directory and edit the dads.conf file.

Add the entry like this:
Alias /i/ "/home/oraappload/product/10.1.3.1/OracleAS_2/images/"
AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex_R12>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString fserver.fdomain:1550:VIS1212 [Hostname:DBListenerPort:SID]
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword welcome [Password chosen by running apxchpwd.sql]
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all
</Location>

Bounce the opmn using

Application_server_base/opmn/bin/./opmnctl stopall

and

Application_server_base/opmn/bin/./opmnctl startall

Login to the Apex using the URL from Application server:

http://localhost:port/pls/apex_r12/apex_admin

this will bring the Apex login screen, login with the username admin and password as specified during the apex install

then create the workspaces as requested from the admin page.









If You are getting ADMIN/ADMIN_PASSWORD as "Invalid Login Credential Error", then
run

SQL> alter user APEX_040000 identified by welcome account unlock;

User altered.

SQL> conn APEX_040000/welcome;
Connected.
SQL> begin
wwv_flow_api.set_security_group_id(p_security_group_id=>10);

wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'admin2',
p_email_address => 'myemail@mydomain.com', (Change as per req)
p_web_password => 'admin2') ;

end;
/ 2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.


SQL> alter user APEX_040000 account lock;

User altered.

SQL>


Cheers.....

Previous Posts of Application Express from my blog (Complete List)

Apex Installation and configuration:

AP to Gl Link in oracle apps

/* Formatted on 2013/11/28 16:07 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, asup.vendor_name,
TO_CHAR (aca.check_number), aca.check_date,
aca.doc_sequence_value voucher_number, aca.creation_date voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc,
ap_suppliers asup,
ap_checks_all aca
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND asup.vendor_id(+) = xal.party_id
AND aca.doc_sequence_id(+) = xah.doc_sequence_id
AND aca.doc_sequence_value(+) = xah.doc_sequence_value
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Payables'
UNION ALL
------ DATA FROM CASH MANAGEMENT --------------------------------

/* Formatted on 2013/11/28 16:16 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Cash Management'
AND gjh.je_category = 'Bank Transfers'


UNION ALL
-------------------Data from Receivable --------------------------------
/* Formatted on 2013/11/28 16:07 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date,
(SELECT ac.customer_name
FROM ar_customers ac
WHERE ac.customer_id = xal.party_id) customer_name,
(SELECT acr.receipt_number
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value)
receipt_number,
(SELECT acr.receipt_date
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value) receipt_date,
(SELECT acr.doc_sequence_value
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value)
voucher_number,
(SELECT acr.creation_date
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value) voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla.xla_transaction_entities xte
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Receivables'
UNION ALL
---------------- Manual -----------------------
/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (gjl.accounted_dr, 0) accounted_dr,
NVL (gjl.accounted_cr, 0) accounted_cr,
gjl.description jv_line_description, '' event_type_code,
'' sla_description, NULL ae_line_num,
gjh.default_effective_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
NVL (gjl.accounted_dr, 0) receipt, NVL (gjl.accounted_cr, 0) payment
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Manual'
UNION ALL
-----ALL OTHER SOURCES OTHER THAN ABOVE----------

/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source NOT IN ('Receivables', 'Payables', 'Cash Management')




/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT DISTINCT ai.invoice_num, ai.gl_date, xah.accounting_date,
gjh.je_category, gjh.je_source, gjh.period_name, gjh.status,
aid.invoice_line_number, aid.line_type_lookup_code,
ail.description, aid.amount, aid.dist_code_combination_id
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_event_types_tl xet,
apps.xla_event_classes_tl xect,
apps.xla_distribution_links xdl,
apps.ap_invoice_distributions_all aid,
apps.ap_invoices_all ai,
apps.ap_invoice_lines_all ail
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xe.application_id = xet.application_id
AND xet.LANGUAGE = USERENV ('LANG')
AND xect.event_class_code = xet.event_class_code
AND xect.application_id = xe.application_id
AND xect.LANGUAGE = USERENV ('LANG')
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_id = ail.invoice_id
AND ail.invoice_id = aid.invoice_id
AND aid.invoice_line_number = ail.line_number
AND xah.event_type_code <> ' MANUAL'
AND gjh.je_source = 'Payables'
AND ai.org_id = p_org_id
AND xah.accounting_date BETWEEN p_period_start_date
AND p_period_end_date;




/* Formatted on 2013/11/28 16:09 (Formatter Plus v4.8.8) */
SELECT aia.invoice_id "Invoice Id", aia.invoice_num "Invoice Number",
aia.invoice_date "Invoice Date", aia.invoice_amount "Amount",
xal.entered_dr "Entered DR in SLA", xal.entered_cr "Entered CR in SLA",
xal.accounted_dr "Accounted DR in SLA",
xal.accounted_cr "Accounted CR in SLA", gjh.je_source,
gjl.entered_dr "Entered DR in GL",
gjl.accounted_dr "Accounted DR in GL",
xal.accounting_class_code "Accounting Class",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 "Code Combination",
aia.invoice_currency_code "Inv Curr Code",
aia.payment_currency_code "Pay Curr Code", aia.gl_date "GL Date",
xah.period_name "Period", aia.payment_method_code "Payment Method",
aia.vendor_id "Vendor Id", aps.vendor_name "Vendor Name",
xah.je_category_name "JE Category Name"
FROM apps.ap_invoices_all aia,
xla.xla_transaction_entities xte,
apps.xla_events xev,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.gl_import_references gir,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
apps.ap_suppliers aps,
(SELECT aid1.invoice_id, pa.project_id,
NVL (pa.segment1, 'NO PROJECT') project
FROM apps.ap_invoice_distributions_all aid1,
apps.pa_projects_all pa
WHERE aid1.ROWID IN (SELECT MAX (ROWID)
FROM apps.ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = aid2.invoice_id
GROUP BY aid1.invoice_id)
AND aid1.project_id = pa.project_id(+)) sql1,
(SELECT aid1.invoice_id, pt.task_id,
NVL (pt.task_number, 'NO TASK') task
FROM apps.ap_invoice_distributions_all aid1, apps.pa_tasks pt
WHERE aid1.ROWID IN (SELECT MAX (ROWID)
FROM apps.ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = aid2.invoice_id
GROUP BY aid1.invoice_id)
AND aid1.task_id = pt.task_id(+)) sql2
WHERE aia.invoice_id = xte.source_id_int_1
AND aia.invoice_id = sql1.invoice_id
AND aia.invoice_id = sql2.invoice_id
AND xev.entity_id = xte.entity_id
AND xah.entity_id = xte.entity_id
AND xah.event_id = xev.event_id
AND xah.ae_header_id = xal.ae_header_id
-- and XAH.je_category_name = 'Purchase Invoices'
AND xah.gl_transfer_status_code = 'Y'
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gjl.je_header_id = gjh.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gcc.code_combination_id = xal.code_combination_id
AND gcc.code_combination_id = gjl.code_combination_id
AND aia.vendor_id = aps.vendor_id
AND gjh.status = 'P'
AND gjh.actual_flag = 'A'
AND gjh.currency_code = 'USD'
AND aia.invoice_id = &invoice_id;



/* Formatted on 2013/11/28 16:10 (Formatter Plus v4.8.8) */
SELECT ph.segment1 po_number, aps.vendor_name, msi.segment1 item_number,
msi.description item_description, 'A/P PO Match' accrual_transaction,
'AP*' SOURCE,
(SELECT organization_name
FROM apps.org_organization_definitions
WHERE organization_id = pd.destination_organization_id) org,
ai.invoice_num doc_number, ai.invoice_date doc_date,
aid.invoice_line_number line, pl.unit_meas_lookup_code uom,
aid.quantity_invoiced quantity, aid.unit_price,
( NVL (xdl.unrounded_accounted_dr, 0)
- NVL (xdl.unrounded_accounted_cr, 0)
) accounted_amount,
(NVL (xdl.unrounded_entered_cr, 0) - NVL (xdl.unrounded_entered_cr, 0)
) entered_amount,
xal.currency_code
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_event_types_tl xet,
apps.xla_event_classes_tl xect,
apps.xla_distribution_links xdl,
apps.ap_invoice_distributions_all aid,
apps.ap_invoices_all ai,
apps.po_headers_all ph,
apps.po_distributions_all pd,
apps.po_lines_all pl,
apps.ap_suppliers aps,
apps.mtl_system_items_b msi
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xe.application_id = xet.application_id
AND xet.LANGUAGE = USERENV ('LANG')
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.application_id = xe.application_id
AND xect.LANGUAGE = USERENV ('LANG')
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
--AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 aid.invoice_distribution_id
AND ai.invoice_id = aid.invoice_id
AND aid.po_distribution_id = pd.po_distribution_id
AND gjh.je_source = 'Payables'
AND ph.po_header_id = pd.po_header_id
AND pl.po_header_id = ph.po_header_id
AND pd.po_line_id = pl.po_line_id
AND pd.org_id = 83
AND ph.org_id = 83
AND ai.org_id = 83
AND aid.org_id = 83
AND gjl.code_combination_id = 1011
AND ph.vendor_id = aps.vendor_id
AND msi.inventory_item_id = pl.item_id
AND msi.organization_id = pd.destination_organization_id




SELECT distinct
aia.INVOICE_ID "R12_Invoice_Id",
AIA.DOC_SEQUENCE_VALUE,
aia.INVOICE_NUM ,
aia.attribute6 "11i_INVOICE_ID",
aia.GL_DATE,
aia.INVOICE_AMOUNT,
xal.ACCOUNTED_DR "Accounted DR IN SLA",
xal.ACCOUNTED_CR "Accounted CR IN SLA",
gjl.ACCOUNTED_CR "ACCOUNTED_CR IN GL",
gjl.ACCOUNTED_DR "Accounted DR IN GL",
xev.event_type_code,
gcc.SEGMENT1
|| '.'
|| gcc.SEGMENT2
|| '.'
|| gcc.SEGMENT3
|| '.'
|| gcc.SEGMENT4
|| '.'
|| gcc.SEGMENT5
|| '.'
|| gcc.SEGMENT6
|| '.'
|| gcc.SEGMENT7
"CODE_COMBINATION",
aia.GL_DATE,
xah.PERIOD_NAME,
aia.VENDOR_ID "Vendor Id",
aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name",
GJH.JE_SOURCE
FROM ap_invoices_all aia,
xla.xla_transaction_entities XTE,
xla_events xev,
xla_ae_headers XAH,
xla_ae_lines XAL,
GL_IMPORT_REFERENCES gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
ap_suppliers aps
WHERE aia.INVOICE_ID = xte.source_id_int_1
and aia.ACCTS_PAY_CODE_COMBINATION_ID = gcc.code_combination_id
AND xev.entity_id = xte.entity_id
AND xah.entity_id = xte.entity_id
AND xah.event_id = xev.event_id
AND XAH.ae_header_id = XAL.ae_header_id
and XAH.je_category_name = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_ID = gjl.GL_SL_LINK_ID
AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
AND gjl.JE_HEADER_ID = gjh.JE_HEADER_ID
AND gjl.ledger_id = gjh.ledger_id
and xah.ledger_id = gjh.ledger_id
AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID
and aia.set_of_books_id = gjh.ledger_id
AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID
AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM
AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID
AND aia.VENDOR_ID = aps.VENDOR_ID
AND gjh.PERIOD_NAME BETWEEN NVL (:PERIOD_FROM, gjh.PERIOD_NAME)
AND NVL (:PERIOD_TO, gjh.PERIOD_NAME)
AND gcc.SEGMENT1 = NVL (:seg1, gcc.SEGMENT1)
AND gcc.SEGMENT3 = NVL (:seg, gcc.SEGMENT3)
ORDER BY 1, aia.GL_DATE




/* Formatted on 2013/11/28 16:17 (Formatter Plus v4.8.8) */
SELECT glcc.concatenated_segments account_number, invoice.gl_date gl_date,
je_lines.entered_dr entered_dr, je_lines.entered_cr entered_cr,
je_lines.accounted_dr accounted_dr, je_lines.accounted_cr accounted_cr
FROM gl_je_lines je_lines,
gl_import_references gl_ref,
xla_ae_lines xla_lines,
xla_ae_headers xla_headers,
xla_events xla_events,
xla_transaction_entities xla_trans,
ap_invoices_all invoice,
gl_je_headers h,
gl_code_combinations_kfv glcc
WHERE je_lines.je_header_id = gl_ref.je_header_id
AND je_lines.je_line_num = gl_ref.je_line_num
AND gl_ref.gl_sl_link_table = xla_lines.gl_sl_link_table
AND gl_ref.gl_sl_link_id = xla_lines.gl_sl_link_id
AND xla_lines.application_id = xla_headers.application_id
AND xla_lines.ae_header_id = xla_headers.ae_header_id
AND xla_headers.application_id = xla_events.application_id
AND xla_headers.event_id = xla_events.event_id
AND h.actual_flag = 'A'
AND xla_trans.application_id = xla_events.application_id
AND xla_trans.entity_id = xla_events.entity_id
--and invoice.invoice_num = xla_trans.transaction_number
AND invoice.invoice_id = xla_trans.source_id_int_1
AND invoice.invoice_id = 10020
AND h.je_source = 'Payables'
AND xla_lines.code_combination_id = glcc.code_combination_id
AND je_lines.je_header_id = h.je_header_id;



/* Formatted on 2013/11/28 16:12 (Formatter Plus v4.8.8) */
SELECT gjh.NAME, gjh.description,
TO_CHAR (gjh.default_effective_date, 'dd-MON-yyyy') eff_date,
DECODE (xte.entity_code,
'AP_INVOICES', pv.vendor_name,
(SELECT ac.vendor_name
FROM ap_checks_all ac
WHERE xte.source_id_int_1 = ac.check_id)
) party,
aia.invoice_num doc_sequence_value, gjh.je_category,
xal.accounted_dr accounted_dr, xal.accounted_cr accounted_cr,
gjl.je_header_id, xal.party_type_code, gjh.je_source, gjh.period_name,
gcc.segment5, gjl.je_line_num, gjh.default_effective_date
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla.xla_transaction_entities xte,
ap_invoices_all aia,
po_vendors pv
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND aia.invoice_id(+) = xte.source_id_int_1
AND aia.vendor_id = pv.vendor_id(+)
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:account_id, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:period_from, TRUNC (gjh.default_effective_date))
AND NVL (:period_to, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Payables'


/* Formatted on 2013/11/28 16:12 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, asup.vendor_name,
TO_CHAR (aca.check_number), aca.check_date,
aca.doc_sequence_value voucher_number, aca.creation_date voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc,
ap_suppliers asup,
ap_checks_all aca
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND asup.vendor_id(+) = xal.party_id
AND aca.doc_sequence_id(+) = xah.doc_sequence_id
AND aca.doc_sequence_value(+) = xah.doc_sequence_value
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Payables'
---------------------------------------------------------------------------------------------------------