ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C16289D530640089D70DA9E1673EEC0Y select to_char(null) action_, to_char(null) status_, to_char(null) message_, to_char(null) modified_columns_, haouv.name operating_unit, cpa.name payment_batch, cpsa.period_name pay_period, jrrev.resource_name, jrs.salesrep_number employee_number, cqa.name plan_element, cn_api.get_pay_element_name(cpta.pay_element_type_id) pay_element, xxen_util.yes(cpta.recoverable_flag) recoverable, cpta.payment_amount, to_char(null) reason, to_char(null) delete_payment, cpta.payment_transaction_id, to_char(null) upload_row from cn_payment_transactions_all cpta, cn_payruns_all cpa, cn_period_statuses_all cpsa, hr_all_organization_units_vl haouv, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev, cn_quotas_all cqa where 1=1 and cpta.incentive_type_code='MANUAL_PAY_ADJ' and cpta.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and cpta.payrun_id=cpa.payrun_id and cpa.pay_period_id=cpsa.period_id and cpa.org_id=cpsa.org_id and cpa.org_id=haouv.organization_id and cpta.credited_salesrep_id=jrs.salesrep_id and cpta.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id and cpta.quota_id=cqa.quota_id(+) Initial version: creates, updates and deletes Incentive Compensation manual payments (paysheet manual payment adjustments) through cn_pmt_trans_pvt, with the paysheet notes the Paysheet Detail page writes. U A xxen_cn_upload.upload_manual_payment US CN Manual Payment Upload Creates, updates and deletes Incentive Compensation manual payments - the Excel equivalent of the Manual Payments tab of the Paysheet Detail page (Payments, Payment Batches). A manual payment adjusts a resource's paysheet in an unpaid payment batch for one plan element. The paysheet must already exist and must not be locked or submitted for approval. A recoverable payment is recovered from future earnings. Each change writes the same system note on the paysheet as the Paysheet Detail page. Text entered in Reason is added as a user note on the paysheet, once per paysheet and text. Existing manual payments are downloaded with Upload Mode Create, Update. Only the Payment Amount and Recoverable flag of an existing manual payment can be changed; to move it to another plan element, delete it and create a new one. Enginatics R12 only Upload 1=1 1 10 1=1 :p_upload_mode like '%'||xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 haouv.name=: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)) Y US Operating Unit 3 30 1=1 cpa.name=:payment_batch LOV custom select cpa.name value, cpsa.period_name||', '||cpa.status description from cn_payruns_all cpa, cn_period_statuses_all cpsa, hr_all_organization_units_vl haouv where cpa.pay_period_id=cpsa.period_id and cpa.org_id=cpsa.org_id and cpa.org_id=haouv.organization_id and haouv.name=:$flex$.operating_unit and (:$flex$.unpaid_batches_only is null or cpa.status='UNPAID') order by cpa.pay_date desc, cpa.name US Payment Batch 4 40 1=1 cpsa.period_name=:pay_period LOV custom select cpsa.period_name value, null description from cn_period_statuses_all cpsa, hr_all_organization_units_vl haouv where cpsa.org_id=haouv.organization_id and haouv.name=:$flex$.operating_unit and cpsa.period_id in (select cpa.pay_period_id from cn_payruns_all cpa where cpa.org_id=cpsa.org_id) order by cpsa.start_date desc US Pay Period 5 50 1=1 jrrev.resource_name=:resource_name LOV custom select distinct jrrev.resource_name value, jrs.salesrep_number description from cn_payment_worksheets_all cpwa, hr_all_organization_units_vl haouv, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where cpwa.quota_id is null and cpwa.org_id=haouv.organization_id and haouv.name=:$flex$.operating_unit and cpwa.salesrep_id=jrs.salesrep_id and cpwa.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id order by 1 US Resource Name 6 60 1=1 cpa.status='UNPAID' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Unpaid Batches Only Downloads only the manual payments of payment batches that are not paid yet, which are the only ones that can be changed. 2E7C7C2C43CF42BDAD498449C4178D86 Create Manual Payments Opens an empty sheet to enter new manual payments, for example a list of one-off payments or corrections prepared outside Oracle. ENGINATICS 5 EMPLOYEE_NUMBER 1 OPERATING_UNIT Y 9 PAYMENT_AMOUNT 2 PAYMENT_BATCH 7 PAY_ELEMENT 3 PAY_PERIOD 6 PLAN_ELEMENT 10 REASON 8 RECOVERABLE 4 RESOURCE_NAME Upload Mode C Y S Site Pay Period Resource Name Unpaid Batches Only A0E96846DE284C2A868850F6C3BE879B Default Creates, updates and deletes manual payments. ENGINATICS 11 DELETE_PAYMENT 5 EMPLOYEE_NUMBER 1 OPERATING_UNIT 9 PAYMENT_AMOUNT 2 PAYMENT_BATCH 7 PAY_ELEMENT 3 PAY_PERIOD 6 PLAN_ELEMENT Y 10 REASON 8 RECOVERABLE 4 RESOURCE_NAME Upload Mode C,U S Site A0E96846DE284C2A868850F6C3BE879B 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 OPERATING_UNIT LOV varchar2 HR Operating Unit for Upload F263DDA3FBF60F8FE0530100007F75CD select hou.organization_id id, hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.operating_unit is null or hou.name=:$flex$.operating_unit) 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.name :$flex$.operating_unit Y Y 6 PAYMENT_BATCH LOV custom varchar2 select cpa.name value, cpsa.period_name description from cn_payruns_all cpa, cn_period_statuses_all cpsa, hr_all_organization_units_vl haouv where cpa.status='UNPAID' and cpa.pay_period_id=cpsa.period_id and cpa.org_id=cpsa.org_id and cpa.org_id=haouv.organization_id and haouv.name=:$column$.operating_unit order by cpa.pay_date desc, cpa.name :$flex$.payment_batch Payment batches that are not paid yet. Y Y 7 PAY_PERIOD Char varchar2 select cpsa.period_name from cn_payruns_all cpa, cn_period_statuses_all cpsa, hr_all_organization_units_vl haouv where cpa.name=:$column$.payment_batch and cpa.org_id=haouv.organization_id and haouv.name=:$column$.operating_unit and cpa.pay_period_id=cpsa.period_id and cpa.org_id=cpsa.org_id Y 8 RESOURCE_NAME LOV custom varchar2 select distinct jrrev.resource_name value, jrs.salesrep_number description from cn_payruns_all cpa, hr_all_organization_units_vl haouv, cn_payment_worksheets_all cpwa, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where cpa.name=:$column$.payment_batch and cpa.org_id=haouv.organization_id and haouv.name=:$column$.operating_unit and cpa.payrun_id=cpwa.payrun_id and cpwa.quota_id is null and cpwa.salesrep_id=jrs.salesrep_id and cpwa.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id order by 1 Resources with a paysheet in the payment batch. Y Y 9 EMPLOYEE_NUMBER Char varchar2 select distinct jrs.salesrep_number from cn_payruns_all cpa, hr_all_organization_units_vl haouv, cn_payment_worksheets_all cpwa, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where cpa.name=:$column$.payment_batch and cpa.org_id=haouv.organization_id and haouv.name=:$column$.operating_unit and cpa.payrun_id=cpwa.payrun_id and cpwa.quota_id is null and cpwa.salesrep_id=jrs.salesrep_id and cpwa.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id and jrrev.resource_name=:$column$.resource_name Identifies the resource when the payment batch has paysheets of more than one resource with the same name. Y 10 PLAN_ELEMENT LOV custom varchar2 select distinct cqa.name value, cqa.description from cn_payruns_all cpa, hr_all_organization_units_vl haouv, cn_srp_period_quotas_all cspqa, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev, cn_quotas_all cqa where cpa.name=:$column$.payment_batch and cpa.org_id=haouv.organization_id and haouv.name=:$column$.operating_unit and cpa.pay_period_id=cspqa.period_id and cpa.org_id=cspqa.org_id and cspqa.salesrep_id=jrs.salesrep_id and cspqa.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id and jrrev.resource_name=:$column$.resource_name and (:$column$.employee_number is null or jrs.salesrep_number=:$column$.employee_number) and cspqa.quota_id=cqa.quota_id and cqa.credit_type_id=-1000 and cqa.incentive_type_code=decode(cpa.incentive_type_code,'ALL',cqa.incentive_type_code,cpa.incentive_type_code) and cqa.quota_id not in ( select cspaa.quota_id from cn_srp_payee_assigns_all cspaa, cn_period_statuses_all cpsa where (cspaa.salesrep_id=cspqa.salesrep_id or cspaa.payee_id=cspqa.salesrep_id) and cpsa.org_id=cpa.org_id and cpsa.period_id=cpa.pay_period_id and cpsa.end_date>=cspaa.start_date and cpsa.start_date<=nvl(cspaa.end_date,cpsa.end_date) ) order by 1 Plan elements of the resource in the pay period of the payment batch, without plan elements assigned to a payee. Y Y 11 PAY_ELEMENT Char varchar2 select distinct cn_api.get_pay_element_name(cn_api.get_pay_element_id(cqa.quota_id,jrs.salesrep_id,cpa.org_id,cpa.pay_date)) from cn_payruns_all cpa, hr_all_organization_units_vl haouv, cn_payment_worksheets_all cpwa, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev, cn_quotas_all cqa where cpa.name=:$column$.payment_batch and cpa.org_id=haouv.organization_id and haouv.name=:$column$.operating_unit and cpa.payrun_id=cpwa.payrun_id and cpwa.quota_id is null and cpwa.salesrep_id=jrs.salesrep_id and cpwa.org_id=jrs.org_id and jrs.resource_id=jrrev.resource_id and jrrev.resource_name=:$column$.resource_name and (:$column$.employee_number is null or jrs.salesrep_number=:$column$.employee_number) and cqa.name=:$column$.plan_element and cqa.org_id=cpa.org_id Y 12 RECOVERABLE LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual A recoverable manual payment is recovered from the resource's future earnings. 13 PAYMENT_AMOUNT Number number Y 14 REASON Char varchar2 Added as a user note on the paysheet. Existing notes are shown on the Notes tab of the Paysheet Detail page. 15 DELETE_PAYMENT LOV custom varchar2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.payment_transaction_id is not null Deletes a downloaded manual payment. 16 PAYMENT_TRANSACTION_ID Number number Y 17 UPLOAD_ROW Char varchar2 Y U 10 status_ out Char STATUS_CODE_ U 20 message_ out Char MESSAGE_ U 30 p_payment_transaction_id in Number PAYMENT_TRANSACTION_ID U 40 p_payment_transaction_id_out out Number PAYMENT_TRANSACTION_ID U 50 p_operating_unit in Char OPERATING_UNIT value U 60 p_payment_batch in Char PAYMENT_BATCH U 70 p_resource in Char RESOURCE_NAME U 80 p_employee_number in Char Y EMPLOYEE_NUMBER U 90 p_plan_element in Char PLAN_ELEMENT U 100 p_recoverable in Char Y RECOVERABLE U 110 p_payment_amount in Number PAYMENT_AMOUNT U 120 p_reason in Char Y REASON U 130 p_delete in Char Y DELETE_PAYMENT Error SQL Order By upload_row, operating_unit, payment_batch, resource_name, plan_element Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, nvl(haouv.name,xu.operating_unit) operating_unit, nvl(cpa.name,xu.payment_batch) payment_batch, nvl(cpsa.period_name,xu.pay_period) pay_period, nvl(jrrev.resource_name,xu.resource_name) resource_name, nvl(jrs.salesrep_number,xu.employee_number) employee_number, nvl(cqa.name,xu.plan_element) plan_element, nvl(cn_api.get_pay_element_name(cpta.pay_element_type_id),xu.pay_element) pay_element, decode(cpta.payment_transaction_id,null,xu.recoverable,xxen_util.yes(cpta.recoverable_flag)) recoverable, nvl(cpta.payment_amount,xu.payment_amount) payment_amount, xu.reason, xu.delete_payment, xu.payment_transaction_id, xu.upload_row from xxen_cn_manual_payment_5068_u xu, cn_payment_transactions_all cpta, cn_payruns_all cpa, cn_period_statuses_all cpsa, hr_all_organization_units_vl haouv, jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev, cn_quotas_all cqa where xu.status_code_=xxen_upload.status_success and xu.payment_transaction_id=cpta.payment_transaction_id(+) and cpta.payrun_id=cpa.payrun_id(+) and cpa.pay_period_id=cpsa.period_id(+) and cpa.org_id=cpsa.org_id(+) and cpa.org_id=haouv.organization_id(+) and cpta.credited_salesrep_id=jrs.salesrep_id(+) and cpta.org_id=jrs.org_id(+) and jrs.resource_id=jrrev.resource_id(+) and cpta.quota_id=cqa.quota_id(+)