GL Report – XXABC: Push the In Process JVs to Required for Re-Sumbit

Procedure:

CREATE OR REPLACE PROCEDURE XXTAW_INPROC_JV(x_errbuf OUT varchar2,
,x_retcode OUT NUMBER
,p_batch_name IN number
)
AS
BEGIN
x_errbuf := NULL;
x_retcode := 0;
UPDATE gl_je_batches
SET approval_status_code = ‘R’
WHERE 1=1
AND approval_status_code =’I’
AND JE_BATCH_ID = p_batch_name;
IF (SQL%ROWCOUNT) = 0 THEN
fnd_file.put_line(fnd_file.output,’Unable to Push from Inprocess to New status’);
fnd_file.put_line(fnd_file.output,’Check if already processed’);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
x_errbuf := ‘Fatal error in procedure XXTAW_INPROC_JV ‘||SQLERRM);
x_retcode := 2;
END;

Note: PL/SQL Stored Procedure

Paramter:
Batch Name (Mandatory)

Advertisements

GL Report – XXABC: Manual Journals Report

select
BAT.doc_sequence_value DOCUMENT_NUMBER,
TO_CHAR(BAT.HEADER_EFFECTIVE_DATE,’DD-MON-RRRR’) JOURNAL_DATE,
BAT.HEADER_PERIOD_NAME_QRY JOURNAL_PERIOD,
FFVV.MEANING APPROVAL_STATUS,
GJS.USER_JE_SOURCE_NAME JOURNAL_SOURCE,
GJC.USER_JE_CATEGORY_NAME JOURNAL_CATEGORY,
BAT.currency_code JOURNAL_CURRENCY,
BAT.HEADER_RUNNING_TOTAL_DR_NUM ENTERED_DEBIT,
BAT.HEADER_RUNNING_TOTAL_CR_NUM ENTERED_CREDIT,
BAT.HEADER_RUN_TOTAL_ACCOUNTED_DR ACCOUNTED_DEBIT,
BAT.HEADER_RUN_TOTAL_ACCOUNTED_CR ACCOUNTED_CREDIT,
FU.user_name CREATED_BY,
BAT.HEADER_NAME JOURNAL_NAME,
BAT.HEADER_DESCRIPTION JOURNAL_DESCRIPTION,
TO_CHAR(BAT.batch_creation_date,’DD-MON-RRRR’) CREATION_DATE,
TO_CHAR(BAT.BATCH_LAST_UPDATE_DATE,’DD-MON-RRRR’) POSTED_DATE,
BAT.accrual_rev_status REVERSAL_STATUS,
BAT.accrual_rev_period_name REVERSAL_PERIOD,
FFVV1.MEANING POST_STAT
from
GL_JE_BATCHES_HEADERS_V BAT,
fnd_lookup_values_vl FFVV,
fnd_lookup_values_vl FFVV1,
gl_je_sources GJS,
gl_je_categories GJC,
fnd_user FU
where 1=1
AND FU.user_id = BAT.created_by
AND BAT.je_source = GJS.je_source_name
AND BAT.je_category = GJC.JE_CATEGORY_NAME
AND BAT.approval_status_code = FFVV.lookup_code
AND FFVV.lookup_type=’JE_BATCH_APPROVAL_STATUS’
AND bat.BATCH_STATUS = FFVV1.lookup_code
AND FFVV1.lookup_type=’BATCH_STATUS’
AND FFVV1.view_application_id =101
AND bat.actual_flag = ‘A’
AND BAT.HEADER_EFFECTIVE_DATE between to_date(:P_PERIOD_FROM,’MON-RRRR’) AND last_day (to_date(:P_PERIOD_TO,’MON-RRRR’))
AND GJS.USER_JE_SOURCE_NAME not in (‘Payables’, ‘Receivables’, ‘Assets’, ‘Cash Management’, ‘Cost Management’)
Order by BAT.HEADER_EFFECTIVE_DATE

Parameters:

Period From (Mandatory)

Period To (Mandatory)