Open Accounting Periods Validation

create or replace PROCEDURE xx_open_period (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
errmsg OUT VARCHAR2,
p_from_org_id IN NUMBER,
p_to_org_id IN NUMBER
)
IS
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_org_id NUMBER := 82;

l_frm_org VARCHAR2(200) := NULL;
l_to_org VARCHAR2(200) := NULL;

l_err_msg VARCHAR2(4000) := NULL;

CURSOR c1_po_from_org
IS
SELECT Count(*) Open_prd
FROM gl_period_statuses ps,
gl_sets_of_books sob,
fnd_application_vl fnd
WHERE ps.application_id = 201 — PO
AND sob.set_of_books_id = (SELECT set_of_books_id
FROM org_organization_definitions
WHERE organization_id = p_from_org_id)
AND fnd.application_id = ps.application_id
AND ps.adjustment_period_flag = ‘N’
AND ps.closing_status = ‘O’
AND (TRUNC(SYSDATE)
BETWEEN TRUNC (ps.start_date)
AND TRUNC (ps.end_date)
);

CURSOR c2_gl_from_org
IS
SELECT Count(*) Open_prd
FROM gl_period_statuses ps,
gl_sets_of_books sob,
fnd_application_vl fnd
WHERE ps.application_id = 101 — GL
AND sob.set_of_books_id = (SELECT set_of_books_id
FROM org_organization_definitions
WHERE organization_id = p_from_org_id)
AND fnd.application_id = ps.application_id
AND ps.adjustment_period_flag = ‘N’
AND ps.closing_status = ‘O’
AND (TRUNC(SYSDATE)
BETWEEN TRUNC (ps.start_date)
AND TRUNC (ps.end_date)
);

CURSOR c3_INV_from_org
IS
SELECT Count(*) Open_prd
FROM org_acct_periods oap ,
org_organization_definitions ood
WHERE oap.organization_id = ood.organization_id
AND oap.open_flag = ‘Y’
and oap.organization_id = p_from_org_id
AND (TRUNC(SYSDATE)
BETWEEN TRUNC(oap.period_start_date) AND TRUNC (oap.schedule_close_date));

CURSOR c4_GL_to_org
IS
SELECT Count(*) Open_prd
FROM gl_period_statuses ps,
gl_sets_of_books sob,
fnd_application_vl fnd
WHERE ps.application_id = 101 — GL
AND sob.set_of_books_id = (SELECT set_of_books_id
FROM org_organization_definitions
WHERE organization_id = p_to_org_id)
AND fnd.application_id = ps.application_id
AND ps.adjustment_period_flag = ‘N’
AND ps.closing_status = ‘O’
AND (TRUNC(SYSDATE)
BETWEEN TRUNC (ps.start_date)
AND TRUNC (ps.end_date)
);

CURSOR c5_AR_to_org
IS
SELECT Count(*) Open_prd
FROM gl_period_statuses ps,
gl_sets_of_books sob,
fnd_application_vl fnd
WHERE ps.application_id = 222 — AR
AND sob.set_of_books_id = (SELECT set_of_books_id
FROM org_organization_definitions
WHERE organization_id = p_to_org_id)
AND fnd.application_id = ps.application_id
AND ps.adjustment_period_flag = ‘N’
AND ps.closing_status = ‘O’
AND (TRUNC(SYSDATE)
BETWEEN TRUNC (ps.start_date)
AND TRUNC (ps.end_date)
);

CURSOR c6_INV_to_org
IS
SELECT Count(*) Open_prd
FROM org_acct_periods oap ,
org_organization_definitions ood
WHERE oap.organization_id = ood.organization_id
AND oap.open_flag = ‘Y’
and oap.organization_id =p_to_org_id
AND (TRUNC(SYSDATE)
BETWEEN TRUNC(oap.period_start_date) AND TRUNC (oap.schedule_close_date));

CURSOR C7_AP_FROM_ORG
IS
SELECT Count(*) Open_prd
FROM gl_period_statuses ps,
gl_sets_of_books sob,
fnd_application_vl fnd
WHERE
ps.application_id = 200 — AP
AND sob.set_of_books_id = (SELECT set_of_books_id
FROM org_organization_definitions
WHERE organization_id =p_from_org_id)
AND fnd.application_id = ps.application_id
AND ps.adjustment_period_flag = ‘N’
AND ps.closing_status = ‘O’
AND (TRUNC(SYSDATE)
BETWEEN TRUNC (ps.start_date)
AND TRUNC (ps.end_date)
);

l_count NUMBER := 0;
l_ret_msg VARCHAR2(4000) := NULL;

BEGIN
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘ARUN’;
EXCEPTION
WHEN OTHERS
THEN
l_user_id := NULL;
END;

BEGIN
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘India Local Purchasing’
AND NVL (TRUNC (end_date), TRUNC (SYSDATE)) >= TRUNC (SYSDATE);
EXCEPTION
WHEN OTHERS
THEN
l_resp_id := NULL;
l_appl_id := NULL;
END;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
mo_global.set_policy_context (‘S’, l_org_id);

IF p_from_org_id > 0 AND p_to_org_id > 0
THEN

Select ood.Organization_name
INTO l_frm_org
FROM org_organization_definitions ood
WHERE ood.organization_id = p_from_org_id;

Select ood.Organization_name
INTO l_to_org
FROM org_organization_definitions ood
WHERE ood.organization_id = p_to_org_id;

———————-
— PO Period Check
———————-

l_count := 0;
l_err_msg := NULL;

For c1 in c1_po_from_org
LOOP
l_count := c1.Open_prd;
l_err_msg := l_frm_org||’ – PO Period NOT OPEN. ’;
END LOOP;