Query to get GL batch details for AR receipts and AR invoice

SELECT gjb.name GL_batch_name,
gjh.name Journal_Name,
gjl.period_name,
gjh.JE_CATEGORY,
gjh.JE_SOURCE,
gjh.currency_code,
DECODE (gjh.actual_flag, 'A', 'Actual','B', 'Budget','E', 'Encumbrance') Balance_Type,
DECODE (gjl.status, 'P', 'Posted', 'U', 'Unposted', gjl.status) Batch_Status,
gjh.posted_date,
acra.receipt_number,
acra.doc_sequence_value
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb, 
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_transaction_entities xte,
ar_cash_receipts_all acra
WHERE gjb.je_batch_id = gjh.je_batch_id
and gjl.je_header_id = gjh.je_header_id
and gjl.je_header_id = gir.je_header_id
and gjl.je_line_num = gir.je_line_num
and gir.gl_sl_link_table = xal.gl_sl_link_table
and gir.gl_sl_link_id = xal.gl_sl_link_id
and xal.application_id = xah.application_id
and xal.ae_header_id = xah.ae_header_id
--and xal.ae_line_num = 1
and xah.application_id = xe.application_id
and xah.event_id = xe.event_id
and xe.application_id = xte.application_id
and xe.entity_id = xte.entity_id
and xte.application_id = 222
and xte.entity_code = 'RECEIPTS'
and xte.source_id_int_1 = acra.cash_receipt_id
and acra.cash_receipt_id in(127282,838383);
SELECT gjb.name GL_batch_name,
gjh.name Journal_Name,
gjl.period_name,
gjh.JE_CATEGORY,
gjh.JE_SOURCE,
gjh.currency_code,
DECODE (gjh.ACTUAL_FLAG, 'A', 'Actual','B', 'Budget','E', 'Encumbrance') Balance_Type,
DECODE (gjl.status, 'P', 'Posted', 'U', 'Unposted', gjl.status) Batch_Status,
gjh.posted_date,
rcta.trx_number,
rcta.trx_date
FROM apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.gl_je_batches gjb, 
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_transaction_entities xte,
apps.ra_customer_trx_all rcta
WHERE gjb.je_batch_id = gjh.je_batch_id
and gjl.je_header_id = gjh.je_header_id
and gjl.je_header_id = gir.je_header_id
and gjl.je_line_num = gir.je_line_num
and gir.gl_sl_link_table = xal.gl_sl_link_table
and gir.gl_sl_link_id = xal.gl_sl_link_id
and xal.application_id = xah.application_id
and xal.ae_header_id = xah.ae_header_id
and xal.ae_line_num = 1 /*If AR Transaction has multiple lines then xla_ae_lines table contains multiple lines*/
and xah.application_id = xe.application_id
and xah.event_id = xe.event_id
and xe.application_id = xte.application_id
and xe.entity_id = xte.entity_id
and xte.application_id = 222
and xte.entity_code = 'TRANSACTIONS'
and xte.source_id_int_1 = rcta.customer_trx_id
and trim(rcta.trx_number) = trim('&AR_trx_number');

Validate the given field is Number or Varchar only

Select sysdate from dual where DECODE(REGEXP_INSTR (field, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER')='NOT_NUMBER'

this query will help to validate the given field is number or not

Request Set - Running details

SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
 fcr.request_id "REQUEST",
 fcr.parent_request_id "PARENT",
 fcr.oracle_process_id "Process ID",
 fcptl.user_concurrent_program_name "Program Name",
 fcr.argument_text,
 decode(fcr.phase_code,
        'X',
        'Terminated',
        'E',
        'Error',
        'C',
        'Completed',
        'P',
        'Pending',
        'R',
        'Running',
        phase_code) "Phase",
 decode(fcr.status_code,
        'X',
        'Terminated',
        'C',
        'Normal',
        'D',
        'Cancelled',
        'E',
        'Error',
        'G',
        'Warning',
        'Q',
        'Scheduled',
        'R',
        'Normal',
        'W',
        'Paused',
        'Not Sure') "Status",
 fcr.request_date,
 fcr.actual_start_date,
 fcr.actual_completion_date,
 (fcr.actual_completion_date - fcr.actual_start_date) * 1440 "Elapsed"
  FROM (SELECT /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
         fcr1.request_id
          FROM fnd_concurrent_requests fcr1
         WHERE 1 = 1
         START WITH fcr1.request_id = <request_id>
        --CONNECT BY PRIOR fcr1.parent_request_id = fcr1.request_id) x,
        CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,
       fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcptl
 WHERE fcr.request_id = x.request_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcp.application_id = fcptl.application_id
   AND fcp.concurrent_program_id = fcptl.concurrent_program_id
   AND fcptl.language = 'US'
 ORDER BY 1;

Customer ShipTo BillTo Query - R12

SELECT hp.party_name
, hp.party_number
, hca.account_number
, hca.cust_account_id
, hp.party_id
, hps.party_site_id
, hps.location_id
, hl.address1
, hl.address2
, hl.address3
, hl.city
, hl.state
, hl.country
, hl.postal_code
, hcsu.site_use_code
, hcsu.site_use_id
, hcsa.bill_to_flag
FROM hz_parties hp
, hz_party_sites hps
, hz_locations hl
, hz_cust_accounts_all hca
, hz_cust_acct_sites_all hcsa
, hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hca.account_number = ;

Register / Attach WEBADI Function to Responsibility in R12

Here the article will describe about how to register WebADI to Responsibility menu

Go to System Administrator --> Security --> Responsibility --> Define

Query the responsibility which want to register the WebADI, Get the menu name

Example

Responsibility Name : XXCDM Test Responsibility
Menu :  AP_NAVIGATE_GUI12

CREATE FUNCTION

Go to System Administrator --> Application  --> Function

Form Function Window

Enter value for Function and  User Function Name under Description

Example 

Function : XXCDM_WEBADI_SIMPLE
User Function Name : XXCDM Simple WebADI Integrator


Enter value for Type as "SSWA servlet function" under Properties

Under the Form tab

Paste the Parameter value as

bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2007&bne:reporting=N&bne:integrator=SQLAP:CDMXX_WEBADI_INTGR_XINTG&bne:layout=SQLAP:CDMXX_WEBADI_LO&bne:content=SQLAP:CDMXX_WEBADI_INTGR_CNT1&bne:noreview=Y

 Change the integrator_code, layout_code and content_code

SQLAP --> Application short name of Integrator, We can use application id instead of short name.

Execute the below query to get all details for your parameter.

SELECT biv.application_id

       ,biv.integrator_code

       ,biv.user_name

       ,bib.interface_code,lo.LAYOUT_CODE,

       (select user_name from BNE_LAYOUTS_TL where LAYOUT_CODE=lo.LAYOUT_CODE) layoutname,

       (select user_name from BNE_CONTENTS_TL where content_code=cont.content_code) contentname,

       cont.content_code,

       cont.param_list_code,

       cont.content_class,(SELECT QUERY FROM BNE_STORED_SQL WHERE CONTENT_CODE=CONT.CONTENT_CODE)QUERY

   FROM bne_integrators_vl biv

       ,bne_interfaces_b   bib,

       BNE_LAYOUTS_B lo,

       BNE_CONTENTS_b cont

  WHERE upper(user_name) like '%your intergrator name%'

    AND bib.integrator_code = biv.integrator_code

    and lo.integrator_code = biv.integrator_code

    and cont.integrator_code = biv.integrator_code

Enter value for HTML Call as "BneApplicationService" under Web HTML

Save it

ATTACH FUNCTION TO MENU

Go to System Administrator --> Application  --> Menu

Query your menu,

Add one more row

Select the function name "XXCDM Simple WebADI Integrator" which we created now

put the same name for Prompt as well

Example : AP_NAVIGATE_GUI12

ATTACH FUNCTION TO WEBADI

Go to Desktop Integration Manager --> Query the Integrator Name --> select the update option

add the function name "XXCDM Simple WebADI Integrator"

Verify the Integrator

WEBADI EXCEL SETUP AND BROWSER SETUP

Find the list of Setups in Internet Explorer to work WebADI

Go to Internet Explorer --> Internet Options --> Security --> Click "Trusted sites"
Add your site informations

example : http://ebs.example.com

Close the window.

Change "Security level for this zone" to Low

FILE DOWNLOAD OPTIONS:

Go to Security --> Trusted sites --> Custom level  --> File Download is Enable

SCRIPT OPTIONS:

Go to Security --> Trusted sites --> Custom level  --> Under Scripting check "Allow status bar updates via script" is Enable

ACTIVEX CONTROL AND PLUG IN OPTIONS:

Go to Security --> Trusted sites --> Custom level  --> Under ActiveX controls and plug-ins check "Initialize and script Active X controls not marked as safe" is Prompt

EXCEL 2016 SETUPS

Open excel 2016 Go to File --> Option -->  Trust Center --> Trust Center Settings

Click Ok

Follow the screenshot below in "Trust Center" Window

Follow the screenshot below in "Trust Center" Window

Multiple Dependent Value Set in Oracle Apps

$FLEX$ is a special bind variable that can be used to base a parameter value on the other parameters (dependent parameters)

Syntax 1 —     :$FLEX$.Value_ Set_Name

Value_Set_Name is the name of value set for a prior parameter in the same parameter window that you want your parameter to depend on.

Syntax 2 —     :$FLEX$.Paramter_Name

Paramter_Name is the name of  prior parameter in the same parameter window that you want your parameter to depend on.

created the ValueSet : XXCDM_ORGS

ValueSet returns 7 Organization IDs

Query :
select ORGANIZATION_NAME,ORGANIZATION_CODE,ORGANIZATION_ID from org_organization_definitions where organization_id in (7882,3535,
3536,
6646,
7884,
7881,
7880)

ORGANIZATION_NAME	ORGANIZATION_CODE	ORGANIZATION_ID
Vision Taiwan	VTW	3535
Vision Thailand	VTH	3536
Progress BE	BE1	6646
Central Warehouse - Field Service	CWH	7880
Regional Warehouse - Field Service	RWH	7881
Field Service Technicians - Defective Parts	FTD	7882
E4W Netherlands WMS	E4W	7884

Make this Valueset is dependent to Input Parameters

Parameters P_ORG1, P_ORG2, P_ORG3 are using the valueset XXCDM_ORGS

Parameters P_ITEM is using the valueset  XXCDM_ITEM_VS

Table type Valueset : XXCDM_ITEM_VS

Definitions:

CREATE OR REPLACE FORCE VIEW XXCDM_ITEMS_V

(

   SEGMENT1,

   DESCRIPTION,

   INVENTORY_ITEM_ID,

   ORGANIZATION_ID

)

AS

   SELECT DISTINCT msi.SEGMENT1,

                   msi.DESCRIPTION,

                   msi.INVENTORY_ITEM_ID,

                   msi.organization_id

     FROM APPS.MTL_SYSTEM_ITEMS_B msi, APPS.MTL_PARAMETERS mp

Table Name :  (select distinct segment1,DESCRIPTION,INVENTORY_ITEM_ID from XXCDM_ITEMS_V) MSI

Value : segment1
Meaning : DESCRIPTION

where clause : 

MSI.SEGMENT1 IN

          (SELECT MSI1.SEGMENT1

             FROM XXCDM_ITEMS_V MSI1

            WHERE ((ORGANIZATION_ID = (CASE WHEN :$FLEX$.XXCDM_ORGS:NULL IS NULL AND :$FLEX$.XXCDM_ORGS:NULL  IS NULL AND :$FLEX$.XXCDM_ORGS:NULL  IS NULL THEN ORGANIZATION_ID

                            ELSE -1

              END)  ) OR ORGANIZATION_ID  IN(:$FLEX$.XXCDM_ORGS:NULL,:$FLEX$.XXCDM_ORGS:NULL,:$FLEX$.XXCDM_ORGS:NULL)

        )

        )

EXAMPLE VALUESET DEPENDENT

Objective of this paramter is, Item should be extract based on three paramters ORG1,ORG2,ORG3 so the valueset is dependent to ITEM paramters



We are selected three Orgs in three parameters

But ITEM parameter always should refer the immediate preceding dependent valueset values, it will not refer ORG1, ORG2 paramters values, so that here got item count is 5 for the ORG3 : VTH

EXAMPLE PARAMETER DEPENDENT

Objective of this paramter is, Item should be extract based on three paramters ORG1,ORG2,ORG3 so the valueset is dependent to ITEM paramters


Change the valueset XXCDM_ITEM_VS, where clause to 

MSI.SEGMENT1 IN

          (SELECT MSI1.SEGMENT1

             FROM XXCDM_ITEMS_V MSI1

            WHERE ((ORGANIZATION_ID = (CASE WHEN :$FLEX$.P_ORG1:NULL IS NULL AND :$FLEX$.P_ORG2:NULL  IS NULL AND :$FLEX$.P_ORG3:NULL  IS NULL THEN ORGANIZATION_ID

                            ELSE -1

              END)  ) OR ORGANIZATION_ID  IN(:$FLEX$.P_ORG1:NULL,:$FLEX$.P_ORG2:NULL,:$FLEX$.P_ORG3:NULL)

        )

        )

here   P_ORG1, P_ORG2, P_ORG3 are parameter names

We are selected three Orgs in three parameters

we got 8 item details for selected three ORGS.