====
At first Create a function for dynamically date range wise column show.
CREATE OR REPLACE FUNCTION GET_DYNAMIC_SALES_COLLECTION_V4
(
P_FROM_DATE_VAL IN VARCHAR2, -- DD-MM-YYYY
P_TO_DATE_VAL IN VARCHAR2, -- DD-MM-YYYY
P_GROUP IN VARCHAR2,
P_ORG IN VARCHAR2,
P_DIVI IN VARCHAR2,
P_ZONE IN VARCHAR2,
-------------------------------------
P_PRY_MIN_COLLECTION IN NUMBER,
P_PRY_MAX_COLLECTION IN NUMBER,
P_SEC_MIN_COLLECTION IN NUMBER,
P_SEC_MAX_COLLECTION IN NUMBER
)
RETURN CLOB
IS
V_FROM_DATE DATE;
V_TO_DATE DATE;
V_DAYS NUMBER;
V_COLS CLOB;
V_SQL CLOB;
BEGIN
/* 1. Convert dates */
V_FROM_DATE := TO_DATE(P_FROM_DATE_VAL, 'DD-MM-YYYY');
V_TO_DATE := TO_DATE(P_TO_DATE_VAL, 'DD-MM-YYYY');
V_DAYS := (V_TO_DATE - V_FROM_DATE) + 1;
/* 2. Dynamic day columns */
SELECT RTRIM(
XMLCAST(
XMLAGG(
XMLELEMENT(e,
'SUM(CASE WHEN report_date = DATE '''
|| TO_CHAR(V_FROM_DATE + LEVEL - 1,'YYYY-MM-DD')
|| ''' THEN primary_collection END) AS "' ||
TO_CHAR(V_FROM_DATE + LEVEL - 1,'DD') || '_P",' ||
'SUM(CASE WHEN report_date = DATE '''
|| TO_CHAR(V_FROM_DATE + LEVEL - 1,'YYYY-MM-DD')
|| ''' THEN secondary_collection END) AS "' ||
TO_CHAR(V_FROM_DATE + LEVEL - 1,'DD') || '_S",'
)
ORDER BY LEVEL
) AS CLOB
),
','
)
INTO V_COLS
FROM dual
CONNECT BY LEVEL <= V_DAYS;
/* 3. Final SQL */
V_SQL :=
'WITH sales_base AS
(
SELECT DISTINCT
c.salesrep_id,
c.salesrep_number,
c.salesrep_name,
XX_EMP_MOBILE_NO(c.salesrep_number) AS mobile_no,
c.product_name,
c.zm_name,
t.division,
t.zone,
t.base
FROM xx_custterrsrzmdm c
JOIN xx_ra_territories t
ON t.territory_id = c.territory_id
WHERE c.end_date_active IS NULL
AND c.salesrep_number <> ''-3''
AND (''' || P_GROUP || ''' IS NULL OR c.product_code = ''' || P_GROUP || ''')
AND (''' || P_ORG || ''' IS NULL OR c.org_id = ''' || P_ORG || ''')
AND (''' || P_DIVI || ''' IS NULL OR t.div_id = ''' || P_DIVI || ''')
AND (''' || P_ZONE || ''' IS NULL OR t.zone_id = ''' || P_ZONE || ''')
AND c.product_code NOT IN
(13,5,20,143,15,11,9,6,801,22,0,3,1,4,802)
),
sales_info AS
(
SELECT
salesrep_id,
salesrep_number,
salesrep_name,
MAX(mobile_no) AS mobile_no,
LISTAGG(DISTINCT zm_name, '', '') WITHIN GROUP (ORDER BY zm_name) AS zm_name,
LISTAGG(DISTINCT product_name, '', '') WITHIN GROUP (ORDER BY product_name) AS product_name,
LISTAGG(DISTINCT division, '', '') WITHIN GROUP (ORDER BY division) AS division,
LISTAGG(DISTINCT zone, '', '') WITHIN GROUP (ORDER BY zone) AS zone,
LISTAGG(DISTINCT base, '', '') WITHIN GROUP (ORDER BY base) AS base
FROM sales_base
GROUP BY salesrep_id, salesrep_number, salesrep_name
),
date_list AS
(
SELECT DATE ''' || TO_CHAR(V_FROM_DATE,'YYYY-MM-DD') || ''' + LEVEL - 1 report_date
FROM dual
CONNECT BY LEVEL <= ' || V_DAYS || '
),
primary_coll AS
(
SELECT
r.salesrepid AS salesrep_id,
TRUNC(r.process_date) AS report_date,
SUM(r.amount) AS primary_collection
FROM xx_mobile_money_receipt r
WHERE r.cash_receipt_id IS NOT NULL
GROUP BY r.salesrepid, TRUNC(r.process_date)
),
secondary_coll AS
(
SELECT
v.created_by AS salesrep_number,
TRUNC(v.visit_date) AS report_date,
SUM(v.do_amount) AS secondary_collection
FROM xx_visit_details v
GROUP BY v.created_by, TRUNC(v.visit_date)
),
collection_data AS
(
SELECT
s.salesrep_id,
s.salesrep_number,
d.report_date,
NVL(p.primary_collection,0) AS primary_collection,
NVL(sc.secondary_collection,0) AS secondary_collection
FROM (SELECT DISTINCT salesrep_id, salesrep_number FROM sales_base) s
CROSS JOIN date_list d
LEFT JOIN primary_coll p
ON p.salesrep_id = s.salesrep_id
AND p.report_date = d.report_date
LEFT JOIN secondary_coll sc
ON sc.salesrep_number = s.salesrep_number
AND sc.report_date = d.report_date
)
SELECT
i.salesrep_number AS SO_ID,
i.salesrep_name,
i.mobile_no,
i.zm_name,
i.product_name AS "GROUP",
i.division,
i.zone,
i.base,
' || V_COLS || '
FROM collection_data c
JOIN sales_info i
ON i.salesrep_id = c.salesrep_id
GROUP BY
i.salesrep_id,
i.salesrep_number,
i.salesrep_name,
i.mobile_no,
i.zm_name,
i.product_name,
i.division,
i.zone,
i.base
ORDER BY i.salesrep_name';
RETURN V_SQL;
EXCEPTION
WHEN OTHERS THEN
RETURN 'SELECT NULL FROM dual WHERE 1=0';
END;
/
Step:2
====
Create a region. (Change Function Name and Parameters)
Type: Dynamic Content
Source:
Language: PL/SQL
PL/SQL Function Body returning a CLOB:
DECLARE
l_sql VARCHAR2(32767);
l_cursor INTEGER;
l_col_cnt NUMBER;
l_desc_tab dbms_sql.desc_tab;
l_val VARCHAR2(4000);
l_html CLOB;
l_status INTEGER;
BEGIN
-- 1. Get the dynamic SQL string from your function
l_sql := GET_DYNAMIC_SALES_COLLECTION_V3 (
TO_DATE (:P23036_FROM_DATE, 'DD-MM-YYYY'),
TO_DATE (:P23036_TO_DATE, 'DD-MM-YYYY'),
:P23036_GROUP,
:P23036_OPERATING_UNIT,
:P23036_DIVISION,
:P23036_ZONE,
:P23036_MIN_COLLECTION,
:P23036_MAX_COLLECTION, :P23036_SEC_MIN_COLLECTION, :P23036_SEC_MAX_COLLECTION
);
-- 2. Open and Parse Cursor
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
dbms_sql.describe_columns(l_cursor, l_col_cnt, l_desc_tab);
FOR i IN 1 .. l_col_cnt LOOP
dbms_sql.define_column(l_cursor, i, l_val, 4000);
END LOOP;
l_status := dbms_sql.execute(l_cursor);
-- 3. Build HTML Table String
l_html := '<div class="t-Report t-Report--standard"><table class="t-Report-report">';
-- Headers
l_html := l_html || '<thead><tr>';
FOR i IN 1 .. l_col_cnt LOOP
l_html := l_html || '<th class="t-Report-colHead">' || apex_escape.html(l_desc_tab(i).col_name) || '</th>';
END LOOP;
l_html := l_html || '</tr></thead><tbody>';
-- Data Rows
WHILE dbms_sql.fetch_rows(l_cursor) > 0 LOOP
l_html := l_html || '<tr>';
FOR i IN 1 .. l_col_cnt LOOP
dbms_sql.column_value(l_cursor, i, l_val);
l_html := l_html || '<td class="t-Report-cell">' || apex_escape.html(l_val) || '</td>';
END LOOP;
l_html := l_html || '</tr>';
END LOOP;
l_html := l_html || '</tbody></table></div>';
dbms_sql.close_cursor(l_cursor);
RETURN l_html;
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(l_cursor) THEN
dbms_sql.close_cursor(l_cursor);
END IF;
RETURN '<div class="u-dangerText">Error: ' || apex_escape.html(SQLERRM) || '</div>';
END;
Page Items to Submit: All Parameters.
====
Name: Download_Dynamic_Excel
====
Create New Items for Row Limitation.
- P23_PAGE_NO ---> Default value: 1
- P23_PAGE_SIZE ---> Default value: 20
Create a region. (Change Function Name and Parameters)
Type: Dynamic Content
Source:
Language: PL/SQL
PL/SQL Function Body returning a CLOB:
====
====
- prev
- next
1️⃣ CONNECT BY LEVEL <= V_DAYS
FROM dual
CONNECT BY LEVEL <= V_DAYS
এটা loop তৈরি করে।
যদি
V_DAYS = 5 হয় তাহলে rows হবে:
| LEVEL | DATE |
|---|---|
| 1 | 01 |
| 2 | 02 |
| 3 | 03 |
| 4 | 04 |
| 5 | 05 |
এগুলা দিয়ে day-wise column তৈরি হচ্ছে।
2️⃣ XMLELEMENT
XMLELEMENT(e,'text')
এটা একটা XML tag তৈরি করে।
Example:
SELECT XMLELEMENT(e,'Hello') FROM dual;
Output:
<e>Hello</e>
আপনার query-তে:
XMLELEMENT(e,
'SUM(CASE WHEN report_date = DATE ''2026-01-01''
THEN primary_collection END) AS "01_P",'
)
Output:
<e>SUM(CASE WHEN report_date = DATE '2026-01-01' ...</e>
অর্থাৎ প্রতিদিনের জন্য SQL text XML tag এর ভিতরে রাখা হচ্ছে।
3️⃣ XMLAGG
XMLAGG(...)
এটা সব XML rows একসাথে join করে।
Example:
| LEVEL | XML |
|---|---|
| 1 | <e>A</e> |
| 2 | <e>B</e> |
| 3 | <e>C</e> |
XMLAGG(XMLELEMENT(e,'A'))
Result:
<e>A</e><e>B</e><e>C</e>
আপনার query-তে:
<e>01_P</e>
<e>01_S</e>
<e>02_P</e>
<e>02_S</e>
...
সব একসাথে join হয়ে যায়।
4️⃣ XMLCAST
XMLCAST( XML AS CLOB )
XML datatype কে normal text (CLOB) এ convert করে।
Example:
XMLCAST(XMLAGG(...) AS CLOB)
Result:
SUM(CASE WHEN report_date=...) AS "01_P",
SUM(CASE WHEN report_date=...) AS "01_S",
SUM(CASE WHEN report_date=...) AS "02_P",
5️⃣ RTRIM
শেষে extra comma remove করা হচ্ছে।
RTRIM(text, ',')
Example:
A,B,C,
After RTRIM:
A,B,C
🔎 পুরো flow
CONNECT BY
↓
XMLELEMENT
↓
XMLAGG
↓
XMLCAST
↓
RTRIM
Result:
SUM(CASE WHEN report_date = DATE '2026-01-01' THEN primary_collection END) AS "01_P",
SUM(CASE WHEN report_date = DATE '2026-01-01' THEN secondary_collection END) AS "01_S",
SUM(CASE WHEN report_date = DATE '2026-01-02' THEN primary_collection END) AS "02_P",
SUM(CASE WHEN report_date = DATE '2026-01-02' THEN secondary_collection END) AS "02_S"
এটাই V_COLS variable-এ store হচ্ছে।
তারপর final query-তে use হচ্ছে:
SELECT
salesrep_name,
|| V_COLS ||
FROM ...
⭐ সহজ ভাষায়
এই query date range অনুযায়ী dynamic pivot column বানাচ্ছে।
Example:
| date | Primary | Secondary |
|---|---|---|
| 01 | 01_P | 01_S |
| 02 | 02_P | 02_S |
| 03 | 03_P | 03_S |