Oracle Apps core LCM tables and query

LCM Shipment Headers (INL_SHIP_HEADERS_ALL)

LCM Shipment Line Groups (INL_SHIP_LINE_GROUPS)

LCM Shipment Lines (INL_SHIP_LINES_ALL)

LCM Charge Lines (INL_CHARGE_LINES)

LCM Tax Lines (INL_TAX_LINES)

Landed Cost Allocations (INL_ALLOCATIONS)

Query to Link LCM, PO and Inventory Table

Oracle Apps PO Line Details Query with Org Restriction

Before Executing the query Execute apps Initialize in your SQL client.

SELECT pha.po_header_id,
pla.po_line_id,
pha.segment1
po_number,
pha.creation_date
po_date,
pha.currency_code
currency,
NVL (pla.CLOSED_CODE, pha.authorization_status)
po_status,
asup.VENDOR_NAME
buyer_name,
pla.line_num
po_line_number,
msib.segment1
item_code,
msib.description
item_description,
msib.primary_unit_of_measure,
pla.unit_price,
pla.quantity
ordered_qty,
(SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
quantity_received,
(SELECT SUM (NVL (quantity_billed, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
quantity_billed,
pla.quantity
– (SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
pending_qty,
( pla.quantity
– (SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id))
* pla.unit_price
pending_value,
pla.quantity
– ( (SELECT SUM (NVL (quantity_received, 0))
FROM po_line_locations_all plla
WHERE plla.po_line_id = pla.po_line_id)
+ (SELECT SUM (NVL (ALLOCATION_QTY, 0))
FROM xxatc_shipment_dtl_v xsdv
WHERE xsdv.po_header_id = pha.PO_HEADER_ID
AND xsdv.po_line_id = pla.PO_LINE_ID))
available_qty,
(SELECT SUM (NVL (ALLOCATION_QTY, 0))
FROM xxatc_shipment_dtl_v xsdv
WHERE xsdv.po_header_id = pha.PO_HEADER_ID
AND xsdv.po_line_id = pla.PO_LINE_ID)
avl_qty,
mc.segment1
family_,
mc.segment2
sub_family,
pha.vendor_id
FROM po_headers_all pha,
ap_suppliers asup,
po_lines_all pla,
mtl_system_items_b msib,
mtl_item_categories mic,
mtl_categories_b_kfv mc
WHERE 1 = 1
AND pha.po_header_id = pla.po_header_id
AND pha.vendor_id = asup.vendor_id
AND pla.item_id = msib.inventory_item_id
AND msib.organization_id = pla.org_id
AND mic.CATEGORY_SET_ID = 1
AND msib.organization_id = mic.organization_id
AND msib.inventory_item_id = mic.inventory_item_id
AND mic.category_id = mc.category_id
AND pha.AUTHORIZATION_STATUS = ‘APPROVED’
AND NVL (pla.CLOSED_CODE, ‘OPEN’) = ‘OPEN’
AND fnd_profile.VALUE (‘ORG_ID’) = 142 –For Org Restriction
ORDER BY PO_NUMBER DESC, PO_LINE_NUMBER ASC

Search Code in OAF

I was facing some Issues when I need was having a Query region.

1. Date From date and To date was not having in Oracle
2. To have a dependent restriction then query has to be executed

Due to the above reasons I have created a search logic and below is the code.

When search Event is pressed from the button the pageContext.getParameter(“ShipmentNumber”) will have the value entered. Based on the value I will generate query to Execute VO.

 

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
System.out.println(“Getting Event Param ———————–>”+pageContext.getParameter(EVENT_PARAM));
OAApplicationModule am=null;
am=(OAApplicationModule)pageContext.getApplicationModule(webBean);
OAViewObject hvo= null;
hvo=(OAViewObject)am.findViewObject(“shipmtHdrVO1”);

if (pageContext.getParameter(EVENT_PARAM).equals(“SEARCH”))
{
pageContext.writeDiagnostics(this, “Inside Search Logic ———————–>”,4);
System.out.println(“Inside Search Logic ———————–>”);
Enumeration x=null;
x=pageContext.getParameterNames();
if(x!=null)

{

System.out.println(“Elements in Enum”+x);
while(x.hasMoreElements())
{
String aParamName = (String)x.nextElement();
System.out.println(“Obatining the Child Names : “+aParamName);
}
System.out.println(“VO Gets Queried”);

}

System.out.println(“pageContext.getParameter(\”ShipmentNumber\”)”+pageContext.getParameter(“ShipmentNumber”));
System.out.println(“pageContext.getParameter(\”SupplierName\”)”+pageContext.getParameter(“SupplierName”));
System.out.println(“pageContext.getParameter(\”SupplierInvoice\”)”+pageContext.getParameter(“SupplierInvoice”));
System.out.println(“pageContext.getParameter(\”AwbNumber\”)”+pageContext.getParameter(“AwbNumber”));
System.out.println(“pageContext.getParameter(\”CreationDate\”)”+pageContext.getParameter(“CreationDate”));
System.out.println(“pageContext.getParameter(\”Status\”)”+pageContext.getParameter(“Status”));

List whClauseParams = new ArrayList();
if (pageContext.getParameter(“ShipmentNumber”) != null && pageContext.getParameter(“ShipmentNumber”) != “”) {
try
{
// vo.setWhereClauseParam(0, pageContext.getParameter(“DocumentName”));
whClauseParams.add(” and QRSLT.SHIPMENT_NUMBER like ‘%”+pageContext.getParameter(“ShipmentNumber”)+ “%'”);
}
catch(Exception e){
System.out.println(e);
}
}

if (pageContext.getParameter(“SupplierName”) != null && pageContext.getParameter(“SupplierName”)!=””) {
try
{
// vo.setWhereClauseParam(0, pageContext.getParameter(“DocumentName”));
whClauseParams.add(” and QRSLT.SUPPLIER_NAME like ‘%”+pageContext.getParameter(“SupplierName”)+ “%'”);
}
catch(Exception e){
System.out.println(e);
}
}
if (pageContext.getParameter(“SupplierInvoice”)!= null && pageContext.getParameter(“SupplierInvoice”)!=””) {
try
{
// vo.setWhereClauseParam(0, pageContext.getParameter(“DocumentName”));
whClauseParams.add(” and QRSLT.SUPPLIER_INVOICE like ‘%”+pageContext.getParameter(“SupplierInvoice”)+ “%'”);
}
catch(Exception e){
System.out.println(e);
}
}
if (pageContext.getParameter(“AwbNumber”) != null &&pageContext.getParameter(“AwbNumber”)!=””) {
try
{
// vo.setWhereClauseParam(0, pageContext.getParameter(“DocumentName”));
whClauseParams.add(” and QRSLT.AWB_NUMBER like ‘%”+pageContext.getParameter(“AwbNumber”)+ “%'”);
}
catch(Exception e){
System.out.println(e);
}
}

if (pageContext.getParameter(“CreationDateFrom”) != null && pageContext.getParameter(“CreationDateFrom”)!=””) {
try
{
// vo.setWhereClauseParam(0, pageContext.getParameter(“EffectiveStartDate”));
whClauseParams.add(” and trunc(QRSLT.CREATION_DATE) >= to_date(‘”+pageContext.getParameter(“CreationDateFrom”)+ “‘, ‘DD-Mon-YYYY’)”);
}
catch(Exception e){
System.out.println(e);
}
}
if (pageContext.getParameter(“CreationDateTo”) != null && pageContext.getParameter(“CreationDateTo”)!=”” ) {
try
{
// vo.setWhereClauseParam(0, pageContext.getParameter(“EffectiveStartDate”));
whClauseParams.add(” and trunc(QRSLT.CREATION_DATE) <= to_date(‘”+pageContext.getParameter(“CreationDateTo”)+ “‘, ‘DD-Mon-YYYY’)”);
}
catch(Exception e){
System.out.println(e);
}
}
if (pageContext.getParameter(“Status”) != null &&pageContext.getParameter(“Status”)!=””) {
try
{
// vo.setWhereClauseParam(0, pageContext.getParameter(“DocumentName”));
whClauseParams.add(” and QRSLT.STATUS like ‘%”+pageContext.getParameter(“Status”)+ “%'”);
}
catch(Exception e){
System.out.println(e);
}
}

System.out.println(“To check VO is not null”+hvo);
if(hvo!=null)
{

String sqlparam=” “;
if(whClauseParams.isEmpty())
{
System.out.println(“No Params for Search”);
hvo.reset();
hvo.setWhereClauseParams(null); // Always reset
hvo.setWhereClause(“1=1”);
hvo.executeQuery();
expVO.reset();
expVO.setWhereClauseParams(null); // Always reset
expVO.setWhereClause(“1=1”);
expVO.executeQuery();

pageContext.writeDiagnostics(this, “Inside Params Empty SAVE – Reset 2 and getting Query ———————–>”+hvo.getQuery(),4);
}
else
{
for (int i = 0; i < whClauseParams.size(); i++)
{
String k=whClauseParams.get(i)+””;
System.out.println(whClauseParams.get(i));
sqlparam=sqlparam.concat(k);
System.out.println(“The param is “+sqlparam);
}

if((sqlparam!=null)&&(sqlparam!=””))
{
sqlparam=sqlparam.substring(5);
System.out.println(“Modified param”+sqlparam);
hvo.reset();
hvo.setWhereClause(sqlparam);
hvo.executeQuery();
pageContext.writeDiagnostics(this, “Inside Params with Values “+sqlparam+”SAVE getting Query ———————–>”+hvo.getQuery(),4);
expVO.reset();
expVO.setWhereClause(sqlparam);
expVO.executeQuery();
pageContext.writeDiagnostics(this, “Inside Params with Values “+sqlparam+”SAVE getting Query ———————–>”+expVO.getQuery(),4);

}

}
}
}
}