Loop on Form 6i to Copy Block A Data to Block B

 declare

-- local variables; should contain all items you'd want to copy

l_empno  emp.empno%type;

l_ename  emp.ename%type;

l_job    emp.job%type;

-- l_currec will contain current row number in the first block

l_currec number := 0;

-- l_exit will be used if we're at the end of the first block

l_exit varchar2(1) := 'N';

begin

loop

  -- go to the source (first block) and the [last row you were in + 1]

  go_block('first');

  l_currec := l_currec + 1;

  go_record(l_currec);`

  -- check whether this is the last row of the first block; if so, exit the loop

  if :system.last_record = 'true' then

     l_exit := 'Y';

  end if;

  -- save current row's items

  l_empno := :first.empno;

  l_ename := :first.ename;

  l_job   := :first.job

  -- go to the second block's bottom and create a new record

  go_block('second');

  last_record;

  create_record;

  -- put stored values into the second block's items

  :second.empno := l_empno;

  :second.ename := l_ename;

  :second.job   := l_job;

  -- exit the loop if this was the last record to be copied

  exit when l_exit = 'Y';

end loop;

end;

end;

Export Data to Excel from Form Block

 DECLARE

  i_reporttitle VARCHAR2(200) := 'My excel';

 COL1 VARCHAR2(2);

 COL2 VARCHAR2(8);

 COL3  VARCHAR2(8);

 COL4 VARCHAR2(15);

 COL5 VARCHAR2(50);

 COL6 NUMBER(3,1);

 COL7 NUMBER(3,1);

 COL8 DATE;

 COL9 NUMBER(2);

 COL10 NUMBER(2);

 COL11 NUMBER(2);

 COL12 NUMBER(9,2);

 COL13 NUMBER(9,2);

 COL14 NUMBER(9,2);

 COL15 NUMBER(6);

 AMT number;

 r number := 1;

 c number := 1;

 i_orientation VARCHAR2(200) := '1';

 i number :=2;

 BELTNAME VARCHAR2(200);

/* definiton for commonly used variables and constants and exceptions */

ExcelID ole2.obj_type;

ExcelWorkBooksID ole2.obj_type;

ExcelWorkBookID ole2.obj_type;

ExcelWorkSheetsID ole2.obj_type;

ExcelWorkSheetID ole2.obj_type;

ExcelCellID ole2.obj_type;

ExcelFontID ole2.obj_type;

ExcelPageSetupID ole2.obj_type;

ExcelArgs ole2.list_type;

BEGIN

ExcelID := ole2.create_obj('Excel.Application'); 

ExcelWorkBooksID := ole2.get_obj_property(ExcelID, 'Workbooks');

ExcelWorkBookID := ole2.invoke_obj(ExcelWorkBooksID, 'Add');

ExcelWorkSheetsID := ole2.get_obj_property(ExcelWorkBookID, 'Worksheets');

ExcelWorkSheetID := ole2.invoke_obj(ExcelWorkSheetsID, 'Add');

ExcelPageSetupID := ole2.get_obj_property(ExcelWorkSheetID, 'PageSetup');

ole2.set_property(ExcelPageSetupID, 'Orientation',i_orientation);

ole2.release_obj(ExcelPageSetupID);

-------------DATED ---------

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i-1);

ole2.add_arg(ExcelArgs,1);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', ' DATE ');

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

------------------------------------------

------------- INVOICE NO ---------

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i-1);

ole2.add_arg(ExcelArgs,2);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', ' INVOICE_NO ');

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

------------------------------------------

------------- PRODUCT NAME  ---------

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i-1);

ole2.add_arg(ExcelArgs,3);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', ' PRODUCT NAME ');

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

------------------------------------------

------------- HS CODE  ---------

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i-1);

ole2.add_arg(ExcelArgs,4);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', ' HS CODE ');

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

------------------------------------------

------------- PACKING  ---------

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i-1);

ole2.add_arg(ExcelArgs,5);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', ' PACKING ');

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

------------------------------------------

------------- UOM  ---------

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i-1);

ole2.add_arg(ExcelArgs,6);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', ' UOM ');

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

------------------------------------------

------------- QTY  ---------

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i-1);

ole2.add_arg(ExcelArgs,7);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', ' QTY ');

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

------------------------------------------

--FOR BELT IN (SELECT CODE,NAME FROM BELT) LOOP

-- MESSAGE('BELT '||BELT.NAME);

go_block('BLK_EXP');

FIRST_RECORD;

  loop

--if :beltname=belt.code then

ole2.set_property(ExcelCellId, 'Value', '1');

go_block('BLK_EXP');

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value',:BLK_EXP.DATED);

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c+1);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', :BLK_EXP.INVNO);

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c+2);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', :BLK_EXP.PRODUCT_NAME);

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c+3);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', :BLK_EXP.HSCODE);

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c+4);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', :BLK_EXP.PACKING);

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c+5);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', :BLK_EXP.UOM);

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c+6);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

ole2.set_property(ExcelCellId, 'Value', :BLK_EXP.QTY);

ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');

ole2.set_property(ExcelFontId, 'Bold', 'True');

ole2.set_property(ExcelFontId, 'Size', '10');

ole2.release_obj(ExcelFontId);

ole2.release_obj(ExcelCellId);

/*

ExcelArgs := ole2.create_arglist;

ole2.add_arg(ExcelArgs,i);

ole2.add_arg(ExcelArgs,c+9);

ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);

ole2.destroy_arglist(ExcelArgs);

--ole2.set_property(ExcelCellId, 'Value', :BELTNAME);

*/

exit when :SYSTEM.LAST_RECORD = 'TRUE'; 

 next_record;

i := i+1;

--end if;

--END LOOP;

end loop;

------ AMOUNT TOTAL --------------

------------------------------------------

ole2.set_property(ExcelID, 'Visible','TRUE');

-- ... and release the allocated resources because they are no longer used by forms

ole2.release_obj(ExcelWorkSheetID);

ole2.release_obj(ExcelWorkSheetsID);

ole2.release_obj(ExcelWorkBookID);

ole2.release_obj(ExcelWorkBooksID);

ole2.release_obj(ExcelID);

END;

Split Qty Query

-- Set parameters

WITH params AS (

  SELECT 290 AS total_qty, 15 AS num_rows FROM dual

),

-- Step 1: Generate a random list of 1s and 2s that add up to total_qty

random_vals AS (

  SELECT 

  CASE WHEN DBMS_RANDOM.VALUE(1,5) < 1 THEN round(DBMS_RANDOM.VALUE(1,5)) ELSE round(DBMS_RANDOM.VALUE(1,5)) END AS val,

   -- CASE WHEN DBMS_RANDOM.VALUE < 290/15 THEN 1      ELSE 2    END AS val,

    ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) AS rn

  FROM dual

  CONNECT BY LEVEL <= 15 -- Generate more than needed

),

running_sum AS (

  SELECT val, SUM(val) OVER (ORDER BY rn) AS running_total

  FROM random_vals

),

limited_vals AS (

  SELECT val, ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) AS id

  FROM running_sum, params

  WHERE running_total <= params.total_qty

),

-- Step 2: Split the list randomly into N groups

grouped AS (

  SELECT

    FLOOR((ROWNUM - 1) * (SELECT COUNT(*) FROM limited_vals) / (SELECT num_rows FROM params)) + 1 AS group_id,

    val

  FROM limited_vals

),

-- Step 3: Aggregate the values per group as comma-separated strings or totals

final_output AS (

  SELECT

    group_id,

    LISTAGG(val, ', ') WITHIN GROUP (ORDER BY DBMS_RANDOM.VALUE) AS qty_series,

    SUM(val) AS group_total

  FROM grouped

  GROUP BY group_id

)

SELECT * FROM final_output ORDER BY group_id;

Query for Product Combinations

 SELECT a.item_id Parent_item,

    a.item_id || '-' || b.item_id AS item_combination,b.item_id child_item FROM  mv_mtl_items a

CROSS JOIN mv_mtl_items b

WHERE 

    a.item_id IN (9000036719, 9000036720, 9000036721, 9000036976)

    AND b.item_id IN (9000036719, 9000036720, 9000036721, 9000036976)

    AND a.item_id != b.item_id

ORDER BY a.item_id, b.item_id;

/************************ H I R A R I C A L ****************************/

SELECT

    TO_CHAR(item_id) AS tree_line,

    item_id AS sort_parent,

    1 AS sort_level

FROM (

    SELECT DISTINCT item_id FROM mv_mtl_items

    WHERE item_id IN (9000036719, 9000036720, 9000036721, 9000036976)

)

UNION ALL

SELECT 

    LPAD(' ', 4) || a.item_id || ' --> ' || b.item_id /*|| ' → ' || b.item_id*/ AS tree_line,

    a.item_id AS sort_parent,

    2 AS sort_level

FROM (

    SELECT item_id FROM mv_mtl_items

    WHERE item_id IN (9000036719, 9000036720, 9000036721, 9000036976)

) a

JOIN (

    SELECT item_id FROM mv_mtl_items

    WHERE item_id IN (9000036719, 9000036720, 9000036721, 9000036976)

) b

ON a.item_id != b.item_id

ORDER BY sort_parent, sort_level, tree_line;



This Query will Show 

9000036719 9000036719 1

    9000036719 --> 9000036720 9000036719 2

    9000036719 --> 9000036721 9000036719 2

    9000036719 --> 9000036976 9000036719 2

9000036720 9000036720 1

    9000036720 --> 9000036719 9000036720 2

    9000036720 --> 9000036721 9000036720 2

    9000036720 --> 9000036976 9000036720 2