Description:
A capital project, defined as any project which requires capital flows for completion, usually refers to a project which requires large sums of capital.
Purpose of this report is to display Project billable hours for Internal
=================================================
/* Formatted on 2018/07/30 15:40 (Formatter Plus v4.8.8) */
SELECT gl_period, expenditure_org, project_number, project_name,
expenditure_type, employee_number, employee_name,
SUM (quantity) quantity, ACCOUNT, account_desc, SUM (amount) amount,
TO_CHAR (acct_period_date, ‘DD-MON-YYYY’) expenditur_item_date
FROM (SELECT v36738276.NAME AS expenditure_bu,
v515220161.creation_date5 AS acct_period_date,
v103611946.NAME AS expenditure_org,
v55152034.full_name AS employee_name,
v55152034.person_number AS employee_number,
v193542730.expenditure_type_name AS expenditure_type,
v144270398.fiscal_period_name AS gl_period,
v451654944.dep0_pk1_value AS ACCOUNT,
v451654944.dep0_description AS account_desc,
v225332622.NAME AS project_name,
v225332622.segment2 AS project_number,
— v103611946.unit_eo_organization_id AS c280602837,
–v36738276.organization_id1 AS c429243303,
–v55152034.person_id AS c111582988,
–v193542730.expenditure_type_id AS c127698147,
v515220161.quantity AS quantity, –c348140685,
v515220161.denom_burdened_cost AS amount
–v144270398.fiscal_quarter_number AS c395144820,
–v144270398.fiscal_year_number AS c239568097,
–v144270398.fiscal_period_number AS c266672090,
–v17715999.ae_header_id AS c405246974,
–v17715999.ref_ae_header_id AS c362521208,
–v17715999.temp_line_num AS c263126436
FROM (SELECT projectcostdistributionpeo.denom_burdened_cost,
projectcostdistributionpeo.expenditure_item_id,
projectcostdistributionpeo.line_num,
projectcostdistributionpeo.org_id,
projectcostdistributionpeo.project_id,
projectcostdistributionpeo.quantity,
(10036) AS source_application_id,
expenditureitempeo.expenditure_item_id
AS expenditure_item_id1,
expenditureitempeo.expenditure_organization_id,
expenditureitempeo.expenditure_type_id,
expenditureitempeo.incurred_by_person_id,
–expenditureitempeo.creation_date AS creation_date5
expenditureitempeo.expenditure_item_date AS creation_date5
FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
pjc_exp_items_all expenditureitempeo,
pjf_exp_types_vl pet
WHERE ( projectcostdistributionpeo.expenditure_item_id =
expenditureitempeo.expenditure_item_id
AND expenditureitempeo.expenditure_type_id =
pet.expenditure_type_id
AND pet.expenditure_type_name IN
(‘Professional Labor’,
‘Contract Professional Labor’)
)) v515220161,
(SELECT expendituretype.expenditure_type_id,
expendituretype.expenditure_type_name
FROM pjf_exp_types_vl expendituretype) v193542730,
(SELECT personnamepeo.person_name_id,
personnamepeo.effective_start_date,
personnamepeo.effective_end_date,
personnamepeo.full_name, personnamepeo.person_id,
persondetailspeo.person_number,
persondetailspeo.person_id AS person_id10,
persondetailspeo.effective_start_date
AS effective_start_date11,
persondetailspeo.effective_end_date
AS effective_end_date12,
assignmentpeo.assignment_id AS assignment_id26,
assignmentpeo.effective_start_date
AS effective_start_date27,
assignmentpeo.effective_end_date
AS effective_end_date28,
assignmentpeo.effective_latest_change,
assignmentpeo.effective_sequence,
assignmentpeo.primary_flag AS primary_flag30,
assignmentpeo.assignment_status_type
AS assignment_status_type31,
assignmentsupervisorpeo.manager_type
AS manager_type32
FROM per_person_names_f_v personnamepeo,
per_all_people_f persondetailspeo,
per_all_assignments_m assignmentpeo,
per_assignment_supervisors_f assignmentsupervisorpeo,
per_persons personpeo
WHERE ( personnamepeo.person_id =
persondetailspeo.person_id
AND persondetailspeo.person_id =
assignmentpeo.person_id
AND assignmentpeo.assignment_id = assignmentsupervisorpeo.assignment_id(+)
AND (‘LINE_MANAGER’) = assignmentsupervisorpeo.manager_type(+)
AND personnamepeo.person_id = personpeo.person_id
AND (SYSDATE
BETWEEN personnamepeo.effective_start_date
AND personnamepeo.effective_end_date
)
AND (SYSDATE
BETWEEN persondetailspeo.effective_start_date
AND persondetailspeo.effective_end_date
)
AND (SYSDATE
BETWEEN assignmentpeo.effective_start_date
AND assignmentpeo.effective_end_date
)
AND (SYSDATE BETWEEN assignmentsupervisorpeo.effective_start_date(+) AND assignmentsupervisorpeo.effective_end_date(+))
)
AND ( ( ((assignmentpeo.effective_latest_change =
‘Y’
)
)
AND (( ((assignmentpeo.assignment_type =
‘E’
)
)
OR ((assignmentpeo.assignment_type =
‘C’
)
)
OR ((assignmentpeo.assignment_type =
‘N’
)
)
OR ((assignmentpeo.assignment_type =
‘P’
)
)
)
)
AND (( ((assignmentsupervisorpeo.manager_type =
‘LINE_MANAGER’
)
)
OR ((assignmentsupervisorpeo.manager_type IS NULL
)
)
)
)
)
AND (((1 = 1)))
)) v55152034,
(SELECT orgunitclassificationpeo.org_unit_classification_id,
orgunitclassificationpeo.effective_start_date,
orgunitclassificationpeo.effective_end_date,
organizationunitpeo.organization_id
AS unit_eo_organization_id,
organizationunitpeo.effective_start_date
AS unit_eo_effective_start_date,
organizationunitpeo.effective_end_date
AS unit_eo_effective_end_date,
organizationunittranslationpeo.organization_id
AS unit_tleo_organization_id,
organizationunittranslationpeo.effective_start_date
AS unit_tleo_effective_start_date,
organizationunittranslationpeo.effective_end_date
AS unit_tleo_effective_end_date,
organizationunittranslationpeo.LANGUAGE,
organizationunittranslationpeo.NAME
FROM hr_all_organization_units_f organizationunitpeo,
hr_org_unit_classifications_f orgunitclassificationpeo,
hr_organization_units_f_tl organizationunittranslationpeo
WHERE ( organizationunitpeo.organization_id = orgunitclassificationpeo.organization_id(+)
AND organizationunitpeo.organization_id = organizationunittranslationpeo.organization_id(+)
AND organizationunitpeo.effective_start_date = organizationunittranslationpeo.effective_start_date(+)
AND organizationunitpeo.effective_end_date = organizationunittranslationpeo.effective_end_date(+)
AND (USERENV (‘LANG’)) = organizationunittranslationpeo.LANGUAGE(+)
AND (DATE ‘2018-07-25’
BETWEEN organizationunitpeo.effective_start_date
AND organizationunitpeo.effective_end_date
)
AND (DATE ‘2018-07-25’ BETWEEN orgunitclassificationpeo.effective_start_date(+) AND orgunitclassificationpeo.effective_end_date(+))
AND (DATE ‘2018-07-25’ BETWEEN organizationunittranslationpeo.effective_start_date(+) AND organizationunittranslationpeo.effective_end_date(+))
)
AND (((orgunitclassificationpeo.classification_code =
‘PA_EXPENDITURE_ORG’
)
)
)) v103611946,
(SELECT organizationunit.organization_id
AS organization_id1,
organizationunit.effective_start_date
AS effective_start_date651,
organizationunit.effective_end_date
AS effective_end_date8,
organizationinformation.org_information_id,
organizationunittranslation.NAME,
organizationunittranslation.organization_id,
organizationunittranslation.effective_start_date,
organizationunittranslation.effective_end_date,
organizationunittranslation.LANGUAGE
FROM hr_all_organization_units_f organizationunit,
hr_organization_information_f organizationinformation,
hr_organization_units_f_tl organizationunittranslation
WHERE organizationunit.organization_id =
organizationinformation.organization_id
AND (‘FUN_BUSINESS_UNIT’) =
organizationinformation.org_information_context
AND organizationunit.organization_id =
organizationunittranslation.organization_id
AND organizationunit.effective_start_date =
organizationunittranslation.effective_start_date
AND organizationunit.effective_end_date =
organizationunittranslation.effective_end_date
AND (USERENV (‘LANG’)) =
organizationunittranslation.LANGUAGE
AND (SYSDATE
BETWEEN organizationunit.effective_start_date
AND organizationunit.effective_end_date
)
AND (SYSDATE
BETWEEN organizationinformation.effective_start_date
AND organizationinformation.effective_end_date
)
AND (SYSDATE
BETWEEN organizationunittranslation.effective_start_date
AND organizationunittranslation.effective_end_date
)) v36738276,
(SELECT projectbasepeo.project_id,
projectbasepeo.segment1 AS segment2,
projecttranslationpeo.LANGUAGE,
projecttranslationpeo.NAME,
projecttranslationpeo.project_id AS project_id3
FROM pjf_projects_all_b projectbasepeo,
pjf_projects_all_tl projecttranslationpeo
WHERE projectbasepeo.project_id =
projecttranslationpeo.project_id
AND (USERENV (‘LANG’)) = projecttranslationpeo.LANGUAGE) v225332622,
(SELECT xladistlink.ae_header_id,
xladistlink.application_id AS xd_application_id,
xladistlink.ref_ae_header_id,
xladistlink.source_distribution_id_num_1,
xladistlink.source_distribution_id_num_2,
xladistlink.temp_line_num,
xlalines.accounting_date,
xlalines.code_combination_id, xlalines.ledger_id,
(‘N’) AS adjustment_period_flag,
ledgers.chart_of_accounts_id,xladistlink.unrounded_accounted_cr,xladistlink.unrounded_accounted_dr
FROM xla_distribution_links xladistlink,
xla_ae_lines xlalines,
gl_ledgers ledgers
WHERE ( xladistlink.ae_header_id =
xlalines.ae_header_id
AND xladistlink.ae_line_num = xlalines.ae_line_num
AND xladistlink.application_id =
xlalines.application_id
AND xlalines.ledger_id = ledgers.ledger_id
)
AND (((xlalines.code_combination_id <> -1)))) v17715999,
(SELECT fiscalday.adjustment_period_flag,
fiscalday.fiscal_period_name,
fiscalday.fiscal_period_number,
fiscalday.fiscal_period_set_name,
fiscalday.fiscal_period_type,
fiscalday.fiscal_quarter_number,
fiscalday.fiscal_year_number,
fiscalday.report_date, ledger.ledger_id
FROM gl_fiscal_day_v fiscalday, gl_ledgers ledger
WHERE fiscalday.fiscal_period_set_name =
ledger.period_set_name
AND fiscalday.fiscal_period_type =
ledger.accounted_period_type) v144270398,
(SELECT biflexfieldeo.code_combination_id AS s_g_0,
biflexfieldeo.chart_of_accounts_id AS s_g_1,
(DECODE (biflexfieldeo.chart_of_accounts_id,
41, biflexfieldeo.segment2,
NULL
)
) AS gl_account_,
(DECODE (biflexfieldeo.chart_of_accounts_id,
41, ‘Account THG’,
NULL
)
) AS gl_account_c,
biflexfieldeo.segment1, biflexfieldeo.segment2,
biflexfieldeo.segment3, biflexfieldeo.segment4,
biflexfieldeo.segment5, biflexfieldeo.segment6,
biflexfieldeo.segment7
FROM gl_code_combinations biflexfieldeo) v208318186,
(SELECT cft.enterprise_id, cft.tree_structure_code,
cft.tree_code, cft.tree_version_id,
cft.cf_tree_node_id, cft.dep0_pk1_value,
cft.dep0_pk2_value,
dsdep0_1.value_id AS dep0_value_id,
dsdep0_1.description AS dep0_description
FROM gl_seg_val_hier_cf cft,
fnd_vs_typed_values_vl dsdep0_1
WHERE ( cft.dep0_pk1_value = dsdep0_1.VALUE(+)
AND cft.dep0_pk2_value = dsdep0_1.value_set_code(+)
)
AND ( ((1 = 1))
AND (cft.tree_code IN (
SELECT a.tree_code
FROM fnd_tree_data_source_params a,
fnd_ts_data_source_params b
WHERE a.tree_structure_code =
‘GL_ACCT_FLEX’
AND b.tree_structure_code =
‘GL_ACCT_FLEX’
AND a.parameter_id = b.parameter_id
AND b.parameter_name =
‘Bind_ValueSetCode’
AND a.parameter_value IN
(‘Account THG’))
)
)) v451654944
WHERE v515220161.expenditure_type_id =
v193542730.expenditure_type_id
AND v515220161.incurred_by_person_id = v55152034.person_id(+)
AND v515220161.expenditure_organization_id =
v103611946.unit_eo_organization_id
AND v515220161.org_id = v36738276.organization_id1
AND v515220161.project_id = v225332622.project_id
AND v515220161.source_application_id =
v17715999.xd_application_id
AND v515220161.expenditure_item_id =
v17715999.source_distribution_id_num_1
AND v515220161.line_num =
v17715999.source_distribution_id_num_2
AND v17715999.accounting_date = v144270398.report_date(+)
AND v17715999.adjustment_period_flag = v144270398.adjustment_period_flag(+)
AND v17715999.ledger_id = v144270398.ledger_id(+)
AND v17715999.code_combination_id = v208318186.s_g_0(+)
AND v17715999.chart_of_accounts_id = v208318186.s_g_1(+)
AND v208318186.gl_account_ = v451654944.dep0_pk1_value(+)
AND v208318186.gl_account_c = v451654944.dep0_pk2_value(+)
AND ( ((v225332622.LANGUAGE = ‘US’))
AND ( ((v55152034.primary_flag30 = ‘Y’))
OR ((v55152034.primary_flag30 IS NULL))
)
AND ( ((v55152034.assignment_status_type31 = ‘ACTIVE’)
)
OR ((v55152034.primary_flag30 IS NULL))
)
AND ((v225332622.segment2 LIKE ‘INT%’))
AND v17715999.unrounded_accounted_cr IS NOT NULL
)
GROUP BY v36738276.NAME,
v515220161.creation_date5,
v103611946.NAME,
v55152034.full_name,
v55152034.person_number,
v193542730.expenditure_type_name,
v144270398.fiscal_period_name,
v451654944.dep0_pk1_value,
v451654944.dep0_description,
v225332622.NAME,
v225332622.segment2,
v515220161.quantity,
v515220161.denom_burdened_cost)
WHERE quantity > 0
GROUP BY gl_period,
expenditure_org,
project_number,
project_name,
expenditure_type,
employee_number,
employee_name,
ACCOUNT,
account_desc,
acct_period_date
ORDER BY project_number, employee_number
Summary:
This Post described the script Create Project party in oracle Capital project hours – includes PTO in Oracle EBS.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
