ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 9DEDCF6C8D3B4BF29C22A7ACBDA3BCF5Y select null action_, null status_, null message_, null request_id_, null modified_columns_, x.* from ( select hp_i.party_name initiator, ftb.batch_number, fttv.trx_type_name batch_type, ftb.currency_code currency, gdct.user_conversion_type exchange_rate_type, ftb.batch_date, ftb.gl_date, ftb.control_total, ftb.description batch_description, ftb.note, xxen_util.meaning(ftb.status,'FUN_BATCH_STATUS',435) batch_status, null submit, hp_r.party_name recipient, fth.trx_number transaction_number, fth.init_amount_dr transaction_debit, fth.init_amount_cr transaction_credit, fth.description transaction_description, xxen_util.meaning(fth.status,'FUN_TRX_STATUS',435) transaction_status, gcck_i.concatenated_segments initiator_account, d.init_amount_dr initiator_debit, d.init_amount_cr initiator_credit, d.init_description initiator_dist_description, gcck_r.concatenated_segments recipient_account, d.reci_amount_dr recipient_debit, d.reci_amount_cr recipient_credit, d.reci_description recipient_dist_description, to_char(ftb.batch_id) batch_id, to_char(fth.trx_id) trx_id, to_char(d.init_dist_id) init_dist_id, to_char(d.reci_dist_id) reci_dist_id, null interface_batch_id, null interface_trx_id, null init_interface_dist_id, null reci_interface_dist_id, row_number() over (order by hp_i.party_name,ftb.batch_number,fth.trx_number,d.rn) upload_row from fun_trx_batches ftb, hz_parties hp_i, fun_trx_types_vl fttv, gl_daily_conversion_types gdct, fun_trx_headers fth, hz_parties hp_r, ( select y.trx_id, y.rn, max(decode(y.party_type_flag,'I',y.dist_id)) init_dist_id, max(decode(y.party_type_flag,'I',y.ccid)) init_ccid, max(decode(y.party_type_flag,'I',y.amount_dr)) init_amount_dr, max(decode(y.party_type_flag,'I',y.amount_cr)) init_amount_cr, max(decode(y.party_type_flag,'I',y.description)) init_description, max(decode(y.party_type_flag,'R',y.dist_id)) reci_dist_id, max(decode(y.party_type_flag,'R',y.ccid)) reci_ccid, max(decode(y.party_type_flag,'R',y.amount_dr)) reci_amount_dr, max(decode(y.party_type_flag,'R',y.amount_cr)) reci_amount_cr, max(decode(y.party_type_flag,'R',y.description)) reci_description from ( select fdl.trx_id, fdl.party_type_flag, fdl.dist_id, fdl.ccid, fdl.amount_dr, fdl.amount_cr, fdl.description, row_number() over (partition by fdl.trx_id,fdl.party_type_flag order by fdl.dist_id) rn from fun_dist_lines fdl where fdl.dist_type_flag='L' ) y group by y.trx_id, y.rn ) d, gl_code_combinations_kfv gcck_i, gl_code_combinations_kfv gcck_r where 1=1 and xxen_fun_upload.initiator_access(ftb.initiator_id)='Y' and ftb.initiator_id=hp_i.party_id and ftb.trx_type_id=fttv.trx_type_id and ftb.exchange_rate_type=gdct.conversion_type(+) and ftb.batch_id=fth.batch_id and fth.recipient_id=hp_r.party_id and fth.trx_id=d.trx_id(+) and d.init_ccid=gcck_i.code_combination_id(+) and d.reci_ccid=gcck_r.code_combination_id(+) ) x Initial version. Creates, updates and submits intercompany batches through the intercompany open interface and the Intercompany Import Program. U A xxen_fun_upload.upload_transaction xxen_fun_upload.import_transactions xxen_fun_upload.validate_transaction( p_initiator=>:$column$.initiator, p_recipient=>:$column$.recipient, p_transaction_number=>:$column$.transaction_number, p_transaction_debit=>:$column$.transaction_debit, p_transaction_credit=>:$column$.transaction_credit, p_initiator_account=>:$column$.initiator_account, p_initiator_debit=>:$column$.initiator_debit, p_initiator_credit=>:$column$.initiator_credit, p_recipient_account=>:$column$.recipient_account, p_recipient_debit=>:$column$.recipient_debit, p_recipient_credit=>:$column$.recipient_credit ) US FUN Intercompany Transaction Upload FUN Intercompany Transaction Upload ================================== Creates, updates and submits Advanced Global Intercompany System (AGIS) intercompany batches as entered on the Intercompany Super User > Transactions > Outbound pages: the batch with its initiator, the transactions to each recipient, and their initiator and recipient distributions. Each row is one initiator distribution and/or one recipient distribution of a transaction. Batch columns repeat on every row of the batch and transaction columns on every row of the transaction. Rows are grouped into a batch by Initiator and Batch Number. With automatic batch numbering (Intercompany System Options) the entered Batch Number only groups the rows and the batch receives the next number, which the result file shows. Rows are loaded into the intercompany open interface and imported by Oracle's Intercompany Import Program, which applies the same validations as the Outbound pages. A batch is imported completely or not at all, and the rows of a rejected batch show the import's rejection reasons. Submit = Yes imports the batch as sent, which starts the intercompany approval workflow, as the Submit button does. Upload Mode =========== Create ------ Opens an empty spreadsheet to enter new batches. Create, Update -------------- Additionally downloads existing batches. Only batches with status New can be changed or submitted. An updated batch is imported again as a whole, including its transactions and distributions not present in the upload, and replaces the existing batch once the import has accepted it. It keeps its batch number but gets a new batch id. Distributions cannot be deleted. Only batches of initiators the user is an intercompany contact for are listed, and the user must be assigned to an employee. Enginatics R12 only Upload 1=1 1 10 1=1 :p_upload_mode like '%'||xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 ftb.initiator_id=:initiator_id LOV custom select hp.party_id id, hp.party_name value, xfiv.name description from hz_parties hp, xle_firstparty_information_v xfiv where hp.party_type='ORGANIZATION' and hp.status='A' and hp.party_id in (select hpua.party_id from hz_party_usg_assignments hpua where hpua.party_usage_code='INTERCOMPANY_ORG' and hpua.effective_start_date<=sysdate and (hpua.effective_end_date>=sysdate or hpua.effective_end_date is null)) and xxen_fun_upload.initiator_access(hp.party_id)='Y' and fun_tca_pkg.get_le_id(hp.party_id)=xfiv.party_id(+) order by value US Initiator 3 30 1=1 ftb.batch_number=:batch_number Char US Batch Number 4 40 1=1 ftb.status=:batch_status LOV custom select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='FUN_BATCH_STATUS' and flvv.view_application_id=435 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by value NEW US Batch Status 5 50 1=1 ftb.gl_date>=:gl_date_from Date US GL Date From 6 60 1=1 ftb.gl_date<:gl_date_to+1 Date :$flex$.gl_date_from US GL Date To 5362ED96660C483CB2EE8228AA8CD9F5 Create Batches Empty sheet to enter new intercompany batches. ENGINATICS 6 BATCH_DATE 9 BATCH_DESCRIPTION 2 BATCH_NUMBER 3 BATCH_TYPE Y 8 CONTROL_TOTAL 4 CURRENCY 5 EXCHANGE_RATE_TYPE 7 GL_DATE 1 INITIATOR 17 INITIATOR_ACCOUNT 19 INITIATOR_CREDIT 18 INITIATOR_DEBIT 20 INITIATOR_DIST_DESCRIPTION 10 NOTE 12 RECIPIENT 21 RECIPIENT_ACCOUNT 23 RECIPIENT_CREDIT 22 RECIPIENT_DEBIT 24 RECIPIENT_DIST_DESCRIPTION 11 SUBMIT 15 TRANSACTION_CREDIT 14 TRANSACTION_DEBIT 16 TRANSACTION_DESCRIPTION 13 TRANSACTION_NUMBER Upload Mode C Y S Site Batch Number Batch Status GL Date From GL Date To Initiator 7EF994B62BC447ADB45C9E05E504423B Default Batches, transactions and their initiator and recipient distributions. ENGINATICS 6 BATCH_DATE 9 BATCH_DESCRIPTION 2 BATCH_NUMBER 11 BATCH_STATUS 3 BATCH_TYPE Y 8 CONTROL_TOTAL 4 CURRENCY 5 EXCHANGE_RATE_TYPE 7 GL_DATE 1 INITIATOR 19 INITIATOR_ACCOUNT 21 INITIATOR_CREDIT 20 INITIATOR_DEBIT 22 INITIATOR_DIST_DESCRIPTION 10 NOTE 13 RECIPIENT 23 RECIPIENT_ACCOUNT 25 RECIPIENT_CREDIT 24 RECIPIENT_DEBIT 26 RECIPIENT_DIST_DESCRIPTION 12 SUBMIT 16 TRANSACTION_CREDIT 15 TRANSACTION_DEBIT 17 TRANSACTION_DESCRIPTION 14 TRANSACTION_NUMBER 18 TRANSACTION_STATUS Upload Mode C S Site A9F0B1EBEDC14CD7BD3530E5ABC76072 Submit New Batches Lists the batches with status New to submit them by setting Submit to Yes. ENGINATICS 6 BATCH_DESCRIPTION 2 BATCH_NUMBER 7 BATCH_STATUS 3 BATCH_TYPE Y 4 CURRENCY 5 GL_DATE 1 INITIATOR 9 RECIPIENT 8 SUBMIT 12 TRANSACTION_CREDIT 11 TRANSACTION_DEBIT 10 TRANSACTION_NUMBER 13 TRANSACTION_STATUS Batch Status NEW Upload Mode C,U S Site 7EF994B62BC447ADB45C9E05E504423B 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 INITIATOR LOV custom varchar2 360 select hp.party_id id, hp.party_name value, xfiv.name description from hz_parties hp, xle_firstparty_information_v xfiv where hp.party_type='ORGANIZATION' and hp.status='A' and hp.party_id in (select hpua.party_id from hz_party_usg_assignments hpua where hpua.party_usage_code='INTERCOMPANY_ORG' and hpua.effective_start_date<=sysdate and (hpua.effective_end_date>=sysdate or hpua.effective_end_date is null)) and xxen_fun_upload.initiator_access(hp.party_id)='Y' and fun_tca_pkg.get_le_id(hp.party_id)=xfiv.party_id(+) order by value Y 7 BATCH_NUMBER Char varchar2 20 Y 8 BATCH_TYPE LOV custom varchar2 25 select fttv.trx_type_id id, fttv.trx_type_name value, fttv.description from fun_trx_types_vl fttv where fttv.enabled_flag='Y' order by value Y 9 CURRENCY LOV varchar2 15 QP Currency 42A28B894C4F42EC961E6C2D252DDED8 select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag = 'Y' and fcv.currency_flag='Y' and trunc(sysdate) between nvl(fcv.start_date_active,trunc(sysdate)) and nvl(fcv.end_date_active,trunc(sysdate)) order by value xxen_fun_upload.default_currency(:$column$.initiator) Y 10 EXCHANGE_RATE_TYPE LOV custom varchar2 30 select gdct.conversion_type id, gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct where gdct.conversion_type<>'User' order by value select gdct.user_conversion_type from fun_system_options fso, gl_daily_conversion_types gdct where fso.exchg_rate_type=gdct.conversion_type 11 BATCH_DATE Date date select trunc(sysdate) from dual Y 12 GL_DATE Date date select trunc(sysdate) from dual Y 13 CONTROL_TOTAL Number number 14 BATCH_DESCRIPTION Char varchar2 240 15 NOTE Char varchar2 240 16 BATCH_STATUS Char varchar2 80 Y 17 SUBMIT LOV varchar2 80 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 18 RECIPIENT LOV custom varchar2 360 select hp.party_id id, hp.party_name value, xfiv.name description from hz_parties hp, xle_firstparty_information_v xfiv where hp.party_type='ORGANIZATION' and hp.status='A' and hp.party_id in (select hpua.party_id from hz_party_usg_assignments hpua where hpua.party_usage_code='INTERCOMPANY_ORG' and hpua.effective_start_date<=sysdate and (hpua.effective_end_date>=sysdate or hpua.effective_end_date is null)) and hp.party_name<>nvl(:$column$.initiator,'~') and fun_tca_pkg.get_le_id(hp.party_id)=xfiv.party_id(+) order by value Y 19 TRANSACTION_NUMBER Char varchar2 15 Y 20 TRANSACTION_DEBIT Number number 21 TRANSACTION_CREDIT Number number 22 TRANSACTION_DESCRIPTION Char varchar2 240 23 TRANSACTION_STATUS Char varchar2 80 Y 24 INITIATOR_ACCOUNT LOV custom varchar2 240 Y select gcck.concatenated_segments value, null description from gl_ledgers gl, gl_code_combinations_kfv gcck where gl.ledger_id=fun_trx_entry_util.get_ledger_id(xxen_fun_upload.intercompany_party_id(:$column$.initiator),'I') and gl.chart_of_accounts_id=gcck.chart_of_accounts_id and gcck.enabled_flag='Y' and gcck.detail_posting_allowed='Y' and gcck.summary_flag='N' order by value 25 INITIATOR_DEBIT Number number 26 INITIATOR_CREDIT Number number 27 INITIATOR_DIST_DESCRIPTION Char varchar2 240 28 RECIPIENT_ACCOUNT LOV custom varchar2 240 Y select gcck.concatenated_segments value, null description from gl_ledgers gl, gl_code_combinations_kfv gcck where gl.ledger_id=fun_trx_entry_util.get_ledger_id(xxen_fun_upload.intercompany_party_id(:$column$.recipient),'R') and gl.chart_of_accounts_id=gcck.chart_of_accounts_id and gcck.enabled_flag='Y' and gcck.detail_posting_allowed='Y' and gcck.summary_flag='N' order by value 29 RECIPIENT_DEBIT Number number 30 RECIPIENT_CREDIT Number number 31 RECIPIENT_DIST_DESCRIPTION Char varchar2 240 32 BATCH_ID Char varchar2 Y Y 33 TRX_ID Char varchar2 Y Y 34 INIT_DIST_ID Char varchar2 Y Y 35 RECI_DIST_ID Char varchar2 Y Y 36 INTERFACE_BATCH_ID Char varchar2 Y Y 37 INTERFACE_TRX_ID Char varchar2 Y Y 38 INIT_INTERFACE_DIST_ID Char varchar2 Y Y 39 RECI_INTERFACE_DIST_ID Char varchar2 Y Y 40 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_action in Char ACTION_CODE_ U 4 p_initiator_id in Number INITIATOR U 5 p_batch_number in Char BATCH_NUMBER U 6 p_batch_type_id in Number BATCH_TYPE U 7 p_currency in Char CURRENCY U 8 p_exchange_rate_type in Char Y EXCHANGE_RATE_TYPE U 9 p_batch_date in Date BATCH_DATE U 10 p_gl_date in Date GL_DATE U 11 p_control_total in Number Y CONTROL_TOTAL U 12 p_batch_description in Char Y BATCH_DESCRIPTION U 13 p_note in Char Y NOTE U 14 p_submit in Char Y SUBMIT U 15 p_recipient_id in Number RECIPIENT U 16 p_transaction_number in Char TRANSACTION_NUMBER U 17 p_transaction_debit in Number Y TRANSACTION_DEBIT U 18 p_transaction_credit in Number Y TRANSACTION_CREDIT U 19 p_transaction_description in Char Y TRANSACTION_DESCRIPTION U 20 p_initiator_account in Char Y INITIATOR_ACCOUNT U 21 p_initiator_debit in Number Y INITIATOR_DEBIT U 22 p_initiator_credit in Number Y INITIATOR_CREDIT U 23 p_initiator_dist_description in Char Y INITIATOR_DIST_DESCRIPTION U 24 p_recipient_account in Char Y RECIPIENT_ACCOUNT U 25 p_recipient_debit in Number Y RECIPIENT_DEBIT U 26 p_recipient_credit in Number Y RECIPIENT_CREDIT U 27 p_recipient_dist_description in Char Y RECIPIENT_DIST_DESCRIPTION U 28 p_batch_id in Char Y BATCH_ID U 29 p_trx_id in Char Y TRX_ID U 30 p_init_dist_id in Char Y INIT_DIST_ID U 31 p_reci_dist_id in Char Y RECI_DIST_ID U 32 p_interface_batch_id in Char Y INTERFACE_BATCH_ID U 33 p_interface_batch_id_out out Char INTERFACE_BATCH_ID U 34 p_interface_trx_id_out out Char INTERFACE_TRX_ID U 35 p_init_interface_dist_id_out out Char INIT_INTERFACE_DIST_ID U 36 p_reci_interface_dist_id_out out Char RECI_INTERFACE_DIST_ID P 1 status_ out Char STATUS_CODE_ P 2 message_ out Char MESSAGE_ P 3 request_id_ out Number REQUEST_ID_ P 4 p_interface_batch_id in Char INTERFACE_BATCH_ID Error SQL Order By upload_row Success SQL select xu.action_, xu.status_, xu.message_, xu.request_id_, xu.modified_columns_, hp_i.party_name initiator, ftb.batch_number, fttv.trx_type_name batch_type, ftb.currency_code currency, gdct.user_conversion_type exchange_rate_type, ftb.batch_date, ftb.gl_date, ftb.control_total, ftb.description batch_description, ftb.note, xxen_util.meaning(ftb.status,'FUN_BATCH_STATUS',435) batch_status, xu.submit, hp_r.party_name recipient, fth.trx_number transaction_number, fth.init_amount_dr transaction_debit, fth.init_amount_cr transaction_credit, fth.description transaction_description, xxen_util.meaning(fth.status,'FUN_TRX_STATUS',435) transaction_status, gcck_i.concatenated_segments initiator_account, fdl_i.amount_dr initiator_debit, fdl_i.amount_cr initiator_credit, fdl_i.description initiator_dist_description, gcck_r.concatenated_segments recipient_account, fdl_r.amount_dr recipient_debit, fdl_r.amount_cr recipient_credit, fdl_r.description recipient_dist_description, xu.batch_id, xu.trx_id, xu.init_dist_id, xu.reci_dist_id, xu.interface_batch_id, xu.interface_trx_id, xu.init_interface_dist_id, xu.reci_interface_dist_id, xu.upload_row from xxen_fun_intercompany_t_3258_u xu, fun_trx_batches ftb, hz_parties hp_i, fun_trx_types_vl fttv, gl_daily_conversion_types gdct, fun_trx_headers fth, hz_parties hp_r, fun_dist_lines fdl_i, gl_code_combinations_kfv gcck_i, fun_dist_lines fdl_r, gl_code_combinations_kfv gcck_r where xu.status_code_=xxen_upload.status_success and to_number(xu.batch_id)=ftb.batch_id and ftb.initiator_id=hp_i.party_id and ftb.trx_type_id=fttv.trx_type_id and ftb.exchange_rate_type=gdct.conversion_type(+) and to_number(xu.trx_id)=fth.trx_id and fth.recipient_id=hp_r.party_id and to_number(xu.init_dist_id)=fdl_i.dist_id(+) and fdl_i.ccid=gcck_i.code_combination_id(+) and to_number(xu.reci_dist_id)=fdl_r.dist_id(+) and fdl_r.ccid=gcck_r.code_combination_id(+)