Linking 'Sales Orders' to 'Sales Agreements' (also known as BSA : Blanket Sales Orders) in Oracle EBS R12

/*SQL Query to Trace Blanket Sales Agreements from Sales Order Headers and Lines*/ 

In Oracle E-Business Suite (EBS) Release 12, Blanket Sales Agreements (BSA) are used to establish long-term agreements between a customer and a supplier. These agreements define specific terms, such as pricing, items, and quantities, which are then "released" via standard Sales Orders.

Key Tables: 

OE_BLANKET_HEADERS_ALL: Table to capture sales agreement header details..

OE_BLANKET_HEADERS_EXT: Table to capture sales agreement header details, specific to sales agreement functionality.

OE_BLANKET_HEADERS_HIST: Table to capture sales agreement header history. Primary for storing revisions.

Query: 

SELECT
-- Blanket Header Details
obh.order_number                                        AS bsa_number,
obh.header_id                                           AS bsa_header_id,
obh.agreement_id,
obhe.start_date_active                                  AS bsa_start_date,
obhe.end_date_active                                    AS bsa_end_date,
-- Blanket Line Details
obl.line_number                                         AS bsa_line_num,
obl.inventory_item_id,
(SELECT segment1
 FROM   mtl_system_items_b msi
 WHERE  msi.inventory_item_id = obl.inventory_item_id
        AND msi.organization_id = ool.ship_from_org_id) bsa_item,
obl.ordered_item                                        AS bsa_ordered_item,
-- Sales Order Header Details
ooh.order_number                                        AS sales_order_number,
ooh.ordered_date,
ooh.flow_status_code                                    AS order_status,
-- Sales Order Line Details
ool.line_number                                         AS so_line_num,
ool.ordered_item                                        AS so_ordered_item,
ool.order_quantity_uom,
ool.ordered_quantity
FROM   oe_order_headers_all ooh,
       oe_order_lines_all ool,
       oe_blanket_headers_all obh,
       oe_blanket_lines_all obl,
       oe_blanket_headers_ext obhe
WHERE  ooh.header_id = ool.header_id
       -- Link SO Header to BSA Header
       AND ooh.blanket_number = obh.order_number
       -- Link BSA Header to Extension table
       AND obh.order_number = obhe.order_number
       -- Link SO Line to BSA Line
       AND ool.blanket_number = obh.order_number
       AND ool.blanket_line_number = obl.line_number
       AND obh.header_id = obl.header_id
       -- Specific Order Filter
       AND ooh.order_number = :P_ORDER_NUMBER; 

How to count number of digits after a decimal place in Oracle?

 /*Query to find length of digits/decimals in a number*/

WITH t
     AS (SELECT 123.456 num
         FROM   dual
         UNION ALL
         SELECT 1234
         FROM   dual
         UNION ALL
         SELECT 0.12345
         FROM   dual)
SELECT num,
       Length(To_char(Trunc(Abs(num))))      vscale,
       Length(To_char(MOD(Abs(num), 1))) - 1 vprecision,

                ( Length(Trim(trailing '0' FROM ( num - Floor(num) ))) - 1 ) decimal_places
FROM   t; 

WITH s
     AS (
        -- sample data --
        SELECT 1234 n
        FROM   dual
        UNION ALL
        SELECT 1234.123
        FROM   dual
         UNION ALL
         SELECT 0.123
         FROM   dual
        -- sample data --
        )
SELECT n,
       Length(Floor(n))
       + Nvl(Length(Rtrim(Substr(cn, Instr(cn, '.')+1), '0')), 0) nb_digits,
       Nvl(Length(Rtrim(Substr(cn, Instr(cn, '.') + 1), '0')), 0) nbr_decimal
FROM   (SELECT n,
               To_char(n, 'fm99999999999999999D99999999999999999',
               'NLS_NUMERIC_CHARACTERS = ''. ''') cn
        FROM   s); 

Form Personalizations Query in Oracle APPS/EBS R12

 /*Form personalisations Query in Oracle APPS R12*/

SELECT fcr.SEQUENCE                                   rule_seq,
       ffv.form_name,
       user_form_name,
       fcr.function_name,
       fff.user_function_name,
       fcr.description                                per_description,
       trigger_event,
       trigger_object,
       fcr.condition,
       fcr.enabled,
       (SELECT user_name
        FROM   fnd_user fu
        WHERE  fu.user_id = fcr.created_by)           created_by,
       Decode (fcr.rule_type, 'F', 'Form',
                              'A', 'Function')        personalize_rule_level,
       Decode (fcs.level_id, 10, 'Industry',
                             20, 'Site',
                             30, 'Responsibility',
                             40, 'User')              context_level,
       Decode (fcs.level_id, 10, '',
                             20, '',
                             30, frt.responsibility_name,
                             40, fu.user_name)        context_level_value,
       ca.SEQUENCE                                    action_seq,
       Decode (ca.action_type, 'P', 'Property',
                               'B', 'Builtin',
                               'M', 'Message',
                               ca.action_type)        ACTION_TYPE,
       ca.object_type                                 action_object_type,
       CASE
         WHEN ca.action_type = 'P' THEN ca.target_object
         WHEN ca.action_type = 'M' THEN
           CASE
             WHEN ca.message_type = 'S' THEN 'Show'
             WHEN ca.message_type = 'H' THEN 'Hint'
             WHEN ca.message_type = 'E' THEN 'Error'
             WHEN ca.message_type = 'D' THEN 'Debug'
             WHEN ca.message_type = 'W' THEN 'Warn'
             ELSE ca.message_type
           END
         WHEN ca.action_type = 'B' THEN
           CASE
             WHEN ca.builtin_type = 'U' THEN 'Launch URL'
             WHEN ca.builtin_type = 'C' THEN 'Launch SRS Form'
             WHEN ca.builtin_type = 'E' THEN 'Launch a Function'
             WHEN ca.builtin_type = 'D' THEN 'Do Key'
             WHEN ca.builtin_type = 'P' THEN 'Exceute a Procedure'
             WHEN ca.builtin_type = 'G' THEN 'Go Item'
             WHEN ca.builtin_type = 'B' THEN 'Go Block'
             WHEN ca.builtin_type = 'F' THEN 'Forms DDL'
             WHEN ca.builtin_type = 'R' THEN 'Raise Forms Trigger Failure'
             WHEN ca.builtin_type = 'T' THEN 'Execute Trigger'
             WHEN ca.builtin_type = 'S' THEN 'Synchronize'
             ELSE ca.builtin_type
           END
         WHEN ca.action_type = 'S' THEN ca.menu_entry
       END                                            action_target_object,
       CASE
         WHEN ca.action_type = 'P' THEN cpl.property_name
         WHEN ca.action_type = 'M' THEN ca.message_text
         WHEN ca.action_type = 'B' THEN ca.builtin_arguments
         WHEN ca.action_type = 'S' THEN ca.menu_label
       END                                            action_property_name,
       CASE
         WHEN ca.action_type = 'P' THEN
           CASE
             WHEN ca.property_value = '4' THEN 'True'
             WHEN ca.property_value = '5' THEN 'False'
             ELSE ca.property_value
           END
         WHEN ca.action_type = 'B' THEN ca.menu_argument_short
         WHEN ca.action_type = 'S' THEN ca.menu_argument_short
       END                                            action_value,
       Decode (ca.message_type, 'W', 'Warn',
                                'E', 'Error',
                                'S', 'Show',
                                ca.message_type)      message_type,
       ca.message_text,--frt.language, frt.SOURCE_LANG
       fnd_load_util.Owner_name (fcr.last_updated_by) apps_owner
FROM   fnd_form_custom_rules fcr,
       fnd_form_custom_scopes fcs,
       fnd_form_functions_vl fff,
       fnd_responsibility_tl frt,
       fnd_user fu,
       fnd_form_vl ffv,
       fnd_form_custom_actions ca,
       applsys.fnd_form_custom_prop_list cpl
WHERE  fcs.rule_id = fcr.id
       AND fcr.function_name = fff.function_name
       AND fcs.level_value = frt.responsibility_id(+)
       AND fcs.level_value = fu.user_id(+)
       AND ffv.form_name = fcr.form_name
       AND fcr.id = ca.rule_id
       AND ca.property_name = cpl.property_id(+)
       AND ca.object_type = cpl.field_type(+)
       --and frt.language= USERENV ('LANG')
       --and frt.SOURCE_LANG= USERENV ('LANG')
       AND ffv.form_name = 'OEXOEORD' --Sales Orders
ORDER  BY ffv.form_name,
          fcr.SEQUENCE;