--OGL Pivot Trial balance report in Text Format
Create Global Temporary Table XXJG_OGL_GL_CLS_TMP (
ogl_code VARCHAR2(50),
ogl_description VARCHAR2(240),
branch_code VARCHAR2(50),
branch_description VARCHAR2(240),
product_code VARCHAR2(50),
product_description VARCHAR2(240),
source_system_code VARCHAR2(50),
source_system_description VARCHAR2(240),
balance_date VARCHAR2(20),
closing_amt NUMBER
)
ON COMMIT Preserve ROWS;
/
CREATE OR REPLACE PROCEDURE XXJG_OGL_GL_CLS_P (
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
P_ACCESS_SET_ID IN VARCHAR2,
P_LEDGER_NAME IN VARCHAR2,
P_LEDGER_ID IN NUMBER,
P_CHART_OF_ACCOUNTS_ID IN NUMBER,
P_CURRENCY_CODE IN VARCHAR2 DEFAULT 'ALL',
P_LEGAL_ENTITY IN VARCHAR2,
P_ACCT_FROM IN VARCHAR2 DEFAULT NULL,
P_ACCT_TO IN VARCHAR2 DEFAULT NULL,
P_START_DATE IN VARCHAR2,
P_END_DATE IN VARCHAR2,
P_BRANCH_FROM IN VARCHAR2,
P_BRANCH_TO IN VARCHAR2,
P_PRODUCT_FROM IN VARCHAR2,
P_PRODUCT_TO IN VARCHAR2,
P_SOURCE_FROM IN VARCHAR2,
P_SOURCE_TO IN VARCHAR2
)
IS
CURSOR c_data IS
SELECT
gcc.segment2 AS OGL_CODE,
apps.gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2) AS OGL_DESCRIPTION,
gcc.segment3 AS BRANCH_CODE,
apps.gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 3, gcc.segment3) AS BRANCH_DESCRIPTION,
gcc.segment6 AS PRODUCT_CODE,
apps.gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 6, gcc.segment6) AS PRODUCT_DESCRIPTION,
gcc.segment8 AS SOURCE_SYSTEM_CODE,
apps.gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 8, gcc.segment8) AS SOURCE_SYSTEM_DESCRIPTION,
TO_CHAR(accounting_date, 'DD-MON-YYYY') AS BALANCE_DATE,
SUM(end_of_date_balance_num) AS CLOSING_AMT
FROM
gl_daily_balances_v gv
JOIN gl_code_combinations_kfv gcc
ON gv.code_combination_id = gcc.code_combination_id
WHERE
gv.ledger_id = P_LEDGER_ID
AND period_type = '21'
AND accounting_date BETWEEN fnd_date.canonical_to_date(P_START_DATE)
AND fnd_date.canonical_to_date(P_END_DATE)
AND currency_type IN ('O', 'T', 'U')
AND (P_CURRENCY_CODE = 'ALL' OR ledger_currency = P_CURRENCY_CODE)
AND gcc.segment2 BETWEEN NVL(P_ACCT_FROM, gcc.segment2) AND NVL(P_ACCT_TO, gcc.segment2)
AND gcc.segment3 BETWEEN NVL(P_BRANCH_FROM, gcc.segment3) AND NVL(P_BRANCH_TO, gcc.segment3)
AND gcc.segment6 BETWEEN NVL(P_PRODUCT_FROM, gcc.segment6) AND NVL(P_PRODUCT_TO, gcc.segment6)
AND gcc.segment8 BETWEEN NVL(P_SOURCE_FROM, gcc.segment8) AND NVL(P_SOURCE_TO, gcc.segment8)
GROUP BY
gcc.chart_of_accounts_id,
gcc.segment2,
gcc.segment3,
gcc.segment6,
gcc.segment8,
TO_CHAR(accounting_date, 'DD-MON-YYYY');
v_cols VARCHAR2(32000);
v_sql CLOB;
v_header VARCHAR2(32767);
v_col_count NUMBER := 0;
BEGIN
p_retcode := 0;
p_errbuf := NULL;
DELETE FROM XXJG_OGL_GL_CLS_TMP;
COMMIT;
FOR rec IN c_data LOOP
INSERT INTO XXJG_OGL_GL_CLS_TMP (
ogl_code,
ogl_description,
branch_code,
branch_description,
product_code,
product_description,
source_system_code,
source_system_description,
balance_date,
closing_amt
) VALUES (
rec.ogl_code,
rec.ogl_description,
rec.branch_code,
rec.branch_description,
rec.product_code,
rec.product_description,
rec.source_system_code,
rec.source_system_description,
rec.balance_date,
rec.closing_amt
);
END LOOP;
COMMIT;
-- Build safe pivot IN clause using valid aliases
v_cols := '';
FOR d IN (
SELECT DISTINCT balance_date
FROM XXJG_OGL_GL_CLS_TMP
ORDER BY TO_DATE(balance_date, 'DD-MON-YYYY')
) LOOP
v_cols := v_cols || '''' || d.balance_date || ''' AS "D_' ||
REPLACE(d.balance_date, '-', '_') || '",';
v_col_count := v_col_count + 1;
END LOOP;
IF v_col_count = 0 THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'No data found for given parameters.');
RETURN;
END IF;
v_cols := RTRIM(v_cols, ',');
-- Build pivot SQL
v_sql := 'SELECT ogl_code, ogl_description, branch_code, branch_description, ' ||
'product_code, product_description, source_system_code, source_system_description';
FOR d IN (
SELECT DISTINCT balance_date
FROM XXJG_OGL_GL_CLS_TMP
ORDER BY TO_DATE(balance_date, 'DD-MON-YYYY')
) LOOP
v_sql := v_sql || ', TO_CHAR(NVL("D_' || REPLACE(d.balance_date, '-', '_') ||
'", 0)) AS "' || d.balance_date || '"';
END LOOP;
v_sql := v_sql ||
' FROM (SELECT ogl_code, ogl_description, branch_code, branch_description, ' ||
'product_code, product_description, source_system_code, source_system_description, balance_date, closing_amt ' ||
'FROM XXJG_OGL_GL_CLS_TMP) ' ||
'PIVOT (SUM(closing_amt) FOR balance_date IN (' || v_cols || ')) ' ||
'ORDER BY ogl_code, branch_code, product_code';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Generated SQL: ' || v_sql);
-- Header
v_header := 'OGL Code|OGL Description|Branch Code|Branch Description|Product Code|Product Description|Source System Code|Source System Description';
FOR d IN (
SELECT DISTINCT balance_date
FROM XXJG_OGL_GL_CLS_TMP
ORDER BY TO_DATE(balance_date, 'DD-MON-YYYY')
) LOOP
v_header := v_header || '|' || d.balance_date;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, v_header);
-- Execute and print
DECLARE
c INTEGER;
col_cnt INTEGER;
desc_tab DBMS_SQL.DESC_TAB;
col_val VARCHAR2(32767);
line_txt VARCHAR2(32767);
ignore INTEGER;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, v_sql, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, desc_tab);
FOR i IN 1 .. col_cnt LOOP
DBMS_SQL.DEFINE_COLUMN(c, i, col_val, 4000);
END LOOP;
ignore := DBMS_SQL.EXECUTE(c);
WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP
line_txt := '';
FOR i IN 1 .. col_cnt LOOP
DBMS_SQL.COLUMN_VALUE(c, i, col_val);
IF i > 1 THEN
line_txt := line_txt || '|';
END IF;
line_txt := line_txt || NVL(col_val, '0');
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, line_txt);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
EXCEPTION
WHEN OTHERS THEN
p_retcode := 2;
p_errbuf := 'Error: ' || SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: ' || SQLERRM);
ROLLBACK;
END XXJG_OGL_GL_CLS_P;
/
