Saturday 29 September 2012

Important TCA API's


FND LOAD Scripts


Forms Personalizations:
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt FND_FORM_CUSTOM_RULES function_name="OZF_FNDLVMLU"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct ((NAME_OF_LDT)).ldt


Form Function:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt FUNCTION FUNCTION_NAME='((func name))'
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ((NAME_OF_LDT)).ldt


Lookups :
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ="XXDIS" LOOKUP_TYPE="((lkp name))"
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct ((NAME_OF_LDT)).ldt


Profile:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD @FND:patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct ((NAME_OF_LDT)).ldt PROFILE PROFILE_NAME="((NAME_OF_profile))" APPLICATION_SHORT_NAME="CANON"


Concurrent Program:
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ((NAME_OF_LDT)).ldt PROGRAM APPLICATION_SHORT_NAME="XXDIS" CONCURRENT_PROGRAM_NAME="((prg_name))"


Request Group:
--------------------------------------
FNDLOAD (usrname)/(paswrd) O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt
FNDLOAD (usrname)/(paswrd) O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct ((NAME_OF_LDT)).ldt REQUEST_GROUP REQUEST_GROUP_NAME="System Administrator Reports" APPLICATION_SHORT_NAME="FND"

Flexi field:
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_LINES'

Flexi field ....category/CONTEXT
--------------------------------------
$FND_TOP/bin/FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DFF_CONTEXT DESCRIPTIVE_FLEX_CONTEXT_CODE="((FLEX_CONTEXT))"


Valuset :
--------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt VALUE_SET FLEX_VALUE_SET_NAME="CANON_E379_MERCH_DROPSHIP_WH_VS"
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct ((NAME_OF_LDT)).ldt


Alert :
-------------------------------------
FNDLOAD (usrname)/(paswrd) 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt ALR_ALERTS APPLICATION_SHORT_NAME='WSH'
FNDLOAD (usrname)/(paswrd) 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct ((NAME_OF_LDT)).ldt

Wednesday 26 September 2012

PO Form Customization



The purpose of this deliverable is to document the functional specifications for the Customization of PO form to ensure no PO is created for less than US$1000.If  enterd amount is less than it will pop-up a messgae.” Amount Should Be Greater Then $1000.Transaction Not Allowed'.if enterd amount Greater then $1000 it will allow to book the order .(CUSTOM.pll.).
Export program components

The following list contains all components required to completely install the PO form customization using CUSTOM.pll

1.  Open CUSTOM.pll in Oracle Forms Developer.
2. Modify the text of the CUSTOM package body
3. Compile all and save your changes. Exit from Oracle Forms developer.

4. Use the Oracle Forms Compiler program to generate a new .plx file for the CUSTOM library.

5. Verify that your file generated successfully. Place the .plx file in AU_TOP/resource.

6. Try it out from the Oracle Applications Navigator.

PO form customization using CUSTOM.pll  library

The CUSTOM.pll program performs the following steps:
1.      Declare The Variables.
2.      Write The Condition.
3.      Save & Compile.

      Filename                                  CUSTOM.pll
Directory                                 $AU_TOP/resources
Package name                         CUSTOM
Functions                                RETURN BOOLEAN
Procedure                                EVENT                                
      Form  Name                            POXPOEPO

1. Declare The Variables In Custom.pll (po)
form_name      varchar2(30) := name_in('system.current_form');
block_name     varchar2(30) := name_in('system.cursor_block');
 field_name     varchar2(30) := name_in('system.current_field');
 po_line_amount number(20);
 po_unit_amount number(20);
       po_total_amount number(20):=1000;

2.Write The Fallowing Code


form_name      varchar2(30) := name_in('system.current_form');
 block_name     varchar2(30) := name_in('system.cursor_block');
 field_name     varchar2(30) := name_in('system.current_field');
 
  po_line_amount number(20);
po_unit_amount number(20);
po_total_amount number(20):=1000;
po_limit_dsp number(20):=1000;
--po_limit_dsp1 number(20):=1000;
--vl1 varchar2(50):=name_in('PO_HEADERS.DOC_TYPE_NAME');
--AMT_LIMIT_DSP
--DOC_TYPE_NAME
  begin
    if (event_name='WHEN-NEW-FORM-INSTANCE') then
set_window_property(FORMS_MDI_WINDOW, TITLE,'Rajsekhar apps');
end if;
    null;
     --------PO FORM CUSTOMIZATION-----
 -- begin
if(event_name='WHEN-VALIDATE-RECORD') then
if(form_name='POXPOEPO' and block_name='PO_LINES') then
if name_in('PO_HEADERS.DOC_TYPE_NAME')='Standard Purchase Order' or name_in('PO_HEADERS.DOC_TYPE_NAME')='Planned Purchase Order' then
po_line_amount :=name_in('PO_LINES.QUANTITY');
po_unit_amount :=name_in('PO_LINES.UNIT_PRICE');
po_total_amount:=po_line_amount*po_unit_amount;
if po_total_amount<1000 then
fnd_message.set_string('Amount Should Be Greater Then $1000.Transaction Not Allowed');
fnd_message.show;
RAISE Form_Trigger_Failure;
--close_form('POXPOEPO');
--          else
--          message('accepted');
--          message('accepted');
end if;
end if;
end if;
end if;
if(event_name='WHEN-VALIDATE-RECORD') then
if(form_name='POXPOEPO' and block_name='PO_HEADERS') then
if name_in('PO_HEADERS.DOC_TYPE_NAME')='Contract Purchase Agreement' or name_in('PO_HEADERS.DOC_TYPE_NAME')='Blanket Purchase Agreement' then
po_limit_dsp :=name_in('PO_HEADERS.AMT_LIMIT_DSP');
if po_limit_dsp<1000 then
fnd_message.set_string('Amount Should Be Greater Then $1000.Transaction Not Allowed');
fnd_message.show;
message('REQUIRED');
message('REQUIRED');
RAISE Form_Trigger_Failure;
--close_form('POXPOEPO');
--          else
--          message('accepted');
--message('accepted');
--commit_form;
end if;
end if;
end if;
end if;
 end event;

Sunday 23 September 2012

GL Interface Code

 Oracle API for GL Interface Package Body

CREATE OR REPLACE PACKAGE BODY xx_import_xfer_gl_iface_api IS


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;