ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN FAC674BBE14F7F85E0530100007F0F80Y select decode(:p_upload_mode,'Copy',xxen_upload.action_meaning(xxen_upload.action_create),null) action_, decode(:p_upload_mode,'Copy',xxen_upload.status_meaning(xxen_upload.status_new),null) status_, decode(:p_upload_mode,'Copy',xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING', 'XXEN_REPORT_TRANSLATIONS', 0),null) message_, null request_id_, null modified_columns_, null row_id, haouv.name operating_unit, plv.lease_name, plv.lease_number, xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) final_draft, plv.lease_class, plv.lease_type, nvl(plv.customer_name,plv.supplier_name) lease_third_party_name, nvl( plv.customer_number, (select asu.segment1 from ap_suppliers asu where asu.vendor_id = plv.supplier_id) ) lease_third_party_number, plv.location_code_disp primary_location, plv.property_name_disp property, plv.user_responsible, -- pptv.term_template, pptv.payment_purpose purpose, pptv.payment_term_type type, pptv.frequency_type frequency, pptv.estimated_amount, pptv.actual_amount, pptv.currency_code, pptv.rate, -- case when not(:p_upload_mode = 'Copy' and :p_date_incr_type is not null and :p_date_incr_unit is not null) then pptv.start_date else case upper(:p_date_incr_type) when 'DAYS' then pptv.start_date + :p_date_incr_unit when 'MONTHS' then add_months(pptv.start_date,:p_date_incr_unit) when 'YEARS' then add_months(pptv.start_date,:p_date_incr_unit*12) end end start_date, case when not(:p_upload_mode = 'Copy' and :p_date_incr_type is not null and :p_date_incr_unit is not null) then pptv.end_date else case upper(:p_date_incr_type) when 'DAYS' then pptv.end_date + :p_date_incr_unit when 'MONTHS' then add_months(pptv.end_date,:p_date_incr_unit) when 'YEARS' then add_months(pptv.end_date,:p_date_incr_unit*12) end end end_date, case when not(:p_upload_mode = 'Copy' and :p_date_incr_type is not null and :p_date_incr_unit is not null) then pptv.target_date else case upper(:p_date_incr_type) when 'DAYS' then pptv.target_date + :p_date_incr_unit when 'MONTHS' then add_months(pptv.target_date,:p_date_incr_unit) when 'YEARS' then add_months(pptv.target_date,:p_date_incr_unit*12) end end target_date, -- pptv.schedule_day, -- pla.location_code location, xxen_util.meaning(pptv.area_type_code,'PN_AREA_TYPE_CODE',0) area_type, pptv.area, pptv.actual_amount * 12 annual_amount, case when nvl(pptv.area,0) = 0 then to_number(null) else round((pptv.actual_amount * 12)/pptv.area,2) end annual_area_amount, -- nvl(pptv.customer_name,pptv.vendor_name) third_party_name, nvl(pptv.customer_number,pptv.vendor_number) third_party_number, nvl(pptv.customer_site_use,pptv.vendor_site) third_party_bill_site, pptv.cust_ship_site_use third_party_ship_site, pptv.cust_po_number third_party_po_number, (select poha.segment1 po_number from po_headers_all poha where poha.po_header_id = pptv.po_header_id) po_number, -- case when plv.lease_class_code in ('THIRD_PARTY','SUB_LEASE','REC') then (select rtv.name from ra_terms_vl rtv where rtv.term_id = pptv.ap_ar_term_id) else (select atv.name from ap_terms_vl atv where atv.term_id = pptv.ap_ar_term_id) end payment_terms, -- billing (select arm.name from ar_receipt_methods arm where arm.receipt_method_id = pptv.receipt_method_id) payment_method, (select rctta.name from ra_cust_trx_types_all rctta where rctta.org_id = pptv.org_id and rctta.cust_trx_type_id = pptv.cust_trx_type_id) ar_invoice_type, (select rsa.name from ra_salesreps_all rsa where rsa.org_id = pptv.org_id and rsa.salesrep_id = pptv.salesrep_id) salesperson, (select rr.name from ra_rules rr where rr.rule_id = pptv.inv_rule_id) invoicing_rule, (select rr.name from ra_rules rr where rr.rule_id = pptv.account_rule_id) accounting_rule, -- payments pptv.inv_group_name invoice_grouping_rule, (select distribution_set_name from ap_distribution_sets_all adsa where adsa.distribution_set_id = pptv.distribution_set_id) distribution_set, (select ppa.name from pa_projects_all ppa where ppa.project_id = pptv.project_id and ppa.org_id = pptv.org_id) project, (select pt.task_name from pa_tasks pt where pptv.task_id=pt.task_id) task, --pptv.agreement_name, --pptv.agreement_number, pptv.expenditure_type, pptv.expenditure_item_date, (select haouv_exp.name from hr_all_organization_units_vl haouv_exp where pptv.organization_id=haouv_exp.organization_id) expenditure_organization, -- tax xxen_util.meaning(decode(pptv.tax_included,'A','Y',pptv.tax_included),'YES_NO',0) tax_included, pptv.tax_classification_code, -- (select gcck.concatenated_segments from pn_distributions_v pdv, gl_code_combinations_kfv gcck where pdv.payment_term_id = pptv.payment_term_id and gcck.code_combination_id = pdv.account_id and pdv.account_class in ('LIA','REC') and pdv.percentage = 100 ) receivables_liability_account, (select gcck.concatenated_segments from pn_distributions_v pdv, gl_code_combinations_kfv gcck where pdv.payment_term_id = pptv.payment_term_id and gcck.code_combination_id = pdv.account_id and pdv.account_class in ('EXP','REV') and pdv.percentage = 100 ) revenue_expense_account, (select gcck.concatenated_segments from pn_distributions_v pdv, gl_code_combinations_kfv gcck where pdv.payment_term_id = pptv.payment_term_id and gcck.code_combination_id = pdv.account_id and pdv.account_class in ('ACC','UNEARN') and pdv.percentage = 100 ) accrual_account, -- pptv.term_comments, -- xxen_util.meaning(pptv.recoverable_flag,'YES_NO',0) recoverable, xxen_util.meaning(pptv.normalize,'YES_NO',0) normalize, null rou_asset, null liability, null report_from_inception, null intercompany, xxen_util.meaning(pptv.changed_flag,'YES_NO',0) changed, xxen_util.meaning(pptv.include_in_var_rent,'PN_PAYMENT_BKPT_BASIS_TYPE',0) natural_breakpoint_basis, xxen_util.meaning(pptv.source_module,'PN_TERM_SOURCE_MODULE',0) source, -- pptv.attribute_category, pptv.attribute1, pptv.attribute2, pptv.attribute3, pptv.attribute4, pptv.attribute5, pptv.attribute6, pptv.attribute7, pptv.attribute8, pptv.attribute9, pptv.attribute10, pptv.attribute11, pptv.attribute12, pptv.attribute13, pptv.attribute14, pptv.attribute15, -- decode(:p_upload_mode,'Copy',to_number(null),pptv.payment_term_id) payment_term_id, pptv.term_template_id, pptv.lease_id, pptv.lease_change_id, nvl(pptv.customer_id,pptv.vendor_id) third_party_id, nvl(pptv.customer_site_use_id,pptv.vendor_site_id) third_party_bill_to_site_id, pptv.cust_ship_site_id third_party_ship_to_site_id, to_number(to_char(pptv.start_date,'j')) start_date_j, to_number(to_char(pptv.end_date,'j')) end_date_j, to_number(null) upload_row from pn_leases_v plv, pn_payment_terms_v pptv, pn_locations_all pla, hr_all_organization_units_vl haouv where 1=1 and :p_upload_mode in (xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update),'Copy') and plv.lease_id = pptv.lease_id and plv.org_id = haouv.organization_id and -- pptv.location_id = pla.location_id (+) and plv.status = nvl(xxen_util.lookup_code(:p_final_draft_status,'PN_LEASE_STATUS_TYPE',0),'F') and plv.lease_class = :p_lease_class Fix ORA-01427 in the download and result for a responsibility with access to several operating units: task and expenditure organization are looked up by id from pa_tasks and hr_all_organization_units_vl. U :Upload_Mode = xxen_upload.action_meaning(xxen_upload.action_create) or ( :Third_Party_Name is not null or :Lease_Type is not null or :Lease_Name is not null or :Lease_Name_From is not null or :Lease_Name_To is not null or :Lease_Number is not null or :Lease_Number_From is not null or :Lease_Number_To is not null or :Location is not null or :Purpose is not null or :Type is not null or :Frequency is not null or :Payment_Start_Date_From is not null or :Payment_Start_Date_To is not null ) A xxen_pn_upload.upload_payment_terms xxen_pn_upload.process_payment_terms US PN Billing/Payment Term Upload Create, update or copy the billing and payment terms of Property Manager leases. Lease Class selects revenue (billing) or expense (payment) leases, and Final Draft Status final or draft leases. A new row adds a term to the lease named in its Lease Name or Lease Number, of the lease class and approval status of these parameters. Upload Mode Create, Update downloads the terms of the selected leases for update and allows new terms to be added. Create opens an empty sheet. Copy downloads the selected terms as new rows, to create copies of them, with their start, end and target dates moved by the Copy: Date Increment Type (Days, Months or Years) and Copy: Date Increment Units parameters. The Revenue Class Template and the Expense Class Template show the columns of their lease class. Please specify one or more criteria to determine the payment terms to be updated or copied Enginatics R12 only Upload 1=1 :p_date_incr_type :p_date_incr_unit :p_final_draft_status :p_lease_class :p_upload_mode 1 10 :p_upload_mode LOV custom select value, description from (select xxen_upload.action_meaning(xxen_upload.action_create) value, 'Create new Billing/Payment entries' description from dual union all select xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, 'Create new and/or Update existing Billing/Payment entries' description from dual union all select 'Copy' value, 'Copy existing Billing/Payment entries to create new Billing/Payment entries' description from dual ) order by value select xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) from dual Y US Upload Mode 2 20 :p_lease_class LOV PN Lease Class FAEA24CB5B4E92CCE0530100007F4C87 select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_LEASE_CLASS' and view_application_id=0 and enabled_flag='Y' order by value,description Y US Lease Class 3 30 :p_final_draft_status LOV PN Lease Approval Status FAE814AFB3DA6C10E0530100007F465A select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_LEASE_STATUS_TYPE' and view_application_id=0 and enabled_flag='Y' order by value,description xxen_util.meaning('F','PN_LEASE_STATUS_TYPE',0) Y US Final Draft Status 4 40 1=1 plv.lease_status = xxen_util.lookup_code(:p_lease_status,'PN_LEASESTATUS_TYPE',0) LOV PN Lease Status FAEA1AD693AB0755E0530100007F4882 select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_LEASESTATUS_TYPE' and view_application_id=0 and enabled_flag='Y' order by value,description xxen_util.meaning('ACT','PN_LEASESTATUS_TYPE',0) Y US Lease Status 5 50 1=1 haouv.name=:p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 6 60 1=1 nvl(plv.customer_name,plv.supplier_name) = :p_third_party_name LOV custom select distinct nvl(plv.customer_name,plv.supplier_name) value, null description from pn_leases_v plv, hr_all_organization_units_vl haouv where plv.org_id = haouv.organization_id and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') order by value US Third Party Name 7 70 1=1 plv.lease_type = :p_lease_type LOV PN Lease Type FAEA382CD59B9A20E0530100007FBCAB select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_LEASE_TYPE' and view_application_id=0 and enabled_flag='Y' order by value,description US Lease Type 8 80 1=1 plv.lease_name = :p_lease_name LOV PN Lease Name FAEB12D01B5CA7F8E0530100007F8E1E select plv.lease_name value, plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name) = 'Y') and (:$flex$.final_draft_status is null or plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0)) and (:$flex$.lease_class is null or plv.lease_class = :$flex$.lease_class) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$flex$.lease_type is null or xxen_util.contains(:$flex$.lease_type,plv.lease_type)='Y') and (:$flex$.third_party_name is null or xxen_util.contains(:$flex$.third_party_name,nvl(plv.customer_name,plv.supplier_name))='Y') order by value US Lease Name 9 90 1=1 plv.lease_name >= :p_lease_name_fr LOV PN Lease Name FAEB12D01B5CA7F8E0530100007F8E1E select plv.lease_name value, plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name) = 'Y') and (:$flex$.final_draft_status is null or plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0)) and (:$flex$.lease_class is null or plv.lease_class = :$flex$.lease_class) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$flex$.lease_type is null or xxen_util.contains(:$flex$.lease_type,plv.lease_type)='Y') and (:$flex$.third_party_name is null or xxen_util.contains(:$flex$.third_party_name,nvl(plv.customer_name,plv.supplier_name))='Y') order by value US Lease Name From 10 100 1=1 plv.lease_name <= :p_lease_name_to LOV PN Lease Name FAEB12D01B5CA7F8E0530100007F8E1E select plv.lease_name value, plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name) = 'Y') and (:$flex$.final_draft_status is null or plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0)) and (:$flex$.lease_class is null or plv.lease_class = :$flex$.lease_class) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$flex$.lease_type is null or xxen_util.contains(:$flex$.lease_type,plv.lease_type)='Y') and (:$flex$.third_party_name is null or xxen_util.contains(:$flex$.third_party_name,nvl(plv.customer_name,plv.supplier_name))='Y') order by value US Lease Name To 11 110 1=1 plv.lease_number = :p_lease_number LOV PN Lease Number FBB70AABF1F41757E0530100007FE881 select plv.lease_number value, plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name) = 'Y') and (:$flex$.final_draft_status is null or plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0)) and (:$flex$.lease_class is null or plv.lease_class = :$flex$.lease_class) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$flex$.lease_type is null or xxen_util.contains(:$flex$.lease_type,plv.lease_type)='Y') and (:$flex$.third_party_name is null or xxen_util.contains(:$flex$.third_party_name,nvl(plv.customer_name,plv.supplier_name))='Y') order by value US Lease Number 12 120 1=1 plv.lease_number >= :p_lease_number_fr LOV PN Lease Number FBB70AABF1F41757E0530100007FE881 select plv.lease_number value, plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name) = 'Y') and (:$flex$.final_draft_status is null or plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0)) and (:$flex$.lease_class is null or plv.lease_class = :$flex$.lease_class) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$flex$.lease_type is null or xxen_util.contains(:$flex$.lease_type,plv.lease_type)='Y') and (:$flex$.third_party_name is null or xxen_util.contains(:$flex$.third_party_name,nvl(plv.customer_name,plv.supplier_name))='Y') order by value US Lease Number From 13 130 1=1 plv.lease_number <= :p_lease_number_to LOV PN Lease Number FBB70AABF1F41757E0530100007FE881 select plv.lease_number value, plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name) = 'Y') and (:$flex$.final_draft_status is null or plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0)) and (:$flex$.lease_class is null or plv.lease_class = :$flex$.lease_class) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$flex$.lease_type is null or xxen_util.contains(:$flex$.lease_type,plv.lease_type)='Y') and (:$flex$.third_party_name is null or xxen_util.contains(:$flex$.third_party_name,nvl(plv.customer_name,plv.supplier_name))='Y') order by value US Lease Number To 14 140 1=1 pla.location_code = :p_location_code LOV PN Lease Locations FB346FAE5D100757E0530100007F7575 Y select distinct pla.location_code value, xxen_util.meaning(pla.location_type_lookup_code,'PN_LOCATION_TYPE',0) || ': ' || decode(pla.location_type_lookup_code, 'OFFICE',pla.office, 'FLOOR',pla.floor, 'BUILDING',pla.building, 'SECTION',pla.office, 'PARCEL',pla.floor, 'LAND',pla.building) || ' (' || to_char(pta.occupancy_date) || '-' || to_char(pta.expiration_date) || ')' description from hr_all_organization_units haouv, pn_leases_v plv, pn_locations_all pla, pn_tenancies_all pta where xxen_util.contains(:$flex$.operating_unit,haouv.name) = 'Y' and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and -- ( (:$flex$.lease_name is null and :$flex$.lease_name_from is null and :$flex$.lease_name_to is null) or (:$flex$.lease_name is not null and xxen_util.contains(:$flex$.lease_name,plv.lease_name) = 'Y') or (:$flex$.lease_name is null and plv.lease_name >= nvl(:$flex$.lease_name_from,plv.lease_name) and plv.lease_name <= nvl(:$flex$.lease_name_to,plv.lease_name)) ) and ( (:$flex$.lease_number is null and :$flex$.lease_number_from is null and :$flex$.lease_number_to is null) or (:$flex$.lease_number is not null and xxen_util.contains(:$flex$.lease_number,plv.lease_number) = 'Y') or (:$flex$.lease_number is null and plv.lease_number >= nvl(:$flex$.lease_number_from,plv.lease_number) and plv.lease_number <= nvl(:$flex$.lease_number_to,plv.lease_number)) ) and -- pta.org_id = plv.org_id and pta.lease_id = plv.lease_id and pla.location_id = pta.location_id order by value US Location 15 150 1=1 pptv.payment_purpose = :p_payment_purpose LOV PN Payment Term Purpose FB14B3F26C1A1D80E0530100007F9EE2 select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_PAYMENT_PURPOSE_TYPE' and view_application_id=0 and enabled_flag='Y' order by value US Purpose 16 160 1=1 pptv.payment_term_type = :p_payment_type LOV PN Payment Term Type FB14BE0BFBDA1E89E0530100007FEB5B select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_PAYMENT_TERM_TYPE' and view_application_id=0 and enabled_flag='Y' order by value US Type 17 170 1=1 pptv.frequency_type = :p_frequency LOV PN Payment Term Frequency FB14BE0BFBDB1E89E0530100007FEB5B select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_PAYMENT_FREQUENCY_TYPE' and view_application_id=0 and enabled_flag='Y' order by value US Frequency 18 180 1=1 pptv.start_date >= :p_pay_start_date_fr Date US Payment Start Date From 19 190 1=1 pptv.start_date < :p_pay_start_date_to + 1 Date US Payment Start Date To 20 200 :p_date_incr_type LOV custom select value, description from (select 'Days' value, 'Increment Payment Dates by x number of Days' description from dual union select 'Months' value, 'Increment Payment Dates by x number of Months' description from dual union select 'Years' value, 'Increment Payment Dates by x number of Years' description from dual ) x where :$flex$.Upload_Mode = 'Copy' order by x.value US Copy: Date Increment Type 21 210 :p_date_incr_unit Number US Copy: Date Increment Units 027C1E7EEF145175D64F119D86EFC005 Expense Class Template ENGINATICS 43 ACCRUAL_ACCOUNT 10 ACTUAL_AMOUNT 18 AREA 17 AREA_TYPE 46 ATTRIBUTE1 55 ATTRIBUTE10 56 ATTRIBUTE11 57 ATTRIBUTE12 58 ATTRIBUTE13 59 ATTRIBUTE14 60 ATTRIBUTE15 47 ATTRIBUTE2 48 ATTRIBUTE3 49 ATTRIBUTE4 50 ATTRIBUTE5 51 ATTRIBUTE6 52 ATTRIBUTE7 53 ATTRIBUTE8 54 ATTRIBUTE9 45 ATTRIBUTE_CATEGORY 39 CHANGED 11 CURRENCY_CODE 25 DISTRIBUTION_SET 14 END_DATE 9 ESTIMATED_AMOUNT 29 EXPENDITURE_ITEM_DATE 30 EXPENDITURE_ORGANIZATION 28 EXPENDITURE_TYPE 8 FREQUENCY 38 INTERCOMPANY 24 INVOICE_GROUPING_RULE 2 LEASE_NAME 3 LEASE_NUMBER 36 LIABILITY 5 LOCATION 40 NATURAL_BREAKPOINT_BASIS 34 NORMALIZE 1 OPERATING_UNIT 23 PAYMENT_TERMS 22 PO_NUMBER 26 PROJECT 6 PURPOSE 12 RATE 41 RECEIVABLES_LIABILITY_ACCOUNT 33 RECOVERABLE 37 REPORT_FROM_INCEPTION 42 REVENUE_EXPENSE_ACCOUNT 35 ROU_ASSET 16 SCHEDULE_DAY 13 START_DATE 15 TARGET_DATE 27 TASK 32 TAX_CLASSIFICATION_CODE 31 TAX_INCLUDED 44 TERM_COMMENTS 4 TERM_TEMPLATE Y 21 THIRD_PARTY_BILL_SITE 19 THIRD_PARTY_NAME 20 THIRD_PARTY_NUMBER 7 TYPE Lease Class Expense S Site 6883BEB9B9F63BA0AE90B69255F0E411 Revenue Class Template ENGINATICS 29 ACCOUNTING_RULE 37 ACCRUAL_ACCOUNT 10 ACTUAL_AMOUNT 18 AREA 17 AREA_TYPE 26 AR_INVOICE_TYPE 11 CURRENCY_CODE 14 END_DATE 9 ESTIMATED_AMOUNT 8 FREQUENCY 28 INVOICING_RULE 2 LEASE_NAME 3 LEASE_NUMBER 5 LOCATION 34 NATURAL_BREAKPOINT_BASIS 33 NORMALIZE 1 OPERATING_UNIT 25 PAYMENT_METHOD 24 PAYMENT_TERMS 6 PURPOSE 12 RATE 35 RECEIVABLES_LIABILITY_ACCOUNT 32 RECOVERABLE 36 REVENUE_EXPENSE_ACCOUNT 27 SALESPERSON 16 SCHEDULE_DAY 13 START_DATE 15 TARGET_DATE 31 TAX_CLASSIFICATION_CODE 30 TAX_INCLUDED 38 TERM_COMMENTS 4 TERM_TEMPLATE Y 21 THIRD_PARTY_BILL_SITE 19 THIRD_PARTY_NAME 20 THIRD_PARTY_NUMBER 23 THIRD_PARTY_PO_NUMBER 22 THIRD_PARTY_SHIP_SITE 7 TYPE Lease Class Revenue S Site 6883BEB9B9F63BA0AE90B69255F0E411 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 varchar2 240 HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name Y Y 8 LEASE_NAME LOV custom varchar2 50 select plv.lease_name value, plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and haouv.name = :$column$.operating_unit and plv.lease_class = :$flex$.lease_class and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$column$.lease_number is null or plv.lease_number = :$column$.lease_number) order by value Y Y 9 LEASE_NUMBER LOV custom varchar2 30 select plv.lease_number value, plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and haouv.name = :$column$.operating_unit and plv.lease_class = :$flex$.lease_class and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and (:$flex$.lease_status is null or xxen_util.contains(:$flex$.lease_status,xxen_util.meaning(plv.lease_status,'PN_LEASESTATUS_TYPE',0))='Y') and (:$column$.lease_name is null or plv.lease_name = :$column$.lease_name) order by value Y Y 10 FINAL_DRAFT LOV custom varchar2 select xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) value, null description from pn_leases_v plv, hr_all_organization_units haouv where plv.org_id = haouv.organization_id and haouv.name = :$column$.operating_unit and (plv.lease_name = :$column$.lease_name or plv.lease_number = :$column$.lease_number) order by value :$flex$.final_draft_status Y 11 LEASE_CLASS Char varchar2 80 Y 12 LEASE_TYPE Char varchar2 80 Y 13 LEASE_THIRD_PARTY_NAME Char varchar2 360 Y 14 LEASE_THIRD_PARTY_NUMBER Char varchar2 30 Y 15 PRIMARY_LOCATION Char varchar2 Y 16 PROPERTY Char varchar2 Y 17 USER_RESPONSIBLE Char varchar2 100 Y 18 TERM_TEMPLATE LOV custom varchar2 100 select pttv.name value, case when pttv.term_template_id = plv.term_template_id then '(Default)' else null end description from hr_all_organization_units_vl haouv, pn_leases_v plv, pn_term_templates_v pttv where haouv.name = :$column$.operating_unit and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and (plv.lease_number = :$column$.lease_number or plv.lease_name = :$column$.lease_name) and pttv.org_id = haouv.organization_id and pttv.active = 'Y' and (pttv.term_template_type = 'NEUTRAL' or pttv.term_template_type = case plv.lease_class_code when 'DIRECT' then 'PAYMENT' else 'BILLING' end ) order by case when pttv.term_template_id = plv.term_template_id then 1 else 2 end, value 19 PURPOSE LOV custom varchar2 80 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='PN_PAYMENT_PURPOSE_TYPE' and flvv.view_application_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code = ( select nvl(pttv.payment_purpose_code,flvv.lookup_code) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value Y 20 TYPE LOV custom varchar2 80 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='PN_PAYMENT_TERM_TYPE' and flvv.view_application_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code = ( select nvl(pttv.payment_term_type_code,flvv.lookup_code) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value Y 21 FREQUENCY LOV varchar2 80 PN Payment Term Frequency FB14BE0BFBDB1E89E0530100007FEB5B select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_PAYMENT_FREQUENCY_TYPE' and view_application_id=0 and enabled_flag='Y' order by value Y 22 ESTIMATED_AMOUNT Number number 23 ACTUAL_AMOUNT Number number 24 CURRENCY_CODE LOV custom varchar2 15 select x.value, x.description from ( select pc.currency_code value, gdct.user_conversion_type description from pn_currencies pc, gl_daily_conversion_types gdct where pc.conversion_type = gdct.conversion_type and pc.currency_code != (select gl.currency_code from hr_operating_units hou, gl_ledgers gl where hou.name = :$column$.operating_unit and gl.ledger_id = to_number(hou.set_of_books_id) ) union select gl.currency_code value, null description from hr_operating_units hou, gl_ledgers gl where hou.name = :$column$.operating_unit and gl.ledger_id = to_number(hou.set_of_books_id) ) x where x.value = ( select nvl(pttv.currency_code,x.value) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by x.value Y 25 RATE LOV custom number Y select 1 value, null description from hr_operating_units hou, gl_ledgers gl where hou.name = :$column$.operating_unit and gl.ledger_id = to_number(hou.set_of_books_id) and gl.currency_code = :$column$.currency_code order by value Y 26 START_DATE Date date Y 27 END_DATE Date date Y 28 TARGET_DATE Date date 29 SCHEDULE_DAY LOV custom number Y select pttv.schedule_day value, null description from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template order by value Y 30 LOCATION LOV custom varchar2 90 select distinct pla.location_code value, xxen_util.meaning(pla.location_type_lookup_code,'PN_LOCATION_TYPE',0) || ': ' || decode(pla.location_type_lookup_code, 'OFFICE',pla.office, 'FLOOR',pla.floor, 'BUILDING',pla.building, 'SECTION',pla.office, 'PARCEL',pla.floor, 'LAND',pla.building) || ' (' || to_char(pta.occupancy_date) || '-' || to_char(pta.expiration_date) || ')' description from hr_all_organization_units haouv, pn_leases_all plv, pn_locations_all pla, pn_tenancies_all pta where haouv.name = :$column$.operating_unit and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and plv.lease_num = :$column$.lease_number and pta.org_id = plv.org_id and pta.lease_id = plv.lease_id and pla.location_id = pta.location_id and pta.location_id = nvl((select pttv.location_id from pn_term_templates_all pttv where pttv.org_id = haouv.organization_id and pttv.name = :$column$.term_template ), pta.location_id ) order by value 31 AREA_TYPE LOV custom varchar2 select flvv.meaning value, flvv.description ,flvv.lookup_code from fnd_lookup_values_vl flvv where flvv.lookup_type='PN_AREA_TYPE_CODE' and flvv.view_application_id=0 and flvv.enabled_flag='Y' and (flvv.lookup_code = 'OTHER' or exists (select null from hr_all_organization_units haouv, pn_leases_all plv, pn_locations_all pla, pn_tenancies_all pta where haouv.name = :$column$.operating_unit and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and plv.lease_num = :$column$.lease_number and pta.org_id = plv.org_id and pta.lease_id = plv.lease_id and pla.location_id = pta.location_id and case flvv.lookup_code when 'LOCTN_RENTABLE' then nvl(pta.location_rentable_area,pla.rentable_area) when 'LOCTN_USABLE' then nvl(pta.location_usable_area,pla.usable_area) when 'LOCTN_ASSIGNABLE' then nvl(pta.location_assignable_area,pla.assignable_area) when 'LEASE_RENTABLE' then pta.lease_rentable_area when 'LEASE_USABLE' then pta.lease_usable_area when 'LEASE_ASSIGNABLE' then pta.lease_assignable_area else 1 end is not null ) ) order by value 32 AREA LOV custom number Y select case xxen_util.lookup_code(:$column$.area_type,'PN_AREA_TYPE_CODE',0) when 'LOCTN_RENTABLE' then nvl(pta.location_rentable_area,pla.rentable_area) when 'LOCTN_USABLE' then nvl(pta.location_usable_area,pla.usable_area) when 'LOCTN_ASSIGNABLE' then nvl(pta.location_assignable_area,pla.assignable_area) when 'LEASE_RENTABLE' then pta.lease_rentable_area when 'LEASE_USABLE' then pta.lease_usable_area when 'LEASE_ASSIGNABLE' then pta.lease_assignable_area else null end value, null description from hr_all_organization_units haouv, pn_leases_all plv, pn_locations_all pla, pn_tenancies_all pta where haouv.name = :$column$.operating_unit and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and plv.lease_num = :$column$.lease_number and pta.org_id = plv.org_id and pta.lease_id = plv.lease_id and pla.location_id = pta.location_id and pla.location_code = :$column$.location 33 ANNUAL_AMOUNT Number number Y 34 ANNUAL_AREA_AMOUNT Number number Y 35 THIRD_PARTY_NAME LOV custom varchar2 360 select x.value, x.description from ( -- customers select hp.party_name value, hca.account_number description from hz_parties hp, hz_cust_accounts hca where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and hp.party_id = hca.party_id and hca.status = 'A' and exists (select null from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where haouv.name = :$column$.operating_unit and hcasa.org_id = haouv.organization_id and hcasa.cust_account_id = hca.cust_account_id ) and hca.cust_account_id = ( select coalesce(pttv.customer_id,nvl2(pla.location_code,pta.customer_id,null),plv.customer_id,hca.cust_account_id) from hr_all_organization_units_vl haouv, pn_leases_all plv, pn_term_templates_all pttv, pn_tenancies_all pta, pn_locations_all pla where haouv.name = :$column$.operating_unit and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and plv.lease_num = :$column$.lease_number and plv.lease_class_code != 'DIRECT' and -- pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template and -- pta.org_id (+) = plv.org_id and pta.lease_id (+) = plv.lease_id and pla.location_id (+) = pta.location_id and pla.location_code (+) = :$column$.location ) union -- suppliers select asu.vendor_name value, asu.segment1 description from ap_suppliers asu where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) = 'DIRECT' and asu.enabled_flag = 'Y' and trunc(sysdate) < trunc(nvl(asu.end_date_active,sysdate + 1)) and exists (select null from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where haouv.name = :$column$.operating_unit and assa.org_id = haouv.organization_id and assa.vendor_id = asu.vendor_id ) and asu.vendor_id = ( select coalesce(pttv.vendor_id,plv.vendor_id,asu.vendor_id) from hr_all_organization_units_vl haouv, pn_leases_all plv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and plv.lease_num = :$column$.lease_number and plv.lease_class_code = 'DIRECT' and -- pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) ) x order by x.value Y 36 THIRD_PARTY_NUMBER LOV custom varchar2 30 select x.value, x.description from ( -- customers select hca.account_number value, hp.party_name description from hz_parties hp, hz_cust_accounts hca where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and hp.party_id = hca.party_id and hca.status = 'A' and exists (select null from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where haouv.name = :$column$.operating_unit and hcasa.org_id = haouv.organization_id and hcasa.cust_account_id = hca.cust_account_id ) and hp.party_name = :$column$.third_party_name union -- suppliers select asu.segment1 value, asu.vendor_name description from ap_suppliers asu where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) = 'DIRECT' and asu.enabled_flag = 'Y' and trunc(sysdate) < trunc(nvl(asu.end_date_active,sysdate + 1)) and exists (select null from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where haouv.name = :$column$.operating_unit and assa.org_id = haouv.organization_id and assa.vendor_id = asu.vendor_id ) and asu.vendor_name = :$column$.third_party_name ) x order by x.value Y 37 THIRD_PARTY_BILL_SITE LOV custom varchar2 60 select x.value, x.description from ( -- customers select hcsua.location value, hz_format_pub.format_address(hps.location_id,null,null,',') || decode(hcsua.primary_flag,'Y',' (Primary)',null) description from hr_all_organization_units_vl haouv, hz_parties hp, hz_cust_accounts hca, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua, hz_party_sites hps where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and haouv.name = :$column$.operating_unit and hca.party_id = hp.party_id and hcasa.org_id = haouv.organization_id and hcasa.cust_account_id = hca.cust_account_id and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id and hcsua.org_id = hcasa.org_id and hcsua.status = 'A' and hcsua.site_use_code = 'BILL_TO' and hps.party_site_id = hcasa.party_site_id and (hp.party_name = :$column$.third_party_name or hca.account_number = :$column$.third_party_number) and hcsua.site_use_id = ( select coalesce(decode(hca.cust_account_id,pttv.customer_id,pttv.customer_site_use_id,null), nvl2(pla.location_code,decode(hca.cust_account_id,pta.customer_id,pta.customer_site_use_id,null),null), hcsua.site_use_id ) from hr_all_organization_units_vl haouv, pn_leases_all plv, pn_term_templates_all pttv, pn_tenancies_all pta, pn_locations_all pla where haouv.name = :$column$.operating_unit and plv.org_id = haouv.organization_id and plv.status = xxen_util.lookup_code(:$flex$.final_draft_status,'PN_LEASE_STATUS_TYPE',0) and plv.lease_num = :$column$.lease_number and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template and pta.org_id (+) = plv.org_id and pta.lease_id (+) = plv.lease_id and pla.location_id (+) = pta.location_id and pla.location_code (+) = :$column$.location ) union -- suppliers select assa.vendor_site_code value, assa.address_line1||','||assa.address_line2||','||assa.address_line3|| ','||assa.city||','||assa.state description from hr_all_organization_units haouv, ap_suppliers asu, ap_supplier_sites_all assa where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) = 'DIRECT' and haouv.name = :$column$.operating_unit and (asu.segment1 = :$column$.third_party_number or asu.vendor_name = :$column$.third_party_name) and assa.org_id = haouv.organization_id and assa.vendor_id = asu.vendor_id and assa.pay_site_flag = 'Y' and sysdate <= nvl(assa.inactive_date,sysdate) and assa.vendor_site_id = ( select coalesce(decode(asu.vendor_id,pttv.vendor_id,pttv.vendor_site_id,null), assa.vendor_site_id ) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) ) x order by x.value Y 38 THIRD_PARTY_SHIP_SITE LOV custom varchar2 40 select x.value, x.description from ( -- customers select hcsua.location value, hz_format_pub.format_address(hps.location_id,null,null,',') || decode(hcsua.primary_flag,'Y',' (Primary)',null) description from hr_all_organization_units_vl haouv, hz_parties hp, hz_cust_accounts hca, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua, hz_party_sites hps where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and haouv.name = :$column$.operating_unit and hca.party_id = hp.party_id and hcasa.org_id = haouv.organization_id and hcasa.cust_account_id = hca.cust_account_id and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id and hcsua.org_id = hcasa.org_id and hcsua.status = 'A' and hcsua.site_use_code = 'SHIP_TO' and hps.party_site_id = hcasa.party_site_id and (hp.party_name = :$column$.third_party_name or hca.account_number = :$column$.third_party_number) and hcsua.site_use_id = ( select coalesce(decode(hca.cust_account_id,pttv.customer_id,pttv.cust_ship_site_id,null), hcsua.site_use_id ) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) ) x order by x.value 39 THIRD_PARTY_PO_NUMBER LOV custom varchar2 50 Y select pttv.cust_po_number value, null description from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template order by value 40 PO_NUMBER LOV custom varchar2 20 select pha.segment1 value, xxen_util.meaning(pha.type_lookup_code,'PO TYPE',201)||nvl2(aps.vendor_name,': '||aps.vendor_name,null) description from po_headers_all pha, ap_suppliers aps where pha.vendor_id=aps.vendor_id and aps.vendor_name = :$column$.third_party_name and (:$column$.operating_unit is null or pha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$column$.operating_unit,haouv.name)='Y') ) order by pha.creation_date desc 41 PAYMENT_TERMS LOV custom varchar2 50 select x.value, x.description from ( select rtv.name value, null description, rtv.term_id from ra_terms_vl rtv where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and trunc(sysdate) between trunc(nvl(rtv.start_date_active,sysdate) ) and trunc(nvl(rtv.end_date_active,sysdate)) union select atv.name value, null description, atv.term_id from ap_terms_vl atv where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) = 'DIRECT' and trunc(sysdate) between trunc(nvl(atv.start_date_active,sysdate)) and trunc(nvl(atv.end_date_active,sysdate)) ) x where x.term_id = ( select nvl(pttv.ap_ar_term_id,x.term_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value Y 42 PAYMENT_METHOD LOV custom varchar2 30 select distinct arm.name value, null description from ar_receipt_methods arm, ra_cust_receipt_methods rcrm where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and rcrm.receipt_method_id = arm.receipt_method_id and trunc(sysdate) between trunc(nvl(arm.start_date,sysdate)) and trunc(nvl(arm.end_date,sysdate)) and rcrm.customer_id = (select hca.cust_account_id from hz_parties hp, hz_cust_accounts hca where hp.party_id = hca.party_id and (hp.party_name = :$column$.third_party_name or hca.account_number = :$column$.third_party_number) ) and exists (select null from hr_all_organization_units_vl haouv, ar_receipt_method_accounts_all armaa, ce_bank_acct_uses_all cbaua where haouv.name = :$column$.operating_unit and armaa.receipt_method_id = arm.receipt_method_id and cbaua.bank_acct_use_id = armaa.remit_bank_acct_use_id and cbaua.org_id = armaa.org_id and cbaua.org_id = haouv.organization_id ) and arm.receipt_method_id = ( select coalesce(pttv.receipt_method_id,arm.receipt_method_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 43 AR_INVOICE_TYPE LOV custom varchar2 20 select rctta.name value, rctta.description from hr_all_organization_units_vl haouv, ra_cust_trx_types_all rctta where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and haouv.name = :$column$.operating_unit and rctta.org_id = haouv.organization_id and rctta.type = 'INV' and trunc(sysdate) between trunc(nvl(rctta.start_date,sysdate)) and trunc(nvl(rctta.end_date,sysdate)) and rctta.cust_trx_type_id = ( select coalesce(pttv.cust_trx_type_id,rctta.cust_trx_type_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 44 SALESPERSON LOV custom varchar2 240 select rsa.name value, rsa.salesrep_number description from hr_all_organization_units_vl haouv, ra_salesreps_all rsa where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and haouv.name = :$column$.operating_unit and rsa.org_id = haouv.organization_id and nvl(rsa.status,'A') = 'A' and trunc(sysdate) between trunc(nvl(rsa.start_date_active,sysdate)) and trunc(nvl(rsa.end_date_active,sysdate)) and rsa.name is not null and rsa.salesrep_id = ( select coalesce(pttv.salesrep_id,rsa.salesrep_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 45 INVOICING_RULE LOV custom varchar2 30 select name value, description from ra_rules rr where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and rr.type = 'I' and rr.status = 'A' and rr.rule_id in(-2,-3) and rr.rule_id = ( select coalesce(pttv.inv_rule_id,rr.rule_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 46 ACCOUNTING_RULE LOV custom varchar2 30 select name value, description from ra_rules rr where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and rr.type in ('A','ACC_DUR') and rr.status = 'A' and rr.rule_id = ( select coalesce(pttv.account_rule_id,rr.rule_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 47 INVOICE_GROUPING_RULE LOV custom varchar2 30 select ppgr.name value, ppgr.description from pn_pay_group_rules ppgr order by value 48 DISTRIBUTION_SET LOV custom varchar2 50 select adsa.distribution_set_name value, adsa.description from hr_all_organization_units_vl haouv, ap_distribution_sets_all adsa where haouv.name = :$column$.operating_unit and adsa.org_id = haouv.organization_id and (adsa.inactive_date is null or adsa.inactive_date > sysdate) and adsa.distribution_set_id = ( select nvl(pttv.distribution_set_id,adsa.distribution_set_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 49 PROJECT LOV custom varchar2 30 select ppa.name value, ppa.segment1 || ' - ' || ppa.start_date || ' - ' || ppa.completion_date || ' - ' || ppta.project_type_class_code description from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_project_types_all ppta where haouv.name = :$column$.operating_unit and ppa.org_id = haouv.organization_id and ppa.project_type = ppta.project_type and ppa.org_id = ppta.org_id and nvl(ppa.template_flag,'N') <>'Y' and ppa.project_id = ( select nvl(pttv.project_id,ppa.project_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 50 TASK LOV custom varchar2 20 select ptev.task_name value, ptev.task_number || ' - ' || ptev.start_date || ' - ' || ptev.completion_date || decode(ptev.chargeable_flag,'Y',' (Chargeable)',null) description from hr_all_organization_units_vl haouv, pa_projects_all ppa, pa_tasks_expend_v ptev where haouv.name = :$column$.operating_unit and ppa.org_id = haouv.organization_id and ppa.name = :$column$.project and ptev.org_id = ppa.org_id and ptev.project_id = ppa.project_id and ptev.task_id = ( select nvl(pttv.task_id,ptev.task_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 51 EXPENDITURE_TYPE LOV custom varchar2 30 select petev.expenditure_type value, petev.description || ' (' || petev.unit_of_measure || ')' description from pa_expenditure_types_expend_v petev where petev.system_linkage_function='VI' and (sysdate between petev.expnd_typ_start_date_active and nvl(petev.expnd_typ_end_date_active, sysdate)) and (sysdate between petev.sys_link_start_date_active and nvl(petev.sys_link_end_date_active,sysdate)) and petev.expenditure_type = ( select nvl(pttv.expenditure_type,petev.expenditure_type) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 52 EXPENDITURE_ITEM_DATE LOV custom date Y select pttv.expenditure_item_date value, null description from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template order by value 53 EXPENDITURE_ORGANIZATION LOV custom varchar2 240 select poev.name value, null description from hr_all_organization_units_vl haouv, pa_organizations_expend_v poev where haouv.name = :$column$.operating_unit and poev.org_id = haouv.organization_id and poev.active_flag='Y' and sysdate between poev.date_from and nvl(poev.date_to,sysdate) and poev.organization_id = ( select nvl(pttv.expenditure_type,poev.organization_id) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 54 TAX_INCLUDED LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code = ( select nvl(pttv.tax_included,flvv.lookup_code) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 55 TAX_CLASSIFICATION_CODE LOV custom varchar2 30 select x.value, x.description from ( select zocv.meaning value, null description, zocv.lookup_code from hr_all_organization_units haouv, zx_output_classifications_v zocv where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) != 'DIRECT' and haouv.name = :$column$.operating_unit and zocv.enabled_flag = 'Y' and trunc(sysdate) between trunc(nvl(zocv.start_date_active,sysdate)) and trunc(nvl(zocv.end_date_active,sysdate)) and (zocv.org_id = haouv.organization_id OR zocv.org_id = -99) union select zicv.meaning value, null description, zicv.lookup_code from hr_all_organization_units haouv, zx_input_classifications_v zicv where xxen_util.lookup_code(:$flex$.lease_class,'PN_LEASE_CLASS',0) = 'DIRECT' and haouv.name = :$column$.operating_unit and zicv.enabled_flag = 'Y' and trunc(sysdate) between trunc(nvl(zicv.start_date_active,sysdate)) and trunc(nvl(zicv.end_date_active,sysdate)) and (zicv.org_id = haouv.organization_id OR zicv.org_id = -99) ) x where x.lookup_code = ( select nvl(pttv.tax_classification_code,x.lookup_code) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by x.value 56 RECEIVABLES_LIABILITY_ACCOUNT LOV custom varchar2 311 Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description from hr_operating_units hou, gl_ledgers gl, gl_code_combinations_kfv gcck, fnd_lookup_values_vl flvv where hou.name = :$column$.operating_unit and gl.ledger_id = to_char(hou.set_of_books_id) and gcck.chart_of_accounts_id = gl.chart_of_accounts_id and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.detail_posting_allowed = 'Y' and flvv.lookup_code = gcck.gl_account_type and flvv.lookup_type = 'ACCOUNT TYPE' and flvv.view_application_id = 101 and trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and gcck.code_combination_id = ( select coalesce(pdv.account_id,gcck.code_combination_id) from pn_term_templates_all pttv, pn_distributions_all pdv where pttv.org_id (+) = hou.organization_id and pttv.name (+) = :$column$.term_template and pdv.term_template_id (+) = pttv.term_template_id and pdv.account_class (+) in ('LIA','REC') and pdv.percentage = 100 ) order by value 57 REVENUE_EXPENSE_ACCOUNT LOV custom varchar2 311 Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description from hr_operating_units hou, gl_ledgers gl, gl_code_combinations_kfv gcck, fnd_lookup_values_vl flvv where hou.name = :$column$.operating_unit and gl.ledger_id = to_char(hou.set_of_books_id) and gcck.chart_of_accounts_id = gl.chart_of_accounts_id and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.detail_posting_allowed = 'Y' and flvv.lookup_code = gcck.gl_account_type and flvv.lookup_type = 'ACCOUNT TYPE' and flvv.view_application_id = 101 and trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and gcck.code_combination_id = ( select coalesce(pdv.account_id,gcck.code_combination_id) from pn_term_templates_all pttv, pn_distributions_all pdv where pttv.org_id (+) = hou.organization_id and pttv.name (+) = :$column$.term_template and pdv.term_template_id (+) = pttv.term_template_id and pdv.account_class (+) in ('EXP','REV') and pdv.percentage = 100 ) order by value 58 ACCRUAL_ACCOUNT LOV custom varchar2 311 Y select gcck.concatenated_segments value, xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description from hr_operating_units hou, gl_ledgers gl, gl_code_combinations_kfv gcck, fnd_lookup_values_vl flvv where hou.name = :$column$.operating_unit and gl.ledger_id = to_char(hou.set_of_books_id) and gcck.chart_of_accounts_id = gl.chart_of_accounts_id and gcck.enabled_flag = 'Y' and gcck.summary_flag = 'N' and gcck.detail_posting_allowed = 'Y' and flvv.lookup_code = gcck.gl_account_type and flvv.lookup_type = 'ACCOUNT TYPE' and flvv.view_application_id = 101 and trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and gcck.code_combination_id = ( select coalesce(pdv.account_id,gcck.code_combination_id) from pn_term_templates_all pttv, pn_distributions_all pdv where pttv.org_id (+) = hou.organization_id and pttv.name (+) = :$column$.term_template and pdv.term_template_id (+) = pttv.term_template_id and pdv.account_class (+) in ('ACC','UNEARN') and pdv.percentage = 100 ) order by value 59 TERM_COMMENTS Char varchar2 2000 60 RECOVERABLE LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' order by value 61 NORMALIZE LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code = ( select nvl(pttv.normalize,flvv.lookup_code) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) order by value 62 ROU_ASSET LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' /* and flvv.lookup_code = ( select nvl(pttv.rou_asset_flag,flvv.lookup_code) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) */ order by value 63 LIABILITY LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' /* and flvv.lookup_code = ( select nvl(pttv.liability_flag,flvv.lookup_code) from hr_all_organization_units_vl haouv, pn_term_templates_all pttv where haouv.name = :$column$.operating_unit and pttv.org_id (+) = haouv.organization_id and pttv.name (+) = :$column$.term_template ) */ order by value 64 REPORT_FROM_INCEPTION LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' 65 INTERCOMPANY LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' 66 CHANGED LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='YES_NO' and flvv.view_application_id=0 and flvv.enabled_flag='Y' 67 NATURAL_BREAKPOINT_BASIS LOV custom varchar2 select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_PAYMENT_BKPT_BASIS_TYPE' and view_application_id=0 and enabled_flag='Y' order by value 68 SOURCE LOV custom varchar2 select meaning value, description description from fnd_lookup_values_vl where lookup_type='PN_TERM_SOURCE_MODULE' and view_application_id=0 and enabled_flag='Y' order by value 69 ATTRIBUTE_CATEGORY Char varchar2 30 70 ATTRIBUTE1 Char varchar2 150 71 ATTRIBUTE2 Char varchar2 150 72 ATTRIBUTE3 Char varchar2 150 73 ATTRIBUTE4 Char varchar2 150 74 ATTRIBUTE5 Char varchar2 150 75 ATTRIBUTE6 Char varchar2 150 76 ATTRIBUTE7 Char varchar2 150 77 ATTRIBUTE8 Char varchar2 150 78 ATTRIBUTE9 Char varchar2 150 79 ATTRIBUTE10 Char varchar2 150 80 ATTRIBUTE11 Char varchar2 150 81 ATTRIBUTE12 Char varchar2 150 82 ATTRIBUTE13 Char varchar2 150 83 ATTRIBUTE14 Char varchar2 150 84 ATTRIBUTE15 Char varchar2 150 85 PAYMENT_TERM_ID Number number Y 86 TERM_TEMPLATE_ID Number number 87 LEASE_ID Number number 88 LEASE_CHANGE_ID Number number 89 THIRD_PARTY_ID Number number 90 THIRD_PARTY_BILL_TO_SITE_ID Number number 91 THIRD_PARTY_SHIP_TO_SITE_ID Number number 92 START_DATE_J Number number 93 END_DATE_J Number number 94 UPLOAD_ROW Number number Y U 1 action_ in Char ACTION_CODE_ U 2 p_operating_unit in Char OPERATING_UNIT U 3 p_lease_number in Char Y LEASE_NUMBER U 4 p_lease_name in Char Y LEASE_NAME U 5 p_final_draft_flag in Char Y FINAL_DRAFT U 6 p_term_comments in Char Y TERM_COMMENTS U 7 p_status in Char Y U 8 p_approved_by in Number Y U 9 p_term_template_name in Char Y TERM_TEMPLATE U 10 p_term_template_id in Number Y U 11 p_payment_purpose in Char Y PURPOSE U 12 p_payment_purpose_code in Char Y U 13 p_payment_term_type in Char Y TYPE U 14 p_payment_term_type_code in Char Y U 15 p_frequency in Char Y FREQUENCY U 16 p_frequency_code in Char Y U 17 p_actual_amount in Number Y ACTUAL_AMOUNT U 18 p_estimated_amount in Number Y ESTIMATED_AMOUNT U 19 p_currency_code in Char Y CURRENCY_CODE U 20 p_rate in Number Y RATE U 21 p_start_date in Date Y START_DATE U 22 p_end_date in Date Y END_DATE U 23 p_target_date in Date Y TARGET_DATE U 24 p_schedule_day in Number Y SCHEDULE_DAY U 25 p_location_code in Char Y LOCATION U 26 p_location_id in Number Y U 27 p_area_type_code in Char Y AREA_TYPE U 28 p_area in Number Y AREA U 29 p_third_party_name in Char Y THIRD_PARTY_NAME U 30 p_third_party_number in Char Y THIRD_PARTY_NUMBER U 31 p_third_party_id in Number Y U 32 p_third_party_bill_to_site in Char Y THIRD_PARTY_BILL_SITE U 33 p_third_party_bill_to_site_id in Number Y U 34 p_third_party_ship_to_site in Char Y THIRD_PARTY_SHIP_SITE U 35 p_third_party_ship_to_site_id in Number Y U 36 p_cust_po_number in Char Y THIRD_PARTY_PO_NUMBER U 37 p_receipt_method in Char Y PAYMENT_METHOD U 38 p_receipt_method_id in Number Y U 39 p_ar_transaction_type in Char Y AR_INVOICE_TYPE U 40 p_cust_trx_type_id in Number Y U 41 p_salesrep in Char Y SALESPERSON U 42 p_salesrep_id in Number Y U 43 p_invoice_rule in Char Y INVOICING_RULE U 44 p_inv_rule_id in Number Y U 45 p_accounting_rule in Char Y ACCOUNTING_RULE U 46 p_account_rule_id in Number Y U 47 p_po_number in Char Y PO_NUMBER U 48 p_po_header_id in Number Y U 49 p_grouping_rule in Char Y INVOICE_GROUPING_RULE U 50 p_grouping_rule_id in Number Y U 51 p_distribution_set in Char Y DISTRIBUTION_SET U 52 p_distribution_set_id in Number Y U 53 p_project in Char Y PROJECT U 54 p_project_id in Number Y U 55 p_task in Char Y TASK U 56 p_task_id in Number Y U 57 p_expenditure_type in Char Y EXPENDITURE_TYPE U 58 p_expenditure_item_date in Date Y EXPENDITURE_ITEM_DATE U 59 p_expenditure_organization in Char Y EXPENDITURE_ORGANIZATION U 60 p_organization_id in Number Y U 61 p_code_combination_id in Number Y U 62 p_rou_asset_flag in Char Y ROU_ASSET U 63 p_liability_flag in Char Y LIABILITY U 64 p_payment_term in Char Y PAYMENT_TERMS U 65 p_ap_ar_term_id in Number Y U 66 p_tax_included in Char Y TAX_INCLUDED U 67 p_tax_classification_code in Char Y TAX_CLASSIFICATION_CODE U 68 p_tax_group_id in Number Y U 69 p_tax_code_id in Number Y U 70 p_source_name in Char Y SOURCE U 71 p_source_code in Char Y U 72 p_normalize in Char Y NORMALIZE U 73 p_norm_start_date in Date Y U 74 p_norm_end_date in Date Y U 75 p_include_in_var_rent in Char Y NATURAL_BREAKPOINT_BASIS U 76 p_recoverable_flag in Char Y RECOVERABLE U 77 p_attribute_category in Char Y ATTRIBUTE_CATEGORY U 78 p_attribute1 in Char Y ATTRIBUTE1 U 79 p_attribute2 in Char Y ATTRIBUTE2 U 80 p_attribute3 in Char Y ATTRIBUTE3 U 81 p_attribute4 in Char Y ATTRIBUTE4 U 82 p_attribute5 in Char Y ATTRIBUTE5 U 83 p_attribute6 in Char Y ATTRIBUTE6 U 84 p_attribute7 in Char Y ATTRIBUTE7 U 85 p_attribute8 in Char Y ATTRIBUTE8 U 86 p_attribute9 in Char Y ATTRIBUTE9 U 87 p_attribute10 in Char Y ATTRIBUTE10 U 88 p_attribute11 in Char Y ATTRIBUTE11 U 89 p_attribute12 in Char Y ATTRIBUTE12 U 90 p_attribute13 in Char Y ATTRIBUTE13 U 91 p_attribute14 in Char Y ATTRIBUTE14 U 92 p_attribute15 in Char Y ATTRIBUTE15 U 93 p_source_payment_term_id in Number Y U 94 p_parent_term_id in Number Y U 95 p_term_altered_flag in Char Y U 96 p_rept_inception_flag in Char Y REPORT_FROM_INCEPTION U 97 p_update_nbp_flag in Char Y U 98 p_index_norm_flag in Char Y U 99 p_intercompany_flag in Char Y INTERCOMPANY U 100 p_changed_flag in Char Y CHANGED U 101 p_error_flag in Char Y U 102 p_expired_flag in Char Y U 103 p_index_term_indicator in Char Y U 104 p_index_period_id in Number Y U 105 p_var_rent_type in Char Y U 106 p_var_rent_inv_id in Number Y U 107 p_event_type_code in Char Y U 108 p_amount_type in Char Y U 109 p_period_billrec_id in Number Y U 110 p_rec_agr_line_id in Number Y U 111 p_recur_bb_calc_date in Date Y U 112 p_opex_type in Char Y U 113 p_opex_agr_id in Number Y U 114 p_opex_recon_id in Number Y U 115 p_project_attribute_category in Char Y U 116 p_project_attribute1 in Char Y U 117 p_project_attribute2 in Char Y U 118 p_project_attribute3 in Char Y U 119 p_project_attribute4 in Char Y U 120 p_project_attribute5 in Char Y U 121 p_project_attribute6 in Char Y U 122 p_project_attribute7 in Char Y U 123 p_project_attribute8 in Char Y U 124 p_project_attribute9 in Char Y U 125 p_project_attribute10 in Char Y U 126 p_project_attribute11 in Char Y U 127 p_project_attribute12 in Char Y U 128 p_project_attribute13 in Char Y U 129 p_project_attribute14 in Char Y U 130 p_project_attribute15 in Char Y U 136 p_lia_rec_concatenated_segs in Char Y RECEIVABLES_LIABILITY_ACCOUNT U 137 p_lia_rec_code_combination_id in Number Y U 138 p_lia_rec_account_class in Char Y U 139 p_exp_rev_concatenated_segs in Char Y REVENUE_EXPENSE_ACCOUNT U 140 p_exp_rev_code_combination_id in Number Y U 141 p_exp_rev_account_class in Char Y U 142 p_accrual_concatenated_segs in Char Y ACCRUAL_ACCOUNT U 143 p_accrual_code_combination_id in Number Y U 144 p_accrual_account_class in Char Y U 145 p_payment_term_id in/out Number PAYMENT_TERM_ID U 146 p_row_id in/out Char ROW_ID U 147 request_id_ out Number REQUEST_ID_ U 148 status_ out Char STATUS_CODE_ U 149 message_ out Char MESSAGE_ P 1 p_operating_unit in Char OPERATING_UNIT P 2 p_lease_number in Char LEASE_NUMBER P 3 p_lease_name in Char LEASE_NAME P 4 request_id_ out Number REQUEST_ID_ P 5 status_ out Char STATUS_CODE_ P 6 message_ out Char MESSAGE_ Error SQL Order By upload_row, operating_unit, lease_name, start_date_j, end_date_j, purpose, type Success SQL select xm.action_, xm.status_, xm.message_, xm.request_id_, xm.modified_columns_, null row_id, haouv.name operating_unit, plv.lease_name, plv.lease_number, xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) final_draft, plv.lease_class, plv.lease_type, nvl(plv.customer_name,plv.supplier_name) lease_third_party_name, nvl( plv.customer_number, (select asu.segment1 from ap_suppliers asu where asu.vendor_id = plv.supplier_id) ) lease_third_party_number, plv.location_code_disp primary_location, plv.property_name_disp property, plv.user_responsible, -- pptv.term_template, pptv.payment_purpose purpose, pptv.payment_term_type type, pptv.frequency_type frequency, pptv.estimated_amount, pptv.actual_amount, pptv.currency_code, pptv.rate, -- pptv.start_date, pptv.end_date, pptv.target_date, -- pptv.schedule_day, -- pla.location_code location, xxen_util.meaning(pptv.area_type_code,'PN_AREA_TYPE_CODE',0) area_type, pptv.area, pptv.actual_amount * 12 annual_amount, case when nvl(pptv.area,0) = 0 then to_number(null) else round((pptv.actual_amount * 12)/pptv.area,2) end annual_area_amount, -- nvl(pptv.customer_name,pptv.vendor_name) third_party_name, nvl(pptv.customer_number,pptv.vendor_number) third_party_number, nvl(pptv.customer_site_use,pptv.vendor_site) third_party_bill_site, pptv.cust_ship_site_use third_party_ship_site, pptv.cust_po_number third_party_po_number, (select poha.segment1 po_number from po_headers_all poha where poha.po_header_id = pptv.po_header_id) po_number, -- case when plv.lease_class_code in ('THIRD_PARTY','SUB_LEASE','REC') then (select rtv.name from ra_terms_vl rtv where rtv.term_id = pptv.ap_ar_term_id) else (select atv.name from ap_terms_vl atv where atv.term_id = pptv.ap_ar_term_id) end payment_terms, -- billing (select arm.name from ar_receipt_methods arm where arm.receipt_method_id = pptv.receipt_method_id) payment_method, (select rctta.name from ra_cust_trx_types_all rctta where rctta.org_id = pptv.org_id and rctta.cust_trx_type_id = pptv.cust_trx_type_id) ar_invoice_type, (select rsa.name from ra_salesreps_all rsa where rsa.org_id = pptv.org_id and rsa.salesrep_id = pptv.salesrep_id) salesperson, (select rr.name from ra_rules rr where rr.rule_id = pptv.inv_rule_id) invoicing_rule, (select rr.name from ra_rules rr where rr.rule_id = pptv.account_rule_id) accounting_rule, -- payments pptv.inv_group_name invoice_grouping_rule, (select distribution_set_name from ap_distribution_sets_all adsa where adsa.distribution_set_id = pptv.distribution_set_id) distribution_set, (select ppa.name from pa_projects_all ppa where ppa.project_id = pptv.project_id and ppa.org_id = pptv.org_id) project, (select pt.task_name from pa_tasks pt where pptv.task_id=pt.task_id) task, --pptv.agreement_name, --pptv.agreement_number, pptv.expenditure_type, pptv.expenditure_item_date, (select haouv_exp.name from hr_all_organization_units_vl haouv_exp where pptv.organization_id=haouv_exp.organization_id) expenditure_organization, -- tax xxen_util.meaning(decode(pptv.tax_included,'A','Y',pptv.tax_included),'YES_NO',0) tax_included, pptv.tax_classification_code, -- (select gcck.concatenated_segments from pn_distributions_v pdv, gl_code_combinations_kfv gcck where pdv.payment_term_id = pptv.payment_term_id and gcck.code_combination_id = pdv.account_id and pdv.account_class in ('LIA','REC') and pdv.percentage = 100 ) receivables_liability_account, (select gcck.concatenated_segments from pn_distributions_v pdv, gl_code_combinations_kfv gcck where pdv.payment_term_id = pptv.payment_term_id and gcck.code_combination_id = pdv.account_id and pdv.account_class in ('EXP','REV') and pdv.percentage = 100 ) revenue_expense_account, (select gcck.concatenated_segments from pn_distributions_v pdv, gl_code_combinations_kfv gcck where pdv.payment_term_id = pptv.payment_term_id and gcck.code_combination_id = pdv.account_id and pdv.account_class in ('ACC','UNEARN') and pdv.percentage = 100 ) accrual_account, -- pptv.term_comments, -- xxen_util.meaning(pptv.recoverable_flag,'YES_NO',0) recoverable, xxen_util.meaning(pptv.normalize,'YES_NO',0) normalize, null rou_asset, null liability, null report_from_inception, null intercompany, xxen_util.meaning(pptv.changed_flag,'YES_NO',0) changed, xxen_util.meaning(pptv.include_in_var_rent,'PN_PAYMENT_BKPT_BASIS_TYPE',0) natural_breakpoint_basis, xxen_util.meaning(pptv.source_module,'PN_TERM_SOURCE_MODULE',0) source, -- pptv.attribute_category, pptv.attribute1, pptv.attribute2, pptv.attribute3, pptv.attribute4, pptv.attribute5, pptv.attribute6, pptv.attribute7, pptv.attribute8, pptv.attribute9, pptv.attribute10, pptv.attribute11, pptv.attribute12, pptv.attribute13, pptv.attribute14, pptv.attribute15, -- pptv.payment_term_id, pptv.term_template_id, pptv.lease_id, pptv.lease_change_id, nvl(pptv.customer_id,pptv.vendor_id) third_party_id, nvl(pptv.customer_site_use_id,pptv.vendor_site_id) third_party_bill_to_site_id, pptv.cust_ship_site_id third_party_ship_to_site_id, to_number(to_char(pptv.start_date,'j')) start_date_j, to_number(to_char(pptv.end_date,'j')) end_date_j, xm.upload_row from (select distinct xm.action_, xm.status_, xm.message_, xm.request_id_, xm.operating_unit, xm.lease_name, xm.lease_number, xm.final_draft, to_number(xm.payment_term_id) payment_term_id, xm.modified_columns_, xm.upload_row from xxen_pn_billing_payment_8331_u xm where xxen_pn_upload.get_status_(xm.request_id_,xm.row_id) = xxen_upload.status_success ) xm, pn_leases_v plv, pn_payment_terms_v pptv, pn_locations_all pla, hr_all_organization_units haouv where pptv.lease_id = plv.lease_id and plv.org_id = haouv.organization_id and pptv.location_id = pla.location_id (+) and xm.operating_unit = haouv.name and xm.lease_name = plv.lease_name and xm.lease_number = plv.lease_number and xm.final_draft = xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) and ( xm.payment_term_id = pptv.payment_term_id or (xm.payment_term_id is null and pptv.created_by = fnd_global.user_id and exists (select null from fnd_concurrent_requests fcr where fcr.request_id = xm.request_id_ and pptv.creation_date between fcr.actual_start_date and nvl(fcr.actual_completion_date,sysdate)) ) )