How to make file name parameter in Sqlload in Oracle concurrent program

Date: 27-Feb-2019

Define concurrent program with parameter.
Parameter name can be any name.
Create control file with infile as '$FILE_NAME'

Dependent Value set in OAF

Date: 10-Jan-2019

JDev version: 10.13.3.0.3

Oracle EBS: 12.1.3

Objective:

In this article we are going to create 2 LOVs Supplier LOV and Supplier Site LOV and see how to setup Supplier Site dependent on Supplier (without using "Programmatic Query" option).

Note:

There are many article you will find on the internet but all the documents talks about using "Programmatic Query".

In this article we are going to see what problem we face when using "Programmatic Query" and how to avoid it.

Important:

Click below link to download the source code

Click to download file

Problem using "Programmatic Query":

After entering supplier name (1st LOV) and when you enter site name in the 2nd LOV and click LOV button, you would expect to see site LOV showing results filtered based on entered site name, but using "Programmatic Query" properly you will see the entire result showing all site for that supplier. So user need to click GO button again to apply the filter. 

Find below screen shows to know the problem which i am talking about.

In below example you can see Supplier is DELL MARKETING and Site is ATLANTA% but the lov shows all the site.

This issue can be avoided by setting False to "Programmatic Query" propriety.

In this article we are going to see the complete demo.

1. Create VO:

1.1 Create supplier VO:

VO name	supplierVO
Query	select vendor_id, vendor_name from ap_suppliers

    

1.2 Create supplier Site VO:

VO name	supplierSiteLov
Query	select vendor_site_code, vendor_id from ap_supplier_sites_all

1.3 Define AM

create AM and shuttle both supplier and supplier site VO to AM

2. Create External LOV:

2.1 Supplier LOV Region

Name	supplierLovRN
Style	listOfValues
Available View Usages	supplierLov1
Region ID	supplierLov1
Region Style	Table
Selected view Attributes:	VendorId VendorName
Go to VendorName (messageStyledText)	change ID= VendorNameLov Search Allowed = True
Go to VendorId (messageStyledText)	change ID=VendorIdLov

2.2 Attach AM to the Lov Region

Region Style	listOfValues
Scope	Public
AM Definition	suren.oracle.apps.ak.Lov.server.lovAM

2.3 SupplierSite LOV Region

Name	supplierSiteLovRN
Style	listOfValues
Available View Usages	supplierSiteLov1
Region ID	supplierSiteLov1
Region Style	Table
Selected view Attributes:	VendorSiteCode VendorId
Go to VendorSiteCode (messageStyledText)	change ID= VendorSiteCodeLov Search Allowed = True
Go to VendorId (messageStyledText)	change ID=VendorIdLov

2.4 Attach AM to the Lov Region

Region Style	listOfValues
Scope	Public
AM Definition	suren.oracle.apps.ak.Lov.server.lovAM

3. Page Setup:

Setup new page.

Name	searchPG
Package	suren.oracle.apps.ak.lov.webui

Change region ID to searchLayout 

Setup controller and AM to the region

Controller	suren.oracle.apps.ak.Lov.webui.searchCO
AM Definition	suren.oracle.apps.ak.Lov.server.lovAM

3.1 Lookup Item (Supplier)

Region	messageComponentLayout
Define messageLovInput	Supplier
External LOV	/suren/oracle/apps/ak/Lov/webui/supplierLovRN

Define new FormValue

ID	supplieridFV
Item Style	formValue

Supplier Lov Mapping (1)

ID	vendorLovMap
Lov Region Item	VendorNameLov
Return Item	supplier
Criteria Item	supplier

Supplier Lov Mapping (2)

ID	parentVendorIdlovMap
Lov Region Item	VendorIdLov
Return Item	supplieridFV

3.2 Lookup Item (Supplier Site)

Define messageLovInput	supplierSite
External LOV	/suren/oracle/apps/ak/Lov/webui/supplierSiteLovRN

Supplier Site Lov Mapping (1)

ID	vendorSiteCodelovMap
Lov Region Item	VendorSiteCodeLov
Return Item	supplierSite
Criteria Item	supplierSite
Programmatic Query	False

Make sure to select "Programmatic Query".

Supplier Site Lov Mapping (2)

ID	childVendorIdlovMap
Lov Region Item	VendorIdLov
Criteria Item	supplieridFV

Final result:

Lets do the same search as we did in the beginning of the article. As you could see when i enter site ATLANTA% and click on LOV button the search window showed only ALTANA site.

Thanks for visiting my blog.