This work is licensed under a Creative Commons Attribution-NonCommercial 2.5 License.


Scripts for Oracle Workflow Training Lesson 3



The wft file for Oracle Workflow Training Lesson 3 can be downloaded by right clicking here

It is assumed that you have already worked upon the scripts from Training lesson 2.

--Create the table in which transaction progress will be tracked
create table xx_po_wf_progress
(
po_id INTEGER
,p_wf_action_stage VARCHAR2(200)
,p_wf_action_name VARCHAR2(200)
) ;

--Create the pl/sql package
CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS
PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE start_training_wf(p_po_id IN INTEGER);

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

END xxxx_po_wf_training_pkg;
/

--create the package body
CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS

PROCEDURE debug(p_text IN VARCHAR2) IS
BEGIN
-- apassi1(p_text);
NULL;
END debug;

FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS
BEGIN
IF p_po_id > 0
THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END is_po_validated;

PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
IF (funcmode != 'RUN')
THEN
RETURN;
END IF;
--Just a pseudo validation
--call a function here, and either assign COMPLETE:Y or COMPLETE:N

IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype
,itemkey => itemkey
,aname => 'PO_ID'))
THEN
RESULT := 'COMPLETE:Y';
ELSE
RESULT := 'COMPLETE:N';
END IF;
END is_po_valid;

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
v_role_email xxxx_po_headers.send_email_to%TYPE;
n_ctr INTEGER := 0;
BEGIN
v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_EMAIL'));
SELECT COUNT(*)
INTO n_ctr
FROM wf_local_roles
WHERE NAME = v_role_email;

IF n_ctr = 0
THEN
wf_directory.createadhocrole(role_name => v_role_email
,role_display_name => v_role_email
,role_description => v_role_email
,notification_preference => 'MAILHTML'
,email_address => v_role_email
,status => 'ACTIVE'
,expiration_date => NULL);
END IF;
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_ROLE'
,avalue => v_role_email);
RESULT := 'COMPLETE:Y';

RESULT := 'COMPLETE:Y';
END set_wf_approver_role;

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
UPDATE xxxx_po_headers
SET po_status = 'VALIDATED'
WHERE po_id = wf_engine.getitemattrnumber(itemtype
,itemkey
,'PO_ID');
RESULT := 'COMPLETE:Y';
END set_wf_status_to_validated;

PROCEDURE start_training_wf(p_po_id IN INTEGER) IS
l_itemtype VARCHAR2(30) := 'XXXXPTR';
l_itemkey VARCHAR2(300) := 'TRAINING-' || p_po_id;
CURSOR c_get IS
SELECT * FROM xxxx_po_headers WHERE po_id = p_po_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;

wf_engine.createprocess(l_itemtype
,l_itemkey
,'MAIN_TRAINING_PROCESS');

wf_engine.setitemuserkey(itemtype => l_itemtype
,itemkey => l_itemkey
,userkey => 'USERKEY: ' || l_itemkey);
wf_engine.setitemowner(itemtype => l_itemtype
,itemkey => l_itemkey
,owner => 'SYSADMIN');

wf_engine.setitemattrnumber(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_ID'
,avalue => p_po_id);

wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'SEND_TO_EMAIL'
,avalue => p_get.send_email_to);
wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_DESCRIPTION'
,avalue => p_get.po_description);
wf_engine.startprocess(l_itemtype
,l_itemkey);

END start_training_wf;

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_response_reason VARCHAR2(50);
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
l_activity_result_code := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_ACTION');
v_response_reason := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_REASON');
IF l_activity_result_code = 'XX_REJECTED' AND
v_response_reason IS NULL
THEN
RESULT := 'ERROR: You must enter rejection reason if rejecting.';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END xx_validate_response;

FUNCTION get_request_id_from_item_key(p_item_key IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN substr(p_item_key
,instr(p_item_key
,'-') + 1);
END get_request_id_from_item_key;

PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
n_po_id INTEGER;
v_action_name VARCHAR2(200);
v_action_stage VARCHAR2(200);
BEGIN
IF funcmode = 'RUN'
THEN
n_po_id := get_request_id_from_item_key(itemkey);

v_action_stage := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_STAGE');

v_action_name := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_NAME');
INSERT INTO xx_po_wf_progress
(po_id
,p_wf_action_stage
,p_wf_action_name)
VALUES
(n_po_id
,v_action_stage
,v_action_name);
END IF;
RESULT := 'COMPLETE:Y';
END log_status_of_action;

END xxxx_po_wf_training_pkg;
/

--initiate the workflow now
DECLARE
n_new_po_id INTEGER;
BEGIN
SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual;
INSERT INTO xxxx_po_headers
(po_id
,vendor_id
,suggested_vendor_id
,po_description
,po_status
,comments_from_approver
,send_email_to)
VALUES
(n_new_po_id --po_id
,10000 --vendor_id
,NULL --suggested_vendor_id
,'This is PO Training Description' --po_description
,'INITIAL' --po_status
,NULL --comments_from_approver
,'ANILPASSI@GMAIL.com' --send_email_to
);
xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id);
COMMIT;
END;
/
Scripts for Workflow Training Lesson 2

This page contains all the required Scripts and Workflow files prepared for Workflow Training Lesson 2. Click here if you wish to return to Oracle Workflow Training Lesson 2


Workflow .wft File For Training Lesson 2
Rightclick to download the above file and save this to database.


--create the table

Create table xxxx_po_headers
(
po_id INTEGER
,vendor_id INTEGER
,suggested_vendor_id INTEGER
,po_description VARCHAR2(250)
,po_status VARCHAR2(30)
,comments_from_approver VARCHAR2(2000)
,send_email_to VARCHAR2(300)
) ;

-----------------
--Create the package
-----------------
CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS
PROCEDURE is_po_valid
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

PROCEDURE set_wf_approver_role
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

PROCEDURE set_wf_status_to_validated
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

PROCEDURE start_training_wf(p_po_id IN INTEGER);

PROCEDURE xx_validate_response (
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
);

END xxxx_po_wf_training_pkg;
/

--------------------
--Create the package body
--------------------
CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS

PROCEDURE debug(p_text IN VARCHAR2) IS
BEGIN
-- apassi1(p_text);
NULL;
END debug;

FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS
BEGIN
IF p_po_id > 0
THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END is_po_validated;

PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
IF (funcmode != 'RUN')
THEN
RETURN;
END IF;
--Just a pseudo validation
--call a function here, and either assign COMPLETE:Y or COMPLETE:N

IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype
,itemkey => itemkey
,aname => 'PO_ID'))
THEN
RESULT := 'COMPLETE:Y';
ELSE
RESULT := 'COMPLETE:N';
END IF;
END is_po_valid;

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
v_role_email xxxx_po_headers.send_email_to%TYPE;
n_ctr INTEGER := 0;
BEGIN
v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_EMAIL'));
SELECT COUNT(*)
INTO n_ctr
FROM wf_local_roles
WHERE NAME = v_role_email;

IF n_ctr = 0
THEN
wf_directory.createadhocrole(role_name => v_role_email
,role_display_name => v_role_email
,role_description => v_role_email
,notification_preference => 'MAILHTML'
,email_address => v_role_email
,status => 'ACTIVE'
,expiration_date => NULL);
END IF;
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_ROLE'
,avalue => v_role_email);
RESULT := 'COMPLETE:Y';

RESULT := 'COMPLETE:Y';
END set_wf_approver_role;

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
UPDATE xxxx_po_headers
SET po_status = 'VALIDATED'
WHERE po_id = wf_engine.getitemattrnumber(itemtype
,itemkey
,'PO_ID');
RESULT := 'COMPLETE:Y';
END set_wf_status_to_validated;

PROCEDURE start_training_wf(p_po_id IN INTEGER) IS
l_itemtype VARCHAR2(30) := 'XXXXPTR';
l_itemkey VARCHAR2(300) := 'TRAINING-' || p_po_id;
CURSOR c_get IS
SELECT * FROM xxxx_po_headers WHERE po_id = p_po_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;

wf_engine.createprocess(l_itemtype
,l_itemkey
,'MAIN_TRAINING_PROCESS');

wf_engine.setitemuserkey(itemtype => l_itemtype
,itemkey => l_itemkey
,userkey => 'USERKEY: ' || l_itemkey);
wf_engine.setitemowner(itemtype => l_itemtype
,itemkey => l_itemkey
,owner => 'SYSADMIN');

wf_engine.setitemattrnumber(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_ID'
,avalue => p_po_id);

wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'SEND_TO_EMAIL'
,avalue => p_get.send_email_to);
wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_DESCRIPTION'
,avalue => p_get.po_description);
wf_engine.startprocess(l_itemtype
,l_itemkey);

END start_training_wf;

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_response_reason VARCHAR2(50);
BEGIN
debug('funcmode is ' || funcmode);
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;

l_activity_result_code := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_ACTION');

debug('l_activity_result_code is ' || l_activity_result_code);
v_response_reason := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_REASON');
debug('v_response_reason is ' || v_response_reason);
IF l_activity_result_code = 'XX_REJECTED' AND
v_response_reason IS NULL
THEN
RESULT := 'ERROR: You must enter rejection reason if rejecting.';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END xx_validate_response;
END xxxx_po_wf_training_pkg;
/


------
--Now Run the Workflow
--Ensure that wft file has been loaded
------
DECLARE
n_new_po_id INTEGER;
BEGIN
SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual;
INSERT INTO xxxx_po_headers
(po_id
,vendor_id
,suggested_vendor_id
,po_description
,po_status
,comments_from_approver
,send_email_to)
VALUES
(n_new_po_id --po_id
,10000 --vendor_id
,NULL --suggested_vendor_id
,'This is PO Training Description' --po_description
,'INITIAL' --po_status
,NULL --comments_from_approver
,'ANILPASSI@GMAIL.com' --send_email_to
);
xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id);
COMMIT;
END;
/

Tuesday, September 26, 2006

Oracle Apps FND User name Locks Record



Ever wondered how to find the person in Oracle Apps that locks a specific record?
Its simple, I find this SQL quite useful. Blaah, you will know the user that is sleeping over the record by locking it. This SQL joins the locking session to FND_USER, so as to display the APPS User Name of the person that locks the record.

In the example below, lets say we need to find the list of people that are locking tables in Purchasing. Aslo, lets assume we need to find the screen/conc process that locks the record.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_user_name
,fl.start_time
,vs.module
,vs.machine
,vs.osuser
,vs.sid
,vp.pid
,vp.spid AS os_process_to_kill
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE 'PO%';


OWNER PO
OBJECT_NAME PO_HEADERS_ALL
OBJECT_TYPE TABLE
LOCKING_USER_NAME PASSIANIL
START_TIME 26-SEP-2006
MODULE POXPOEPO
MACHINE xxappsserver01
OSUSER Xxtstuser
SID 2785
PID 661
OS_PROCESS_TO_KILL 27975
SERIAL# 5518
STATUS INACTIVE
SADDR 000000077DD814F0
AUDSID 13482253
PROCESS 3563

Oracle APPS Warn Bounce of Environment to Recent Users

Very often, we find ourselves in a need to bounce the development or test environment. This takes some developers/testers etc by surprise.
Hence I developed a simple script to list the list of recent users that use the Oracle Apps instance. We can then email the people returned by this list, to inform them of the system unavailability.




SELECT ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address) AS email_address
FROM per_people_x ppx, fnd_user fu, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppx.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST')
AND fu.user_name != 'PASSIA' --exclude anil
GROUP BY ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address)
ORDER BY 2
Oracle Activity on FND LOGINS FND USER
My client asked me to write a quick SQL to find out the load on the Production system, in terms of User activity. This was required for be broken down by External Logins(iSourcing, Students etc) and Internal Employees.

Use the SQL below
===============
SELECT COUNT(fl.login_id) count_of_logins
,trunc(fl.start_time) start_date
,COUNT(DISTINCT fl.user_id) count_users
,decode(fu.employee_id
,NULL
,'EXTERNAL'
,'INTERNAL') AS emp_type
FROM fnd_user fu, fnd_logins fl
WHERE fl.start_time > SYSDATE - 10
AND fl.user_id NOT IN (6, 0, -1)
AND fu.user_id = fl.user_id
GROUP BY trunc(start_time)
,decode(fu.employee_id
,NULL
,'EXTERNAL'
,'INTERNAL')
ORDER BY 2

In fact after running this, I discovered that OAM gives a better breakdown, i.e. it will list the breakdown at Module level.
That list will be available from Sitemap Tab, after having logged into OAM ( Oracle Applications Manager ).

Tuesday, September 19, 2006

Oracle API for GL Interface Package Body



CREATE OR REPLACE PACKAGE BODY xx_import_xfer_gl_iface_api IS
/* $Header: XXXXXXXX.pkh, Version 1.0, 07-FEB-2003 UKEXPI $
***********************************************************************
* *
* History Log *
* *
***********************************************************************
* *
* App/Release : Oracle e-Business Suite RXXX *
* Oracle App : SHORTNAME - FULL_NAME *
* Module : SHORTNAME - DESCRIPTION *
* Author : Anil Passi(Go 4 Gold), *
* Version : 1.0 *
* Revised : 11-SEP-2005 *
* Description : This package delivers GL interface API *
* *
* Change History Log *
* ================== *
* *
* Version Date Author Change *
* ======= =========== ============ ================================*
*
* 1.0 10-OCT-2002 A. Passi Initial Version
* 1.2 12-OCT-2002 A. Passi Added ability to "transfer_to_gl" such that
both the credit and debit can either happen
in single API call, or multiple calls(when an
amount is distributed).
* 1.3 24-Jan-2006 A. Passi Add the trx_number to the batch details.
This is so because the recurring journal will
have incremental trx_number for each period,
with the batch number remaining the same.
* 1.4 08-Sep-2005 A. Passi Move the journal to next
period if current period is closed
**********************************************************************/

g_sob_id INTEGER := fnd_profile.VALUE('GL_SET_OF_BKS_ID');
g_source_name gl_je_headers.je_source%TYPE;
g_error_flag VARCHAR2(1) := NULL;

g_xx_transaction_source CONSTANT VARCHAR2(80) := 'XX_COST_XFER';

--move these three into the config screen.
--$?$
g_gl_user_je_source_name CONSTANT VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_JE_SOURCE_NAME')
,'Manual');
--$?$
g_gl_user_je_category_name CONSTANT VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_JE_CATEGORY_NAME')
,'Miscellaneous Transaction');
--$?$
g_conversion_type VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_RATE_CONVERSION_TYPE')
,'Corporate');

g_gl_batch_prefix VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_BATCH_PREFIX')
,'XX_GL_FEED_NAME:');

g_gl_appl_name CONSTANT VARCHAR2(30) := 'SQLGL';
g_conversion_type_code VARCHAR2(30);

g_debug_procedure_context VARCHAR2(30);
g_debug_header_context CONSTANT VARCHAR2(80) := 'xx_gl_feed.plsql.xx_import_xfer_gl_iface_api.';

--Programming variables
--g_group_id and g_interface_run_id will be set each time separately for a batch
g_group_id NUMBER;
g_interface_run_id NUMBER;

PROCEDURE new_gl_interface_batch IS
BEGIN
g_group_id := NULL;
g_interface_run_id := NULL;
END new_gl_interface_batch;

PROCEDURE debug_stmt(p_msg IN VARCHAR2) IS
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => g_debug_header_context ||
g_debug_procedure_context
,message => p_msg);
IF fnd_global.conc_request_id > 0 AND
fnd_profile.VALUE('AFLOG_ENABLED') = 'Y'
THEN
fnd_file.put_line(which => fnd_file.log
,buff => p_msg);
END IF;

END debug_stmt;

PROCEDURE debug_begin_procedure IS
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => g_debug_header_context ||
g_debug_procedure_context
,message => 'Begin ' || g_debug_procedure_context);
END debug_begin_procedure;

PROCEDURE debug_end_procedure IS
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => g_debug_header_context ||
g_debug_procedure_context
,message => 'End ' || g_debug_procedure_context);
END debug_end_procedure;

PROCEDURE set_debug_context(p_procedure_name IN VARCHAR2) IS
BEGIN
g_debug_procedure_context := p_procedure_name;
debug_begin_procedure;
END set_debug_context;

PROCEDURE log_error(p_batch_header_id IN INTEGER
,p_trx_header_id IN INTEGER
,p_error_message IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF fnd_global.conc_request_id > 0
THEN
fnd_file.put_line(fnd_file.output
,'ERROR::' || p_error_message);
g_error_flag := 'Y';
END IF;
--log error into your error logging table
/* INSERT INTO xx_gl_api_error_logs
(error_log_id
,batch_header_id
,trx_header_id
,error_message
,creation_date
,fnd_request_id)
VALUES
(xx_error_log_s.NEXTVAL -- x_error_log_id
,p_batch_header_id
,p_trx_header_id
,substr(p_error_message, 1, 2000)
,SYSDATE --creation_date
,fnd_global.conc_request_id --fnd_request_id
);
*/
debug_stmt('ERROR: ' || p_error_message);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
debug_stmt('ERROR: in Log_error');
ROLLBACK;
END log_error;

FUNCTION get_sob_id RETURN INTEGER IS
BEGIN
set_debug_context('get_sob_id');
debug_stmt('get_sob_id() Returning g_sob_id=>' || g_sob_id);
RETURN g_sob_id;
debug_end_procedure;
END get_sob_id;

PROCEDURE set_sob_id(p_sob_id IN INTEGER) IS
BEGIN
set_debug_context('set_sob_id');

g_sob_id := p_sob_id;
debug_stmt('set_sob_id() Setting g_sob_id=>' || g_sob_id);
debug_end_procedure;
END set_sob_id;

PROCEDURE set_source_name(p_source_name IN VARCHAR2) IS
BEGIN
set_debug_context('set_source_name');

g_source_name := p_source_name;
debug_stmt('set_source_name() Setting g_source_name=>' ||
g_source_name);
debug_end_procedure;
END set_source_name;

FUNCTION get_source_name RETURN VARCHAR2 IS
BEGIN
RETURN g_source_name;
END get_source_name;

FUNCTION get_xx_transaction_source RETURN VARCHAR2 IS
BEGIN
RETURN g_xx_transaction_source;
END get_xx_transaction_source;

PROCEDURE populate_interface_control(p_user_je_source_name IN VARCHAR2
,p_group_id IN OUT NUMBER
,p_set_of_books_id NUMBER
,p_interface_run_id IN OUT NUMBER) IS
re_use_gl_interface_control EXCEPTION;
BEGIN
set_debug_context('populate_interface_control');

IF g_group_id IS NOT NULL AND g_interface_run_id IS NOT NULL
THEN
p_group_id := g_group_id;
p_interface_run_id := g_interface_run_id;
RAISE re_use_gl_interface_control;
END IF;
gl_journal_import_pkg.populate_interface_control(user_je_source_name => p_user_je_source_name
,group_id => p_group_id
,set_of_books_id => p_set_of_books_id
,interface_run_id => p_interface_run_id);

g_group_id := p_group_id;
g_interface_run_id := p_interface_run_id;

debug_stmt('Returning New Group p_group_id=>' || p_group_id ||
' p_interface_run_id=>' || p_interface_run_id);

debug_end_procedure;
EXCEPTION
WHEN re_use_gl_interface_control THEN
debug_stmt('Returning Global Group g_group_id=>' || g_group_id ||
' g_interface_run_id=>' || g_interface_run_id);
debug_end_procedure;
END populate_interface_control;

PROCEDURE interface_record IS
n_group_id INTEGER;
n_sob_id INTEGER;
n_interface_run_id INTEGER;
BEGIN
set_debug_context('interface_record');

n_sob_id := get_sob_id;
populate_interface_control(p_user_je_source_name => g_source_name
,p_group_id => n_group_id
,p_set_of_books_id => n_sob_id
,p_interface_run_id => n_interface_run_id);
debug_end_procedure;
END interface_record;

PROCEDURE insert_statement(p_gl_int_rec IN OUT NOCOPY g_gl_int_type_rec) IS
BEGIN
set_debug_context('insert_statement');

INSERT INTO gl_interface
(reference_date
,attribute20
,CONTEXT
,context2
,invoice_date
,tax_code
,invoice_identifier
,invoice_amount
,context3
,ussgl_transaction_code
,descr_flex_error_message
,jgzz_recon_ref
,segment23
,segment24
,segment25
,segment26
,segment27
,segment28
,segment29
,segment30
,entered_dr
,entered_cr
,accounted_dr
,accounted_cr
,transaction_date
,reference1
,reference2
,reference3
,reference4
,reference5
,reference6
,reference7
,reference8
,reference9
,reference10
,reference11
,reference12
,reference13
,reference14
,reference15
,reference16
,reference17
,reference18
,reference19
,reference20
,reference21
,reference22
,reference23
,reference24
,reference25
,reference26
,reference27
,reference28
,reference29
,reference30
,je_batch_id
,period_name
,je_header_id
,je_line_num
,chart_of_accounts_id
,functional_currency_code
,code_combination_id
,date_created_in_gl
,warning_code
,status_description
,stat_amount
,group_id
,request_id
,subledger_doc_sequence_id
,subledger_doc_sequence_value
,attribute1
,attribute2
,gl_sl_link_id
,gl_sl_link_table
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,status
,set_of_books_id
,accounting_date
,currency_code
,date_created
,created_by
,actual_flag
,user_je_category_name
,user_je_source_name
,currency_conversion_date
,encumbrance_type_id
,budget_version_id
,user_currency_conversion_type
,currency_conversion_rate
,average_journal_flag
,originating_bal_seg_value
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,segment10
,segment11
,segment12
,segment13
,segment14
,segment15
,segment16
,segment17
,segment18
,segment19
,segment20
,segment21
,segment22)
VALUES
(p_gl_int_rec.reference_date -- reference_date
,p_gl_int_rec.attribute20 -- attribute20
,p_gl_int_rec.CONTEXT -- context
,p_gl_int_rec.context2 -- context2
,p_gl_int_rec.invoice_date -- invoice_date
,p_gl_int_rec.tax_code -- tax_code
,p_gl_int_rec.invoice_identifier -- invoice_identifier
,p_gl_int_rec.invoice_amount -- invoice_amount
,p_gl_int_rec.context3 -- context3
,p_gl_int_rec.ussgl_transaction_code -- ussgl_transaction_code
,p_gl_int_rec.descr_flex_error_message -- descr_flex_error_message
,p_gl_int_rec.jgzz_recon_ref -- jgzz_recon_ref
,p_gl_int_rec.segment23 -- segment23
,p_gl_int_rec.segment24 -- segment24
,p_gl_int_rec.segment25 -- segment25
,p_gl_int_rec.segment26 -- segment26
,p_gl_int_rec.segment27 -- segment27
,p_gl_int_rec.segment28 -- segment28
,p_gl_int_rec.segment29 -- segment29
,p_gl_int_rec.segment30 -- segment30
,p_gl_int_rec.entered_dr -- entered_dr
,p_gl_int_rec.entered_cr -- entered_cr
,p_gl_int_rec.accounted_dr -- accounted_dr
,p_gl_int_rec.accounted_cr -- accounted_cr
,p_gl_int_rec.transaction_date -- transaction_date
,p_gl_int_rec.reference1 -- reference1
,p_gl_int_rec.reference2 -- reference2
,p_gl_int_rec.reference3 -- reference3
,p_gl_int_rec.reference4 -- reference4
,p_gl_int_rec.reference5 -- reference5
,p_gl_int_rec.reference6 -- reference6
,p_gl_int_rec.reference7 -- reference7
,p_gl_int_rec.reference8 -- reference8
,p_gl_int_rec.reference9 -- reference9
,p_gl_int_rec.reference10 -- reference10
,p_gl_int_rec.reference11 -- reference11
,p_gl_int_rec.reference12 -- reference12
,p_gl_int_rec.reference13 -- reference13
,p_gl_int_rec.reference14 -- reference14
,p_gl_int_rec.reference15 -- reference15
,p_gl_int_rec.reference16 -- reference16
,p_gl_int_rec.reference17 -- reference17
,p_gl_int_rec.reference18 -- reference18
,p_gl_int_rec.reference19 -- reference19
,p_gl_int_rec.reference20 -- reference20
,p_gl_int_rec.reference21 -- reference21
,p_gl_int_rec.reference22 -- reference22
,p_gl_int_rec.reference23 -- reference23
,p_gl_int_rec.reference24 -- reference24
,p_gl_int_rec.reference25 -- reference25
,p_gl_int_rec.reference26 -- reference26
,p_gl_int_rec.reference27 -- reference27
,p_gl_int_rec.reference28 -- reference28
,p_gl_int_rec.reference29 -- reference29
,p_gl_int_rec.reference30 -- reference30
,p_gl_int_rec.je_batch_id -- je_batch_id
,p_gl_int_rec.period_name -- period_name
,p_gl_int_rec.je_header_id -- je_header_id
,p_gl_int_rec.je_line_num -- je_line_num
,p_gl_int_rec.chart_of_accounts_id -- chart_of_accounts_id
,p_gl_int_rec.functional_currency_code -- functional_currency_code
,p_gl_int_rec.code_combination_id -- code_combination_id
,p_gl_int_rec.date_created_in_gl -- date_created_in_gl
,p_gl_int_rec.warning_code -- warning_code
,p_gl_int_rec.status_description -- status_description
,p_gl_int_rec.stat_amount -- stat_amount
,p_gl_int_rec.group_id -- group_id
,p_gl_int_rec.request_id -- request_id
,p_gl_int_rec.subledger_doc_sequence_id
-- subledger_doc_sequence_id
,p_gl_int_rec.subledger_doc_sequence_value
-- subledger_doc_sequence_value
,p_gl_int_rec.attribute1 -- attribute1
,p_gl_int_rec.attribute2 -- attribute2
,p_gl_int_rec.gl_sl_link_id -- gl_sl_link_id
,p_gl_int_rec.gl_sl_link_table -- gl_sl_link_table
,p_gl_int_rec.attribute3 -- attribute3
,p_gl_int_rec.attribute4 -- attribute4
,p_gl_int_rec.attribute5 -- attribute5
,p_gl_int_rec.attribute6 -- attribute6
,p_gl_int_rec.attribute7 -- attribute7
,p_gl_int_rec.attribute8 -- attribute8
,p_gl_int_rec.attribute9 -- attribute9
,p_gl_int_rec.attribute10 -- attribute10
,p_gl_int_rec.attribute11 -- attribute11
,p_gl_int_rec.attribute12 -- attribute12
,p_gl_int_rec.attribute13 -- attribute13
,p_gl_int_rec.attribute14 -- attribute14
,p_gl_int_rec.attribute15 -- attribute15
,p_gl_int_rec.attribute16 -- attribute16
,p_gl_int_rec.attribute17 -- attribute17
,p_gl_int_rec.attribute18 -- attribute18
,p_gl_int_rec.attribute19 -- attribute19
,p_gl_int_rec.status -- status
,p_gl_int_rec.set_of_books_id -- set_of_books_id
,p_gl_int_rec.accounting_date -- accounting_date
,p_gl_int_rec.currency_code -- currency_code
,p_gl_int_rec.date_created -- date_created
,p_gl_int_rec.created_by -- created_by
,p_gl_int_rec.actual_flag -- actual_flag
,p_gl_int_rec.user_je_category_name -- user_je_category_name
,p_gl_int_rec.user_je_source_name -- user_je_source_name
,p_gl_int_rec.currency_conversion_date -- currency_conversion_date
,p_gl_int_rec.encumbrance_type_id -- encumbrance_type_id
,p_gl_int_rec.budget_version_id -- budget_version_id
,p_gl_int_rec.user_currency_conversion_type
-- user_currency_conversion_type
,p_gl_int_rec.currency_conversion_rate -- currency_conversion_rate
,p_gl_int_rec.average_journal_flag -- average_journal_flag
,p_gl_int_rec.originating_bal_seg_value
-- originating_bal_seg_value
,p_gl_int_rec.segment1 -- segment1
,p_gl_int_rec.segment2 -- segment2
,p_gl_int_rec.segment3 -- segment3
,p_gl_int_rec.segment4 -- segment4
,p_gl_int_rec.segment5 -- segment5
,p_gl_int_rec.segment6 -- segment6
,p_gl_int_rec.segment7 -- segment7
,p_gl_int_rec.segment8 -- segment8
,p_gl_int_rec.segment9 -- segment9
,p_gl_int_rec.segment10 -- segment10
,p_gl_int_rec.segment11 -- segment11
,p_gl_int_rec.segment12 -- segment12
,p_gl_int_rec.segment13 -- segment13
,p_gl_int_rec.segment14 -- segment14
,p_gl_int_rec.segment15 -- segment15
,p_gl_int_rec.segment16 -- segment16
,p_gl_int_rec.segment17 -- segment17
,p_gl_int_rec.segment18 -- segment18
,p_gl_int_rec.segment19 -- segment19
,p_gl_int_rec.segment20 -- segment20
,p_gl_int_rec.segment21 -- segment21
,p_gl_int_rec.segment22 -- segment22
);
debug_stmt(SQL%ROWCOUNT || ' records inserted into GL_INTERFACE');
debug_end_procedure;
END insert_statement;

FUNCTION check_ccid(p_sob_id IN INTEGER
,p_ccid IN INTEGER) RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x'
FROM gl_code_combinations gcc, gl_sets_of_books gsob
WHERE gsob.set_of_books_id = p_sob_id
AND gcc.code_combination_id = p_ccid
AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
AND gcc.enabled_flag = 'Y';

p_check c_check%ROWTYPE;
BEGIN
set_debug_context('check_ccid');
debug_stmt('p_sob_id=>' || p_sob_id || ' p_ccid=>' || p_ccid);
OPEN c_check;
FETCH c_check
INTO p_check;

IF c_check%NOTFOUND
THEN
CLOSE c_check;
debug_stmt('check_ccid() RETURNED FALSE;');
RETURN FALSE;
END IF;

CLOSE c_check;
debug_stmt('check_ccid() RETURNED TRUE;');
RETURN TRUE;
debug_end_procedure;
END check_ccid;

FUNCTION get_gl_application_id(p_appl_name IN VARCHAR2) RETURN INTEGER IS
CURSOR c_check IS
SELECT application_id
FROM fnd_application_vl
WHERE application_short_name = p_appl_name;

p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
RETURN p_check.application_id;
END get_gl_application_id;

FUNCTION check_conversion_type RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x'
FROM gl_daily_conversion_types
WHERE user_conversion_type = g_conversion_type;

p_check c_check%ROWTYPE;
BEGIN
set_debug_context('check_conversion_type');

debug_stmt('g_conversion_type=>' || g_conversion_type);
OPEN c_check;
FETCH c_check
INTO p_check;

IF c_check%NOTFOUND
THEN
CLOSE c_check;
debug_stmt('check_conversion_type() RETURNED FALSE;');
RETURN FALSE;
END IF;

CLOSE c_check;
debug_stmt('check_conversion_type() RETURNED TRUE;');
RETURN TRUE;
END check_conversion_type;

FUNCTION check_gl_source(p_gl_user_je_source_name IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x'
FROM gl_je_sources
WHERE user_je_source_name =
nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name);

p_check c_check%ROWTYPE;
BEGIN
set_debug_context('check_gl_source');
debug_stmt('g_gms_cost_xfer_source=>' || g_gl_user_je_source_name);
OPEN c_check;
FETCH c_check
INTO p_check;

IF c_check%NOTFOUND
THEN
CLOSE c_check;
debug_stmt('check_gl_source() RETURNED FALSE;');
RETURN FALSE;
END IF;

CLOSE c_check;
debug_stmt('check_gl_source() RETURNED TRUE;');
RETURN TRUE;
END check_gl_source;

FUNCTION get_next_available_open_date(p_accounting_date IN DATE
,p_gl_appl_id IN NUMBER
,p_sob_id IN NUMBER)
RETURN DATE IS
CURSOR c_get IS
SELECT gps.start_date
FROM gl_period_statuses gps
WHERE gps.application_id = p_gl_appl_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ('O', 'F')
AND trunc(gps.start_date) > p_accounting_date
ORDER BY trunc(gps.start_date);
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.start_date;
END get_next_available_open_date;

FUNCTION validate_accounting_date(p_accounting_date IN OUT DATE
,p_gl_appl_id IN NUMBER
,p_sob_id IN NUMBER
,p_batch_header_id IN NUMBER
,p_trx_header_id IN NUMBER)
RETURN BOOLEAN IS
v_count NUMBER := 0;
v_accounting_date DATE;
BEGIN
set_debug_context('validate_accounting_date');
v_accounting_date := p_accounting_date;
debug_stmt('p_accounting_date=>' || p_accounting_date ||
' p_gl_appl_id=>' || p_gl_appl_id || ' p_sob_id=>' ||
p_sob_id);
SELECT COUNT(*)
INTO v_count
FROM gl_period_statuses gps
WHERE gps.application_id = p_gl_appl_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ('O', 'F')
AND trunc(p_accounting_date) BETWEEN
nvl(trunc(gps.start_date)
,trunc(p_accounting_date)) AND
nvl(trunc(gps.end_date)
,trunc(p_accounting_date));

IF v_count > 0
THEN
debug_stmt('validate_accounting_date() RETURNED TRUE');
RETURN TRUE;
ELSE
debug_stmt('validate_accounting_date() Cant find open Period for ' ||
p_accounting_date);
--try to find next available date now
p_accounting_date := get_next_available_open_date(p_accounting_date => v_accounting_date
,p_gl_appl_id => p_gl_appl_id
,p_sob_id => p_sob_id);
IF p_accounting_date IS NULL
THEN
log_error(p_batch_header_id => p_batch_header_id
,p_trx_header_id => p_trx_header_id
,p_error_message => 'Accounting Date ' ||
to_char(v_accounting_date
,'DD-MON-YYYY') ||
' does not belong to a Open Period');

/* --!!!!!Abort the process here
raise_application_error(-20001
,v_accounting_date ||
' Can not find open/future period for this date. Batch Number ' ||
p_batch_header_id
||
' . There must be at least one future/open period for this process to run'
);
*/
RETURN FALSE;
ELSE
debug_stmt('validate_accounting_date() Using next available open date ' ||
p_accounting_date);
RETURN TRUE;
END IF;
END IF;

END validate_accounting_date;

-- Function to validate currency code
FUNCTION validate_currency(p_currency_code IN VARCHAR2
,p_accounting_date IN DATE) RETURN BOOLEAN IS
v_count NUMBER := 0;
BEGIN
set_debug_context('validate_currency');
SELECT COUNT(*)
INTO v_count
FROM fnd_currencies fc
WHERE fc.currency_code = p_currency_code
AND enabled_flag = 'Y'
AND p_accounting_date BETWEEN
nvl(start_date_active
,p_accounting_date) AND
nvl(end_date_active
,p_accounting_date);

IF v_count > 0
THEN
debug_stmt('validate_currency() RETURNED TRUE');
RETURN TRUE;
ELSE
debug_stmt('validate_currency() RETURNED FLASE');
RETURN FALSE;
END IF;
END validate_currency;

PROCEDURE set_conversion_type_code IS
CURSOR c_get IS
SELECT conversion_type
FROM gl_daily_conversion_types
WHERE user_conversion_type = g_conversion_type;
BEGIN
set_debug_context('set_conversion_type_code');
debug_stmt('g_conversion_type=>' || g_conversion_type);
OPEN c_get;
FETCH c_get
INTO g_conversion_type_code;
CLOSE c_get;
debug_stmt('g_conversion_type_code=>' || g_conversion_type_code);
debug_end_procedure;
END set_conversion_type_code;

FUNCTION get_batch_employee_number(p_batch_header_id IN INTEGER)
RETURN VARCHAR2 IS
CURSOR c_get IS
SELECT ppx.employee_number
FROM per_people_x ppx
--,"your own table here for the feed " bhdr
--WHERE ppx.person_id = bhdr.employee_id
;
p_get c_get%ROWTYPE;
BEGIN
--change this function as per your need
--i am returning a constant value
RETURN '101010';
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;
RETURN p_get.employee_number;
END get_batch_employee_number;

FUNCTION get_gl_je_reference1(p_batch_header_id IN INTEGER
,p_trx_number IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN g_gl_batch_prefix || p_batch_header_id || '-' || p_trx_number || ':' || get_batch_employee_number(p_batch_header_id) || ':';
END get_gl_je_reference1;

FUNCTION get_sob_currency(p_sob_id IN INTEGER) RETURN VARCHAR2 IS
CURSOR c_check IS
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob_id;

p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
RETURN p_check.currency_code;
END get_sob_currency;

/**************************************************
Main code for gms_cost_xfer GL Interface begins here
**************************************************/
PROCEDURE transfer_to_gl(p_sob_id IN INTEGER
,p_batch_header_id IN INTEGER
,p_trx_header_id IN INTEGER
,p_trx_line_id IN INTEGER
,p_date IN OUT DATE
,p_currency_code IN VARCHAR2
,p_amount IN NUMBER
,p_dr_account IN INTEGER
,p_cr_account IN INTEGER
,p_journal_name IN VARCHAR2
,p_header_description IN VARCHAR2
,p_line_description IN VARCHAR2
,p_success_flag OUT BOOLEAN
,p_first_record_in_batch IN VARCHAR2
,p_submit_gl_interface IN VARCHAR2
,p_gl_user_je_source_name IN VARCHAR2
,p_gl_user_je_category_name IN VARCHAR2
,p_trx_number IN VARCHAR2) IS
v_gl_appl_id NUMBER;
l_gl_int_type_rec g_gl_int_type_rec;
v_conc_id INTEGER;
conversion_rate_does_not_exist EXCEPTION;
invalid_conversion_type EXCEPTION;
invalid_dr_ccid EXCEPTION;
invalid_cr_ccid EXCEPTION;
invalid_currency_code EXCEPTION;
not_in_open_period EXCEPTION;
invalid_gl_source EXCEPTION;
v_func_curr VARCHAR2(30);
v_group_id NUMBER;
v_interface_run_id NUMBER;

v_ret_val BOOLEAN;
v_request_id INTEGER;
phase VARCHAR2(100);
status VARCHAR2(100);
dev_phase VARCHAR2(100);
dev_status VARCHAR2(100);
v_message VARCHAR2(100);
v_bool BOOLEAN;
v_old_status VARCHAR2(30);

BEGIN
set_debug_context('transfer_to_gl');

SAVEPOINT gms_cost_xfer_gl_int;

set_conversion_type_code;

/* Get the GL Application ID */
v_gl_appl_id := get_gl_application_id(p_appl_name => g_gl_appl_name);

IF p_first_record_in_batch = 'Y'
THEN
debug_stmt('p_first_record_in_batch is Y. Hence will reset global variables to initiate new batch');
new_gl_interface_batch;
ELSE
debug_stmt('Not being the first record, accumulating this line to existing batch.');
END IF;
/* May be GL team forgets to create source for gms_cost_xfer, lets see...*/
IF NOT check_gl_source(p_gl_user_je_source_name)
THEN
RAISE invalid_gl_source;
END IF;

/* During the design phase in Swiss Re the conversion type has already been changed once
Lets not take any chances and check is this is a valid conversion type */
IF NOT check_conversion_type
THEN
RAISE invalid_conversion_type;
END IF;

/* Lets validate the accounting date.
Although gms_cost_xfer almost always passes the current date.
But still this is a good to have validation. */
IF NOT validate_accounting_date(p_accounting_date => p_date
,p_gl_appl_id => v_gl_appl_id
,p_sob_id => p_sob_id
,p_batch_header_id => p_batch_header_id
,p_trx_header_id => p_trx_header_id)
THEN
RAISE not_in_open_period;
END IF;

/* Lets validate the currency code.
Although gms_cost_xfer will certainly pass correct curr code*/
IF NOT validate_currency(p_accounting_date => p_date
,p_currency_code => p_currency_code)
THEN
RAISE invalid_currency_code;
END IF;

v_func_curr := get_sob_currency(p_sob_id);

IF nvl(v_func_curr
,'XX') != nvl(p_currency_code
,'XX')
THEN
IF 'N' =
gl_currency_api.rate_exists(x_from_currency => p_currency_code
,x_to_currency => v_func_curr
,x_conversion_date => p_date
,x_conversion_type => g_conversion_type_code)
THEN
RAISE conversion_rate_does_not_exist;
ELSE
l_gl_int_type_rec.user_currency_conversion_type := g_conversion_type;
l_gl_int_type_rec.currency_conversion_date := p_date;
END IF;
END IF;

set_sob_id(p_sob_id => p_sob_id);
set_source_name(p_source_name => nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name));
populate_interface_control(p_user_je_source_name => nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name)
,p_group_id => v_group_id
,p_set_of_books_id => p_sob_id
,p_interface_run_id => v_interface_run_id);

--batch name; part of the je batch name
l_gl_int_type_rec.reference1 := get_gl_je_reference1(p_batch_header_id => p_batch_header_id
,p_trx_number => p_trx_number);

--je batch description
l_gl_int_type_rec.reference2 := p_batch_header_id || ': ' ||
p_header_description || '-' ||
p_trx_number;

--part of the je header name
l_gl_int_type_rec.reference4 := p_journal_name || '-' || p_trx_number;

--je header description will be the description of the gms_cost_xfer receipt
l_gl_int_type_rec.reference5 := p_batch_header_id || ': ' ||
nvl(p_header_description
,p_journal_name) || '-' ||
p_trx_number;
--if the description is left blank, then use the Journal Name instead

--je lines description will be the description of the gms_cost_xfer receipt
l_gl_int_type_rec.reference10 := p_line_description;

l_gl_int_type_rec.reference_date := p_date;
l_gl_int_type_rec.reference21 := p_header_description;
l_gl_int_type_rec.group_id := v_group_id;
l_gl_int_type_rec.set_of_books_id := p_sob_id;
l_gl_int_type_rec.user_je_source_name := nvl(p_gl_user_je_source_name
,g_gl_user_je_source_name);
l_gl_int_type_rec.user_je_category_name := nvl(p_gl_user_je_category_name
,g_gl_user_je_category_name);
l_gl_int_type_rec.accounting_date := p_date;
l_gl_int_type_rec.transaction_date := p_date;
l_gl_int_type_rec.currency_code := p_currency_code;
l_gl_int_type_rec.date_created := p_date;
l_gl_int_type_rec.created_by := fnd_global.user_id;
l_gl_int_type_rec.actual_flag := 'A';
l_gl_int_type_rec.status := 'S';
l_gl_int_type_rec.attribute1 := p_trx_header_id;
l_gl_int_type_rec.attribute2 := p_trx_line_id;
l_gl_int_type_rec.attribute3 := p_trx_number;
l_gl_int_type_rec.attribute4 := get_xx_transaction_source;
l_gl_int_type_rec.attribute5 := p_batch_header_id;
IF p_dr_account IS NOT NULL AND
(NOT check_ccid(p_sob_id => p_sob_id
,p_ccid => p_dr_account))
THEN
RAISE invalid_dr_ccid;
END IF;

IF p_cr_account IS NOT NULL AND
(NOT check_ccid(p_sob_id => p_sob_id
,p_ccid => p_cr_account))
THEN
RAISE invalid_cr_ccid;
END IF;

/* First the gms_cost_xfer Debit Line */
l_gl_int_type_rec.entered_dr := p_amount;
l_gl_int_type_rec.entered_cr := NULL;
l_gl_int_type_rec.code_combination_id := p_dr_account;
debug_stmt('p_dr_account ' || l_gl_int_type_rec.code_combination_id);

IF p_dr_account IS NOT NULL
THEN
insert_statement(p_gl_int_rec => l_gl_int_type_rec);
END IF;
l_gl_int_type_rec.entered_dr := NULL;
l_gl_int_type_rec.entered_cr := p_amount;
l_gl_int_type_rec.code_combination_id := p_cr_account;
IF p_cr_account IS NOT NULL
THEN
insert_statement(p_gl_int_rec => l_gl_int_type_rec);
debug_stmt('p_cr_account ' || l_gl_int_type_rec.code_combination_id);
END IF;

IF p_submit_gl_interface = 'Y'
THEN
debug_stmt('p_submit_gl_interface=>' || p_submit_gl_interface);
/* After inset, submit the request*/
COMMIT;
v_conc_id := fnd_request.submit_request(application => 'SQLGL'
,program => 'GLLEZL'
,description => NULL
,start_time => SYSDATE
,sub_request => FALSE
,argument1 => v_interface_run_id
,argument2 => p_sob_id
,argument3 => 'N'
,argument4 => NULL
,argument5 => NULL
,argument6 => 'N'
,argument7 => 'W');

SELECT status_code
INTO v_old_status
FROM fnd_concurrent_requests
WHERE request_id = fnd_global.conc_request_id;

UPDATE fnd_concurrent_requests
SET status_code = 'W'
WHERE request_id = fnd_global.conc_request_id;

COMMIT;
v_bool := fnd_concurrent.wait_for_request(v_conc_id
,5
,1000
,phase
,status
,dev_phase
,dev_status
,v_message);
UPDATE fnd_concurrent_requests
SET status_code = v_old_status
WHERE request_id = fnd_global.conc_request_id;
COMMIT;

debug_stmt('Request id is ' || v_conc_id);

IF (v_conc_id = 0)
THEN
/* If request not submitted, return false */
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
debug_stmt(' Returning false as request could not be submitted');
RETURN;
END IF;
END IF;
p_success_flag := TRUE;
EXCEPTION
WHEN conversion_rate_does_not_exist THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('XTR'
,'XTR_2207');
fnd_message.set_token('CURR1'
,v_func_curr);
fnd_message.set_token('CURR2'
,p_currency_code);
fnd_message.set_token('XCHG_DATE'
,to_char(p_date));
fnd_message.set_token('C_TYPE'
,g_conversion_type);
WHEN invalid_currency_code THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'R_PPOS0026');
WHEN invalid_gl_source THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'SHRD0152');
WHEN invalid_conversion_type THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'GL_JE_INVALID_CONVERSION_TYPE');
WHEN not_in_open_period THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('SQLGL'
,'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
WHEN invalid_cr_ccid THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('AR'
,'AR_AAPI_INVALID_CCID');
fnd_message.set_token('CCID'
,p_cr_account);
WHEN invalid_dr_ccid THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('AR'
,'AR_AAPI_INVALID_CCID');
fnd_message.set_token('CCID'
,p_dr_account);
WHEN OTHERS THEN
ROLLBACK TO gms_cost_xfer_gl_int;
p_success_flag := FALSE;
fnd_message.set_name('FND'
,'FS-UNKNOWN');
fnd_message.set_token('ERROR'
,SQLERRM);
END transfer_to_gl;

END xx_import_xfer_gl_iface_api;

Oracle API for GL Interface Package Header



CREATE OR REPLACE PACKAGE xx_import_xfer_gl_iface_api
/* $Header: XXXXXXXX.pkh, Version 1.0, 07-FEB-2002 UKEXPI $
***********************************************************************
* *
* History Log *
* *
***********************************************************************
* *
* App/Release : Oracle e-Business Suite RXXX *
* Oracle App : SHORTNAME - FULL_NAME *
* Module : SHORTNAME - DESCRIPTION *
* Author : Anil Passi(Go 4 Gold) *
* Version : 1.0 *
* Created : 11-OCT-2002 *
* Description : This package gives API for GL interface calls *
* *
* Change History Log *
* ================== *
* *
* Version Date Author Change *
* ======= =========== ============ ================================*
* *
* 1.0 11-OCT-2002 A. Passi Initial Version *
* *
* Parameters : *
* *
**********************************************************************/
AUTHID CURRENT_USER IS
PROCEDURE set_sob_id(p_sob_id IN INTEGER);

PROCEDURE set_source_name(p_source_name IN VARCHAR2);

PROCEDURE populate_interface_control(p_user_je_source_name IN VARCHAR2
,p_group_id IN OUT NUMBER
,p_set_of_books_id NUMBER
,p_interface_run_id IN OUT NUMBER);

TYPE g_gl_int_type_rec IS RECORD(
reference_date DATE
,attribute20 VARCHAR2(150)
,CONTEXT VARCHAR2(150)
,context2 VARCHAR2(150)
,invoice_date DATE
,tax_code VARCHAR2(15)
,invoice_identifier VARCHAR2(20)
,invoice_amount NUMBER
,context3 VARCHAR2(150)
,ussgl_transaction_code VARCHAR2(30)
,descr_flex_error_message VARCHAR2(240)
,jgzz_recon_ref VARCHAR2(240)
,segment23 VARCHAR2(25)
,segment24 VARCHAR2(25)
,segment25 VARCHAR2(25)
,segment26 VARCHAR2(25)
,segment27 VARCHAR2(25)
,segment28 VARCHAR2(25)
,segment29 VARCHAR2(25)
,segment30 VARCHAR2(25)
,entered_dr NUMBER
,entered_cr NUMBER
,accounted_dr NUMBER
,accounted_cr NUMBER
,transaction_date DATE
,reference1 VARCHAR2(100)
,reference2 VARCHAR2(240)
,reference3 VARCHAR2(100)
,reference4 VARCHAR2(100)
,reference5 VARCHAR2(240)
,reference6 VARCHAR2(100)
,reference7 VARCHAR2(100)
,reference8 VARCHAR2(100)
,reference9 VARCHAR2(100)
,reference10 VARCHAR2(240)
,reference11 VARCHAR2(100)
,reference12 VARCHAR2(100)
,reference13 VARCHAR2(100)
,reference14 VARCHAR2(100)
,reference15 VARCHAR2(100)
,reference16 VARCHAR2(100)
,reference17 VARCHAR2(100)
,reference18 VARCHAR2(100)
,reference19 VARCHAR2(100)
,reference20 VARCHAR2(100)
,reference21 VARCHAR2(240)
,reference22 VARCHAR2(240)
,reference23 VARCHAR2(240)
,reference24 VARCHAR2(240)
,reference25 VARCHAR2(240)
,reference26 VARCHAR2(240)
,reference27 VARCHAR2(240)
,reference28 VARCHAR2(240)
,reference29 VARCHAR2(240)
,reference30 VARCHAR2(240)
,je_batch_id NUMBER
,period_name VARCHAR2(15)
,je_header_id NUMBER
,je_line_num NUMBER
,chart_of_accounts_id NUMBER
,functional_currency_code VARCHAR2(15)
,code_combination_id NUMBER
,date_created_in_gl DATE
,warning_code VARCHAR2(4)
,status_description VARCHAR2(240)
,stat_amount NUMBER
,group_id NUMBER
,request_id NUMBER
,subledger_doc_sequence_id NUMBER
,subledger_doc_sequence_value NUMBER
,attribute1 VARCHAR2(150)
,attribute2 VARCHAR2(150)
,gl_sl_link_id NUMBER
,gl_sl_link_table VARCHAR2(30)
,attribute3 VARCHAR2(150)
,attribute4 VARCHAR2(150)
,attribute5 VARCHAR2(150)
,attribute6 VARCHAR2(150)
,attribute7 VARCHAR2(150)
,attribute8 VARCHAR2(150)
,attribute9 VARCHAR2(150)
,attribute10 VARCHAR2(150)
,attribute11 VARCHAR2(150)
,attribute12 VARCHAR2(150)
,attribute13 VARCHAR2(150)
,attribute14 VARCHAR2(150)
,attribute15 VARCHAR2(150)
,attribute16 VARCHAR2(150)
,attribute17 VARCHAR2(150)
,attribute18 VARCHAR2(150)
,attribute19 VARCHAR2(150)
,status VARCHAR2(50)
,set_of_books_id NUMBER
,accounting_date DATE
,currency_code VARCHAR2(15)
,date_created DATE
,created_by NUMBER
,actual_flag VARCHAR2(1)
,user_je_category_name VARCHAR2(25)
,user_je_source_name VARCHAR2(25)
,currency_conversion_date DATE
,encumbrance_type_id NUMBER
,budget_version_id NUMBER
,user_currency_conversion_type VARCHAR2(30)
,currency_conversion_rate NUMBER
,average_journal_flag VARCHAR2(1)
,originating_bal_seg_value VARCHAR2(25)
,segment1 VARCHAR2(25)
,segment2 VARCHAR2(25)
,segment3 VARCHAR2(25)
,segment4 VARCHAR2(25)
,segment5 VARCHAR2(25)
,segment6 VARCHAR2(25)
,segment7 VARCHAR2(25)
,segment8 VARCHAR2(25)
,segment9 VARCHAR2(25)
,segment10 VARCHAR2(25)
,segment11 VARCHAR2(25)
,segment12 VARCHAR2(25)
,segment13 VARCHAR2(25)
,segment14 VARCHAR2(25)
,segment15 VARCHAR2(25)
,segment16 VARCHAR2(25)
,segment17 VARCHAR2(25)
,segment18 VARCHAR2(25)
,segment19 VARCHAR2(25)
,segment20 VARCHAR2(25)
,segment21 VARCHAR2(25)
,segment22 VARCHAR2(25));

PROCEDURE insert_statement(p_gl_int_rec IN OUT NOCOPY g_gl_int_type_rec);

PROCEDURE transfer_to_gl(p_sob_id IN INTEGER
,p_batch_header_id IN INTEGER
,p_trx_header_id IN INTEGER
,p_trx_line_id IN INTEGER
,p_date IN OUT DATE
,p_currency_code IN VARCHAR2
,p_amount IN NUMBER
,p_dr_account IN INTEGER
,p_cr_account IN INTEGER
,p_journal_name IN VARCHAR2
,p_header_description IN VARCHAR2
,p_line_description IN VARCHAR2
,p_success_flag OUT BOOLEAN
,p_first_record_in_batch IN VARCHAR2
,p_submit_gl_interface IN VARCHAR2
,p_gl_user_je_source_name IN VARCHAR2
,p_gl_user_je_category_name IN VARCHAR2
,p_trx_number IN VARCHAR2);
PROCEDURE new_gl_interface_batch;

FUNCTION get_gl_je_reference1(p_batch_header_id IN INTEGER
,p_trx_number IN VARCHAR2)
RETURN VARCHAR2;

FUNCTION get_gl_application_id(p_appl_name IN VARCHAR2) RETURN INTEGER;

END xx_import_xfer_gl_iface_api;