AP Employee Update Program - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Employee Update Program (XML) - Not Supported: Reserved For Future Use
Short Name: APXHRUPD_XML
DB package: AP_APXHRUPD_XMLP_PKG
SELECT   pv1.vendor_name  Supplier_Name
      ,  1  Ord_Col
      , :C_NAME                   Updated_Value
      , pv1.vendor_name         Updated_From
      , DECODE(ppf.middle_names, null,
                          ppf.last_name||', '||ppf.first_name,
                          ppf.last_name||', '||ppf.first_name||' '
                                ||ppf.middle_names)
        Updated_To,
	AP_APXHRUPD_XMLP_PKG.c_duplicate_yes_noformula(:C_NAME, DECODE ( ppf.middle_names , null , ppf.last_name || ', ' || ppf.first_name , ppf.last_name || ', ' || ppf.first_name || ' ' || ppf.middle_names )) C_DUPLICATE_YES_NO
/*From
      , DECODE(ppf.middle_names, null,
                          ppf.last_name||', '||ppf.first_name,
                          ppf.last_name||', '||ppf.first_name||' '
                                ||ppf.middle_names)
        Updated_To*/
FROM   per_all_people_f        ppf
 ,      ap_suppliers                  pv1
 ,      financials_system_parameters fsp
 WHERE  pv1.employee_id  = ppf.person_id
 AND    ppf.business_group_id    = fsp.business_group_id
 AND    trunc(sysdate)
               BETWEEN ppf.effective_start_date
                    AND ppf.effective_end_date
 &C_NAME_UPDATE_DATE
AND    DECODE(ppf.middle_names,
        null, ppf.last_name||', '||ppf.first_name,
              ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
        <> pv1.vendor_name
&C_SUPPLIER_PREDICATE
UNION
SELECT  pv1.vendor_name Supplier_Name
      ,  2 Ord_Col
      , :C_INACTIVE_ON     Updated_Value
      , TO_CHAR(pv1.end_date_active, 'DD/MM/YYYY')
        Updated_From
      ,TO_CHAR(ppos.actual_termination_date, 'DD/MM/YYYY')         Updated_To,
	AP_APXHRUPD_XMLP_PKG.c_duplicate_yes_noformula(:C_INACTIVE_ON, TO_CHAR(ppos.actual_termination_date, 'DD/MM/YYYY') ) C_DUPLICATE_YES_NO
/*From
      ,TO_CHAR(ppos.actual_termination_date, 'DD/MM/YYYY')
        Updated_To*/
FROM   per_all_assignments_f   paf
 ,      per_periods_of_service  ppos
 ,      per_all_people_f        ppf
 ,      ap_suppliers              pv1
 ,      financials_system_parameters fsp
 WHERE   pv1.employee_id     = ppf.person_id
 AND    ppf.person_id         = paf.person_id
 AND    ppf.person_id         = ppos.person_id
 AND    ppf.business_group_id    = fsp.business_group_id
 AND    DECODE(ppos.actual_termination_date,
                                       null, trunc(sysdate),
                                             ppos.actual_termination_date)
                BETWEEN paf.effective_start_date
                    AND paf.effective_end_date
 AND    ppos.date_start = (SELECT max(ppos2.date_start)
                           FROM   per_periods_of_service ppos2
                           WHERE  ppos2.person_id       = ppos.person_id
                           AND    ppos2.date_start      <= trunc(sysdate))
 AND    trunc(sysdate)
               BETWEEN ppf.effective_start_date
                    AND ppf.effective_end_date
 AND    paf.assignment_type = 'E'
 &C_INACTIVE_UPDATE_DATE
 AND    nvl(ppos.actual_termination_date,trunc(sysdate)) <>
                nvl(pv1.end_date_active,trunc(sysdate))
&C_SUPPLIER_PREDICATE
ORDER BY 1, 2
Ask a question
Parameter Name SQL text Validation
Ledger