PJC_LABOR_SCHEDULE_COSTS

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
LD_INTERFACE_ID PAY_AMOUNT PAY_PERIOD_START_DATE PAY_PERIOD_END_DATE BU_ID BATCH_NAME PERSON_ID PERSON_NAME FIRST_NAME LAST_NAME PERSON_NUMBER EMAIL_ADDRESS ASSIGNMENT_ID ASSIGNMENT_NAME ASSIGNMENT_NUMBER CURRENCY_CODE PAY_ELEMENT PAY_ELEMENT_ID ORIGINAL_TRANSACTION_REFERENCE RUN_STATUS_CODE RUN_STATUS RUN_STATUS_SUMMARY DOCUMENT_CODE DOCUMENT_ID TRANSACTION_SOURCE_ID TRANSACTION_SOURCE USER_TRANSACTION_SOURCE NET_ZERO_ADJUSTMENT_FLAG PAYROLL_NAME PAYROLL_ID COST_ID COST_ALLOCATION_KEYFLEX_ID

Name

LD_INTERFACE_ID

PAY_AMOUNT

PAY_PERIOD_START_DATE

PAY_PERIOD_END_DATE

BU_ID

BATCH_NAME

PERSON_ID

PERSON_NAME

FIRST_NAME

LAST_NAME

PERSON_NUMBER

EMAIL_ADDRESS

ASSIGNMENT_ID

ASSIGNMENT_NAME

ASSIGNMENT_NUMBER

CURRENCY_CODE

PAY_ELEMENT

PAY_ELEMENT_ID

ORIGINAL_TRANSACTION_REFERENCE

RUN_STATUS_CODE

RUN_STATUS

RUN_STATUS_SUMMARY

DOCUMENT_CODE

DOCUMENT_ID

TRANSACTION_SOURCE_ID

TRANSACTION_SOURCE

USER_TRANSACTION_SOURCE

NET_ZERO_ADJUSTMENT_FLAG

PAYROLL_NAME

PAYROLL_ID

COST_ID

COST_ALLOCATION_KEYFLEX_ID

Query

SQL_Statement
SELECT ProjectAllocTxnPEO.LD_INTERFACE_ID, SUM(ProjectAllocTxnPEO.TOTAL_POOL_AMOUNT) AS PAY_AMOUNT, ProjectAllocTxnPEO.LD_PERIOD_START_DATE AS PAY_PERIOD_START_DATE, ProjectAllocTxnPEO.LD_PERIOD_END_DATE AS PAY_PERIOD_END_DATE, ProjectAllocTxnPEO.ORG_ID AS BU_ID, ProjectAllocTxnPEO.LD_BATCH_NAME AS BATCH_NAME, ProjectAllocTxnPEO.LD_PERSON_ID AS PERSON_ID, PersonNameDPEO.DISPLAY_NAME AS PERSON_NAME, PersonNameDPEO.FIRST_NAME AS FIRST_NAME, PersonNameDPEO.LAST_NAME AS LAST_NAME, PersonDPEO.PERSON_NUMBER AS PERSON_NUMBER, EmailAddressPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS, ProjectAllocTxnPEO.LD_ASSIGNMENT_ID AS ASSIGNMENT_ID, AssignmentDPEO.ASSIGNMENT_NAME AS ASSIGNMENT_NAME, AssignmentDPEO.ASSIGNMENT_NUMBER AS ASSIGNMENT_NUMBER, ProjectAllocTxnPEO.DENOM_CURRENCY_CODE AS CURRENCY_CODE, ProjectAllocTxnPEO.LD_ELEMENT_TYPE AS PAY_ELEMENT, ProjectAllocTxnPEO.LD_ELEMENT_TYPE_ID AS PAY_ELEMENT_ID, ProjectAllocTxnPEO.LD_ORIG_TRANSACTION_REFERENCE AS ORIGINAL_TRANSACTION_REFERENCE, ProjectAllocTxnPEO.RUN_STATUS AS RUN_STATUS_CODE, RunStatusLookup.MEANING AS RUN_STATUS, CASE WHEN ProjectAllocTxnPEO.RUN_STATUS = 'RS' THEN RunStatusSummarySuccess.MEANING ELSE RunStatusSummaryError.MEANING END AS RUN_STATUS_SUMMARY, nvl(documentPEO.document_code, 'LABOR_DIST') as document_code, nvl(ProjectAllocTxnPEO.document_id, labor_dist_doc.document_id) as document_id, nvl(documentPEO.transaction_source_id,labor_dist_doc.transaction_source_id) as transaction_source_id, DECODE(documentPEO.document_code, null,'PROJECTS',documentPEO.transaction_source) as transaction_source, nvl(documentPEO.user_transaction_source, labor_dist_doc.user_transaction_source) as user_transaction_source, ProjectAllocTxnPEO.net_zero_adjustment_flag, ProjectAllocTxnPEO.payroll_name as PAYROLL_NAME, ProjectAllocTxnPEO.payroll_id as PAYROLL_ID, ProjectAllocTxnPEO.cost_id as COST_ID, ProjectAllocTxnPEO.COST_ALLOCATION_KEYFLEX_ID as COST_ALLOCATION_KEYFLEX_ID FROM PJC_ALLOC_RUNS_ALL ProjectAllocTxnPEO, PER_ALL_PEOPLE_F PersonDPEO, PER_PERSON_NAMES_F_V PersonNameDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO, PER_ALL_ASSIGNMENTS_F AssignmentDPEO, FND_LOOKUPS RunStatusLookup, FND_LOOKUPS RunStatusSummaryError, FND_LOOKUPS RunStatusSummarySuccess, (SELECT a.document_id, a.document_code, a.transaction_source_id, b.transaction_source, b.user_transaction_source FROM pjf_txn_document_b a, pjf_txn_sources_VL b WHERE a.transaction_source_id = b.transaction_source_id AND b.transaction_source in ('PROJECTS', 'OPAY') AND document_code in ('LABOR_DIST', 'PAYROLL_COSTS')) documentPEO, (SELECT a.document_id, a.document_code, a.transaction_source_id, b.transaction_source, b.user_transaction_source FROM pjf_txn_document_b a, pjf_txn_sources_VL b WHERE a.transaction_source_id = b.transaction_source_id AND b.transaction_source = 'PROJECTS' AND document_code = 'LABOR_DIST' and rownum = 1) labor_dist_doc WHERE (ProjectAllocTxnPEO.RUN_STATUS = 'RS' OR ProjectAllocTxnPEO.RUN_STATUS = 'RF') AND ProjectAllocTxnPEO.LD_PERSON_ID = PersonDPEO.PERSON_ID AND GREATEST(SYSDATE, ProjectAllocTxnPEO.LD_PERIOD_START_DATE) BETWEEN TRUNC(PersonDPEO.EFFECTIVE_START_DATE) AND TRUNC(PersonDPEO.EFFECTIVE_END_DATE) AND ProjectAllocTxnPEO.LD_PERSON_ID = PersonNameDPEO.PERSON_ID AND GREATEST(SYSDATE, ProjectAllocTxnPEO.LD_PERIOD_START_DATE) BETWEEN TRUNC(PersonNameDPEO.EFFECTIVE_START_DATE) AND TRUNC(PersonNameDPEO.EFFECTIVE_END_DATE) AND ProjectAllocTxnPEO.LD_PERSON_ID = EmailAddressPEO.PERSON_ID(+) AND PersonDPEO.PRIMARY_EMAIL_ID = EmailAddressPEO.EMAIL_ADDRESS_ID(+) AND ProjectAllocTxnPEO.LD_ASSIGNMENT_ID = AssignmentDPEO.ASSIGNMENT_ID AND GREATEST(SYSDATE, ProjectAllocTxnPEO.LD_PERIOD_START_DATE) BETWEEN TRUNC(AssignmentDPEO.EFFECTIVE_START_DATE) AND TRUNC(AssignmentDPEO.EFFECTIVE_END_DATE) AND ProjectAllocTxnPEO.RUN_STATUS = RunStatusLookup.LOOKUP_CODE AND RunStatusLookup.LOOKUP_TYPE = 'PJC_ALLOC_RUN_STATUS' AND RunStatusSummaryError.LOOKUP_TYPE = 'ORA_PJC_LD_COST_RUN_STATUS' AND RunStatusSummarySuccess.LOOKUP_TYPE = 'ORA_PJC_LD_COST_RUN_STATUS' AND RunStatusSummaryError.LOOKUP_CODE = 'LD_RUN_STATUS_ERROR' AND RunStatusSummarySuccess.LOOKUP_CODE = 'LD_RUN_STATUS_SUCCESS' AND ProjectAllocTxnPEO.document_id = documentPEO.document_id(+) GROUP BY ProjectAllocTxnPEO.ORG_ID, ProjectAllocTxnPEO.LD_INTERFACE_ID, ProjectAllocTxnPEO.LD_PERIOD_START_DATE, ProjectAllocTxnPEO.LD_PERIOD_END_DATE, ProjectAllocTxnPEO.LD_BATCH_NAME, ProjectAllocTxnPEO.LD_PERSON_ID, ProjectAllocTxnPEO.LD_ELEMENT_TYPE, ProjectAllocTxnPEO.LD_ELEMENT_TYPE_ID, ProjectAllocTxnPEO.LD_ASSIGNMENT_ID, ProjectAllocTxnPEO.LD_ORIG_TRANSACTION_REFERENCE, PersonNameDPEO.DISPLAY_NAME, PersonNameDPEO.FIRST_NAME, PersonNameDPEO.LAST_NAME, PersonDPEO.PERSON_NUMBER, EmailAddressPEO.EMAIL_ADDRESS, AssignmentDPEO.ASSIGNMENT_NAME, AssignmentDPEO.ASSIGNMENT_NUMBER, ProjectAllocTxnPEO.DENOM_CURRENCY_CODE, ProjectAllocTxnPEO.RUN_STATUS, RunStatusLookup.MEANING, nvl(documentPEO.document_code, 'LABOR_DIST'), ProjectAllocTxnPEO.payroll_name, ProjectAllocTxnPEO.payroll_id, ProjectAllocTxnPEO.cost_id, ProjectAllocTxnPEO.COST_ALLOCATION_KEYFLEX_ID, nvl(ProjectAllocTxnPEO.document_id, labor_dist_doc.document_id) , nvl(documentPEO.transaction_source_id,labor_dist_doc.transaction_source_id), DECODE(documentPEO.document_code,null,'PROJECTS',documentPEO.transaction_source), nvl(documentPEO.user_transaction_source, labor_dist_doc.user_transaction_source), ProjectAllocTxnPEO.net_zero_adjustment_flag, CASE WHEN ProjectAllocTxnPEO.RUN_STATUS = 'RS' THEN RunStatusSummarySuccess.MEANING ELSE RunStatusSummaryError.MEANING END

SQL_Statement

SELECT ProjectAllocTxnPEO.LD_INTERFACE_ID,

SUM(ProjectAllocTxnPEO.TOTAL_POOL_AMOUNT) AS PAY_AMOUNT,

ProjectAllocTxnPEO.LD_PERIOD_START_DATE AS PAY_PERIOD_START_DATE,

ProjectAllocTxnPEO.LD_PERIOD_END_DATE AS PAY_PERIOD_END_DATE,

ProjectAllocTxnPEO.ORG_ID AS BU_ID,

ProjectAllocTxnPEO.LD_BATCH_NAME AS BATCH_NAME,

ProjectAllocTxnPEO.LD_PERSON_ID AS PERSON_ID,

PersonNameDPEO.DISPLAY_NAME AS PERSON_NAME,

PersonNameDPEO.FIRST_NAME AS FIRST_NAME,

PersonNameDPEO.LAST_NAME AS LAST_NAME,

PersonDPEO.PERSON_NUMBER AS PERSON_NUMBER,

EmailAddressPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS,

ProjectAllocTxnPEO.LD_ASSIGNMENT_ID AS ASSIGNMENT_ID,

AssignmentDPEO.ASSIGNMENT_NAME AS ASSIGNMENT_NAME,

AssignmentDPEO.ASSIGNMENT_NUMBER AS ASSIGNMENT_NUMBER,

ProjectAllocTxnPEO.DENOM_CURRENCY_CODE AS CURRENCY_CODE,

ProjectAllocTxnPEO.LD_ELEMENT_TYPE AS PAY_ELEMENT,

ProjectAllocTxnPEO.LD_ELEMENT_TYPE_ID AS PAY_ELEMENT_ID,

ProjectAllocTxnPEO.LD_ORIG_TRANSACTION_REFERENCE AS ORIGINAL_TRANSACTION_REFERENCE,

ProjectAllocTxnPEO.RUN_STATUS AS RUN_STATUS_CODE,

RunStatusLookup.MEANING AS RUN_STATUS,

CASE WHEN ProjectAllocTxnPEO.RUN_STATUS = 'RS' THEN RunStatusSummarySuccess.MEANING ELSE RunStatusSummaryError.MEANING END AS RUN_STATUS_SUMMARY,

nvl(documentPEO.document_code, 'LABOR_DIST') as document_code,

nvl(ProjectAllocTxnPEO.document_id, labor_dist_doc.document_id) as document_id,

nvl(documentPEO.transaction_source_id,labor_dist_doc.transaction_source_id) as transaction_source_id,

DECODE(documentPEO.document_code, null,'PROJECTS',documentPEO.transaction_source) as transaction_source,

nvl(documentPEO.user_transaction_source, labor_dist_doc.user_transaction_source) as user_transaction_source,

ProjectAllocTxnPEO.net_zero_adjustment_flag,

ProjectAllocTxnPEO.payroll_name as PAYROLL_NAME,

ProjectAllocTxnPEO.payroll_id as PAYROLL_ID,

ProjectAllocTxnPEO.cost_id as COST_ID,

ProjectAllocTxnPEO.COST_ALLOCATION_KEYFLEX_ID as COST_ALLOCATION_KEYFLEX_ID

FROM PJC_ALLOC_RUNS_ALL ProjectAllocTxnPEO,

PER_ALL_PEOPLE_F PersonDPEO,

PER_PERSON_NAMES_F_V PersonNameDPEO,

PER_EMAIL_ADDRESSES EmailAddressPEO,

PER_ALL_ASSIGNMENTS_F AssignmentDPEO,

FND_LOOKUPS RunStatusLookup,

FND_LOOKUPS RunStatusSummaryError,

FND_LOOKUPS RunStatusSummarySuccess,

(SELECT a.document_id, a.document_code, a.transaction_source_id, b.transaction_source, b.user_transaction_source

FROM pjf_txn_document_b a, pjf_txn_sources_VL b

WHERE a.transaction_source_id = b.transaction_source_id AND b.transaction_source in ('PROJECTS', 'OPAY')

AND document_code in ('LABOR_DIST', 'PAYROLL_COSTS')) documentPEO,

(SELECT a.document_id, a.document_code, a.transaction_source_id, b.transaction_source, b.user_transaction_source

FROM pjf_txn_document_b a, pjf_txn_sources_VL b

WHERE a.transaction_source_id = b.transaction_source_id AND b.transaction_source = 'PROJECTS'

AND document_code = 'LABOR_DIST' and rownum = 1) labor_dist_doc

WHERE (ProjectAllocTxnPEO.RUN_STATUS = 'RS' OR ProjectAllocTxnPEO.RUN_STATUS = 'RF')

AND ProjectAllocTxnPEO.LD_PERSON_ID = PersonDPEO.PERSON_ID

AND GREATEST(SYSDATE, ProjectAllocTxnPEO.LD_PERIOD_START_DATE) BETWEEN TRUNC(PersonDPEO.EFFECTIVE_START_DATE) AND TRUNC(PersonDPEO.EFFECTIVE_END_DATE)

AND ProjectAllocTxnPEO.LD_PERSON_ID = PersonNameDPEO.PERSON_ID

AND GREATEST(SYSDATE, ProjectAllocTxnPEO.LD_PERIOD_START_DATE) BETWEEN TRUNC(PersonNameDPEO.EFFECTIVE_START_DATE) AND TRUNC(PersonNameDPEO.EFFECTIVE_END_DATE)

AND ProjectAllocTxnPEO.LD_PERSON_ID = EmailAddressPEO.PERSON_ID(+)

AND PersonDPEO.PRIMARY_EMAIL_ID = EmailAddressPEO.EMAIL_ADDRESS_ID(+)

AND ProjectAllocTxnPEO.LD_ASSIGNMENT_ID = AssignmentDPEO.ASSIGNMENT_ID

AND GREATEST(SYSDATE, ProjectAllocTxnPEO.LD_PERIOD_START_DATE) BETWEEN TRUNC(AssignmentDPEO.EFFECTIVE_START_DATE) AND TRUNC(AssignmentDPEO.EFFECTIVE_END_DATE)

AND ProjectAllocTxnPEO.RUN_STATUS = RunStatusLookup.LOOKUP_CODE

AND RunStatusLookup.LOOKUP_TYPE = 'PJC_ALLOC_RUN_STATUS'

AND RunStatusSummaryError.LOOKUP_TYPE = 'ORA_PJC_LD_COST_RUN_STATUS'

AND RunStatusSummarySuccess.LOOKUP_TYPE = 'ORA_PJC_LD_COST_RUN_STATUS'

AND RunStatusSummaryError.LOOKUP_CODE = 'LD_RUN_STATUS_ERROR'

AND RunStatusSummarySuccess.LOOKUP_CODE = 'LD_RUN_STATUS_SUCCESS'

AND ProjectAllocTxnPEO.document_id = documentPEO.document_id(+)

GROUP BY ProjectAllocTxnPEO.ORG_ID,

ProjectAllocTxnPEO.LD_INTERFACE_ID,

ProjectAllocTxnPEO.LD_PERIOD_START_DATE,

ProjectAllocTxnPEO.LD_PERIOD_END_DATE,

ProjectAllocTxnPEO.LD_BATCH_NAME,

ProjectAllocTxnPEO.LD_PERSON_ID,

ProjectAllocTxnPEO.LD_ELEMENT_TYPE,

ProjectAllocTxnPEO.LD_ELEMENT_TYPE_ID,

ProjectAllocTxnPEO.LD_ASSIGNMENT_ID,

ProjectAllocTxnPEO.LD_ORIG_TRANSACTION_REFERENCE,

PersonNameDPEO.DISPLAY_NAME,

PersonNameDPEO.FIRST_NAME,

PersonNameDPEO.LAST_NAME,

PersonDPEO.PERSON_NUMBER,

EmailAddressPEO.EMAIL_ADDRESS,

AssignmentDPEO.ASSIGNMENT_NAME,

AssignmentDPEO.ASSIGNMENT_NUMBER,

ProjectAllocTxnPEO.DENOM_CURRENCY_CODE,

ProjectAllocTxnPEO.RUN_STATUS,

RunStatusLookup.MEANING,

nvl(documentPEO.document_code, 'LABOR_DIST'),

ProjectAllocTxnPEO.payroll_name,

ProjectAllocTxnPEO.payroll_id,

ProjectAllocTxnPEO.cost_id,

ProjectAllocTxnPEO.COST_ALLOCATION_KEYFLEX_ID,

nvl(ProjectAllocTxnPEO.document_id, labor_dist_doc.document_id) ,

nvl(documentPEO.transaction_source_id,labor_dist_doc.transaction_source_id),

DECODE(documentPEO.document_code,null,'PROJECTS',documentPEO.transaction_source),

nvl(documentPEO.user_transaction_source, labor_dist_doc.user_transaction_source),

ProjectAllocTxnPEO.net_zero_adjustment_flag,

CASE WHEN ProjectAllocTxnPEO.RUN_STATUS = 'RS' THEN RunStatusSummarySuccess.MEANING ELSE RunStatusSummaryError.MEANING END