PA Payroll Distribution Employee Detail

➜ Back to Library
Description
Categories: BI Publisher
Application: Projects
Source: AUD: Payroll Distribution Employee Detail Report
Short Name: PAPAYREP
DB package:
Run PA Payroll Distribution Employee Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT PPS.DISTRIBUTION_BASIS_CODE,
  PET.ELEMENT_NAME ,
  PPS.COST_TYPE_CODE ,
  PPD.DENOM_RAW_COST ,
  PROJ1.segment1 PAYROLL_PROJECT ,
  PROJ2.segment1 TC_PROJECT_ID ,
  SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
  SUBSTR(hr2.name,1,30) TC_ORGANIZATION ,
  TASK1.task_number PAYROLL_TASK ,
  TASK2.task_number TC_TASK_ID ,
  DECODE (PPD.system_linkage_function,'ST','ST',
                                      'OT','OT',
									  'PJ','Misc',
									  'BTC','Burden' )system_linkage_function ,
  PPD.TARGET_EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID ,
  PPD.EXPENDITURE_ITEM_DATE  ,
 'PA_PAY_' || Decode( PPS.DISTRIBUTION_BASIS_CODE,
'ST_AMT', 'AMT'   ,
'OT_AMT', 'AMT',
'ST_OT_AMT' ,'AMT',
'STHOURS',     decode(pps.timecard_element,'YES','HRY','HRN'),                                                
'OTHOURS',     Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),                                               
'TOTAL_HOURS' ,Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),
'NONE' ,'NONE')
||(CASE WHEN   DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
Decode(PPD.system_linkage_function ,
   'ST','_TC',
   'OT','_TC',
   'PJ','_PJ',
   'BTC','_BTC'
   )
    END)
  ||(CASE WHEN   DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
        '_' || PRIORITY
    END)   MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
  PA_PROJECTS PROJ1,
  PA_PROJECTS PROJ2,
  PA_TASKS TASK1,
  PA_TASKS TASK2,
  PA_PAY_DIST_LINES PPD,
  PA_PAY_AUDIT PPA ,
  PAY_ELEMENT_TYPES_F PET,
  hr_organization_units hr1,
  hr_organization_units hr2
WHERE PPA.INTERFACE_RUN_ID  = :P_INTERFACE_RUN_ID
AND PPA.INT_EXT_INDICATOR   = 'INT'
AND PPS.person_id           = :P_PERSON_ID
AND PPA.interface_run_id    = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID    = PPD.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID       = PPD.PAY_SOURCE_ID
AND PPD.DENOM_CURRENCY_CODE = :CURRENCY
and ppd.pay_element_type_id = pet.element_type_id
and pay_source_project_id   = proj1.project_id(+)
and ppd.project_id          = proj2.project_id(+)
and pay_source_task_id      = task1.task_id(+)
and ppd.task_id             = task2.task_id(+)
and pay_source_organization_id = hr1.organization_id(+)   
AND PPD.ORGANIZATION_ID =  hr2.organization_id(+)
UNION
SELECT PPS.DISTRIBUTION_BASIS_CODE,
  PET.LOOKUP_CODE ELEMENT_NAME ,
  PPS.COST_TYPE_CODE ,
  PPD.DENOM_RAW_COST ,
  PROJ1.segment1 PAYROLL_PROJECT ,
  PROJ2.segment1 TC_PROJECT_ID ,
  SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
  SUBSTR(hr2.name,1,30) TC_ORGANIZATION ,
  TASK1.task_number PAYROLL_TASK ,
  TASK2.task_number TC_TASK_ID ,
 DECODE (PPD.system_linkage_function,'ST','ST',
                                      'OT','OT',
									  'PJ','Misc',
									  'BTC','Burden' )system_linkage_function ,
  PPD.TARGET_EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID ,
    PPD.EXPENDITURE_ITEM_DATE  ,
  'PA_PAY_' || Decode( PPS.DISTRIBUTION_BASIS_CODE,
'ST_AMT', 'AMT'   ,
'OT_AMT', 'AMT',
'ST_OT_AMT' ,'AMT',
'STHOURS',     decode(pps.timecard_element,'YES','HRY','HRN'),                                                
'OTHOURS',     Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),                                               
'TOTAL_HOURS' ,Decode(PPS.TIMECARD_ELEMENT,'YES','HRY','HRN'),
'NONE' ,'NONE')
||(CASE WHEN   DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
Decode(PPD.system_linkage_function ,
   'ST','_TC',
   'OT','_TC',
   'PJ','_PJ',
   'BTC','_BTC'
   )
    END)
  ||(CASE WHEN   DISTRIBUTION_BASIS_CODE <> 'NONE' THEN
        '_' || PRIORITY
    END)   MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
  PA_PROJECTS PROJ1,
  PA_PROJECTS PROJ2,
  PA_TASKS TASK1,
  PA_TASKS TASK2,
  PA_PAY_DIST_LINES PPD,
  PA_PAY_AUDIT PPA ,
  (SELECT LOOKUP_TYPE,
    LOOKUP_CODE,
    MEANING
  FROM pa_lookups
  WHERE lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS'
  ) PET,
  hr_organization_units hr1,
  hr_organization_units hr2
WHERE PPA.INTERFACE_RUN_ID    = :P_INTERFACE_RUN_ID
AND PPS.person_id             = :P_PERSON_ID
AND PPA.INT_EXT_INDICATOR     = 'EXT'
AND PPA.interface_run_id      = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID      = PPD.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID         = PPD.PAY_SOURCE_ID
AND PPD.DENOM_CURRENCY_CODE   = :CURRENCY
AND PPD.PAY_ELEMENT_TYPE_CODE = PET.LOOKUP_CODE
and pay_source_project_id     = proj1.project_id(+)
and ppd.project_id            = proj2.project_id(+)
AND PAY_SOURCE_TASK_ID        = TASK1.task_id(+)
and ppd.task_id               = task2.task_id(+)
and pay_source_organization_id = hr1.organization_id(+)   
AND PPD.ORGANIZATION_ID =  hr2.organization_id(+)
UNION
SELECT PPS.DISTRIBUTION_BASIS_CODE,
  PET.ELEMENT_NAME ,
  PPS.COST_TYPE_CODE ,
  NULL DENOM_RAW_COST,
  PROJ1.segment1 PAYROLL_PROJECT ,
  NULL TC_PROJECT_ID ,
  SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
  NULL TC_ORGANIZATION,
  TASK1.task_number PAYROLL_TASK ,
  NULL TC_TASK_ID ,
  NULL SYSTEM_LINKAGE_FUNCTION,
  NULL EXPENDITURE_ITEM_ID,
  NULL EXPENDITURE_ITEM_DATE  ,
  REJ.REJECTION_CODE MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
  PA_PROJECTS PROJ1,
  PA_TASKS TASK1,
  pa_pay_audit ppa ,
  pa_pay_rejections_all REJ,
  PAY_ELEMENT_TYPES_F PET,
  hr_organization_units hr1
WHERE PPA.INTERFACE_RUN_ID = :P_INTERFACE_RUN_ID
AND PPS.person_id           = :P_PERSON_ID
AND PPA.INT_EXT_INDICATOR  = 'INT'
AND PPA.interface_run_id   = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID   = REJ.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID      = REJ.PAY_SOURCE_ID
AND PPS.PAY_CURRENCY_CODE  = :CURRENCY
AND PPA.INTERFACE_RUN_ID   = REJ.INTERFACE_RUN_ID
and pet.element_type_id    = rej.pay_element_type_id
AND PAY_SOURCE_PROJECT_ID  = PROJ1.project_id(+)
and pay_source_task_id     = task1.task_id(+)
AND PAY_SOURCE_ORGANIZATION_ID  = hr1.organization_id(+)
UNION
SELECT PPS.DISTRIBUTION_BASIS_CODE,
  PET.LOOKUP_CODE ELEMENT_NAME ,
  PPS.COST_TYPE_CODE ,
  NULL DENOM_RAW_COST,
  PROJ1.segment1 PAYROLL_PROJECT ,
  NULL TC_PROJECT_ID ,
  SUBSTR(hr1.name,1,30) PAYROLL_ORGANIZATION ,
  NULL TC_ORGANIZATION,
  TASK1.task_number PAYROLL_TASK ,
  NULL TC_TASK_ID,
  NULL SYSTEM_LINKAGE_FUNCTION,
  NULL EXPENDITURE_ITEM_ID,
NULL EXPENDITURE_ITEM_DATE  ,
  REJ.REJECTION_CODE MSG
FROM PA_PAY_SOURCE_AMOUNTS PPS,
  PA_PROJECTS PROJ1,
  PA_TASKS TASK1,
  PA_PAY_AUDIT PPA ,
  pa_pay_rejections_all REJ,
  (SELECT LOOKUP_TYPE,
    LOOKUP_CODE,
    MEANING
  FROM pa_lookups
  WHERE lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS'
  ) PET,
  hr_organization_units hr1
WHERE PPA.INTERFACE_RUN_ID = :P_INTERFACE_RUN_ID
AND PPS.person_id           = :P_PERSON_ID
AND PPA.INT_EXT_INDICATOR  = 'EXT'
AND PPA.interface_run_id   = pps.interface_run_id
AND PPS.INTERFACE_RUN_ID   = REJ.INTERFACE_RUN_ID
AND PPS.PAY_SOURCE_ID      = REJ.PAY_SOURCE_ID
AND pps.pay_currency_code  = :CURRENCY
AND pet.lookup_code        = rej.pay_element_type_code
and pay_source_project_id  = proj1.project_id(+)
and pay_source_task_id     = task1.task_id(+)
AND PAY_SOURCE_ORGANIZATION_ID   = hr1.organization_id(+)
Parameter Name SQL text Validation
Costed Set Id 
 
 LOV Oracle
Employee 
 
 LOV Oracle
Download
Related Blitz Reports
PO Endeca Template GL Account Analysis ECC Projects, Projects Costing: Transactions, SQL1 COPY OF: GL Account Analysis GL Account Analysis (Distributions) PON Solicitation data template AP Invoices and Lines PA Project Subledger: Detail by Expenditure Type PA Project Subledger: Detail by Project
Blitz Report™

Blitz Report™ provides multiple benefits: