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.

MOAC Setup - Concurrent Program

CREATE SECURITY PROFILE

Go to Human Resource Responsiblity --> Security --> Define Security Profile

Name : CDMAP Security Profile
Business Group : Vision Corporation

Submit concurrent program "Security List Maintenance"

ATTACH SECURITY PROFILE TO RESPONSIBLITY

Go To System Administrator Responsibility --> Profile --> System

check Responsibility "Payables, Vision Operations (USA)"

Profile :

Select the "MO: Security Profile" row the values is CDMAP Security Profile

Check the concurrent program SRS Window , Concurrent program "CDMAP_TEST" Its Default Operating Unit is Null

This program calls the package CDMAP_TEST_PKG.MAIN, It using the Oracle View AP_INVOICES view, it will returns the data once the OU is initialized

create or replace package CDMAP_TEST_PKG as

procedure MAIN(piv_errorbuf OUT VARCHAR2, pin_errorcode OUT NUMBER);

END;


create or replace package BODY CDMAP_TEST_PKG as

procedure MAIN(piv_errorbuf OUT VARCHAR2, pin_errorcode OUT NUMBER) IS

ln_count number := 0;

BEGIN

--ln_orgid :=fnd_global.org_id;

select count(*) into ln_count from AP_INVOICES;

fnd_file.put_line(1, 'count-' || ln_count);

END;

END;

Enable MOAC for the Concurrent Program

sdsdsd

Go to System Administration Responsibility  --> System Administration --> Concurrent --> Programs

Click to Update Icon --> Go to Request Tab --> Under Request Settings --> Operating Unit Mode--> Change to "Single"

Apply --> Click Ok in confirmation page

Retry the program in SRS Window

Go to the responsibility "Payables Vision Operations (USA)"

Go to concurrent program SRS Window , Concurrent program "CDMAP_TEST" Its Operating Unit is Populated from the Security profile "CDMAP Security Profile"

Based on Selected Operating Unit will get the data from AP_INVOICES views

Web ADI Configuration Error - BNE_UIX_PHYSICAL_DIRECTORY is not configured.

Issue : Web ADI Configuration Error - BNE_UIX_PHYSICAL_DIRECTORY is not configured.

Below screenshot from EBS

Solutions : Delete the profile option "BNE UIX Physical Directory" and bounce the Apache server, It is absolute in R12

O2C FLOW IN ORACLE APPS R12 BACKEND PROCESS

DATA FLOW Diagram - Order to Cash

 YouTube Videos


https://www.youtube.com/channel/UCWFJCCCLUY0sgLrBsJQtjiw?view_as=subscriber

ORDER 2 CASH

CREATE ITEM IN MASTER ORG-V1

select * from  MTL_SYSTEM_ITEMS_B where  SEGMENT1='CDMO2C'

-- INVENTORY_ITEM_ID=244205

select * from  MTL_ONHAND_QUANTITIES_DETAIL where  INVENTORY_ITEM_ID=244205

select * from  MTL_MATERIAL_TRANSACTIONS where  INVENTORY_ITEM_ID=244205

CREATE THE ORDER

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

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS ENTERED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS ENTERED

--BOOKED THE ORDER

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS BOOKED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS AWAITING_SHIPPING

select A.RELEASED_STATUS,A.* from  WSH_DELIVERY_DETAILS A   where  SOURCE_HEADER_ID=362781

-- R --> Ready to Release

select * from  WSH_DELIVERY_ASSIGNMENTS where  DELIVERY_DETAIL_ID=6219051 -- UNTIL RELEASE THE ITEM, DELIVERY ID IS NOT POPULATED

-- RELEASE THE ORDER

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

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS ENTERED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS ENTERED

select A.RELEASED_STATUS,A.* from  WSH_DELIVERY_DETAILS A   where  SOURCE_HEADER_ID=362781

-- Y --> Pick Confirmed

-- S --> Pick released

Staged/Pick Confirmed

select * from  WSH_DELIVERY_ASSIGNMENTS where  DELIVERY_DETAIL_ID=6219051 -- DELIVERY ID IS  POPULATED

select * from  wsh_new_deliveries where  delivery_id=6242831

--status OP OPEN

--Ship Confirm/Close Trip Stop

--MOVE ORDER

6391343

select * from  mtl_reservations where  INVENTORY_ITEM_ID=244205

select * from  mtl_txn_request_headers where  request_number='6391343'

select * from  mtl_txn_request_lines where  header_id=6391344

select * from  MTL_MATERIAL_TRANSACTIONS where  INVENTORY_ITEM_ID=244205

---

SHIP CONFIRMATION

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

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS ENTERED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS SHIPPED

select A.RELEASED_STATUS,A.* from  WSH_DELIVERY_DETAILS A   where  SOURCE_HEADER_ID=362781

-- C --> Shihp Confirmed

select * from  WSH_DELIVERY_ASSIGNMENTS where  DELIVERY_DETAIL_ID=6219051 -- DELIVERY ID IS  POPULATED

select * from  wsh_new_deliveries where  delivery_id=6242831

--status CL CLOSED

select * from  mtl_reservations where  INVENTORY_ITEM_ID=244205-- DELTED ONCE SHIP CONFIRMED

select * from  mtl_txn_request_headers where  request_number='6391343'

select * from  mtl_txn_request_lines where  header_id=6391344

select * from  MTL_MATERIAL_TRANSACTIONS where  INVENTORY_ITEM_ID=244205

-- DECREASED THE ONHAND QUANTITIES

select * from  MTL_ONHAND_QUANTITIES where  INVENTORY_ITEM_ID=244205

---- CREATE AR INVOICE

 RUN WORKFLOW BACKGROUND PROCESS FROM OM RESP

 -- INTERFACE THE INV RECORDS

 -- AUTOMATICALLY CALL AUTOINVOICE PROGRAM

-- AR INVOICE IS IMPORTED IN EBS

select * from  RA_INTERFACE_LINES_ALL where  INTERFACE_LINE_ATTRIBUTE1='69341'

select * from  RA_INTERFACE_SALESCREDITS_ALL where  INTERFACE_LINE_ID=1952381

select * from  RA_INTERFACE_DISTRIBUTIONS_ALL where  INTERFACE_LINE_ID=1952381

select * from  RA_INTERFACE_ERRORS_ALL

select * from  RA_CUSTOMER_TRX_ALL where  CUSTOMER_TRX_ID=1205195

--TRX NUMBER 10047340

select * from  RA_CUSTOMER_TRX_LINES_ALL where  INTERFACE_LINE_ATTRIBUTE1='69341'

select * from  RA_CUST_TRX_LINE_GL_DIST_ALL  where  CUSTOMER_TRX_LINE_ID=1952381

select * from  RA_CUST_TRX_LINE_SALESREPS_ALL where  SALESREP_ID=1006 AND CUSTOMER_TRX_ID=1205195

select * from  JTF_RS_SALESREPS where  SALESREP_ID=1006

select * from  AR_PAYMENT_SCHEDULES_ALL where  CUSTOMER_TRX_ID=1205195

-- AMOUNT_DUE_REMAINIG 795.01

SLA TABLES

----------

select * from  XLA_EVENTS where  EVENT_ID=6198573

select * from  XLA.XLA_TRANSACTION_ENTITIES where  TRANSACTION_NUMBER='10047340'

-- CREATE ACOUNTNG PROGRAM

select * from  XLA_AE_HEADERS where  EVENT_ID=6198573

select * from  XLA_AE_LINES where  AE_HEADER_ID=8110572

select * from  GL_IMPORT_REFERENCES where  (GL_SL_LINK_ID,GL_SL_LINK_TABLE) IN (

select GL_SL_LINK_ID,GL_SL_LINK_TABLE from  XLA_AE_LINES where  AE_HEADER_ID=8110572)

GL TABLES

select * from  GL_INTERFACE where  REFERENCE26=6198573

select * from  GL_JE_BATCHES where  je_batch_id=5351742

-- Receivables A 3825428 7481722

select * from  GL_JE_HEADERS where  je_header_id=7105885

select * from  GL_JE_LINES where  je_header_id=7105885

----

CREATE RECEIPTS

select * from  AR_CASH_RECEIPTS_ALL where  RECEIPT_NUMBER='10047340_REC'

select * from  AR_PAYMENT_SCHEDULES_ALL where  CUSTOMER_TRX_ID=1205195

-- AMOUNT_DUE_REMAINIG 795.01

select * from  AR_RECEIVABLE_APPLICATIONS_ALL where  CASH_RECEIPT_ID=211297

select * from  AR_CASH_RECEIPT_HISTORY_ALL where  CASH_RECEIPT_ID=211297

---

select * from  XLA_EVENTS where  EVENT_ID=6198574

select * from  XLA.XLA_TRANSACTION_ENTITIES where  TRANSACTION_NUMBER='10047340_REC'

-- CREATE ACOUNTNG PROGRAM

select * from  XLA_AE_HEADERS where  EVENT_ID=6198574

select * from  XLA_AE_LINES where  AE_HEADER_ID=8110573

select * from  GL_IMPORT_REFERENCES where  (GL_SL_LINK_ID,GL_SL_LINK_TABLE) IN (

select GL_SL_LINK_ID,GL_SL_LINK_TABLE from  XLA_AE_LINES where  AE_HEADER_ID=8110573)

GL TABLES

select * from  GL_INTERFACE where  REFERENCE26=6198574

select * from  GL_JE_BATCHES where  je_batch_id=5351744

-- Receivables A 3825429 7481727

select * from  GL_JE_HEADERS where  je_header_id=7105887

select * from  GL_JE_LINES where  je_header_id=7105887

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

SETUP DETAILS

select * from  RA_CUSTOMER_TRX_ALL where  CUSTOMER_TRX_ID=1205195

--TRX NUMBER 10047340

select * from  RA_CUSTOMER_TRX_LINES_ALL where  INTERFACE_LINE_ATTRIBUTE1='69341'

select * from  RA_BATCH_SOURCES_ALL where  BATCH_SOURCE_ID=1001

select * from  RA_CUST_TRX_TYPES_ALL where  CUST_TRX_TYPE_ID=1361

select * from  RA_TERMS where  TERM_ID=4

select * from  RA_GROUPING_RULES