ENGINATICSSYSTEM_ADMINISTRATORSYSADMIN2F97920750BC0858E0636DFB0905569AYselect
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
ppsv.payment_schedule_id,
--
haouv.name operating_unit,
ppsv.lease_name,
ppsv.lease_number,
xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) lease_approval_status,
xxen_util.meaning(ppsv.lease_class_code,'PN_LEASE_CLASS',0) lease_class,
xxen_util.user_name(plda.responsible_user,'N') responsible_user,
ppsv.schedule_date,
xxen_util.meaning(ppsv.payment_status_lookup_code,'PN_PAYMENT_STATUS_TYPE',0) payment_billing_status,
ppsv.user_approved_by approved_by,
ppsv.period_name period,
pnp_util_func.get_total_payment_item_amt
(ppsv.payment_status_lookup_code,
:p_sob_currency,
ppsv.payment_schedule_id,
'PNTAUPMT'
) total_amount,
ppsv.user_transferred_by,
ppsv.approval_date,
ppsv.transfer_date,
--
xxen_util.meaning(ppsv.lease_status,'PN_LEASESTATUS_TYPE',0) lease_status,
ppsv.lease_id,
ppsv.lease_change_id,
:p_set_of_books_id set_of_books_id,
:p_sob_currency set_of_books_currency
from
pn_payment_schedules_v ppsv,
pn_leases_v plv,
pn_lease_details plda,
hr_all_organization_units_vl haouv
where
1=1 and
:p_upload_mode like '%' || xxen_upload.action_update || '%' and
:p_set_of_books_id=:p_set_of_books_id and
ppsv.lease_id = plv.lease_id and
ppsv.lease_id = plda.lease_id and
ppsv.org_id = haouv.organization_id and
(ppsv.status = 'F' or
(ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D')
)UAxxen_pn_upload.upload_schedule_statusUSPN Schedule Approval Status UploadProperty Management - Schedule Approval Status Upload
This upload allows users to Approve (Approved status), Un-Approve (Draft status), or place On Hold Payment and Billing Schedules in Property Management. This upload offers the same functionality as the Oracle Authorize Payments and the Authorize Billings forms.
Upload Mode
=================
When run in “Create, Update” mode, the Payment/Billing Schedules matching the criteria specified in the report parameters will be downloaded into Excel where the user can review and amend the Payment/Billing Approval Status and, for approved schedules, the Approver and the Period.
When run in “Create” mode, an empty excel will be generated. This mode allows the user to paste Payment/Billing schedule data from another source. In this mode the upload will find the matching Payment/Billing Schedule based on the data in each excel row and apply the approval change. The upload will return an error message if the upload cannot find a matching schedule, or if it finds multiple matching schedules.
Approved Schedules
=================
- if the Approver is not specified in the upload excel, then the username of the user executing the upload is used.
- if the Period is not specified in the upload excel, then this will be derived during the upload based on the Schedule Date
Notes
=================
You cannot create new Payment/Billing schedules using this upload. It can only be used for updating the approval status of existing Payment/Billing Schedules.
EnginaticsUpload1=1:p_set_of_books_id:p_sob_currency:p_upload_mode1-20:p_set_of_books_idLOV OraclePN_SETS_OF_BOOKSYselect
set_of_books_id id,
name value,
null description
from
gl_sets_of_books
where
name in (select name from gl_ledgers
where ledger_id in (select ledger_id from gl_access_set_ledgers
where access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')))
order by value,descriptionselect
pn.set_of_books_id
from
pn_system_setup_options pn,
hr_operating_units hou
where
pn.org_id = hou.organization_id and
(xxen_util.contains(:$flex$.operating_unit,hou.name) = 'Y' or
(:$flex$.operating_unit is null and hou.organization_id = fnd_profile.value('ORG_ID'))
) and
rownum = 1USLedger ID2-10:p_sob_currencyCharselect
gsob.currency_code
from
gl_sets_of_books gsob
where
gsob.set_of_books_id = :$flex$.Ledger_IDUSLedger Currency310:p_upload_modeLOVBlitz Upload ModeE811F942597A2075E0530100007FF4F2Yselect 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 dualC,UYUSUpload Mode4201=1haouv.name=:p_operating_unitLOVHR Operating Unit8E2FF36EDEB979D2E0530100007F1FF2Yselect
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 mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
order by
hou.namecoalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id))USOperating Unit5301=1nvl(plv.customer_name,plv.supplier_name) = :p_third_party_nameLOV customYYselect 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
valueUSThird Party Name6401=1plv.lease_class = :p_lease_classLOVPN Lease ClassFAEA24CB5B4E92CCE0530100007F4C87Yselect
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,descriptionUSLease Class7501=1plv.lease_type = :p_lease_typeLOVPN Lease TypeFAEA382CD59B9A20E0530100007FBCABYselect
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,descriptionUSLease Type8601=1plv.lease_name = :p_lease_nameLOVPN Lease Name - with Schedules2F9413DFCBFD085CE0636DFB0905FB02YYselect
plv.lease_name value,
plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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') and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
valueUSLease Name9701=1plv.lease_name >= :p_lease_name_frLOVPN Lease Name - with Schedules2F9413DFCBFD085CE0636DFB0905FB02YYselect
plv.lease_name value,
plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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') and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
valueUSLease Name From10801=1plv.lease_name <= :p_lease_name_toLOVPN Lease Name - with Schedules2F9413DFCBFD085CE0636DFB0905FB02YYselect
plv.lease_name value,
plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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') and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
value:$flex$.Lease_Name_FromUSLease Name To11901=1plv.lease_number = :p_lease_numberLOVPN Lease Number - with Schedules2F9FAC2F4D57086AE0636DFB09056994YYselect
plv.lease_number value,
plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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') and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
valueUSLease Number121001=1plv.lease_number >= :p_lease_number_frLOVPN Lease Number - with Schedules2F9FAC2F4D57086AE0636DFB09056994YYselect
plv.lease_number value,
plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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') and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
valueUSLease Number From131101=1plv.lease_number <= :p_lease_number_toLOVPN Lease Number - with Schedules2F9FAC2F4D57086AE0636DFB09056994YYselect
plv.lease_number value,
plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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') and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
value:$flex$.Lease_Number_FromUSLease Number To141151=1ppsv.payment_status_lookup_code=:p_schedule_statusLOV customYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where lookup_type='PN_PAYMENT_STATUS_TYPE'
order by value,descriptionDRAFTUSSchedule Status151201=1ppsv.schedule_date>=:p_schedule_from_dateDateUSSchedule Date From161301=1ppsv.schedule_date<trunc(:p_schedule_to_date)+1Date:$flex$.Schedule_Date_FromUSSchedule Date To171401=1exists (
select
null
from
pn_payment_items_all ppia
where
ppia.payment_schedule_id = ppsv.payment_schedule_id and
ppia.due_date >= :p_trx_from_date
)
DateUSTransaction Date From181501=1exists (
select
null
from
pn_payment_items_all ppia
where
ppia.payment_schedule_id = ppsv.payment_schedule_id and
ppia.due_date < trunc(:p_trx_to_date)+1
)Date:$flex$.Transaction_Date_FromUSTransaction Date To191601=1exists (
select
null
from
pn_tenancies_all pta,
pn_locations_all pla
where
pta.lease_id = ppsv.lease_id and
pta.location_id = pla.location_id and
pla.location_code = :p_location_code
)LOVPN Location2F97920750BD0858E0636DFB0905569AYselect
pl.location_code id,
pl.location_code value,
pl.description description
from
pn_locations pl
order by
value,
descriptionUSLocation201701=1exists (
select
null
from
pn_tenancies_all pta,
pn_locations_all pla
where
pta.lease_id = ppsv.lease_id and
pta.location_id = pla.location_id and
pla.location_code >= :p_location_from_code
)LOVPN Location2F97920750BD0858E0636DFB0905569AYselect
pl.location_code id,
pl.location_code value,
pl.description description
from
pn_locations pl
order by
value,
descriptionUSLocation From211801=1exists (
select
null
from
pn_tenancies_all pta,
pn_locations_all pla
where
pta.lease_id = ppsv.lease_id and
pta.location_id = pla.location_id and
pla.location_code <= :p_location_to_code
)LOVPN Location2F97920750BD0858E0636DFB0905569AYselect
pl.location_code id,
pl.location_code value,
pl.description description
from
pn_locations pl
order by
value,
description:$flex$.Location_FromUSLocation To222501=1plda.responsible_user=xxen_util.user_id(:p_responsible_user)LOVFND User Name8E2FF36EDE8479D2E0530100007F1FF2YYselect
fu.user_name value,
trim(coalesce(
trim(papf.first_name||' '||papf.last_name),
fu.description,
fu.email_address,
papf.email_address
)||fu.inactive) description
from
(select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu,
(select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf,
(
select distinct
furg.user_id,
count(*) over (partition by furg.user_id) resp_count,
max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key
from
fnd_responsibility fr,
fnd_user_resp_groups_direct furg
where
fr.responsibility_id=furg.responsibility_id and
fr.application_id=furg.responsibility_application_id
) furg
where
fu.employee_id=papf.person_id(+) and
fu.user_id=furg.user_id(+) and
not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE')
order by
fu.inactive desc,
fu.user_nameUSLease Responsible User232601=1ppsv.user_approved_by=:p_approved_by_userLOVFND User Name8E2FF36EDE8479D2E0530100007F1FF2YYselect
fu.user_name value,
trim(coalesce(
trim(papf.first_name||' '||papf.last_name),
fu.description,
fu.email_address,
papf.email_address
)||fu.inactive) description
from
(select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu,
(select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf,
(
select distinct
furg.user_id,
count(*) over (partition by furg.user_id) resp_count,
max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key
from
fnd_responsibility fr,
fnd_user_resp_groups_direct furg
where
fr.responsibility_id=furg.responsibility_id and
fr.application_id=furg.responsibility_application_id
) furg
where
fu.employee_id=papf.person_id(+) and
fu.user_id=furg.user_id(+) and
not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE')
order by
fu.inactive desc,
fu.user_nameUSSchedule Approved By User242701=1ppsv.period_name=:p_approved_periodLOV customYselect distinct
gps.period_name id,
gps.period_name value,
gps.start_date || ' - ' || gps.end_date || ' (' || gps.closing_status || ')' description,
gps.start_date
from
gl_period_statuses gps,
gl_sets_of_books gsob
where
gps.set_of_books_id=:$flex$.ledger_id and
gsob.set_of_books_id=gps.set_of_books_id and
gsob.accounted_period_type=gps.period_type and
gps.adjustment_period_flag='N' and
gps.closing_status in ('O','F') and
gps.application_id in (200,222)
order by
gps.start_date desc,
gps.period_nameUSSchedule Approved Period1ACTION_Charvarchar22STATUS_Charvarchar23MESSAGE_Charvarchar24REQUEST_ID_Charvarchar2Y5MODIFIED_COLUMNS_Charvarchar2Y6PAYMENT_SCHEDULE_IDNumbernumberY7OPERATING_UNITLOVvarchar2240HR Operating Unit8E2FF36EDEB979D2E0530100007F1FF2Yselect
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 mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
order by
hou.nameYY8LEASE_NAMELOV customvarchar250Yselect
plv.lease_name value,
plv.lease_number || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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
(:$column$.lease_number is null or
(plv.lease_number = :$column$.lease_number and
plv.status = nvl(xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0),plv.status)
)
) and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
valueselect
plv.lease_name
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_number = :$column$.lease_number and
plv.status = nvl(xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0),plv.status)
Y9LEASE_NUMBERLOV customvarchar230Yselect
plv.lease_number value,
plv.lease_name || ' - ' || plv.lease_class || ' - ' || plv.lease_type || ' - ' || nvl(plv.customer_name,plv.supplier_name) || ' - ' || xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) 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
(:$column$.lease_name is null or plv.lease_name = :$column$.lease_name) and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
order by
valueYY10LEASE_APPROVAL_STATUSLOV customvarchar2Yselect
lookup_code id,
meaning value,
description description
from
fnd_lookups
where
lookup_type = 'PN_LEASE_STATUS_TYPE' and
enabled_flag = 'Y' and
lookup_code in
(
select
plv.status
from
pn_leases_v plv,
hr_all_organization_units_vl haouv
where
plv.org_id = haouv.organization_id and
haouv.name = :$column$.operating_unit and
plv.lease_number = :$column$.lease_number and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)
)
order by value,descriptionselect
fl.lookup_code id,
fl.meaning value
from
fnd_lookups fl
where
fl.lookup_type = 'PN_LEASE_STATUS_TYPE' and
fl.enabled_flag = 'Y'select
xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0)
from
pn_leases_v plv,
hr_all_organization_units_vl haouv
where
plv.org_id = haouv.organization_id and
haouv.name = :$column$.operating_unit and
plv.lease_number = :$column$.lease_number and
exists
(select
null
from
pn_payment_schedules_v ppsv
where ppsv.lease_id = plv.lease_id and
(ppsv.status = 'F' or (ppsv.lease_status in ('LOF','SGN') and ppsv.status ='D'))
)YY11LEASE_CLASSLOV customvarchar2Yselect
lookup_code id,
meaning value,
description description
from
fnd_lookups
where
lookup_type='PN_LEASE_CLASS' and
enabled_flag='Y' and
lookup_code in
(
select
plv.lease_class_code
from
pn_leases_v plv,
hr_all_organization_units_vl haouv
where
plv.org_id = haouv.organization_id and
haouv.name = :$column$.operating_unit and
plv.lease_number = :$column$.lease_number and
plv.status = nvl(xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0),plv.status)
)
order by value,descriptionselect
plv.lease_class
from
pn_leases_v plv,
hr_all_organization_units_vl haouv
where
plv.org_id = haouv.organization_id and
haouv.name = :$column$.operating_unit and
plv.lease_number = :$column$.lease_number and
plv.status = nvl(xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0),plv.status)Y12RESPONSIBLE_USERLOV customvarchar2YYselect
fu.user_name value,
trim(coalesce(
trim(papf.first_name||' '||papf.last_name),
fu.description,
fu.email_address,
papf.email_address
)||fu.inactive) description
from
(select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu,
(select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf,
(
select distinct
furg.user_id,
count(*) over (partition by furg.user_id) resp_count,
max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key
from
fnd_responsibility fr,
fnd_user_resp_groups_direct furg
where
fr.responsibility_id=furg.responsibility_id and
fr.application_id=furg.responsibility_application_id
) furg
where
fu.employee_id=papf.person_id(+) and
fu.user_id=furg.user_id(+) and
not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') and
fu.user_id in
(
select
plda.responsible_user
from
pn_leases_v plv,
pn_lease_details plda,
hr_all_organization_units_vl haouv
where
plv.lease_id = plda.lease_id and
plv.org_id = haouv.organization_id and
haouv.name = :$column$.operating_unit and
plv.lease_number = :$column$.lease_number and
plv.status = nvl(xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0),plv.status)
)
order by
fu.inactive desc,
fu.user_nameselect
fu.user_name
from
fnd_user fu
where
fu.user_id in
(
select
plda.responsible_user
from
pn_leases_v plv,
pn_lease_details plda,
hr_all_organization_units_vl haouv
where
plv.lease_id = plda.lease_id and
plv.org_id = haouv.organization_id and
haouv.name = :$column$.operating_unit and
plv.lease_number = :$column$.lease_number and
plv.status = nvl(xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0),plv.status)
)Y13SCHEDULE_DATEDatedateYY14PAYMENT_BILLING_STATUSLOV customvarchar2Yselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where
lookup_type='PN_PAYMENT_STATUS_TYPE'
order by value,descriptionselect
fl.lookup_code id,
fl.meaning value
from
fnd_lookups fl
where
fl.lookup_type = 'PN_PAYMENT_STATUS_TYPE' and
fl.enabled_flag = 'Y'Y15APPROVED_BYLOV customvarchar2100YYselect
fu.user_name value,
trim(coalesce(
trim(papf.first_name||' '||papf.last_name),
fu.description,
fu.email_address,
papf.email_address
)||fu.inactive) description
from
(select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu,
(select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf,
(
select distinct
furg.user_id,
count(*) over (partition by furg.user_id) resp_count,
max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key
from
fnd_responsibility fr,
fnd_user_resp_groups_direct furg
where
fr.responsibility_id=furg.responsibility_id and
fr.application_id=furg.responsibility_application_id
) furg
where
fu.employee_id=papf.person_id(+) and
fu.user_id=furg.user_id(+) and
not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') and
:$column$.payment_billing_status = xxen_util.meaning('APPROVED','PN_PAYMENT_STATUS_TYPE',0)
order by
fu.inactive desc,
fu.user_nameselect
fu.user_id id,
fu.user_name value
from
fnd_user fuselect
fnd_global.user_name
from
dual
where
:$column$.payment_billing_status = xxen_util.meaning('APPROVED','PN_PAYMENT_STATUS_TYPE',0)16PERIODLOV customvarchar215Yselect distinct
gps.period_name id,
gps.period_name value,
to_char(gps.start_date,'DD-MON-RR') || ' - ' || to_char(gps.end_date,'DD-MON-RR') || ' (' || gps.closing_status || ')' description,
gps.start_date
from
gl_period_statuses gps,
gl_sets_of_books gsob
where
gps.set_of_books_id=:$flex$.ledger_id and
gsob.set_of_books_id=gps.set_of_books_id and
gsob.accounted_period_type=gps.period_type and
gps.adjustment_period_flag='N' and
gps.closing_status in ('O','F') and
gps.application_id = (select decode(plv.lease_class_code,'DIRECT',200,222) from pn_leases_v plv where plv.lease_number = :$column$.lease_number and plv.status = xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0)) and
:$column$.payment_billing_status = xxen_util.meaning('APPROVED','PN_PAYMENT_STATUS_TYPE',0)
order by
gps.start_date desc,
gps.period_nameselect
pnp_util_func.get_default_gl_period
(:$column$.schedule_date,
decode(plv.lease_class_code,'DIRECT',200,222),
plv.org_id
)
from
pn_leases_v plv,
hr_all_organization_units_vl haouv
where
plv.org_id = haouv.organization_id and
haouv.name = :$column$.operating_unit and
plv.lease_number = :$column$.lease_number and
plv.status = nvl(xxen_util.lookup_code(:$column$.lease_approval_status,'PN_LEASE_STATUS_TYPE',0),plv.status) and
:$column$.payment_billing_status = xxen_util.meaning('APPROVED','PN_PAYMENT_STATUS_TYPE',0)17TOTAL_AMOUNTNumbernumberY18USER_TRANSFERRED_BYCharvarchar2100Y19APPROVAL_DATEDatedateY20TRANSFER_DATEDatedateY21LEASE_STATUSCharvarchar2Y22LEASE_IDNumbernumberY23LEASE_CHANGE_IDNumbernumberY24SET_OF_BOOKS_IDCharvarchar22000:$flex$.Ledger_IDYY25SET_OF_BOOKS_CURRENCYCharvarchar22000:$flex$.Ledger_CurrencyYYU2p_operating_unitinCharOPERATING_UNITU3p_lease_numberinCharLEASE_NUMBERU4p_lease_nameinCharLEASE_NAMEU5p_lease_approval_statusinCharLEASE_APPROVAL_STATUSU6p_schedule_dateinDateSCHEDULE_DATEU7p_payment_status_lookup_codeinCharPAYMENT_BILLING_STATUSU8p_approved_by_user_idinNumberAPPROVED_BYU9p_periodinCharPERIODU10p_set_of_books_idinNumberSET_OF_BOOKS_IDU11p_set_of_books_currencyinCharSET_OF_BOOKS_CURRENCYU12p_payment_schedule_idin/outCharPAYMENT_SCHEDULE_IDU13request_id_outNumberREQUEST_ID_U14status_outCharSTATUS_CODE_U15message_outCharMESSAGE_Error SQLOrder Bylease_name,
lease_number,
lease_id,
schedule_dateSuccess SQLselect
xxen_upload.action_meaning(xxen_upload.action_update) action_,
xu.status_,
xu.message_,
xu.request_id_,
xu.modified_columns_,
xu.payment_schedule_id,
--
haouv.name operating_unit,
ppsv.lease_name,
ppsv.lease_number,
xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) lease_approval_status,
xxen_util.meaning(ppsv.lease_class_code,'PN_LEASE_CLASS',0) lease_class,
xxen_util.user_name(plda.responsible_user,'N') responsible_user,
ppsv.schedule_date,
xxen_util.meaning(ppsv.payment_status_lookup_code,'PN_PAYMENT_STATUS_TYPE',0) payment_billing_status,
ppsv.user_approved_by approved_by,
ppsv.period_name period,
pnp_util_func.get_total_payment_item_amt
(ppsv.payment_status_lookup_code,
:p_sob_currency,
ppsv.payment_schedule_id,
'PNTAUPMT'
) total_amount,
ppsv.user_transferred_by,
ppsv.approval_date,
ppsv.transfer_date,
--
xxen_util.meaning(ppsv.lease_status,'PN_LEASESTATUS_TYPE',0) lease_status,
ppsv.lease_id,
ppsv.lease_change_id,
xu.set_of_books_id,
xu.set_of_books_currency
from
xxen_pn_schedule_approv_7305_u xu,
pn_payment_schedules_v ppsv,
pn_leases_v plv,
pn_lease_details plda,
hr_all_organization_units_vl haouv
where
xu.status_code_ = xxen_upload.status_success and
xu.payment_schedule_id = ppsv.payment_schedule_id and
ppsv.lease_id = plv.lease_id and
ppsv.lease_id = plda.lease_id and
ppsv.org_id = haouv.organization_id