ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y 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 US 41E1FBAD60F5B5E0E0630100007F1199Y select xxen_upload.action_meaning(xxen_upload.action_create) action_, xxen_upload.status_meaning(xxen_upload.status_new) status_, xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) message_, null request_id_, null modified_columns_, null row_id, -- haouv.name operating_unit, (select pts.user_transaction_source from pa_transaction_sources pts where pts.transaction_source = ptia.transaction_source) transaction_source, (select psl.meaning from pa_system_linkages psl where psl.function = ptia.system_linkage and rownum = 1) expnd_type_class, ptia.expenditure_ending_date expnd_ending_date, ptia.batch_name batch_name, ptia.orig_transaction_reference original_trans_ref, -- ptia.organization_name organization_name, ptia.expenditure_item_date expnd_item_date, ptia.project_number project_number, ptia.task_number task_number, ptia.expenditure_type expnd_type, ptia.quantity quantity, ptia.denom_raw_cost trans_raw_cost, ptia.raw_cost_rate, xxen_util.meaning(ptia.unmatched_negative_txn_flag,'YES_NO',0) negative_txn_flag, ptia.expenditure_comment, ptia.orig_user_expnd_trans_ref, ptia.orig_exp_txn_reference1, ptia.orig_exp_txn_reference2, ptia.vendor_number, -- xxen_util.meaning(nvl(:p_submit_import,'Y'),'YES_NO',0) import_transactions from ( --pa_ic_mtl_events_to_exp_items_upload_01 --scm ar invoices select distinct 0 org_id, 'IC Materials Inv/Date: '||ct.trx_number||' '|| to_char(ct.trx_date, 'DD-MON-YY') batch_name, to_date(ct.trx_date + decode(to_char(ct.trx_date, 'DY'), 'FRI', 0, 'THU', 1, 'WED', 2, 'TUE', 3, 'MON', 4, 'SUN', 5, 6), 'DD-MON-RR') expenditure_ending_date, 'SSS IC Materials' transaction_source, 'PJ' system_linkage, 'Y' unmatched_negative_txn_flag, '5200' organization_name, 'SCM Newington' orig_user_expnd_trans_ref, to_date(ct.trx_date, 'DD-MON-RR') expenditure_item_date, substr(p.segment1, 5, 6) project_number, decode ( (select distinct t1.task_number from apps.pa_tasks t1, apps.pa_projects_all p1 where substr(p.segment1, 5, 6) = p1.segment1 and p1.project_id = t1.project_id and t1.task_number = '6A01' ), null,decode(t.task_number, 'Fiber', '3AA', 'Extrusions', '3AB', 'Armor & Transitions', '3AC', 'Integrations', '3AD', 'Miscellaneous', '3AE', 'Product Loading', '3AF'), '6A01' ) task_number, 'Materials ('||upper(e.uom_code)||')' expenditure_type, e.quantity_billed quantity, e.unit_price raw_cost_rate, e.project_bill_amount denom_raw_cost, replace( ct.trx_number||'.'|| to_char(ctl.line_number, '000'), ' ', '') orig_transaction_reference, e.reference2 orig_exp_txn_reference1, 'Inv Org.Item ID: '|| e.inventory_org_id ||'.'|| e.inventory_item_id orig_exp_txn_reference2, '2275' vendor_number, e.description expenditure_comment from apps.pa_tasks t, apps.pa_events e, apps.pa_draft_invoice_items dii, apps.pa_draft_invoices_all di, apps.pa_projects_all p, apps.ra_customer_trx_lines_all ctl, apps.ra_customer_trx_all ct where 1=1 and --joins ct.customer_trx_id = ctl.customer_trx_id and ct.interface_header_attribute1 = p.segment1 and p.project_id = di.project_id and ct.interface_header_attribute2 = di.draft_invoice_num and di.project_id = dii.project_id and di.draft_invoice_num = dii.draft_invoice_num and ctl.interface_line_attribute6 = dii.line_num and dii.project_id = e.project_id and dii.task_id = e.task_id and ctl.description = e.description and dii.event_num = e.event_num and e.task_id = t.task_id and --permanent criteria ct.interface_header_context = 'PA INVOICES' and ct.bill_to_customer_id = 17311 and --subcom submarine systems (sss 61) ct.interface_header_attribute1 like 'SCM%' and e.event_type = 'PJM Manual Event' and e.project_bill_amount <> 0 and --exclude invoice lines previously imported or pending import --/* not exists ( (select 'ic invoice line previously imported' from apps.pa_expenditure_items_all ei, apps.pa_projects_all p1 where replace(ct.trx_number||'.'||to_char(ctl.line_number, '000'), ' ', '') = ei.orig_transaction_reference ) ) and not exists ( (select 'ic invoice line pending import' from apps.pa_transaction_interface_all ti where replace(ct.trx_number||'.'||to_char(ctl.line_number, '000'), ' ', '') = ti.orig_transaction_reference ) ) and --*/ --temporary criteria ct.trx_date >= :p_trx_date_fr union all --ots ic invoices select distinct 0 org_id, 'IC Materials Inv/Date: '||ct.trx_number||' '|| to_char(ct.trx_date, 'DD-MON-YY') batch_name, to_date(ct.trx_date + decode(to_char(ct.trx_date, 'DY'), 'FRI', 0, 'THU', 1, 'WED', 2, 'TUE', 3, 'MON', 4, 'SUN', 5, 6), 'DD-MON-RR') expenditure_ending_date, 'SSS IC Materials' transaction_source, 'PJ' system_linkage, 'Y' unmatched_negative_txn_flag, '5200' organization_name, 'Optical Transmission Systems' orig_user_expnd_trans_ref, to_date(ct.trx_date, 'DD-MON-RR') expenditure_item_date, (select distinct p.segment1 from apps.pa_projects_all p, apps.gl_code_combinations cc, apps.ra_cust_trx_line_gl_dist_all d where ctl.customer_trx_line_id = d.customer_trx_line_id and d.code_combination_id = cc.code_combination_id and cc.segment7 = p.segment1 ) project_number, (select distinct t.task_number from apps.pa_tasks t, apps.pa_projects_all p, apps.gl_code_combinations cc, apps.ra_cust_trx_line_gl_dist_all d where ctl.customer_trx_line_id = d.customer_trx_line_id and d.code_combination_id = cc.code_combination_id and cc.segment7 = p.segment1 and p.project_id = t.project_id and t.task_number in ( '6B01', '3BA') ) task_number, 'Materials ('||upper(ctl.uom_code)||')' expenditure_type, ctl.quantity_invoiced quantity, ctl.unit_selling_price raw_cost_rate, ctl.extended_amount denom_raw_cost, replace( ct.trx_number||'.'|| to_char(ctl.line_number, '000'), ' ', '') orig_transaction_reference, 'SO Ref: '||ct.interface_header_attribute1||'.'||interface_line_attribute10 orig_exp_txn_reference1, 'Inv Org.Item ID: '||ct.interface_header_attribute10||'.'||ctl.inventory_item_id orig_exp_txn_reference2, '2042' vendor_number, (select distinct i.segment9 from apps.mtl_system_items_b i where ctl.interface_line_attribute10 = i.organization_id and ctl.inventory_item_id = i.inventory_item_id ) ||' '|| ctl.description expenditure_comment from apps.ra_customer_trx_lines_all ctl, apps.ra_customer_trx_all ct, apps.ra_batch_sources_all bs where 1=1 and --joins ct.customer_trx_id = ctl.customer_trx_id and --permanent criteria bs.name = 'OTS MFG' and bs.batch_source_id = ct.batch_source_id and ct.bill_to_customer_id = 17311 and --subcom submarine systems (sss 61) ct.customer_trx_id = ctl.customer_trx_id and --exclude invoice lines previously imported or pending import --/* not exists ( (select 'ic invoice line previously imported' from apps.pa_expenditure_items_all ei where replace(ct.trx_number||'.'||to_char(ctl.line_number, '000'), ' ', '') = ei.orig_transaction_reference ) ) and not exists ( (select 'ic invoice line pending import' from apps.pa_transaction_interface_all ti where replace(ct.trx_number||'.'||to_char(ctl.line_number, '000'), ' ', '') = ti.orig_transaction_reference ) ) and --*/ --temporary criteria ct.trx_date >= :p_trx_date_fr ) ptia, hr_all_organization_units_vl haouv where 1=1 and ptia.org_id = haouv.organization_id and :p_upload_mode like '%' || xxen_upload.action_update U 197A8DE62B015A76E0636AFB0905DDF9 XXEN_PA_UPLOAD A Y xxen_pa_upload.upload_project_transaction xxen_pa_upload.submit_transation_import US SSS AR IC to PA Transaction Upload Extracts billing transactions from an internal supplier AR invoice for an internal customer then uploads as expenditure items to the internal customer project Upload 1=1 :p_report_code :p_submit_import :p_trx_date_fr :p_upload_mode 1 -20 :p_report_code Char PAXTRTRX US Report Code 2 -10 :p_upload_mode LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y 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 select xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) from dual Y US Upload Mode 3 10 :p_submit_import LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y US Import Transactions 4 20 :p_trx_date_fr Date to_date('01/04/2025','DD/MM/YYYY') Y US Transaction Date From 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 ROW_ID Char varchar2 Y 7 OPERATING_UNIT LOV custom varchar2 Y select hou.name value, null description from hr_operating_units hou where hou.organization_id in (select pi.org_id from pa_implementations pi) and hou.name = nvl(:$flex$.operating_unit,hou.name) order by hou.name select haouv.organization_id id, haouv.name value from hr_all_organization_units_vl haouv Y 8 TRANSACTION_SOURCE LOV custom varchar2 Y select pts.transaction_source id, pts.user_transaction_source value, pts.description from pa_transaction_sources pts where trunc(sysdate) between trunc(pts.start_date_active) and nvl(trunc(pts.end_date_active),trunc(sysdate)) and pts.transaction_source = nvl(:$flex$.transaction_source,pts.transaction_source) order by value select pts.transaction_source id, pts.user_transaction_source value from pa_transaction_sources pts where trunc(sysdate) between trunc(pts.start_date_active) and nvl(trunc(pts.end_date_active),trunc(sysdate)) Y 9 EXPND_TYPE_CLASS LOV custom varchar2 Y select psl.function id, psl.meaning value, null description from pa_transaction_sources pts, pa_system_linkages psl where pts.user_transaction_source = :$column$.transaction_source and trunc(sysdate) between nvl(trunc(pts.start_date_active),trunc(sysdate)) and nvl(trunc(pts.end_date_active),trunc(sysdate)) and (pts.system_linkage_function = psl.function or pts.system_linkage_function = 'ST' and psl.function in ('ST','OT') ) order by value select psl.function id, psl.meaning value from pa_system_linkages psl Y 10 EXPND_ENDING_DATE Date date Y 11 BATCH_NAME Char varchar2 Y 12 ORIGINAL_TRANS_REF Char varchar2 Y 13 ORGANIZATION_NAME LOV custom varchar2 Y select poev.organization_id id, poev.name value, null description from hr_all_organization_units_vl haouv, pa_organizations_expend_v poev where haouv.organization_id = poev.org_id and haouv.name = :$column$.operating_unit and poev.active_flag = 'Y' and sysdate between nvl(poev.date_from,sysdate) and nvl(poev.date_to,sysdate) order by value Y 14 EXPND_ITEM_DATE Date date Y 15 PROJECT_NUMBER LOV custom varchar2 Y select ppev.project_id id, ppev.project_number value, ppev.project_name || nvl2(ppev.project_description,' (' || ppev.project_description || ')',null) description from hr_all_organization_units_vl haouv, pa_projects_expend_v ppev where haouv.organization_id = ppev.expenditure_ou and haouv.name = :$column$.operating_unit and :$column$.expnd_item_date between nvl(ppev.start_date,:$column$.expnd_item_date) and nvl(ppev.completion_date,:$column$.expnd_item_date) order by value, description Y 16 TASK_NUMBER LOV custom varchar2 Y select ptev.task_id id, ptev.task_number value, ptev.task_name description from hr_all_organization_units_vl haouv, pa_projects_expend_v ppev, pa_tasks_expend_v ptev where haouv.organization_id = ppev.expenditure_ou and ppev.project_id = ptev.project_id and ppev.project_ou = ptev.org_id and haouv.name = :$column$.operating_unit and ppev.project_number = :$column$.project_number and :$column$.expnd_item_date between nvl(ptev.start_date,:$column$.expnd_item_date) and nvl(ptev.completion_date,:$column$.expnd_item_date) order by value, description Y 17 EXPND_TYPE LOV custom varchar2 Y select petsl.expenditure_type value, pet.description || ' (' || pet.expenditure_category || ') (' || pet.unit_of_measure || ')' description from pa_system_linkages psl, pa_expend_typ_sys_links petsl, pa_expenditure_types pet where petsl.system_linkage_function = psl.function and petsl.expenditure_type = pet.expenditure_type and :$column$.expnd_item_date between trunc(greatest(petsl.start_date_active,pet.start_date_active)) and nvl(trunc(least(petsl.end_date_active,pet.end_date_active)),:$column$.expnd_item_date) and psl.meaning = :$column$.expnd_type_class order by value Y 18 QUANTITY Number number Y 19 TRANS_RAW_COST Number number 20 RAW_COST_RATE Number number 21 NEGATIVE_TXN_FLAG LOV custom varchar2 Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' and lookup_code = 'Y' order by value,description select fl.lookup_code id, fl.meaning value from fnd_lookups fl where fl.lookup_type = 'YES_NO' 22 EXPENDITURE_COMMENT Char varchar2 23 ORIG_USER_EXPND_TRANS_REF Char varchar2 24 ORIG_EXP_TXN_REFERENCE1 Char varchar2 25 ORIG_EXP_TXN_REFERENCE2 Char varchar2 26 VENDOR_NUMBER Char varchar2 27 IMPORT_TRANSACTIONS LOV Oracle varchar2 Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description xxen_util.meaning(:$flex$.Import_Transactions,'YES_NO',0) Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 request_id_ out Number REQUEST_ID_ U 4 p_row_id in/out Char ROW_ID U 5 p_transaction_source in Char TRANSACTION_SOURCE U 6 p_batch_name in Char BATCH_NAME U 7 p_expenditure_ending_date in Date EXPND_ENDING_DATE U 8 p_employee_name in Char value U 9 p_employee_number in Char value U 10 p_organization_name in Char ORGANIZATION_NAME value U 11 p_expenditure_item_date in Date EXPND_ITEM_DATE U 12 p_project_name in Char value U 13 p_project_number in Char PROJECT_NUMBER value U 14 p_task_name in Char value U 15 p_task_number in Char TASK_NUMBER value U 16 p_expenditure_type in Char EXPND_TYPE U 17 p_non_labor_resource in Char U 18 p_non_labor_resource_org_name in Char value U 19 p_quantity in Number QUANTITY U 20 p_raw_cost in Number U 21 p_expenditure_comment in Char EXPENDITURE_COMMENT U 22 p_transaction_status_code in Char U 23 p_transaction_rejection_code in Char U 24 p_expenditure_id in Number U 25 p_orig_transaction_reference in Char ORIGINAL_TRANS_REF U 26 p_attribute_category in Char U 27 p_attribute1 in Char U 28 p_attribute2 in Char U 29 p_attribute3 in Char U 30 p_attribute4 in Char U 31 p_attribute5 in Char U 32 p_attribute6 in Char U 33 p_attribute7 in Char U 34 p_attribute8 in Char U 35 p_attribute9 in Char U 36 p_attribute10 in Char U 37 p_raw_cost_rate in Number RAW_COST_RATE U 38 p_interface_id in Number U 39 p_unmatched_negative_txn_flag in Char NEGATIVE_TXN_FLAG U 40 p_expenditure_item_id in Number U 41 p_org_id in Number OPERATING_UNIT U 42 p_dr_code_combination_id in Number U 43 p_cr_code_combination_id in Number U 44 p_cdl_system_reference1 in Char U 45 p_cdl_system_reference2 in Char U 46 p_cdl_system_reference3 in Char U 47 p_gl_date in Date U 48 p_burdened_cost in Number U 49 p_burdened_cost_rate in Number U 50 p_system_linkage in Char EXPND_TYPE_CLASS U 51 p_txn_interface_id in Number U 52 p_user_transaction_source in Char U 53 p_receipt_currency_amount in Number U 54 p_receipt_currency_code in Char U 55 p_receipt_exchange_rate in Number U 56 p_denom_currency_code in Char U 57 p_denom_raw_cost in Number TRANS_RAW_COST U 58 p_denom_burdened_cost in Number U 59 p_acct_rate_date in Date U 60 p_acct_rate_type in Char U 61 p_acct_exchange_rate in Number U 62 p_acct_raw_cost in Number U 63 p_acct_burdened_cost in Number U 64 p_acct_exchange_rounding_limit in Number U 65 p_project_currency_code in Char U 66 p_project_rate_date in Date U 67 p_project_rate_type in Char U 68 p_project_exchange_rate in Number U 69 p_orig_exp_txn_reference1 in Char ORIG_EXP_TXN_REFERENCE1 U 70 p_orig_exp_txn_reference2 in Char ORIG_EXP_TXN_REFERENCE2 U 71 p_orig_exp_txn_reference3 in Char U 72 p_orig_user_exp_txn_reference in Char ORIG_USER_EXPND_TRANS_REF U 73 p_vendor_number in Char VENDOR_NUMBER U 74 p_override_to_organization_nam in Char value U 75 p_reversed_orig_txn_reference in Char U 76 p_billable_flag in Char U 77 p_person_business_group_name in Char value U 78 p_projfunc_currency_code in Char U 79 p_projfunc_cost_rate_type in Char value U 80 p_projfunc_cost_rate_date in Date U 81 p_projfunc_cost_exchange_rate in Number U 82 p_project_raw_cost in Number U 83 p_project_burdened_cost in Number U 84 p_assignment_name in Char value U 85 p_work_type_name in Char value U 86 p_cdl_system_reference4 in Char U 87 p_accrual_flag in Char U 88 p_project_id in Number U 89 p_task_id in Number U 90 p_person_id in Number U 91 p_organization_id in Number U 92 p_non_labor_resource_org_id in Number U 93 p_vendor_id in Number U 94 p_override_to_organization_id in Number U 95 p_assignment_id in Number U 96 p_work_type_id in Number U 97 p_person_business_group_id in Number U 98 p_inventory_item_id in Number U 99 p_wip_resource_id in Number U 100 p_unit_of_measure in Char U 101 p_po_number in Char value U 102 p_po_header_id in Number U 103 p_po_line_num in Number value U 104 p_po_line_id in Number U 105 p_person_type in Char U 106 p_po_price_type in Char U 107 p_adjusted_expenditure_item_id in Number U 108 p_fc_document_type in Char U 109 p_document_type in Char U 110 p_document_distribution_type in Char U 111 p_si_assets_addition_flag in Char U 112 p_cdl_system_reference5 in Number U 113 p_sc_xfer_code in Char U 114 p_adjusted_txn_interface_id in Number U 115 p_net_zero_adjustment_flag in Char U 116 p_legal_entity_id in Number U 117 p_receiver_legal_entity_id in Number U 118 p_legal_entity_name in Char U 119 p_receiver_legal_entity_name in Char P 1 p_submit_import in Char IMPORT_TRANSACTIONS P 2 p_transaction_source in Char TRANSACTION_SOURCE P 3 p_batch_name in Char BATCH_NAME P 4 p_org_id in Number OPERATING_UNIT P 5 request_id_ out Number REQUEST_ID_ P 6 status_ out Char STATUS_CODE_ P 7 message_ out Char MESSAGE_ Error SQL select xm.action_, xxen_upload.status_meaning(xxen_upload.status_error) status_, case when ptia.transaction_status_code = 'P' and nvl(:p_submit_import,'Y') = 'Y' then nvl(xm.message_,'Transaction Import has not been processed') when ptia.transaction_status_code = 'R' then case when ptia.transaction_rejection_code is not null then xxen_util.meaning(ptia.transaction_rejection_code,'TRANSACTION REJECTION REASON',275) else 'One or more related expenditure items is in error' end else xm.message_ end message_, xm.request_id_, xm.modified_columns_, xm.row_id, xm.operating_unit, xm.transaction_source, xm.expnd_type_class, xm.expnd_ending_date, xm.batch_name, xm.original_trans_ref, xm.organization_name, xm.expnd_item_date, xm.project_number, xm.task_number, xm.expnd_type, xm.quantity, xm.trans_raw_cost, xm.raw_cost_rate, xm.negative_txn_flag, xm.expenditure_comment, xm.orig_user_expnd_trans_ref, xm.orig_exp_txn_reference1, xm.orig_exp_txn_reference2, xm.vendor_number, xm.import_transactions from xxen_sss_ar_ic_to_pa_tr_9499_u xm, pa_transaction_interface_all ptia where chartorowid(xm.row_id) = ptia.rowid (+) and (nvl(xm.status_code_,xxen_upload.status_error) != xxen_upload.status_success or nvl(ptia.transaction_status_code,'X') != decode(nvl(:p_submit_import,'Y'),'Y','A','P') ) Order By x.operating_unit, x.transaction_source, x.expnd_ending_date, x.batch_name, x.organization_name, x.expnd_item_date, x.original_trans_ref Success SQL select xm.action_, xm.status_, nvl(xm.message_,'Transaction Imported') message_, xm.request_id_, xm.modified_columns_, xm.row_id, -- ou.name operating_unit, (select pts.user_transaction_source from pa_transaction_sources pts where pts.transaction_source = peia.transaction_source) transaction_source, (select psl.meaning from pa_system_linkages psl where psl.function = pega.system_linkage_function and rownum = 1) expnd_type_class, pea.expenditure_ending_date expnd_ending_date, ptxca.batch_name batch_name, peia.orig_transaction_reference original_trans_ref, incur_org.name organization_name, peia.expenditure_item_date expnd_item_date, ppa.segment1 project_number, pt.task_number task_number, peia.expenditure_type expnd_type, nvl(pcdla.quantity,peia.quantity) quantity, nvl(pcdla.denom_raw_cost,peia.denom_raw_cost) trans_raw_cost, peia.raw_cost_rate, xm.negative_txn_flag, (select pec.expenditure_comment from pa_expenditure_comments pec where pec.expenditure_item_id = peia.expenditure_item_id and rownum <= 1) expenditure_comment, pea.orig_user_exp_txn_reference orig_user_expnd_trans_ref, pea.orig_exp_txn_reference1, pea.orig_exp_txn_reference2, (select aps.segment1 from ap_suppliers aps where aps.vendor_id = pea.vendor_id) vendor_number, -- xm.import_transactions from xxen_sss_ar_ic_to_pa_tr_9499_u xm, pa_transaction_interface_all ptia, pa_transaction_xface_ctrl_all ptxca, pa_expenditures_all pea, pa_expenditure_items_all peia, pa_cost_distribution_lines_all pcdla, pa_expenditure_groups_all pega, pa_projects_all ppa, pa_tasks pt, hr_all_organization_units_vl ou, hr_all_organization_units_vl incur_org where chartorowid(xm.row_id) = ptia.rowid and ptia.interface_id = ptxca.interface_id and ptia.expenditure_id = pea.expenditure_id and pea.expenditure_group = pega.expenditure_group and pea.org_id = ou.organization_id and pea.incurred_by_organization_id = incur_org.organization_id (+) and peia.project_id = ppa.project_id and ptia.expenditure_item_id = peia.expenditure_item_id and peia.expenditure_item_id = pcdla.expenditure_item_id (+) and peia.task_id = pt.task_id and xm.status_code_ = xxen_upload.status_success and ptia.transaction_status_code = 'A' and nvl(:p_submit_import,'Y') = 'Y' union all select xm.action_, xm.status_, nvl(xm.message_,'Transaction Interfaced. Please review and import the transactions from Oracle') message_, xm.request_id_, xm.modified_columns_, xm.row_id, -- haouv.name operating_unit, (select pts.user_transaction_source from pa_transaction_sources pts where pts.transaction_source = ptia.transaction_source) transaction_source, (select psl.meaning from pa_system_linkages psl where psl.function = ptia.system_linkage and rownum = 1) expnd_type_class, ptia.expenditure_ending_date expnd_ending_date, ptia.batch_name batch_name, ptia.orig_transaction_reference original_trans_ref, ptia.organization_name organization_name, ptia.expenditure_item_date expnd_item_date, ptia.project_number project_number, ptia.task_number task_number, ptia.expenditure_type expnd_type, ptia.quantity quantity, ptia.denom_raw_cost trans_raw_cost, ptia.raw_cost_rate, xxen_util.meaning(ptia.unmatched_negative_txn_flag,'YES_NO',0) negative_txn_flag, ptia.expenditure_comment, ptia.orig_user_exp_txn_reference orig_user_expnd_trans_ref, ptia.orig_exp_txn_reference1, ptia.orig_exp_txn_reference2, ptia.vendor_number, -- xm.import_transactions from xxen_sss_ar_ic_to_pa_tr_9499_u xm, pa_transaction_interface_all ptia, hr_all_organization_units_vl haouv where chartorowid(xm.row_id) = ptia.rowid and ptia.org_id = haouv.organization_id and xm.status_code_ = xxen_upload.status_success and ptia.transaction_status_code = 'P' and nvl(:p_submit_import,'Y') = 'N'