How to define oracle Day360 Formula function equivalent to Excel Day360 Function

create or replace function days_360(
       p_start_date           date,
       p_end_date             date,
       p_rule_type            char default 'F'
       )
    RETURN number
IS
  v_mm1    pls_integer;
  v_dd1    pls_integer;
  v_yyyy1  pls_integer;
  v_mm2    pls_integer;
  v_dd2    pls_integer;
  v_yyyy2  pls_integer;
BEGIN
  v_yyyy1 := to_number(to_char(p_start_date,'yyyy'));
  v_mm1   := to_number(to_char(p_start_date,'mm'));
  v_dd1   := to_number(to_char(p_start_date,'dd'));
  v_yyyy2 := to_number(to_char(p_end_date,'yyyy'));
  v_mm2   := to_number(to_char(p_end_date,'mm'));
  v_dd2   := to_number(to_char(p_end_date,'dd'));
  IF p_rule_type = 'F' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31
          THEN IF v_dd1 < 30
                    THEN v_dd2 := 1;
                         v_mm2 := v_mm2 + 1;
                         IF v_mm2 = 13 THEN v_mm2 := 1;
                                            v_yyyy2 := v_yyyy2 +1;
                         END IF;
                    ELSE v_dd2 := 30;
               END IF;
     END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30;
               IF  (v_dd1 < 30)
                   THEN v_dd2 := 1;
                        v_mm2 := 3;
               END IF;
     END IF;
     IF v_mm2 IN (4, 6, 9, 11) AND v_dd2 = 30
          AND v_dd1 < 30
          THEN v_dd2 := 1;
               v_mm2 := v_mm2 + 1;
     END IF;
  ELSIF p_rule_type = 'T' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31 THEN v_dd2 := 30; END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30; END IF;
  ELSE RAISE_APPLICATION_ERROR('-20002','3VL Not Allowed Here');
  END IF;
  RETURN (v_yyyy2 - v_yyyy1) * 360
       + (v_mm2 - v_mm1) * 30
       + (v_dd2 - v_dd1);
END;

/

Testing SQL Script:

select days_360('03-FEB-2016','31-MAR-2019') from dual;

How to define oracle fusion gapless custom sequence at Ship Confirm Form Level

Fusion Generate Gapless Custom Sequence at

Ship Confirm Form Level

Requirement: As Oracle fusion, ship-confirm level shipment number sequence having a random seq. generate nature. We have required to assign a unique sequence number to shipment transaction.


Goto Home > Setup and Maintenance

Select Manufacturing and Supply Chain Material Management.

 

    1)      Select the Shipping tab from the left panel. 

    2)      Select ALL Task from LOV

Select  ‘Manage Shipping Document Sequences’. ·         Before creating shipping document sequence, create a sequence category.   ·         Go to > Setup and Maintenance > Search > Manage Document Sequence Category.
Click on Search.
Create Document New Sequences.
Add details as per below screenshots.   Assign Newly created Document Sequence with Legal Entity. Now we have to assign the following Document Sequence in shipping parameter window. Shipping Document Job Set Rule. ·         Create Shipping document Job set as per below screenshot              Define Ship Confirm Rule:- ·         Define Ship confirm rule and assign this to manage shipping parameter Goto Home > Setup and Maintenance > Manufacturing and Supply Chain Material Management.            1)      Select the Shipping tab from the left panel.      2)      Select ALL Task from LOV      3)      Select ‘Manage Shipping Parameter’
Now Create SO transaction and create Ship Confirm for the same. Now Packing Slip Number (Which is in sequence in reference of sales order) automatically generated at Ship Confirm Level. You can get that value from backend by using the following query. Shipment Number = Delivery_id = 15016 SQL Query: select delivery_id,packing_slip_number from wsh_new_deliveries where packing_slip_number is not null and delivery_id=15016

Output:

 

How to define custom sequence at DFF attributes level - Oracle Fusion

Fusion: Define Custom Sequence with DFF attributes

Goto Home > Select Setup and Maintenance Icon

 

Select Manufacturing and Supply Chain Material Management.

 

    1)      Select the shipping tab from the left panel.

    2)      Select ALL Task from LOV

 

Select ‘Manage Shipping Descriptive Flexfields’

 

Select the Search Button to find all available option.

Select Shipment for Edit.

 

Enter Details as per the screenshots

 

For Add or update Global Segment details

 

Default Value:

select trim(nvl(to_char(max(to_number(attribute1))+1,'00000000'),'10000001')) xx from wsh_new_deliveries where attribute1 is not null

and to_number(attribute1) >= 10000000

and REGEXP_LIKE(trim(attribute1), '^[[:digit:]]+$')

 

Now when you complete ship confirms process, it will automatically generate the sequence and insert into attribute1 value.

(Note: Sometimes due to browser cache issue sequence number will be duplicate)