Document 1

  • Published on
    20-Nov-2014

  • View
    249

  • Download
    15

Transcript

CREATE OR REPLACE PACKAGE BODY XXYH_TRIT_PKG AS -/******************************************************************************* ********* ** TITLE :- XXYH_TRIT_PKG ** $Id: XXYH_TRIT_PKG.pkb,v 1.11 2009/04/28 21:52:10 haribabu Exp $ ** FILE NAME :- XXYH_TRIT_PKG.pls ** AUTHOR :- Haribabu ** VERSION :- 1.0 ** CONTENTS :- Package creation script ** DESCRIPTION :- This is common package which has all the code units for ** Trintech extracts ** INPUTS :- all the procedures in one package ** OUTPUTS :- extract file ** CREATION DATE :** CHANGE HISTORY :** Date Changed By Description ** 30-JUN-08 Balajee Kalla Initial Version of Code ** 29-JUL-08 Haribabu Pantakapu recon_net_disbursements ** 16-SEP-08 Haribabu Pantakapu Companies to be included: based on GL Bala nce extract value listing ** Accounts to be included: only include 15xx xx or 16xxxx accounts from FA subledger ** 29-SEP-08 Haribabu Pantakapu rcn dv reconciliation, rcn_dv_gen, rcn_dv_ ap, rcn_dv_ar ** 28-OCT-08 Haribabu Pantakapu Change Requests - Reconnet Reconciliation ** 14-NOV-08 Haribabu Pantakapu Reverse the sign on the amounts for Accumu lated Depr accounts ** 18-NOV-08 Haribabu Pantakapu ASN GL Extract ** Exclude enabled_flag and end_date condit ion for value set YGLOBAL_ACCOUNT ** ASN AP Extract ** Enabled Flag and end_date condition shou ld be added to all the ** XXYH value sets and exclude for YGLOBAL_ ACCOUNT ** FA SubLedget Extract ** Exclude enabled_flag and end_date condit ion for value set YGLOBAL_ACCOUNT ** Reconnet disbursement program,Reconnet Dep osits Extract program ** The two programs will be removed from Trin tech Package, will be no longer used. ** Yahoo! Trintech Reconnet Reconciliation re placement for two programs. ** 08-Dec-08 Haribabu Pantakapu Change Request ** 1) Extract File Name change for all extrac ts ** 2) DFFs and Value Sets for Selection and G rouping rules change ** for all assurenet extracts ** 20-Jan-09 Haribabu Pantakapu Change Request ** 1) DFF at Set of Books to exclude the STAT set of books ** 26-Jan-09 Haribabu Pantakapu Change Request 1) Exclude the FA books from the collapsed entities view defined based on FA_BOOK_CONTROLS to exclude ** 10-Feb-09 Haribabu Pantakapu 1) AX Changes - for Reconnet AR and AP.** 20-NOV-08 Haribabu/Kthakur Changes to xxyh_trin_get_trial_bal_data call to process ap trial bal for 11.5.10 CRP ** 09-Mar-09 Haribabu Pantakapu 1) Added new function to get gl_date - get _max_gl_date This function is copied from ap_invoice _payments_pkg 2) Added 3 new columns void_date, cleared_ date and gl_date ap extract, rcn_dv_gen, rcn_dv_ap proce dure modified ** 19-Mar-09 Haribabu Pantakapu 1) Removed the function get_max_gl_date. ** 14-SEP-2009 Sai Prasad Added Latam logic for GL ** 17-SEP-09 Gowtam Kamath Created procedure asn_gl_trx ******************************************************************************** **********/ x_progress VARCHAR2(250); x_rec_cnt NUMBER; x_rec_sum NUMBER; x_user_id NUMBER := fnd_global.user_id; x_request_id NUMBER := fnd_global.conc_request_id; x_prog_appl_id NUMBER := fnd_global.prog_appl_id; x_conc_program_id NUMBER := fnd_global.conc_program_id; x_resp_id NUMBER := fnd_global.resp_id; x_resp_appl_id NUMBER := fnd_global.resp_appl_id; -x_sfile VARCHAR2(50); x_dfile VARCHAR2(50); -x_tfile VARCHAR2(50) := 'Y'; -FUNCTION clean_str(in_string in varchar2) RETURN VARCHAR2 IS out_string varchar2(4000); remove_char varchar2(100); BEGIN remove_char:='(!@#$%^&*?):;,?~'; -- added few other special characters SELECT TRANSLATE(in_string,remove_char , ' ') INTO out_string FROM dual; RETURN (out_string); END; -PROCEDURE println (p_mode NUMBER, p_string IN VARCHAR2) IS BEGIN IF p_mode = 1 THEN fnd_file.put_line(fnd_file.output, p_string); ELSIF p_mode = 2 THEN fnd_file.put_line(fnd_file.log, p_string); ELSIF p_mode = 3 THEN fnd_file.put_line(fnd_file.output, p_string); fnd_file.put_line(fnd_file.log, p_string); END IF; END println; -PROCEDURE cp_output(p_prg VARCHAR2, p_sfile VARCHAR2, p_dfile VARCHAR2) IS lv_conreq_id NUMBER := 0; lv_resp_id NUMBER; BEGIN --ReturnCode := FND_REQUEST.SET_MODE(TRUE);println(2,'Program : ' p_prg); println(2,'S File : ' p_sfile); println(2,'D File ' p_dfile); fnd_global.apps_initialize ( user_id => x_user_id, resp_appl_id => x_resp_appl_id, resp_id => x_resp_id); IF x_tfile = 'Y' THEN lv_conreq_id := FND_REQUEST.SUBMIT_REQUEST('CP', 'XXYH_FTP_TRN_FILE_COPY', '', SYSDATE+0.001 , FALSE, p_prg, p_sfile, p_dfile); /*, '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','');*/ END IF; IF lv_conreq_id IS NULL THEN println(2, 'Error occured in copying the file '); ELSE println(2, 'concurrent program id : ' lv_conreq_id); END IF; END cp_output; -/******************************************************************************* ********* ** TITLE :- PROCEDURE asn_gl_trx ** FILE NAME :** AUTHOR :- Gowtam Kamath ** VERSION :- 0.0 ** CONTENTS :- Procedure creation script ** DESCRIPTION :- This procedure is to extract GL Transactions ** INPUTS :- p_posting_date (mandatory) ,p_period_name (mandatory) ** ,p_entity (mandatory) ** OUTPUTS :- extract file ** CREATION DATE :** CHANGE HISTORY :** Date Changed By Description ** 17-SEP-09 Gowtam Kamath Created procedure asn_gl_trx ******************************************************************************** **********/ PROCEDURE asn_gl_trx(errbuf OUT NOCOPY VARCHAR2 ,retcode OUT NOCOPY NUMBER ,p_posting_date IN VARCHAR2 ,p_period_name IN VARCHAR2 ,p_entity IN VARCHAR2) AS -- declare cursor CURSOR lcu_gl_trxs(posting_date IN VARCHAR2, period IN VARCHAR2, entity IN VARCHAR2 ) IS SELECT gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, last_day ('01-' gjh.period_name) effective_date, gsb.currency_code ccy2code, NVL(gjl.accounted_dr,0)-NVL(gjl.accounted_cr,0) ccy2_balance, gjh.currency_code ccy3code, NVL(gjl.entered_dr,0)-NVL(gjl.entered_cr,0) ccy3_balance, gjh.name journal, gjh.description journal_description, gjh.period_name, gjh.je_source, gcc.concatenated_segments string, git.transaction_number ic_number, ssub.name ic_sender, rsub.name ic_receiver, git.type_name ic_type, git.currency_code ic_currency, git.description ic_description, git.note ic_note, gjh.posted_date FROM gl_je_headers gjh, gl_je_lines gjl, gl_iea_transaction_lines gil, gl_iea_transactions_v git, gl_sets_of_books gsb, xxyh_trit_sob_v gsob, gl_code_combinations_kfv gcc, gl_iea_subsidiaries ssub, gl_iea_subsidiaries rsub, fnd_flex_value_sets ffvs, fnd_flex_values ffv WHERE gjl.je_header_id = gjh.je_header_id AND gjl.reference_1 = git.transaction_number AND ssub.subsidiary_id = git.sending_subsidiary_id AND rsub.subsidiary_id = git.receiving_subsidiary_id AND ffvs.flex_value_set_id = ffv.flex_value_set_id AND ffvs.flex_value_set_name = 'YGLOBAL_COMPANY' AND ffv.flex_value = gcc.segment1 AND NVL(ffv.attribute14, 'Y') = 'Y' AND gjl.reference_2 = TO_CHAR(git.transaction_id) AND gil.sender_receiver_code = DECODE (gjl.reference_5, 'SENDER', 'S', 'RECEI VER', 'R') AND gjl.code_combination_id = gcc.code_combination_id AND git.transaction_id = gil.transaction_id AND gsb.set_of_books_id = gjh.set_of_books_idAND gsob.set_of_books_id = gjh.set_of_books_id AND gjl.code_combination_id = 142789 AND gcc.segment2 IN (SELECT flex_value FROM fnd_flex_values_vl where flex_value_set_id IN (SELECT flex_value_set_id FROM fnd_flex_value_sets where flex_value_set_name like 'XXYH_TRINTECH_IC_ACCOUNTS' ) ) AND TRUNC(gjh.posted_date) = TO_DATE(posting_date,'YYYY/MM/DD HH24:MI:SS') AND gjh.period_name = period AND ((entity = 'XXYH_TRIT_US' AND GSOB.currency_code = 'USD') OR (entity = 'X XYH_TRIT_EU' AND GSOB.currency_code 'USD')) -UNION SELECT gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, last_day ('01-' gjh.period_name) effective_date, gsb.currency_code ccy2code, NVL(gjl.accounted_dr,0)-NVL(gjl.accounted_cr,0) ccy2_balance, gjh.currency_code ccy3code, NVL(gjl.entered_dr,0)-NVL(gjl.entered_cr,0) ccy3_balance, gjh.name journal, gjh.description journal_description, gjh.period_name, gjh.je_source, gcc.concatenated_segments string, NULL ic_number, NULL ic_sender, NULL ic_receiver, NULL ic_type, NULL ic_currency, NULL ic_description, NULL ic_note, gjh.posted_date FROM gl_je_headers gjh, gl_je_lines gjl, gl_sets_of_books gsb, xxyh_trit_sob_v gsob, gl_code_combinations_kfv gcc, fnd_flex_value_sets ffvs, fnd_flex_values ffv WHERE gjl.je_header_id = gjh.je_header_id AND gjl.code_combination_id = gcc.code_combination_id AND gsb.set_of_books_id = gjh.set_of_books_id AND gjl.reference_2 IS NULL AND ffvs.flex_value_set_id = ffv.flex_value_set_id AND ffvs.flex_value_set_name = 'YGLOBAL_COMPANY' AND ffv.flex_value = gcc.segment1 AND NVL(ffv.attribute14, 'Y') = 'Y' -- AND gjh.je_source = 'Revaluation' AND gsob.set_of_books_id = gjh.set_of_books_id -- AND gjl.code_combination_id = 142789 AND gcc.segment2 IN (SELECT flex_value FROM fnd_flex_values_vl where flex_value_set_id IN (SELECT flex_value_set_id FROM fnd_flex_value_setswhere flex_value_set_name like 'XXYH_TRINTECH_IC_ACCOUNTS' ) ) AND TRUNC(gjh.posted_date) = TO_DATE(posting_date,'YYYY/MM/DD HH24:MI:SS') AND gjh.period_name = period AND ((entity = 'XXYH_TRIT_US' AND GSOB.currency_code = 'USD') OR (entity = 'X XYH_TRIT_EU' AND GSOB.currency_code 'USD')); CURSOR lcu_ccy1_data(currency IN VARCHAR2, amount IN NUMBER, period IN VARCHAR2 ) IS SELECT GDR.conversion_rate, ROUND(amount * GDR.conversion_rate,2) converted_amount FROM gl_daily_rates GDR WHERE 1 = 1 AND GDR.from_currency = currency AND GDR.to_currency = 'USD' AND GDR.conversion_type = 'Corporate' AND GDR.conversion_date = last_day('01-' period); -- declare variables lv_stage lv_rec lv_header lv_footer ln_counter ln_conv_rate ln_conv_amount ln_conv_total_amount lkv_CCY1_CODE_USD lv_extract_date lkv_ACCOUNT_SEGMENT1 lkv_ACCOUNT_SEGMENT2 lkv_ACCOUNT_SEGMENT3 lkv_ACCOUNT_SEGMENT4 lkv_ACCOUNT_SEGMENT5 lkv_ACCOUNT_SEGMENT6 lkv_ACCOUNT_SEGMENT7 lkv_ACCOUNT_SEGMENT8 lkv_ACCOUNT_SEGMENT9 lkv_ACCOUNT_SEGMENT10 lkv_EFFECTIVE_DATE lkv_DATE1 lkv_DATE2 lkv_CCY1_CODE lkv_CCY1_GLEND_BALANCE lkv_CCY2_CODE lkv_CCY2_GLEND_BALANCE lkv_CCY3_CODE lkv_CCY3_GLEND_BALANCE lkv_GL_DESCRIPTION lkv_GL_PERIOD lkv_GL_SOURCE lkv_GL_STRING lkv_GL_JOURNAL lkv_IC_NUMBER lkv_IC_SENDER lkv_IC_RECEIVER lkv_IC_TYPE VARCHAR2(100); VARCHAR2(1000); VARCHAR2(1000); VARCHAR2(200); NUMBER; NUMBER; NUMBER; NUMBER; VARCHAR2(5) := 'USD'; VARCHAR2(20) := TO_CHAR(SYSDATE,'YYYYMMDD'); VARCHAR2(20) := 'Account Segment1'; VARCHAR2(20) := 'Account Segment2'; VARCHAR2(20) := 'Account Segment3'; VARCHAR2(20) := 'Account Segment4'; VARCHAR2(20) := 'Account Segment5'; VARCHAR2(20) := 'Account Segment6'; VARCHAR2(20) := 'Account Segment7'; VARCHAR2(20) := 'Account Segment8'; VARCHAR2(20) := 'Account Segment9'; VARCHAR2(20) := 'Account Segment10'; VARCHAR2(20) := 'Effective Date'; VARCHAR2(10) := 'Date1'; VARCHAR2(10) := 'Date2'; VARCHAR2(10) := 'CCY1 Code'; VARCHAR2(20) := 'CCY1 GL End Balance'; VARCHAR2(10) := 'CCY2 Code'; VARCHAR2(20) := 'CCY2 GL End Balance'; VARCHAR2(10) := 'CCY3 Code'; VARCHAR2(20) := 'CCY3 GL End Balance'; VARCHAR2(20) := 'GL Description'; VARCHAR2(20) := 'GL Period'; VARCHAR2(20) := 'GL Source'; VARCHAR2(20) := 'GL String'; VARCHAR2(20) := 'GL Journal'; VARCHAR2(20) := 'IC Number'; VARCHAR2(20) := 'IC Sender'; VARCHAR2(20) := 'IC Receiver'; VARCHAR2(20) := 'IC Type';CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANT CONSTANTlkv_IC_CURRENCY lkv_IC_DESCRIPTION lkv_IC_NOTE lkv_FOOTER lkv_FILE_NAME_SUFFIXCONSTANT CONSTANT CONSTANT CONSTANT CONSTANTVARCHAR2(20) VARCHAR2(20) VARCHAR2(20) VARCHAR2(20) VARCHAR2(20):= := := := :='IC Currency'; 'IC Description'; 'IC Note'; 'FOOTER'; 'GL_transactions';BEGIN println(2,'asn_gl_trx BEGIN'); println(2,'p_posting_date = ' p_posting_date); println(2,'p_period_name = ' p_period_name); println(2,'p_entity = ' p_entity); lv_stage := '10'; ln_counter := 0; ln_conv_total_amount :=0; --Populate Extract Header lv_header := NULL; lv_header := lkv_ACCOUNT_SEGMENT1 ',' lkv_ACCOUNT_SEGMENT2 ',' lkv_ACCOUNT_SEGMENT3 ',' lkv_ACCOUNT_SEGMENT4 ',' lkv_ACCOUNT_SEGMENT5 ',' lkv_ACCOUNT_SEGMENT6 ',' lkv_ACCOUNT_SEGMENT7 ',' lkv_ACCOUNT_SEGMENT8 ',' lkv_ACCOUNT_SEGMENT9 ',' lkv_ACCOUNT_SEGMENT10 ',' lkv_EFFECTIVE_DATE ',' lkv_DATE1 ',' lkv_DATE2 ',' lkv_CCY1_CODE ',' lkv_CCY1_GLEND_BALANCE ',' lkv_CCY2_CODE ',' lkv_CCY2_GLEND_BALANCE ',' lkv_CCY3_CODE ',' lkv_CCY3_GLEND_BALANCE ',' lkv_GL_JOURNAL ',' lkv_GL_DESCRIPTION ',' lkv_GL_PERIOD ',' lkv_GL_SOURCE ',' lkv_GL_STRING ',' lkv_IC_NUMBER ',' lkv_IC_SENDER ',' lkv_IC_RECEIVER ',' lkv_IC_TYPE ',' lkv_IC_CURRENCY ',' lkv_IC_DESCRIPTION ',' lkv_IC_NOTE; println(1,lv_header); lv_stage := '20'; lv_header := p_period_name '_' TO_CHAR(TO_DATE(posting_date,'YYYY/MM/DD HH 24:MI:SS'), 'YYYYMMDD'); println(1,lv_header); println(2,'Extract Header Populated'); lv_stage := '30'; --Populate Extract Body FOR lr_rec IN lcu_gl_trxs(p_posting_date ,p_period_name ,p_entity)LOOP ln_conv_amount := ROUND(lr_rec.ccy3_balance,2); IF lr_rec.ccy2code 'USD' THEN OPEN lcu_ccy1_data(lr_rec.ccy2code, lr_rec.ccy2_balance, p_period_name ); FETCH lcu_ccy1_data INTO ln_conv_rate,ln_conv_amount; CLOSE lcu_ccy1_data; END IF; lv_rec := NULL; ln_conv_total_amount := ln_conv_total_amount + ln_conv_amount; lv_rec := NVL(lr_rec.segment1,NULL) ',' NVL(lr_rec.segment2,NULL) ',' NVL(lr_rec.segment3,NULL) ',' NVL(lr_rec.segment4,NULL) ',' NVL(lr_rec.segment5,NULL) ',' NVL(lr_rec.segment6,NULL) ',' NVL(lr_rec.segment7,NULL) ',' NVL(lr_rec.segment8,NULL) ',' NVL(lr_rec.segment9,NULL) ',' NVL(lr_rec.segment10,NULL) ',' TO_CHAR(lr_rec.effective_date, 'MM/DD/YYYY') ',' TO_CHAR(lr_rec.effective_date,'MM/DD/YYYY') ',' NULL ',' lkv_CCY1_CODE_USD ',' ln_conv_amount ',' lr_rec.ccy2code ',' ROUND(lr_rec.ccy2_balance,2) ',' lr_rec.ccy3code ',' ROUND(lr_rec.ccy3_balance,2) ',' NVL(REPLACE(lr_rec.journal,',', ' '),NULL) ',' NVL(REPLACE(lr_rec.journal_description,',', ' '),NULL) ',' NVL(lr_rec.period_name,NULL) ',' NVL(lr_rec.je_source,NULL) ',' NVL(REPLACE(lr_rec.string,',', ' '),NULL) ',' NVL(REPLACE(lr_rec.ic_number,',', ' '),NULL) ',' NVL(REPLACE(lr_rec.ic_sender,',', ' '),NULL) ',' NVL(REPLACE(lr_rec.ic_receiver,',', ' '),NULL) ',' NVL(REPLACE(lr_rec.ic_type,',', ' '),NULL) ',' NVL(lr_rec.ic_currency,NULL) ',' NVL(REPLACE(lr_rec.ic_description,',', ' '),NULL) ',' NVL(REPLACE(lr_rec.ic_note,',', ' '),NULL); println(1,lv_rec); ln_counter := ln_counter+1; END LOOP; lv_stage := '40'; --Populate Extract Footer lv_footer := lkv_FOOTER ',' ln_counter ',' ln_conv_total_amount; println(1,lv_footer); println(2,'Extract Footer Populated'); lv_stage := '50'; -- Launch FileCopy program println(2,'launching XXYH_FTP_TRN_FILE_COPY program ...'); x_sfile := 'o' x_request_id '.out'; x_dfile := lkv_FILE_NAME_SUFFIX '_' p_period_name '_' TO_CHAR(TO_DATE(p_ posting_date,'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDD') '_' lv_extract_date '.csv';lv_stage := '60'; cp_output('TRIT-OUT', x_sfile, x_dfile); println(2,'launching XXYH_FTP_TRN_FILE_COPY program done'); lv_stage := '70'; println(2,'asn_gl_trx END'); EXCEPTION WHEN OTHERS THEN println(2,'Program reached the stage ' lv_stage); println(2,'Error Code = ' SQLCODE ' Error Message = ' SQLERRM); END asn_gl_trx; /******************************************************************************* ********* ** TITLE :- PROCEDURE fx_rates_extract ** $Id: XXYH_TRIT_PKG.pkb,v 1.11 2009/04/28 21:52:10 haribabu Exp $ ** FILE NAME :** AUTHOR :- Tanay Behera ** VERSION :- 1.0 ** CONTENTS :- Procedure creation script ** DESCRIPTION :- This is used to fetch data on a monthly basis for ** FX rates for Trintech ** INPUTS :- p_period_name (mandatory) ** ,p_sob_id (optional) ** OUTPUTS :- extract file ** CREATION DATE :** CHANGE HISTORY :** Date Changed By Description ** 09-JUN-08 Tanay Behera Initial Version of Code ** 20-Jan-09 Haribabu Pantakapu Change Request ** 1) DFF at Set of Books to exclude the STAT set of books ******************************************************************************** **********/ PROCEDURE asn_fx_rates ( errbuf OUT VARCHAR2 ,retcode OUT NUMBER ,p_period_name IN VARCHAR2 ,p_sob_id IN NUMBER) AS -- This cursor is to select the info for the extract. CURSOR lcu_fx_rates ( name_period IN VARCHAR2 ,sobs_id IN NUMBER) IS SELECT DISTINCT to_char(to_date('01-' GLR.period_name),'MM-DD-YYYY') tive_Date ,GSOB.currency_code ode ,GLR.to_currency_code ode ,GLR.eop_rate -- GLR.avg_rate ,'Monthly' Type FROM gl_translation_rates GLR ,xxyh_trit_sob_v GSOBEffec CCY1C CCY2C Rate Rate_WHERE AND AND AND UNION SELECT _DateGLR.set_of_books_id = GSOB.set_of_books_id GLR.period_name = name_period GSOB.set_of_books_id = NVL(sobs_id,GSOB.set_of_books_id) GLR.actual_flag = 'A' DISTINCT to_char(to_date('01-' name_period),'MM-DD-YYYY') Effective CCY1Code CCY2Code Rate Rate_Type,GSOB.currency_code ,GSOB.currency_code ,1 ,'Monthly' FROM xxyh_trit_sob_v GSOB WHERE GSOB.set_of_books_id = NVL(sobs_id,GSOB.set_of_books_id) ORDER BY 2,3; -- declaration of variables. ln_counter NUMBER; ln_rate_total NUMBER; lv_err_stage VARCHAR2(4000); lv_rec VARCHAR2(200); -BEGIN FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering the loop: ' FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Date,' 'CCY1Code,' 'CCY1Code,' 'Rate,' 'Rate Type'); ln_counter := 0; ln_rate_total := 0; -- Start of the for loop FOR lr_rec IN lcu_fx_rates ( p_period_name ,p_sob_id) LOOP lv_err_stage := 'Printing the values to a output file.'; lv_rec := NULL; ln_counter := ln_counter + 1; ln_rate_total := ln_rate_total + lr_rec.Rate;SYSDATE);lv_rec := lr_rec.Effective_Date ',' lr_rec.CCY1Code ',' lr_rec .CCY2Code ',' lr_rec.Rate ',' lr_rec.Rate_Type; -FND_FILE.PUT_LINE(FND_FILE.LOG, 'The values are: ' lv_rec);println(1, lv_rec); END LOOP; -- End of the for loop lv_err_stage := 'Printing the values for the total rate value and re cord count.';println(1, 'FOOTER,'ln_counter ',' ln_rate_total);-- launch concurrent program to cp out put to trintech directory println(2, 'launching cp program ...'); x_sfile := 'o' x_request_id '.out'; x_dfile := 'FXRATES_' to_char(to_date(p_period_name, 'MON-YY'), 'MMYYYY') T O_CHAR(SYSDATE,'MMDDYYYY') '.csv'; -cp_output('TRIT-OUT', x_sfile, x_dfile); println(2, 'launching cp program done'); -EXCEPTION WHEN OTHERS THEN errbuf := SUBSTR(SQLERRM, 1, 200) 'at :' lv_err_stage; retcode := 2; END asn_fx_rates; -FUNCTION xxyh_trin_get_trial_bal_data (p_sob_id IN NUMBER ,p_request_id IN NUMBER ,p_acctng_date IN DATE ) RETURN BOOLEAN IS lv_p_org_where_alb lv_p_org_where_ael lv_p_org_where_asp ld_from_date VARCHAR2(2000); VARCHAR2(2000); VARCHAR2(2000); DATE;--variable added in CRP 11.5.10 as change in process date 20-Nov-2008 lv_supp_det_alb_where VARCHAR2(100); lv_supp_acc_where VARCHAR2(100); lv_supp_ax_acc_where VARCHAR2(100); --where clause added in CRP 11.5.10 as change in process date 20-Nov-2008 BEGIN ld_from_date := NULL; XLA_MO_REPORTING_API.Initialize('1000',p_sob_id,'AUTO'); lv_p_org_where_alb := XLA_MO_REPORTING_API.Get_Predicate('alb',null); lv_p_org_where_ael := XLA_MO_REPORTING_API.Get_Predicate('ael',null); lv_p_org_where_asp := XLA_MO_REPORTING_API.Get_Predicate('asp',null); -- added in CRP 11.5.10 lv_supp_det_alb_where lv_supp_acc_where lv_supp_ax_acc_where -- added in CRP 11.5.10 as := := := as change in ' AND 1 = ' AND 1 = ' AND 1 = change in process date 20-Nov-2008 1 '; 1 '; 1 '; process date 20-Nov-2008-- Since the Multi org API, hard codes the value ":p_reporting_entity_id" as a -- part of the return string, this will work fine with the reports. But for th e -- trial balance performance enhancements we have moved the reports side logic to -- the server side logic. So we cannot direclty use the bind variables. So -- used the database REPLACE function on the same strings to replace the-- hard coded ":p_reporting_entity_id" to the actual value itself. And the sam e is -- passed to the server side code. lv_p_org_where_ael := REPLACE(lv_p_org_where_ael , ':p_reporting_entity_id', p_sob_id); lv_p_org_where_alb := REPLACE(lv_p_org_where_alb, ':p_reporting_entity_id', p_sob_id); lv_p_org_where_asp := REPLACE(lv_p_org_where_asp, ':p_reporting_entity_id', p_sob_id); IF (AP_TRIAL_BALANCE_PKG.Process_Trial_Balance( p_accounting_date p_acctng_date ,p_from_date ld_from_date ,p_request_id p_request_id ,p_reporting_entity_id p_sob_id ,p_org_where_alb lv_p_org_where_alb ,p_org_where_ael lv_p_org_where_ael ,p_org_where_asp lv_p_org_where_asp ,p_neg_bal_only 'N' ,p_debug_switch 'Y' ,p_supp_det_alb_where lv_supp_det_alb_where -- Changed for CRP By Kthakur/Haribabu on 20-Nov-2008 ,p_supp_acc_where lv_supp_acc_where -- Changed for CRP By Kthakur/Haribabu on 20-Nov-2008 ,p_supp_ax_acc_where lv_supp_ax_acc_where -- Changed for CRP By Kthakur/Haribabu on 20-Nov-2008 ) TRUE) THEN RETURN FALSE; END IF; RETURN TRUE; EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Occured in Get_Trial_Bal_Data'); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Code :' SQLCODE); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message :' SQLERRM); RETURN FALSE; END; /******************************************************************************* ********* ** TITLE :- XXYH_AP_SUBLEDGER_RECON_PKG ** $Id: XXYH_TRIT_PKG.pkb,v 1.11 2009/04/28 21:52:10 haribabu Exp $ ** FILE NAME :- XXYH_AP_SUBLEDGER_RECON.plb ** AUTHOR :- Tanay Behera ** VERSION :- 1.0 => => => => => => => => => => => =>** CONTENTS :- Package creation script ** DESCRIPTION :- This is used to fetch data for Trintech ** INPUTS :- p_account_number (mandatory) ** ,p_sob_id (mandatory) ** OUTPUTS :- extract file ** CREATION DATE :** CHANGE HISTORY :** Date Changed By Description ** 21-JUN-08 Tanay Behera Initial Version of Code ** 18-Nov-08 Haribabu Pantakapu Enabled Flag and end_date condition should be added to all the XXYH value sets and exclude for YGLOBAL_ACCOUNT. ** 08-Dec-08 Haribabu Pantakapu DFFs and Value Sets for selection and groupin g rules change ** File name change to include sysdate ** 20-Jan-09 Haribabu Pantakapu Change Request ** 1) DFF at Set of Books to exclude the STAT set of books ** 20-NOV-08 Haribabu/Kthakur Changes to xxyh_trin_get_trial_bal_data call to process ap trial bal for 11.5.10 CRP ******************************************************************************** **********/ PROCEDURE asn_ap ( errbuf ,retcode ,p_period_name ,p_extract_type ) AS -- This cursor is to select the info for all the SOB's CURSOR lcu_sobs IS SELECT set_of_books_id FROM xxyh_trit_sob_v; --WHERE set_of_books_id = 42 ; --in (145,446); ---used this for testing -- This cursor is to select the info for the extract. CURSOR lcu_subledger_extract IS SELECT XASE.* ,XASE.rowid FROM XXYH_TRIT_AP_SBL_EXT_TMP XASE ORDER BY 1,2,3,4; -- declaration of variables. ln_user_id NUMBER; ln_request_id NUMBER; lv_err_stage VARCHAR2(2000); lv_rec VARCHAR2(2000); lv_period_name GL_PERIODS.PERIOD_NAME%TYPE; ln_period_year GL_PERIODS.PERIOD_YEAR%TYPE; ld_start_date GL_PERIODS.START_DATE%TYPE; ld_end_date GL_PERIODS.END_DATE%TYPE; ln_counter NUMBER; ld_accntg_date GL_PERIODS.START_DATE%TYPE; OUT OUT IN IN VARCHAR2 NUMBER VARCHAR2 VARCHAR2-BEGIN ln_request_id := lv_period_name:= ln_period_year:= ld_start_date := ln_counter := --ln_request_id := _TMP. BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE XXYH.XXYH_TRIT_AP_TRIAL_BAL_TMP '; EXCEPTION WHEN OTHERS THEN DELETE FROM XXYH_TRIT_AP_TRIAL_BAL_TMP; COMMIT; END; BEGIN SELECT period_name ,period_year ,start_date ,end_date INTO lv_period_name ,ln_period_year ,ld_start_date ,ld_end_date FROM gl_periods WHERE period_name = p_period_name AND period_set_name = 'Accounting'; EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Code :' SQLCODE); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message :' SQLERRM); END; -- this is to cover all the SOBs FOR lr_sob_rec IN lcu_sobs LOOP BEGIN --DELETE FROM ap_trial_bal; DELETE FROM ap_trial_bal_gt; COMMIT; lv_err_stage := 'Inserting data by calling the standard Oracle package for SOB:' lr_sob_rec.set_of_books_id; FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting data by calling the standar d Oracle package for SOB:' lr_sob_rec.set_of_books_id ' ' ln_re quest_id); IF (xxyh_trin_get_trial_bal_data (lr_sob_rec.set_of_books_id, ln_reque st_id, ld_end_date) != TRUE) THEN FND_FILE.PUT_LINE(FND_FILE.LOG, 'This is not success'); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting data by calling the stan dard Oracle package for SOB:' lr_sob_rec.set_of_books_id); FND_GLOBAL.CONC_REQUEST_ID ; NULL; NULL; NULL; NULL; 999999999;-- Delete all the records from the interim table XXYH_TRIT_AP_TRIAL_BALELSE BEGIN lv_err_stage := 'Inserting data into the interim table:' lr_sob_r ec.set_of_books_id; FND_FILE.PUT_LINE(FND_FILE.LOG, 'This is success for SOB:' lr_sob _rec.set_of_books_id); INSERT INTO XXYH_TRIT_AP_TRIAL_BAL_TMP SELECT invoice_id ,code_combination_id ,remaining_amount ,vendor_id ,set_of_books_id ,org_id ,request_id ,invoice_amount --FROM ap_trial_bal; FROM ap_trial_bal_gt -- ap_trial_ba l; -- change for 11.5.10 upgrade WHERE set_of_books_id = lr_sob_rec.set_of_books_id; --where clause added in CRP as change in process date 20-Nov-2008 EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Code :' SQLCODE); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message :' SQLERRM); END; END IF; EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Code :' SQLCODE); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message :' SQLERRM); END; END LOOP; -- end of this is to cover all the SOBs COMMIT; FND_FILE.PUT_LINE(FND_FILE.LOG, 'This is success'); lv_err_stage := 'After Inserting data by calling the standard Oracle pack age'; -- Delete all the records from the interim table. BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE XXYH.XXYH_TRIT_AP_SBL_EXT_TMP'; EXCEPTION WHEN OTHERS THEN DELETE FROM XXYH_TRIT_AP_SBL_EXT_TMP; COMMIT; END; ln_user_id := FND_GLOBAL.USER_ID;FOR lr_inst_rec IN lcu_sobs LOOP BEGINlv_err_stage := 'Inserting Data for all the Records'; INSERT INTO XXYH_TRIT_AP_SBL_EXT_TMP ( legal_entity ,operating_unit ,concatenated_segments ,supplier_name ,invoice_number ,invoice_date ,invoice_curr ,invoice_amount ,remaining_amount ,accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 --,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amount ,local_amount ,reporting_amount ,period_name ,period_year ,gl_set_of_books_id ,sob_currency ,last_update_date ,last_updated_by ) SELECT le.name ,ou.name ,gcc.concatenated_segments ,pv.vendor_name ,ai.invoice_num ,ai.invoice_date ,ai.invoice_currency_code ,atb.invoice_amount ,atb.remaining_amount ,gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 -- ,REPLACE(REPLACE(REPLACE(XTAV.flex_desc, ',',''),':',''), ';','') ,ai.invoice_currency_code ,NULL ,NULL,0 --ai.invoice_amount--,atb.invoice_amount ,atb.remaining_amount -- NULL ,NULL ,lv_period_name ,ln_period_year ,lr_inst_rec.set_of_books_id ,gsob.currency_code ,SYSDATE ,ln_user_id FROM hr_organization_information OI ,hr_all_organization_units_tl LE ,hr_all_organization_units_tl OU ,XXYH_TRIT_AP_TRIAL_BAL_TMP ATB ,po_vendors PV ,ap_system_parameters_all ASP ,ap_invoices_all AI ,gl_code_combinations_kfv GCC --,xxyh_trit_account_v XTAV ,xxyh_trit_sob_v GSOB WHERE NVL(ATB.org_id,-99) = NVL(AI.org_id,-99) AND NVL(ATB.org_id,-99) = NVL(ASP.org_id,-99) AND ATB.vendor_id = PV.vendor_id AND ATB.invoice_id = AI.invoice_id AND ATB.code_combination_id = GCC.code_combination_ id -- tanay added AND ATB.set_of_books_id = lr_inst_rec.set_of_bo oks_id AND GSOB.set_of_books_id = lr_inst_rec.set_of_bo oks_id AND ATB.org_id = OI.organization_id AND OI.org_information_context = 'Operating Unit Infor mation' AND DECODE(LTRIM(OI.org_information3,'0123456789'), NULL , TO_NUMBER(OI.org_information3) , NULL ) = lr_inst_rec.set_of_bo oks_id AND DECODE(LTRIM(OI.org_information2,'0123456789'), NULL , TO_NUMBER(OI.org_information2) , NULL ) = LE.organization_id AND OU.organization_id = OI.organization_id AND OU.language = USERENV('LANG') AND LE.language = USERENV('LANG') /*AND GCC.segment2 = XTAV.flex_value AND XTAV.entity IN ('US','EU') AND GCC.segment1 IN (SELECT XTCV.flex_value FROM xxyh_trit_company_v XTCV WHERE XTCV.entity IN ('XXYH_TRIT_US','XX YH_TRIT_EU') )*/ AND EXISTS (SELECT 'X' FROM xxyh_trit_account_v XTAV WHERE XTAV.flex_value = GCC.segment2 AND ROWNUM = 1) AND EXISTS (SELECT 'X' FROM xxyh_trit_company_v XCTV WHERE XCTV.flex_value = GCC.segment1 AND ROWNUM = 1) AND GCC.segment2 NOT IN (SELECT ffv.flex_value FROM fnd_flex_values_vl ffv ,fnd_flex_value_sets ffvsWHERE lex_value_set_id AND XYH_TRIT_ASN_AP_ACCS' ov-2008 AND AND _date_active, SYSDATE) YSDATE));ffv.flex_value_set_id = ffvs.f ffvs.flex_value_set_name = 'X-- Condition Added By Haribabu on 18-N ffv.enabled_flag = 'Y' SYSDATE BETWEEN nvl(ffv.start AND nvl(ffv.end_date_active, S EXCEPTION WHEN OTHERS THEN errbuf := SUBSTR(SQLERRM, 1, 200) 'at :' lv_err_stage; retcode := 2; END; END LOOP; -- this commit is to make the changes permanent. COMMIT; UPDATE XXYH_TRIT_AP_SBL_EXT_TMP XASE SET accountname = (SELECT REPLACE(REPLACE(REPLACE(XTAV.flex_desc, ', ',''),':',''),';','') FROM xxyh_trit_account_v XTAV WHERE XTAV.flex_value = XASE.accountsegment2 AND ROWNUM = 1 ); COMMIT; -- This it to update for the functional currency and amount BEGIN lv_err_stage := 'This it to update for the functional currency and amo unt for entered_cur = sob_currency '; UPDATE XXYH_TRIT_AP_SBL_EXT_TMP XASE SET (--XASE.local_amount, XASE.local_cur) = (SELECT --XASE1.entered_amount XASE1.entered_cur FROM XXYH_TRIT_AP_SBL_EXT_TMP XASE1 WHERE XASE.accountsegmen t1 = XASE1.accountsegment1 AND NVL(XASE.accountse gment2,'$') = NVL(XASE1.accountsegment2,'$') AND NVL(XASE.accountse gment3,'$') = NVL(XASE1.accountsegment3,'$') AND NVL(XASE.accountse gment4,'$') = NVL(XASE1.accountsegment4,'$') AND NVL(XASE.accountse gment5,'$') = NVL(XASE1.accountsegment5,'$') AND NVL(XASE.accountse gment6,'$') = NVL(XASE1.accountsegment6,'$') AND NVL(XASE.accountse gment7,'$') = NVL(XASE1.accountsegment7,'$') AND NVL(XASE.accountsegment8,'$') = NVL(XASE1.accountsegment8,'$') AND t r = XASE1.operating_unit AND = XASE1.invoice_number AND = XASE1.supplier_name AND 1.sob_currency ) WHERE XASE.entered_cur = XASE.sob_currency ; lv_err_stage := 'This it to update for the functional currency and amo unt for entered_cur sob_currency '; UPDATE XXYH_TRIT_AP_SBL_EXT_TMP XASE SET (--XASE.local_amount, XASE.local_cur) =(SELECT -- NVL(XASE1.entered_amount,0)*NVL(GT R.eop_rate,0), XASE1.sob_currency FROM XXYH_TRIT_AP_SBL_EXT_TMP XASE1 ,gl_translation_rates GTR WHERE XASE.accountsegment 1 = XASE1.accountsegment1 AND NVL(XASE.accountseg ment2,'$') = NVL(XASE1.accountsegment2,'$') AND NVL(XASE.accountseg ment3,'$') = NVL(XASE1.accountsegment3,'$') AND NVL(XASE.accountseg ment4,'$') = NVL(XASE1.accountsegment4,'$') AND NVL(XASE.accountseg ment5,'$') = NVL(XASE1.accountsegment5,'$') AND NVL(XASE.accountseg ment6,'$') = NVL(XASE1.accountsegment6,'$') AND NVL(XASE.accountseg ment7,'$') = NVL(XASE1.accountsegment7,'$') AND NVL(XASE.accountseg ment8,'$') = NVL(XASE1.accountsegment8,'$') AND XASE.operating_unit = XASE1.operating_unit AND XASE.invoice_number = XASE1.invoice_number AND XASE.supplier_name = XASE1.supplier_name AND XASE.entered_cur XASE1.sob_currency AND GTR.period_name = p_period_name AND GTR.to_curren cy_code = XASE.entered_cur AND GTR.set_of_bo oks_id = XASE.gl_set_of_books_id AND GTR.set_of_books_id = XASE1 .gl_set_of_books_id ) WHERE XASE.entered_cur XASE.sob_currency ; -- This it to update for the transactional currency and amount XASE.entered_cur = XASE XASE.supplier_name XASE.invoice_numbe XASE.operating_unilv_err_stage := 'This it to update for the transactional currency and amount for local_cur = USD '; UPDATE XXYH_TRIT_AP_SBL_EXT_TMP XASE SET (XASE.reporting_amount, XASE.reporting_cur) = (SELECT XASE1.local_amount ,XASE1.local_cur FROM XXYH_TRIT_AP_SBL _EXT_TMP XASE1 WHERE XASE.accountse gment1 = XASE1.accountsegment1 AND NVL(XASE.accou ntsegment2,'$') = NVL(XASE1.accountsegment2,'$') AND NVL(XASE.accountsegment3,'$') = NVL(XASE1.accountsegment3,'$') AND NVL(XASE.accou ntsegment4,'$') = NVL(XASE1.accountsegment4,'$') AND NVL(XASE.accou ntsegment5,'$') = NVL(XASE1.accountsegment5,'$') AND NVL(XASE.accou ntsegment6,'$') = NVL(XASE1.accountsegment6,'$') AND NVL(XASE.accou ntsegment7,'$') = NVL(XASE1.accountsegment7,'$') AND NVL(XASE.accou ntsegment8,'$') = NVL(XASE1.accountsegment8,'$') AND XASE.operating _unit = XASE1.operating_unit AND XASE.invoice_n umber = XASE1.invoice_number AND XASE.supplier_name = XASE1.supplier_name AND XASE.local_cur = XASE1.local_cur AND XASE.local_cur = 'USD' ) WHERE XASE.local_cur = 'USD' ; lv_err_stage := 'This it to update for the transactional currency and amount for local_cur USD '; UPDATE XXYH_TRIT_AP_SBL_EXT_TMP XASE SET (XASE.reporting_amount, XASE.reporting_cur) = (SELECT NVL(XASE1.local_amount,0)*NVL(GT R.eop_rate,0) , GTR.to_currency_code FROM XXYH_TRIT_AP_SBL _EXT_TMP XASE1 , gl_translation _rates GTR WHERE XASE.accountse gment1 = XASE1.accountsegment1 AND NVL(XASE.accou ntsegment2,'$') = NVL(XASE1.accountsegment2,'$') AND NVL(XASE.accountsegment3,'$') = NVL(XASE1.accountsegment3,'$') AND NVL(XASE.accou ntsegment4,'$') = NVL(XASE1.accountsegment4,'$') AND NVL(XASE.accou ntsegment5,'$') = NVL(XASE1.accountsegment5,'$')AND ntsegment6,'$') = NVL(XASE1.accountsegment6,'$') AND ntsegment7,'$') = NVL(XASE1.accountsegment7,'$') AND ntsegment8,'$') = NVL(XASE1.accountsegment8,'$') AND _unit umber name e urrency_code of_books_id of_books_id = XASE1.operating_unit AND = XASE1.invoice_number AND = XASE1.supplier_name AND = p_period_name = 'USD'NVL(XASE.accou NVL(XASE.accou NVL(XASE.accou XASE.operating XASE.invoice_n XASE.supplier_ GTR.period_nam AND AND GTR.to_c GTR.set_ GTR.set_= XASE1.gl_set_of_books_id AND = XASE.gl_set_of_books_id )WHERE XASE.local_cur 'USD' ; COMMIT; EXCEPTION WHEN OTHERS THEN errbuf := SUBSTR(SQLERRM, 1, 200) 'at :' lv_err_stage; retcode := 2; END; -- Printing the Header here. -- processing the summay IF p_extract_type = 'Y' THEN INSERT INTO XXYH_TRIT_AP_SBL_EXT_TMP ( legal_entity ,operating_unit ,concatenated_segments ,supplier_name ,invoice_number ,invoice_date ,invoice_curr ,invoice_amount ,remaining_amount ,invoice_description ,accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,local_cur ,entered_cur,reporting_cur ,entered_amount ,local_amount ,reporting_amount ,period_type ,period_name ,period_year ,period_num --,last_update_date --,last_updated_by ,gl_set_of_books_id ,sob_currency --,user_id --,date_created ) SELECT null --legal_entity ,null --,operating_unit ,null --,concatenated_segments ,null --,supplier_name ,null --,invoice_number ,null --,invoice_date ,null --,invoice_curr ,null --,invoice_amount ,null --,remaining_amount ,null --,invoice_description ,accountsegment1 ,accountsegment2 ,accountsegment3 ,null --,accountsegment4 ,null --,accountsegment5 ,null --,accountsegment6 ,null --,accountsegment7 ,null --,accountsegment8 ,null --,accountsegment9 ,null --,accountsegment10 ,accountname ,local_cur ,'USD' -- entered_cur ,reporting_cur ,SUM(entered_amount) ,SUM(local_amount) ,SUM(reporting_amount) ,period_type ,period_name ,period_year ,period_num --,last_update_date --,last_updated_by ,gl_set_of_books_id ,sob_currency --,user_id --,date_created FROM XXYH_TRIT_AP_SBL_EXT_TMP GROUP BY accountsegment1 ,accountsegment2 ,accountsegment3 ,accountname ,local_cur-- ,entered_cur ,reporting_cur ,period_type ,period_name ,period_year ,period_num ,gl_set_of_books_id ,sob_currency; delete from XXYH_TRIT_AP_SBL_EXT_TMP where legal_entity is not null; commit; END IF; -- end processing summary -- Printing the Header here. println(2, 'Printing the Header Info: ' println(1, ' AccountSegment1,' 'AccountSegment2,' 'AccountSegment3,' 'AccountSegment4,' 'AccountSegment5,' 'AccountSegment6,' 'AccountSegment7,' 'AccountSegment8,' 'AccountSegment9,' 'AccountSegment10,' 'Account_Name,' 'CCY1Code,' 'CCY1GLEndBalance,' 'CCY2Code,' 'CCY2GLEndBalance,' 'CCY3Code,' 'CCY3GLEndBalance,' 'Period,' 'Year' ); -- Start of the for loop x_rec_cnt := 0; x_rec_sum := 0; FOR lr_rec IN lcu_subledger_extract LOOP lv_err_stage := 'Printing the values to a output file.'; lv_rec := NULL; /*lv_rec := lr_rec.legal_entity ',' lr_rec.operating_unit ',' lr_rec.concatenated_segments ',' clean_str(lr_rec.supplier_name) ',' lr_rec.invoice_numb er ',' to_char(lr_rec.invoice_date,'DD-MON-YYYY') ',' lr_rec.invoice_curr ',' lr_rec.invoice_amount ',' lr_ rec.remaining_amount ',' xxyhclean_str(lr_rec.invoice_description);*/ SYSDATE);lv_rec := lr_rec.accountsegment1 ',' lr_rec.accountsegment2 ',' lr_rec.accountsegment3 ',' lr_rec.accountsegment4 ',' lr_rec.accountsegment5 ',' lr_rec.accountsegment6 ',' lr_rec.accountsegment7 ',' lr_rec.accountsegment8 ',' lr_rec.accountsegment9 ',' lr_rec.accountsegment10 ',' clean_str(lr_rec.accountnam e) ',' lr_rec.reporting_cur ',' round(lr_rec.reporting_amount, 2)*-1 ',' lr_rec.local_cur ',' round(lr_rec.local_amount,2)*-1 ',' lr_rec.entered_cur ',' round(lr_rec.entered_amount,2)*1 ',' to_char(to_date(lr_rec.period_name, 'MON-YY'), 'MM') ',' lr_rec.period_year; println(1, lv_rec); x_rec_cnt := x_rec_cnt + 1; x_rec_sum := x_rec_sum + round(lr_rec.reporting_amount,2)*-1; END LOOP; -- End of the for loop println(1,'FOOTER,' x_rec_cnt ',' x_rec_sum); -- launch concurrent program to cp out put to trintech directory println(2, 'launching cp program ...'); x_sfile := 'o' x_request_id '.out'; x_dfile := 'APBAL_' to_char(to_date(p_period_name, 'MON-YY'), 'MMYYYY') TO_ CHAR(SYSDATE,'MMDDYYYY') '.csv'; -cp_output('TRIT-OUT', x_sfile, x_dfile); println(2, 'launching cp program done'); -EXCEPTION WHEN OTHERS THEN errbuf := SUBSTR(SQLERRM, 1, 200) 'at :' lv_err_stage; retcode := 2; END asn_ap; -PROCEDURE asn_gl_USSOB(p_period_name IN VARCHAR2, p_entity IN VARCHAR2 ) AS BEGIN INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,final_yn ,attribute16 ,attribute15 ) SELECT gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc ,GB.currency_code ,GSOB.currency_code ,1 ,SUM( ( NVL(GB.begin_balance_dr,0) + NVL(GB.period_net_dr,0) ) - ( NVL(GB.begin_balance_cr,0) + NVL(GB.period_net_cr,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr _beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr _beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) ,GB.period_name ,GB.period_year ,'N' ,XTAV.group_by --attribute16 ,XTAV.eu_entered -- attribute15 FROM gl_balances GB ,gl_code_combinations GCC ,xxyh_trit_sob_v GSOB ,xxyh_trit_account_v XTAV ,xxyh_trit_company_v XTCV WHERE GB.set_of_books_id = GSOB.set_of_books_id AND GSOB.chart_of_accounts_id = GCC.chart_of_accounts_id AND GCC.code_combination_id = GB.code_combination_id AND GCC.segment2 = XTAV.flex_value AND XTAV.entity = p_entity--SUBSTR(lv_value_set,11, 2) --AND GSOB.name NOT IN ('YAHOO! CONSOLIDATED', 'YAHOO! DUMMY') AND GB.period_name = p_period_name AND NVL(GB.translated_flag,'R') = 'R' AND GB.currency_code != 'STAT' -- Added By Haribabu AND GCC.segment1 = XTCV.flex_value AND XTCV.entity = p_entity--lv_value_set/*ANDEXISTS (SELECT 'X' FROM xxyh_trit_company_v XTCV WHERE XTCV.flex_value = GCC.segment1 AND XTCV.entity = lv_value_set )*/ and gsob.currency_code = 'USD' GROUP BY gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc ,GB.currency_code ,GSOB.currency_code ,GB.period_name ,GB.period_year ,1 --,decode(GSOB.currency_code,GTR.to_currency_code,1,NVL(GTR.e op_rate,0)) ,XTAV.group_by ,XTAV.eu_entered; fnd_file.put_line(fnd_file.log,'Total Recs' SQL%ROWCOUNT); END asn_gl_USSOB; /******************************************************************************* ****************************** ** TITLE :- PROCEDURE gl_balances_extract ** $Id: XXYH_TRIT_PKG.pkb,v 1.11 2009/04/28 21:52:10 haribabu Exp $ ** FILE NAME :** AUTHOR :- Tanay Behera ** VERSION :- 1.0 ** CONTENTS :- Procedure creation script ** DESCRIPTION :- This is used to fetch data on a monthly basis for ** GL Balances for Trintech ** INPUTS :- p_period_name,p_value_set (mandatory) ** OUTPUTS :- extract file ** CREATION DATE :** CHANGE HISTORY :** Date Changed By Description ** 11-JUN-08 Tanay Behera Initial Version of Code ** 19-JUN-08 Tanay Behera Code modified not to include consolidated SOB ** 20-JUN-08 Tanay Behera Code modified to have year to date info. ** 10-JUL-08 Balajee Kalla clean_str added and few changes related flex val ues validations ** 11-JUL-08 Tanay Behera Made changes to the function clean_str and other changes for issues raised ** 04-AUG-08 Tanay Behera Added a new interim table to improve performance , i.e. XXYH_TRIN_TECH_INT ** 27-AUG-08 Haribabu Pantakapu Added condition to handle Unchecked and end da ted flex values for XXYH_TRIT_ASN_EU_GL_ACCS ** 29-AUG-08 Haribabu Pantakapu Added condition to exclude STAT currencies. ** 10-OCT-08 Haribabu Pantakapu Uncommented Group By clause in the insert stmtto fix the issue in PROD ** 10-OCT-08 Haribabu Pantakapu Incorporate the new value set to derive the fu nctional amts XXYH_TRIT_ASN_US_GL_GB_ACC ** 18-NOV-08 Haribabu Pantakapu Exclude enabled_flag and end_date condition fo r value set YGLOBAL_ACCOUNT ** 08-DEC-08 Haribabu Pantakapu Change to DFFs Sets for Selection Grouping Rul es ** File name convention to include the entity and sysdate ** 20-Jan-09 Haribabu Pantakapu Change Request ** 1) DFF at Set of Books to exclude the STAT set of books ******************************************************************************** ********************************/ PROCEDURE asn_gl ( errbuf ,retcode ,p_period_name ,p_value_set ) AS -- This cursor is to select the info for the extract. CURSOR lc_dat_cur IS SELECT XTTE.* ,XTTE.rowid FROM xxyh_trit_tmp XTTE where final_yn = 'Y'; -- commented by Balajee -- WHERE NVL(XTTE.entered_amount, 0) 0 ; --ORDER BY accountsegment1 -,accountsegment2 -,accountsegment3 ; --- declaration of variables. ln_counter NUMBER; ln_user_id NUMBER; lv_value_set VARCHAR2(100); ln_rate_total VARCHAR2(100); lv_err_stage VARCHAR2(1000); lv_rec VARCHAR2(500); -BEGIN x_progress := 'XXYH_TRIT_PKG.ASN_GL:10'; println(2, x_progress); FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Value of the Period Name is: ' p _period_name); FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Value of the Value Set Name is: ' p_value_set); -- Delete all the records from the interim table. BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE xxyh.xxyh_trit_tmp'; OUT OUT IN IN VARCHAR2 NUMBER VARCHAR2 VARCHAR2EXCEPTION WHEN OTHERS THEN DELETE FROM xxyh_trit_tmp; COMMIT; END; ln_user_id := FND_GLOBAL.USER_ID; lv_value_set := p_value_set ; IF lv_value_set = 'XXYH_TRIT_US' THEN -- First we are handling for the US related cases x_progress := 'XXYH_TRIT_PKG.ASN_GL:20'; println(2, x_progress); INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,final_yn ,attribute16 ,attribute15 -- New Added for EU Entered ) SELECT gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc -- FLEX_DESC -- Added By Haribabu on 08-Dec-2008 ,GB.currency_code ,GSOB.currency_code ,GSOB.currency_code ,SUM( ( NVL(GB.begin_balance_dr,0) + NVL(GB.period_net_dr,0) ) - ( NV L(GB.begin_balance_cr,0) + NVL(GB.period_net_cr,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr_beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr_beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) ,GB.period_name,GB.period_year -,'N' ,XTAV.group_by --attribute16 u FROM-- Changed on 08-Dec-2008 By Haribab,XTAV.eu_entered -- attribute15 gl_balances GB ,gl_code_combinations GCC ,xxyh_trit_sob_v GSOB ,xxyh_trit_account_v XTAV -- Added By Haribabu on 08-Dec-2008 ,xxyh_trit_company_v XTCV WHERE GB.set_of_books_id = GSOB.set_of_books_id AND GSOB.chart_of_accounts_id = GCC.chart_of_accounts_id AND GCC.code_combination_id = GB.code_combination_id AND GCC.segment2 = XTAV.flex_value --AND GSOB.name NOT IN ('YAHOO! CONSOLIDATED', 'YAHOO! DUMMY') -- 19-JUN-08 Tanay Behera -- "YAHOO! DUMMY" ADDED BY BALAJEE AND XTAV.entity = 'US'--SUBSTR(lv_value_set,11,2) AND GB.period_name = p_period_name AND NVL(GB.translated_flag,'R') = 'R' AND GB.currency_code != 'STAT' -- Added By Haribabu AND GCC.segment1 = XTCV.flex_value AND XTCV.entity = 'US'--lv_value_set -- Added By Haribabu on 08-Dec-2008 /*AND EXISTS (SELECT 'X' FROM xxyh_trit_company_v XTCV WHERE XTCV.flex_value = GCC.segment1 AND XTCV.entity = lv_value_set )*/ GROUP BY gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc ,GB.currency_code ,GSOB.currency_code ,GSOB.currency_code ,GB.period_name ,GB.period_year ,XTAV.group_by --attribute16; Changed on 08-Dec-2008 By Haribabu ,XTAV.eu_entered; -- Added By Haribabu on 08-Dec-2008 By Haribabu ELSIF lv_value_set = 'XXYH_TRIT_EU' THEN -- Here we are handling for the EU related cases x_progress := 'XXYH_TRIT_PKG.ASN_GL:30'; println(2, x_progress); INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,final_yn ,attribute16 ,attribute15 ) SELECT gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc ,GB.currency_code ,GSOB.currency_code ,NVL(GTR.eop_rate,0) ,SUM( ( NVL(GB.begin_balance_dr,0) + NVL(GB.period_net_dr,0) ) - ( NV L(GB.begin_balance_cr,0) + NVL(GB.period_net_cr,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr_beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr_beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) * NVL(GTR .eop_rate,0) ,GB.period_name ,GB.period_year ,'N' ,XTAV.group_by --attribute16 ,XTAV.eu_entered -- attribute15 FROM gl_balances GB ,gl_code_combinations GCC ,xxyh_trit_sob_v GSOB ,gl_translation_rates GTR ,xxyh_trit_account_v XTAV ,xxyh_trit_company_v XTCV WHERE GB.set_of_books_id = GSOB.set_of_books_id AND GSOB.set_of_books_id = GTR.set_of_books_id AND GSOB.chart_of_accounts_id = GCC.chart_of_accounts_id AND GCC.code_combination_id = GB.code_combination_id AND GCC.segment2 = XTAV.flex_value AND XTAV.entity = 'EU'--SUBSTR(lv_value_set,11,2) --AND GSOB.name NOT IN ('YAHOO! CONSOLIDATED', 'YAHOO! DUMMY') -- 19-JUN-08 Tanay Behera -- "YAHOO! DUMMY" ADDED BY BALAJEE AND GB.period_name = p_period_name AND NVL(GB.translated_flag,'R') = 'R'AND GB.currency_code != 'STAT' -- Added By Haribabu AND GCC.segment1 = XTCV.flex_value AND XTCV.entity = 'EU'--lv_value_set /*AND EXISTS (SELECT 'X' FROM xxyh_trit_company_v XTCV WHERE XTCV.flex_value = GCC.segment1 AND XTCV.entity = lv_value_set )*/ AND NVL(GTR.period_name,p_period_name) = p_period_name AND NVL(GTR.to_currency_code,'USD') = 'USD' GROUP BY gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc ,GB.currency_code ,GSOB.currency_code ,GB.period_name ,GB.period_year ,NVL(GTR.eop_rate,0) ,XTAV.group_by -- Added By Haribabu on 08-Dec-2008 ,XTAV.eu_entered; -- Added By Haribabu on 08-Dec-2008 ELSIF lv_value_set = 'XXYH_TRIT_LA' THEN -- Here we are handling for the EU related cases x_progress := 'XXYH_TRIT_PKG.ASN_GL:30'; println(2, x_progress); INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,final_yn ,attribute16 ,attribute15 ) SELECT gcc.segment1,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7 ,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc ,GB.currency_code ,GSOB.currency_code ,NVL(GTR.eop_rate,0) ,SUM( ( NVL(GB.begin_balance_dr,0) + NVL(GB.period_net_dr,0) ) - ( NV L(GB.begin_balance_cr,0) + NVL(GB.period_net_cr,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr_beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) ,SUM( ( NVL(GB.begin_balance_dr_beq,0) + NVL(GB.period_net_dr_beq,0) ) - ( NVL(GB.begin_balance_cr_beq,0) + NVL(GB.period_net_cr_beq,0) )) * NVL(GTR .eop_rate,0)--decode(GSOB.currency_code,GTR.to_currency_code,1,NVL(GTR.eop_rate, 0)) ,GB.period_name ,GB.period_year ,'N' ,XTAV.group_by --attribute16 ,XTAV.eu_entered -- attribute15 FROM gl_balances GB ,gl_code_combinations GCC ,xxyh_trit_sob_v GSOB ,gl_translation_rates GTR ,xxyh_trit_account_v XTAV ,xxyh_trit_company_v XTCV WHERE GB.set_of_books_id = GSOB.set_of_books_id AND GSOB.set_of_books_id = GTR.set_of_books_id AND GSOB.chart_of_accounts_id = GCC.chart_of_accounts_id AND GCC.code_combination_id = GB.code_combination_id AND GCC.segment2 = XTAV.flex_value AND XTAV.entity = 'LA'--SUBSTR(lv_value_set,11,2) --AND GSOB.name NOT IN ('YAHOO! CONSOLIDATED', 'YAHOO! DUMMY') AND GB.period_name = p_period_name AND NVL(GB.translated_flag,'R') = 'R' AND GB.currency_code != 'STAT' -- Added By Haribabu AND GCC.segment1 = XTCV.flex_value AND XTCV.entity = 'LA'--lv_value_set /*AND EXISTS (SELECT 'X' FROM xxyh_trit_company_v XTCV WHERE XTCV.flex_value = GCC.segment1 AND XTCV.entity = lv_value_set )*/ AND NVL(GTR.period_name,p_period_name) = p_period_name AND NVL(GTR.to_currency_code,'USD') = 'USD' GROUP BY gcc.segment1 ,gcc.segment2 ,gcc.segment3 ,gcc.segment4 ,gcc.segment5 ,gcc.segment6 ,gcc.segment7,gcc.segment8 ,gcc.segment9 ,gcc.segment10 ,XTAV.flex_desc ,GB.currency_code ,GSOB.currency_code ,GB.period_name ,GB.period_year ,NVL(GTR.eop_rate,0) --,decode(GSOB.currency_code,GTR.to_currency_code,1,NVL(GTR.eop_rate, 0)) ,XTAV.group_by ,XTAV.eu_entered; asn_gl_USSOB(p_period_name => p_period_name ,p_entity => 'LA' ); END IF; --- This commit is to commit the data into interim table COMMIT; -- This is to update the values for the data. x_progress := 'XXYH_TRIT_PKG.ASN_GL:40'; println(2, x_progress); BEGIN lv_err_stage := 'Update Part to get the correct balance' ; UPDATE xxyh_trit_tmp f1 SET f1.entered_amt = f1.entered_amt - NVL((SELECT SUM(f2.local_amt) FROM xxyh_trit_tmp f2 WHERE f2.accountsegment1 = f1.accountsegment1 AND NVL(f2.accountsegment 2,'$') = NVL(f1.accountsegment2,'$') AND NVL(f2.accountsegment 3,'$') = NVL(f1.accountsegment3,'$') AND NVL(f2.accountsegment 4,'$') = NVL(f1.accountsegment4,'$') AND NVL(f2.accountsegment 5,'$') = NVL(f1.accountsegment5,'$') AND NVL(f2.accountsegment 6,'$') = NVL(f1.accountsegment6,'$') AND NVL(f2.accountsegment 7,'$') = NVL(f1.accountsegment7,'$') AND NVL(f2.accountsegment 8,'$') = NVL(f1.accountsegment8,'$') AND f2.accountname = f1.a ccountname AND f2.period_year = f1.p eriod_year AND f2.period_name = f1.p eriod_name AND f2.entered_cur f1. entered_cur AND f2.local_cur = f1.loc al_cur ),0) WHERE f1.local_cur = f1.entered_cur;lv_err_stage := 'Update Part to get the correct balance for same currency '; COMMIT; x_progress := 'XXYH_TRIT_PKG.ASN_GL:50'; println(2, x_progress); UPDATE xxyh_trit_tmp f1 SET f1.local_amt = NVL((SELECT SUM(f2.entered_amt) FROM xxyh_trit_tmp f2 WHERE f2.accountsegment1 = f1.accountsegment 1 AND ccountsegment2,'$') AND ccountsegment3,'$') AND ccountsegment4,'$') AND ccountsegment5,'$') AND ccountsegment6,'$') AND ccountsegment7,'$') AND ccountsegment8,'$') AND f2.accountname AND f2.period_year AND f2.period_name AND f2.entered_cur AND f2.local_cur ),0) WHERE f1.local_cur = f1.entered_cur; COMMIT; IF p_value_set = 'XXYH_TRIT_US' THEN UPDATE xxyh_trit_tmp f1 SET f1.reporting_amt = NVL((SELECT SUM((f2.local_amt)) FROM xxyh_trit_tmp f2 WHERE f2.accountsegment1 = f1.accountsegme nt1 AND .accountsegment2,'$') AND .accountsegment3,'$') AND .accountsegment4,'$') AND .accountsegment5,'$') AND .accountsegment6,'$') AND .accountsegment7,'$') AND .accountsegment8,'$') AND f2.accountname = f1.accountname NVL(f2.accountsegment8,'$') = NVL(f1 NVL(f2.accountsegment7,'$') = NVL(f1 NVL(f2.accountsegment6,'$') = NVL(f1 NVL(f2.accountsegment5,'$') = NVL(f1 NVL(f2.accountsegment4,'$') = NVL(f1 NVL(f2.accountsegment3,'$') = NVL(f1 NVL(f2.accountsegment2,'$') = NVL(f1 = = = = = f1.accountname f1.period_year f1.period_name f1.entered_cur f1.local_cur NVL(f2.accountsegment8,'$') = NVL(f1.a NVL(f2.accountsegment7,'$') = NVL(f1.a NVL(f2.accountsegment6,'$') = NVL(f1.a NVL(f2.accountsegment5,'$') = NVL(f1.a NVL(f2.accountsegment4,'$') = NVL(f1.a NVL(f2.accountsegment3,'$') = NVL(f1.a NVL(f2.accountsegment2,'$') = NVL(f1.aAND f2.period_year AND f2.period_name AND f2.entered_cur AND f2.local_cur ),0) WHERE f1.reporting_cur = f1.local_cur; END IF; x_progress := 'XXYH_TRIT_PKG.ASN_GL:60'; println(2, x_progress); COMMIT;= = = =f1.period_year f1.period_name f1.entered_cur f1.local_curIF p_value_set IN ('XXYH_TRIT_EU','XXYH_TRIT_LA') THEN UPDATE xxyh_trit_tmp f1 SET f1.reporting_amt = NVL((SELECT SUM((f2.local_amt*f1.reporting_cu r)) -- SUM ADDED BY BALAJEE KALLA FROM xxyh_trit_tmp f2 WHERE f2.accountsegment1 = f1.accountsegme nt1 AND NVL(f2.accountsegment2,'$') = NVL(f1 .accountsegment2,'$') AND NVL(f2.accountsegment3,'$') = NVL(f1 .accountsegment3,'$') AND NVL(f2.accountsegment4,'$') = NVL(f1 .accountsegment4,'$') AND NVL(f2.accountsegment5,'$') = NVL(f1 .accountsegment5,'$') AND NVL(f2.accountsegment6,'$') = NVL(f1 .accountsegment6,'$') AND NVL(f2.accountsegment7,'$') = NVL(f1 .accountsegment7,'$') AND NVL(f2.accountsegment8,'$') = NVL(f1 .accountsegment8,'$') AND f2.accountname = f1.accountname AND f2.period_year = f1.period_year AND f2.period_name = f1.period_name AND f2.entered_cur = f1.entered_cur AND f2.local_cur = f1.local_cur ),0) WHERE f1.local_cur = f1.entered_cur; END IF; EXCEPTION WHEN OTHERS THEN errbuf := SUBSTR(SQLERRM, 1, 200) 'at :' lv_err_stage; retcode := 2; END; --- This commit is to commit the data into interim table COMMIT; BEGIN lv_err_stage := 'The currency USD for all CCY1Code' ; UPDATE xxyh_trit_tmp f1 SET f1.reporting_cur = 'USD'WHERE f1.reporting_amt IS NOT NULL AND f1.reporting_cur 'USD'; EXCEPTION WHEN OTHERS THEN errbuf := SUBSTR(SQLERRM, 1, 200) 'at :' lv_err_stage; retcode := 2; END; --- This commit is to commit the data into interim table after inserting th e translated value. COMMIT; x_progress := 'XXYH_TRIT_PKG.ASN_GL:70'; println(2, x_progress); -- check for the entered currency group by IF lv_value_set IN ('XXYH_TRIT_EU','XXYH_TRIT_LA') THEN UPDATE xxyh_trit_tmp SET entered_cur = 'USD' , entered_amt = 0 WHERE NVL(attribute15,'N') = 'N'; END IF; COMMIT; -- Added By Haribabu on 10-OCT-2008. x_progress := 'XXYH_TRIT_PKG.ASN_GL:72'; UPDATE xxyh_trit_tmp SET attribute1 = accountsegment1 '-' accountsegment2 '-' accountseg ment3 '-' accountsegment4 '-' accountsegment5 '-' accountsegment6; COMMIT; -- Added By Haribabu on 10-OCT-2008 for US x_progress := 'XXYH_TRIT_PKG.ASN_GL:75'; IF lv_value_set = 'XXYH_TRIT_US' THEN UPDATE xxyh_trit_tmp SET entered_cur = 'USD' ,entered_amt = 0 WHERE attribute1 NOT IN (SELECT ffv.flex_value FROM fnd_flex_values_vl ffv ,fnd_flex_value_sets ffvs WHERE ffv.flex_value_set_id = ffvs.flex_val ue_set_id AND ffvs.flex_value_set_name = 'XXYH_TRIT_A SN_US_GL_GB_ACC' AND ffv.enabled_flag = 'Y' AND sysdate between nvl(ffv.start_date_activ e, sysdate) and nvl(ffv.end_date_active, sysdate)); END IF; COMMIT; x_progress := 'XXYH_TRIT_PKG.ASN_GL:76'; -- End 10-OCT-2008 -- Printing the Header here.-- begin compute group by -- segment1 INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT1' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:80';println(2, x_progress); INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT2' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num;x_progress := 'XXYH_TRIT_PKG.ASN_GL:90'; println(2, x_progress);INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT3' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:100'; println(2, x_progress); INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT4' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3,xtte.accountsegment4 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:110'; println(2, x_progress); INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,NULL ,NULL ,NULL ,NULL ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT5' AND xtte.final_yn = 'N'-- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:120'; println(2, x_progress);INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,NULL ,NULL ,NULL ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt),xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT6' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:130'; println(2, x_progress); INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,NULL ,NULL ,NULL ,xtte.accountname,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT7' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:140'; println(2, x_progress);INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,xtte.accountsegment8 ,NULL ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT8' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,xtte.accountsegment8 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:150'; println(2, x_progress);INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3 ,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,xtte.accountsegment8 ,xtte.accountsegment9 ,NULL ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT9' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,xtte.accountsegment8 ,xtte.accountsegment9 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year ,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:160'; println(2, x_progress);INSERT INTO xxyh_trit_tmp ( accountsegment1 ,accountsegment2 ,accountsegment3,accountsegment4 ,accountsegment5 ,accountsegment6 ,accountsegment7 ,accountsegment8 ,accountsegment9 ,accountsegment10 ,accountname ,entered_cur ,local_cur ,reporting_cur ,entered_amt ,local_amt ,reporting_amt ,period_name ,period_year ,period_num ,final_yn ) SELECT xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,xtte.accountsegment8 ,xtte.accountsegment9 ,xtte.accountsegment10 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,SUM(xtte.entered_amt) ,SUM(xtte.local_amt) ,SUM(xtte.reporting_amt) ,xtte.period_name ,xtte.period_year ,xtte.period_num ,'Y' FROM xxyh_trit_tmp xtte WHERE xtte.attribute16 = 'SEGMENT10' AND xtte.final_yn = 'N' -- AND valueset_name = lv_value_set GROUP by xtte.accountsegment1 ,xtte.accountsegment2 ,xtte.accountsegment3 ,xtte.accountsegment4 ,xtte.accountsegment5 ,xtte.accountsegment6 ,xtte.accountsegment7 ,xtte.accountsegment8 ,xtte.accountsegment9 ,xtte.accountsegment10 ,xtte.accountname ,xtte.entered_cur ,xtte.local_cur ,xtte.reporting_cur ,xtte.period_name ,xtte.period_year,xtte.period_num; x_progress := 'XXYH_TRIT_PKG.ASN_GL:170'; println(2, x_progress); -- end compute group by println(2, 'Printing the Header Info: ' println(1, 'AccountSegment1,' 'AccountSegment2,' 'AccountSegment3,' 'AccountSegment4,' 'AccountSegment5,' 'AccountSegment6,' 'AccountSegment7,' 'AccountSegment8,' 'AccountSegment9,' 'AccountSegment10,' 'AccountName,' 'CCY1Code,' 'CCY1GLEndBalance,' 'CCY2Code,' 'CCY2GLEndBalance,' 'CCY3Code,' 'CCY3GLEndBalance,' 'Period,' 'Year'); ln_counter := 0; ln_rate_total := 0; -FOR lr_rec IN lc_dat_cur LOOP lv_err_stage := 'Printing the values to a output file.'; lv_rec := NULL; ln_counter := ln_counter + 1; ln_rate_total := ln_rate_total + round(lr_rec.reporting_amt,2); /*lv_rec := '"' lr_rec.accountsegment1 '"' ',' '"' lr_rec.accountseg ment2 '"' ',' '"' lr_rec.accountsegment3 '"' ',' '"' lr_rec.accountsegment4 '"' ',' '"' lr_rec.accoun tsegment5 '"' ',' '"' lr_rec.accountsegment6 '"' ',' '"' lr_rec.accountsegment7 '"' ',' '"' lr_rec.accoun tsegment8 '"' ',' '"' lr_rec.accountsegment9 '"' ',' '"' lr_rec.accountsegment10 '"' ',' '"' clean_str(lr _rec.accountname) '"' ',' '"' lr_rec.reporting_cur '"' ',' '"' lr_rec.reportin g_amount '"' ',' '"' lr_rec.local_cur '"' ',' '"' lr_rec.local_amount '"' ',' '"' lr_rec.entered_cur '"' ',' '"' lr_rec.entered_amount '"' ',' '"' lr_rec.period_ name '"' ',' '"' lr_rec.period_year '"';*/ lv_rec := lr_rec.accountsegment1 ',' lr_rec.accountsegment2 ',' lr_rec.accountsegment3 ',' lr_rec.accountsegment4 ',' lr_rec.accountsegment5 ',' lr_rec.accountsegment6 ',' lr_rec.accountsegment7 ',' lr_rec.accountsegment8 ',' SYSDATE);lr_rec.accountsegment9 ',' lr_rec.accountsegment10 ',' clean_str(lr_rec.accountnam e) ',' lr_rec.reporting_cur ',' round(lr_rec.reporting_amt,2) ',' lr_rec.local_cur ',' round(lr_rec.local_amt,2) ',' lr_rec.entered_cur ',' round(lr_rec.entered_amt,2) ',' to_char(to_date(lr_rec.period_name, 'MON-YY'), 'MM') ',' lr_rec.period_year; ---FND_FILE.PUT_LINE(FND_FILE.LOG, 'The values are: ' ); println(1, lv_rec); END LOOP; lv_err_stage := 'Printing the values for the total rate value and record cou nt.'; println(1, 'FOOTER,' ln_counter ',' round(ln_rate_total,2)); -- launch concurrent program to cp out put to trintech directory println(2, 'launching cp program ...'); x_sfile := 'o' x_request_id '.out'; x_dfile := 'GLBAL_' to_char(to_date(p_period_name, 'MON-YY'), 'MMYYYY') SUB STR(p_value_set,11,2) '_' TO_CHAR(SYSDATE,'MMDDYYYY') '.csv'; -cp_output('TRIT-OUT', x_sfile, x_dfile); println(2, 'launching cp program done'); -EXCEPTION WHEN OTHERS THEN errbuf := SUBSTR(SQLERRM, 1, 200) 'at :' lv_err_stage; retcode := 2; END asn_gl; -PROCEDURE rcn_psi_ie(errbuf OUT VARCHAR2 ,retcode OUT NUMBER ) AS CURSOR c_seg IS SELECT substr(msi.segment1,instr(msi.segment1, '-')+1) segment1 ,gcc.concatenated_segments account FROM mtl_system_items msi ,org_organization_definitions ood ,gl_code_combinations_kfv gcc WHERE msi.organization_id = ood.organization_id AND ood.organization_code = 'PSI' AND msi.sales_account = gcc.code_combination_id; BEGIN x_progress := 'XXYH_TRIT_EXTRACTS_PKG.RCN_PSI_IE:10'; -println(1,'"Item","Account"'); -FOR i in c_seg LOOP println(1,'"' i.segment1 '","' i.account '"'); END LOOP; --- launch concurrent program to cp out put to trintech directory println(2, 'launching cp program ...'); x_tfile := 'N'; lv_recx_sfile := 'o' x_request_id '.out'; x_dfile := 'PSI-' TO_CHAR(SYSDATE, 'MON-YY') '.csv'; -cp_output('TRIT-OUT', x_sfile, x_dfile); println(2, 'launching cp program done'); -EXCEPTION WHEN OTHERS THEN println(2, 'Error Occured at : ' x_progress '-' sqlerrm); retcode := 2; END rcn_psi_ie; -/******************************************************************************* ********* ** TITLE :- PROCEDURE asn_fa_bal ** $Id: XXYH_TRIT_PKG.pkb,v 1.11 2009/04/28 21:52:10 haribabu Exp $ ** FILE NAME :** AUTHOR :- Haribabu ** VERSION :- 1.0 ** CONTENTS :- Procedure creation script ** DESCRIPTION :- This is used to fetch data on a monthly basis for ** FA Subledger Balances ** INPUTS :- p_period_name (mandatory) ** Summary Y/N (mandatory) default to 'Y' ** Company List ** OUTPUTS :- extract file ** CREATION DATE :** CHANGE HISTORY :** Date Changed By Description ** 12-AUG-08 Haribabu Pantakapu Initial Version of Code ** 28-AUG-08 Haribabu Pantakapu Added to fix conversion amount not populat ed for foreign currencies ** Sum(Ending Balance) of cost and reserve su mmary match fix. ** 16-SEP-08 Haribabu Pantakapu Companies to be included: based on GL Bala nce extract value listing ** Accounts to be included: only include 15xx xx or 16xxxx accounts from FA subledger ** 23-SEP-08 Haribabu Pantakapu Added ROUND function to round the amount c olumns values format to ####.## ** 14-NOV-08 Haribabu Pantakapu Reverse the sign on the amounts for Accumu lated Depr accounts ** 18-NOV-08 Haribabu Pantakapu Exclude enabled_flag and end_date conditio n for YGLOBAL_ACCOUNT Value Set ** 08-DEC-08 Haribabu Pantakapu DFFs and Value Sets for Selection ** File name convention to include the entity and sysdate ** 20-Jan-09 Haribabu Pantakapu Change Request ** 1) DFF at Set of Books to exclude the STAT set of books 2) Include only Corporate Books. ** 26-Jan-09 Haribabu Pantakapu Change Request 1) Exclude the FA books from the collapsed entities view defined based on FA_BOOK_CONTROLS to exclude ******************************************************************************** **********/ PROCEDURE asn_fa_bal(errbuf OUT NOCOPY VARCHAR2,retcode ,p_period_name ,p_entity ,p_flag ) ISOUT IN IN INNOCOPY NUMBER VARCHAR2 VARCHAR2 VARCHAR2 DEFAULT 'Y'CURSOR period_details IS SELECT P1.Period_Counter Period1_PC ,P1.Period_Open_Date Period1_POD ,NVL(P1.Period_Close_Date, SYSDATE) Period1_PCD ,P2.Period_Counter Period2_PC ,NVL(P2.Period_Close_Date, SYSDATE) Period2_PCD ,BC.Distribution_Source_Book Dist_Book ,GSOB.set_of_books_id set_of_books_id ,GSOB.currency_code currency_code ,GSOB.chart_of_accounts_id coa_id FROM FA_DEPRN_PERIODS P1, FA_DEPRN_PERIODS P2, XXYH_TRIT_FA_BOOK_CONTROLS_V BC, xxyh_trit_sob_v GSOB WHERE GSOB.set_of_books_id = BC.set_of_books_id --AND GSOB.name NOT IN ('YAHOO! CONSOLIDATED','YAHOO! DUMMY') --AND BC.BOOK_CLASS = 'CORPORATE' -- Included this condition in the view x xyh_trit_fa_book_controls_v AND P1.Book_Type_Code = BC.Book_Type_Code AND P1.Period_Name = p_period_name AND P2.Book_Type_Code = BC.Book_Type_Code AND P2.Period_Name = p_period_name AND (p_entity = 'XXYH_TRIT_US' AND GSOB.currency_code = 'USD' OR p_entity = 'XXYH_TRIT_EU' AND GSOB.currency_code 'USD'); l_period_rec CURSOR IS SELECT FROM WHERE period_details%ROWTYPE;l_data_output * xxyh_trit_tmp final_YN = p_flag;l_data VARCHAR2(4000); ln_counter NUMBER := 0; ln_rate_total NUMBER := 0; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE XXYH.XXYH_TRIT_TMP DROP STORAGE'; FOR l_period_rec IN period_details LOOP -- FND_FILE PRINT OF L PERIOD REC Record For Debugging. FND_FILE.PUT_LINE(FND_FILE.LOG,'Dist_Book - ' l_period_rec.Dist_Book ); FND_FILE.PUT_LINE(FND_FILE.LOG,'Period1_PC C); FND_FILE.PUT_LINE(FND_FILE.LOG,'Period1_POD OD); FND_FILE.PUT_LINE(FND_FILE.LOG,'Period1_PCD CD); - ' l_period_rec.Period1_P - ' l_period_rec.Period1_P - ' l_period_rec.Period1_PFND_FILE.PUT_LINE(FND_FILE.LOG,'Period2_PC C); FND_FILE.PUT_LINE(FND_FILE.LOG,'Period2_PCD CD);- ' l_period_rec.Period2_P - ' l_period_rec.Period2_PFND_FILE.PUT_LINE(FND_FILE.LOG,'set_of_books_id - ' l_period_rec.set_of_bo oks_id); FND_FILE.PUT_LINE(FND_FILE.LOG,'currency_code - ' l_period_rec.currency_ code); FND_FILE.PUT_LINE(FND_FILE.LOG,'coa_id - ' l_period_rec.coa_id); x_progress := 'XXYH_TRIT_PKG.asn_fa_bal:10'; INSERT INTO xxyh_trit_tmp(ATTRIBUTE16 ,ACCOUNTSEGMENT1 ,ACCOUNTSEGMENT2 ,LOCAL_CUR ,ENTERED_CUR ,REPORTING_CUR ,LOCAL_AMT ,ENTERED_AMT ,REPORTING_AMT ,PERIOD_NAME ,PERIOD_YEAR ,PERIOD_NUM ,USE_NUMBER_REP_1 ,USE_NUMBER_REP_2 ,USE_NUMBER_REP_3 ,LAST_UPDATE_DATE ,LAST_UPDATED_BY ,FINAL_YN ) SELECT /*+ index(dd FA_DEPRN_DETAIL_U1) */ 'COST' ,GLCC.segment1 ,CB.Asset_Cost_Acct ,l_period_rec.currency_code ,'USD' ,'USD' ,DD.cost ,0 ,DECODE(l_period_rec.currency_code,'USD',DD.cost,0) ,p_period_name ,TO_CHAR(TO_DATE(p_period_name,'MON-YY'),'YY') ,TO_CHAR(TO_DATE(p_period_name,'MON-YY'),'MM') ,DH.Asset_ID ,DH.Code_Combination_ID ,l_period_rec.set_of_books_id ,SYSDATE ,x_user_id ,p_flag FROM FA_DISTRIBUTION_HISTORY DH, GL_CODE_COMBINATIONS GLCC, FA_DEPRN_DETAIL DD, FA_ASSET_HISTORY AH, FA_CATEGORY_BOOKS CB, FA_BOOKS BK, XXYH_TRIT_COMPANY_V XTCV WHERE DH.Book_Type_Code = l_period_rec.Dist_Book AND GLCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID AND GLCC.chart_of_accounts_id = l_period_rec.coa_id AND l_period_rec.Period2_PCD BETWEEN DH.Date_Effective AND NVL(DH.Date_Ineffective, AND AND AND AND AND AND ter) l_period_rec.Dist_BookSYSDATE) DD.Asset_ID = DH.Asset_ID DD.Book_Type_Code = l_period_rec.Dist_Book DD.Distribution_ID = DH.Distribution_ID DD.Period_Counter

Recommended

View more >