Samle Script to Delete Qualifiers Assocaited with Price List

This is a sample Script to Delete Qualifiers Associated with Price List .

NOTE  - This is a sample script that I have designed to delete the qualifiers associated with Price list.This is I designed to fulfill requirement for my customer.This is just a sample.

DECLARE
l_list_header_id  qp_list_headers_all.list_header_id%type :=&enter_list_header_id;
l_active_flag     qp_list_headers_all.active_flag%type;
l_name            qp_list_headers_all.name%type;
l_count_lines     NUMBER:=0;
l_count_quali     NUMBER:=0;
l_step            VARCHAR2(100);
qp_line_exists    EXCEPTION;
l_error           EXCEPTION;
BEGIN
  BEGIN
  l_step := 'Get List Details';
  SELECT active_flag,name
    INTO l_active_flag,l_name
    FROM qp_list_headers_all
  where list_header_id = l_list_header_ID;
  dbms_output.put_line('List Status - '||l_active_flag);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('Error  - in Step '||l_step||'  '||sqlerrm);
      raise l_error;
    WHEN OTHERS THEN
      dbms_output.put_line('Error  - in Step '||l_step||'  '||sqlerrm);
      raise l_error;
  END ;
  --Check if lines Exists for Price List

Sample Code about how to use Array in Java

package java_testArry;

public class srarray {
    public srarray() {
        super();
    }
   
public static void main(String[] args){
     int i =90;
     int[] i_arry;
     int i_index  =5;
     i_arry = new int[i_index];
     System.out.println("Integer Value -"+i);
     for (i =0; i<=i_index-1;i++)
        {    
       i_arry[i] = i;
        //System.out.println("Array value -"+i_arry);
    }
  for (i =0; i<=i_index-1;i++) {
    System.out.println("Array value -"+i_arry[i]);
  }
}
}

Sample Java Program to Implment Java Interfaces

Write a Simple Interface in Java as shown below , and compile it.

package java_testIface;

public interface Java_Interfaces {
  //  public Java_Interfaces() {
    //    super();
   // }
   
  /*public static void main(String[] args){
    System.out.println("This is an Example of Interfaces");
  }*/
 
  public void cust();
  public void item();
  public void price();
  public void order();
   
}



Once Interface is ready , write Java code to implement these Interfaces

package javaSO;

import java_testIface.Java_Interfaces;

public class java_imp_ifaces implements Java_Interfaces {
    public java_imp_ifaces() {
        super();
    }
    //Unless we Implement Methods of Interface System will
    //Prompt a error message.
    public void cust(){
      System.out.println("Get Customer data");
    }
    public void item(){
      System.out.println("Get Item data");
    }
    public void price(){
      System.out.println("Get Price data");
    }
  public void order() {
    System.out.println("Get Order data");
  }
   
public static void main(String[] args) {
  java_imp_ifaces oom = new java_imp_ifaces();
  oom.cust();
  oom.item();
  oom.price();
  oom.order();
                        
}
}
//We always Implement the Interfaces and Extend the Class.

Sample Java Program to Extend Class

In this Code I am extending Class srt (Super-Class ) in srclass1 (sub-Class)

package java_test1;

import java_test.srt;  //I am using other Class , just line another Pl/sql API
//Extends means I am extending Methods of srt in srclass1
public class srclass1 extends srt {
    public srclass1() {
        super(); //Callto Super Call Constructor
      super.pub_method();//call to Method in Super Class(Parent Class
    }
    //Over-ride Method in Sub-Class
    public void pub_method(int a){
      System.out.println("I am Publice Method from srclass1"+a);
      System.out.println("Sub Class Method should have same Signature for Override");
    }
   
public static void main(String[] args)
{
System.out.println("I am Main in Class srClass1");
 srclass1 lclass = new srclass1();
 //lclass.pub_method();
 lclass.pub_method(200);  //It will call Method in SubClass and not in Super Class
 if (lclass instanceof srclass1) {   //Example about onstanceof
   System.out.println("Yes I am instance of srClass1");
 }
 if (lclass instanceof srt){
    System.out.println("Yes I am instance of srt");
    }
 srt lclass1 = new srt();
 if (lclass1 instanceof srt){
  System.out.println("Yes I am instance of srt");
}
 if (lclass1 instanceof srclass1) {
   System.out.println("Yes I am instance of srt");
 }

  }
}

Code for srt class is available at  Class srt

Script to Check if Workflow for Order line in Oracle Order Management is CLOSED or NOT

This is a simple script to check if Workflow for Sales order line in Oracle Order Management is CLOSED or NOT .

DECLARE
l_count     NUMBER;
l_line_id   NUMBER :=&Enter_line_id;
BEGIN
SELECT count(*)
  INTO l_count
  FROM wf_item_activity_statuses s,
       wf_process_activities p
 WHERE  s.process_activity = p.instance_id
   AND  s.item_type = 'OEOL'
   AND  s.item_key = to_char(l_line_id)
   AND  p.activity_name = 'CLOSE_LINE'
   AND  activity_result_code in ('NOT_ELIGIBLE','COMPLETE')
   AND  s.activity_status = 'COMPLETE';
  IF l_count >= 1 THEN

Query to Derive Freight Carrier Information for a Delivery

Query to Derive Freight Carrier Information for a Delivery

SELECT wlok.meaning status,
     to_char(NULL) name,
         WSH_UTIL_CORE.get_location_description(wnd.initial_pickup_location_id,'CSZ') pickup_loc,
     wnd.ship_method_code,
         hp.party_name carrier_name,
     to_char(NULL) bill_of_lading
  FROM     wsh_lookups wlok,
     wsh_new_deliveries wnd,
         hz_parties hp, hz_party_usg_assignments hpu
  WHERE     wnd.name = :v_delivery_name
  AND    hp.party_id(+) = wnd.carrier_id
  AND    hp.party_id = hpu.party_id(+)
  AND    hpu.party_usage_code(+) = 'TRANSPORTATION_PROVIDER'
  AND     wlok.lookup_type = 'DELIVERY_STATUS'
  AND     wlok.lookup_code = wnd.status_code
  AND    nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
  AND     wnd.status_code not in ('CL','IT');


Note - Please Note that Freight Carriers are Defined as Party in TCA.With Party Type = Organization.

You are not allowed to cancel Order Line because: This is an ATO item.

You are not allowed to cancel Order Line because: This is an ATO item.
This is very common error and usually occurred when you try to cancel an ATO line. Please note that this is error enforce by processing constraint to maintain the integrity of an ATO Order line.
As per the Standard behaviour

1. If line is ATO line (ato_line_id is NOT Null).
2. Item_Type_Code  = Standard and
3. Workflow for the activity SHIP_LINE has a Status Complete then system will not let customer to cancel a line.

It is because the activity 3 (workflow activity SHIP_LINE activity in status = COMPLETE) indicates very clearly that Order line is shipped.( and it is no way a good practice to cancel an Shipped line).

Above was the concept, but recently we notice that system is not allowing to cancel an order line for ato item even when the delivery details was not shipped at all.

That was very strange, because per the processing constraint if any of the cobndition fails that the complete processing constrant not work. I then have close look at the Order line and workflow and found that line has ato_line_id NOT NULL , item_type_code = Standard and Wow the workflow for the SHIP_LINE activity was also in status COMPLETE , But on the Other hand release_status has a value <> C ( Shipped), and that was very strange.On further Investigation I noticed that workflow for the order line was stuck at the SHIP_LINE activity with the status = COMPLETE and not proceed any further ( that means line was not Shipped at all).

Cannot create reservation for a closed sales order.

Cannot create reservation for a closed sales order.
This Post is about the Error message "Can not create reservation for a closed sales order”, from the error message it is very clear that while Pick release process try to process the Delivery details associated with a line that is either closed or cancelled thus error out the pick release process.
Below is Checklist
Check the Order line for

• Open Flag
• Flow Status Code
• Shipped and Invoice Qty

For a line to be process by Pick Release process

• Open Flag          =Y
• Flow Status Code    = (it should not be CLOSED)
• Shipped and Invoice Qty    = should be NULL

But if

• Open Flag          = Y
• Flow Status Code    = CLOSED/CANCELLED
• Shipped and Invoice Qty    > 0 (For a non Cancelled line) and = 0 for a Cancelled Line and workflow for Order line is completed

then check the following columns in Delivery Detail table

Activities are in Deferred Status but still not processed by workflow background Process

Activities are in Deferred Status but still not processed by workflow background Process

Many a times we notice that thorugh our line is in Deferred Status, but it still not processed by the Workflow background process (WBP), on the other had if we execute RETRY activity, it progress further from Deferred status.

In case cases there are fare chances that issue is with the Deferred Workflow Queue.Below are the steps about how to identifies if the issue is with Deferred Queue or the workflow.

As usual my examples are based on Oracle Order Management.

NOTE - When ever workflow background process Pick the line for process, irrespective of the final outcome of the processing, begin_date in the wf_item_activity_statuses will changes. So one quick check is, after the workflow background process is completed , check the begin_date of the activity that was in deferred status before the WBP and remain same status after the WBP, if the begin_date changed that means workflow background process do picked the line for processing , but it failed and stay in same status for some other valid reason.But if begin_date is same as old , that for sure means this line is not processed by workflow background process.

Simple Java Class with Public and Private Methods

package java_test;

public class srt {
    public srt() {
   System.out.println("empty Constructor without Parametyeres in Class srt");    
     //   super();
     }
    public srt( int a){
    System.out.println("Constructor with Parameter in Class srt- "+a);
    }
    //public method
    public void pub_method(){
      System.out.println("I am Public Method in Class Srt");
    }
   
    //Private Method
    private void pvt_method() {
      System.out.println("I am private method srt");
    }


public static void main(String[] args) {
int a;
  System.out.println("Main"); 
  srt myclass = new srt();  //Call to Default Constructor
  srt myclass_var = new srt(1972);  //call to Constructor with Parameter
  myclass.pub_method(); //call to public method
  myclass.pvt_method(); //call to Private method
}
}