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.

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