SELECT
COMMIT1.MEANING APPROVED,
COMMIT1.CMT_NUMBER COMMITMENT_NUMBER,
COMMIT1.LINE_TYPE COMMITMENT_TYPE,
EXP_TYPE.DESCRIPTION ,
EXP_TYPE.UOM UOM,
EXP_CAT.EXPENDITURE_CATEGORY_NAME ,
EXP_ORG.NAME EXPENDITURE_ORGANIZATION_NAME,
EXP_TYPE.EXPENDITURE_TYPE_NAME EXPENDITURE_TYPE,
SUP.SUP1 SUPPLIER_NAME,
TASK.SEGMENT1 ,
TASK.ELEMENT_NUMBER TASK_NUMBER,
COMMIT1.PROJFUNC_RAW_COST ,
COMMIT1.PROJFUNC_BRDND_COST ,
COMMIT1.TOT_CMT_QUANTITY QUANTITY,
TO_CHAR(COMMIT1.EXPENDITURE_ITEM_DATE,'MM/DD/YYYY') COMMITMENT_DATE,
NULL RELEASE_NUMBER
FROM
(
SELECT
PCT.COMMITMENT_TXN_ID,
PCT.TASK_ID,
PCT.LINE_TYPE,
PCT.CMT_NUMBER,
PCT.EXPENDITURE_TYPE_ID AS CMT_EXPENDITURE_TYPE_ID,
PCT.ORGANIZATION_ID,
PCT.VENDOR_ID,
PCT.TOT_CMT_QUANTITY,
PCT.LAST_UPDATE_DATE,
PCT.PROJFUNC_BRDND_COST,
PCT.PROJFUNC_RAW_COST,
PETB.EXPENDITURE_CATEGORY_ID,
PJCL.MEANING,
PJCL.LOOKUP_TYPE,
PJCL.LOOKUP_CODE,
PCT.EXPENDITURE_ITEM_DATE
FROM
PJC_COMMITMENT_TXNS PCT,
PJF_EXP_TYPES_B PETB,
PJC_LOOKUPS PJCL
WHERE
PCT.EXPENDITURE_TYPE_ID = PETB.EXPENDITURE_TYPE_ID
AND PCT.CMT_APPROVED_STATUS = PJCL.LOOKUP_CODE(+)
AND ('PJC_YES_NO') = PJCL.LOOKUP_TYPE(+)
AND (
(
EXISTS (
SELECT
NULL
FROM
fnd_grants gnt
WHERE
exists (
SELECT
null
FROM
fnd_session_role_sets
WHERE
session_role_set_key = fnd_global.session_role_set_key
and role_guid = gnt.grantee_key
UNION ALL
SELECT
fnd_global.user_guid AS path
FROM
dual
WHERE
fnd_global.user_guid = gnt.grantee_key
)
AND exists (
select
null
from
fnd_compiled_menu_functions cmf
where
cmf.menu_id = gnt.menu_id
)
AND gnt.grant_type = 'ALLOW'
AND gnt.instance_type = 'SET'
AND gnt.start_date <= SYSDATE
and (
gnt.end_date is null
or gnt.end_date >= sysdate
)
AND (
(gnt.CONTEXT_NAME is NULL)
or (
gnt.context_name is not null
and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name)
)
)
AND (
PCT.BU_ID IN (
SELECT
ORG_ID
FROM
FUN_USER_ROLE_DATA_ASGNMNTS
WHERE
USER_GUID = FND_GLOBAL.USER_GUID
AND ROLE_NAME = GNT.ROLE_NAME
AND ACTIVE_FLAG != 'N'
)
)
)
OR EXISTS (
SELECT
NULL
FROM
fnd_grants gnt
WHERE
exists (
SELECT
null
FROM
fnd_session_role_sets
WHERE
session_role_set_key = fnd_global.session_role_set_key
and role_guid = gnt.grantee_key
UNION ALL
SELECT
fnd_global.user_guid AS path
FROM
dual
WHERE
fnd_global.user_guid = gnt.grantee_key
)
AND exists (
select
null
from
fnd_compiled_menu_functions cmf
where
cmf.menu_id = gnt.menu_id
)
AND gnt.grant_type = 'ALLOW'
AND gnt.instance_type = 'SET'
AND gnt.start_date <= SYSDATE
and (
gnt.end_date is null
or gnt.end_date >= sysdate
)
AND (
(gnt.CONTEXT_NAME is NULL)
or (
gnt.context_name is not null
and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name)
)
)
AND (
PCT.PROJECT_ID IN (
SELECT
AWDP.PROJECT_ID
FROM
GMS_AWARD_HEADERS_B awd,
gms_award_projects awdp,
fun_user_role_data_asgnmnts furd,
pjf_org_hierarchy_denorm orgd
WHERE
furd.user_guid = fnd_global.user_guid
AND awdp.award_id = awd.id
AND AWDP.PROJECT_ID = PCT.PROJECT_ID
AND FURD.ROLE_NAME = GNT.ROLE_NAME
AND NVL(furd.active_flag, 'N') != 'N'
AND furd.DATASEC_CONTEXT_TYPE_CODE = 'ORA_PRJ_AWARD_ORG_HIER_ID'
AND furd.DATASEC_CONTEXT_VALUE1 = orgd.tree_structure_code
AND furd.DATASEC_CONTEXT_VALUE2 = orgd.tree_version_id
AND furd.DATASEC_CONTEXT_VALUE3 = orgd.parent_organization_id
AND furd.DATASEC_CONTEXT_VALUE4 = orgd.tree_code
AND orgd.child_organization_id = AWD.AWARD_OWNING_ORG_ID
)
)
)
OR EXISTS (
SELECT
NULL
FROM
fnd_grants gnt
WHERE
exists (
SELECT
null
FROM
fnd_session_role_sets
WHERE
session_role_set_key = fnd_global.session_role_set_key
and role_guid = gnt.grantee_key
UNION ALL
SELECT
fnd_global.user_guid AS path
FROM
dual
WHERE
fnd_global.user_guid = gnt.grantee_key
)
AND exists (
select
null
from
fnd_compiled_menu_functions cmf
where
cmf.menu_id = gnt.menu_id
)
AND gnt.grant_type = 'ALLOW'
AND gnt.instance_type = 'SET'
AND gnt.start_date <= SYSDATE
and (
gnt.end_date is null
or gnt.end_date >= sysdate
)
AND (
(gnt.CONTEXT_NAME is NULL)
or (
gnt.context_name is not null
and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name)
)
)
AND (
PCT.PROJECT_ID IN (
select
a.project_id
from
pjf_projects_all_b a,
FUN_USER_ROLE_DATA_ASGNMNTS B
where
A.ORG_ID = B.ORG_ID
and B.USER_GUID = FND_GLOBAL.USER_GUID
AND NVL(B.ACTIVE_FLAG, 'N') != 'N'
AND B.ROLE_NAME = GNT.ROLE_NAME
)
)
)
OR EXISTS (
SELECT
NULL
FROM
fnd_grants gnt
WHERE
exists (
SELECT
null
FROM
fnd_session_role_sets
WHERE
session_role_set_key = fnd_global.session_role_set_key
and role_guid = gnt.grantee_key
UNION ALL
SELECT
fnd_global.user_guid AS path
FROM
dual
WHERE
fnd_global.user_guid = gnt.grantee_key
)
AND exists (
select
null
from
fnd_compiled_menu_functions cmf
where
cmf.menu_id = gnt.menu_id
)
AND gnt.grant_type = 'ALLOW'
AND gnt.instance_type = 'SET'
AND gnt.start_date <= SYSDATE
and (
gnt.end_date is null
or gnt.end_date >= sysdate
)
AND (
(gnt.CONTEXT_NAME is NULL)
or (
gnt.context_name is not null
and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name)
)
)
AND (
PCT.PROJECT_ID IN (
select
a.project_id
from
pjf_projects_all_b a,
FUN_USER_ROLE_DATA_ASGNMNTS B
where
a.CARRYING_OUT_ORGANIZATION_ID = b.PRJ_ORGANIZATION_ID
and B.USER_GUID = FND_GLOBAL.USER_GUID
AND NVL(B.ACTIVE_FLAG, 'N') != 'N'
AND B.ROLE_NAME = GNT.ROLE_NAME
)
)
)
OR EXISTS (
SELECT
NULL
FROM
fnd_grants gnt
WHERE
exists (
SELECT
null
FROM
fnd_session_role_sets
WHERE
session_role_set_key = fnd_global.session_role_set_key
and role_guid = gnt.grantee_key
UNION ALL
SELECT
fnd_global.user_guid AS path
FROM
dual
WHERE
fnd_global.user_guid = gnt.grantee_key
)
AND exists (
select
null
from
fnd_compiled_menu_functions cmf
where
cmf.menu_id = gnt.menu_id
)
AND gnt.grant_type = 'ALLOW'
AND gnt.instance_type = 'SET'
AND gnt.start_date <= SYSDATE
and (
gnt.end_date is null
or gnt.end_date >= sysdate
)
AND (
(gnt.CONTEXT_NAME is NULL)
or (
gnt.context_name is not null
and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name)
)
)
AND (
PCT.PROJECT_ID IN (
SELECT
PROJECT_ID
FROM
PJF_PROJECT_PARTIES PPP,
(
select
PROJECT_ROLE_ID,
ROLE_ID
from
PJF_PROJ_ROLE_TYPES_B
UNION ALL
select
1 PROJECT_ROLE_ID,
b.ROLE_ID
from
PER_ROLES_DN b
where
b.ROLE_COMMON_NAME = 'ORA_PJF_PROJECT_MANAGEMENT_DUTY_ABSTRACT'
) PRT,
PER_ROLES_DN PRD,
FND_SESSION_ROLES SROLE
WHERE
PPP.PROJECT_ID = PCT.PROJECT_ID
AND PPP.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND PRT.ROLE_ID = PRD.ROLE_ID
AND SYSDATE BETWEEN PPP.START_DATE_ACTIVE
AND NVL(PPP.END_DATE_ACTIVE, SYSDATE + 1)
AND SROLE.SESSION_ID = fnd_global.session_id
AND SROLE.ROLE_NAME = PRD.ROLE_COMMON_NAME
AND SROLE.ROLE_GUID = GNT.GRANTEE_KEY
AND hrc_session_util.get_user_personid = PPP.RESOURCE_SOURCE_ID
AND PPP.PROJECT_PARTY_TYPE = 'IN'
)
)
)
)
)
) COMMIT1,
(
SELECT
Supplier.VENDOR_ID AS VEN_ID,
Party.PARTY_ID AS PAR_ID,
Party.PARTY_NAME AS SUP1
FROM
POZ_SUPPLIERS Supplier,
HZ_PARTIES Party
WHERE Supplier.PARTY_ID = Party.PARTY_ID
) SUP,
(
SELECT
PDSC.CRITERION_ID,
PDSC.VENDOR_ID
FROM
poz_df_scoring_criteria PDSC
) POZC,
(
SELECT
PETV.EXPENDITURE_TYPE_ID,
PETV.EXPENDITURE_TYPE_NAME,
PETV.UNIT_OF_MEASURE AS UOM,
PETV.DESCRIPTION
FROM
PJF_EXP_TYPES_VL PETV
) EXP_TYPE,
(
SELECT
HOUCF.ORG_UNIT_CLASSIFICATION_ID,
HOUCF.EFFECTIVE_START_DATE,
HOUCF.EFFECTIVE_END_DATE,
HOUF.ORGANIZATION_ID AS ORG_IDF,
HOUF.EFFECTIVE_START_DATE AS ESTART_DATE,
HOUF.EFFECTIVE_END_DATE AS E_END_DATE,
HOUFT.ORGANIZATION_ID AS T_ORG_ID,
HOUFT.EFFECTIVE_START_DATE AS TE_START_DATE,
HOUFT.EFFECTIVE_END_DATE AS TE_END_DATE,
HOUFT.LANGUAGE,
HOUFT.NAME
FROM
HR_ALL_ORGANIZATION_UNITS_F HOUF,
HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF,
HR_ORGANIZATION_UNITS_F_TL HOUFT
WHERE
HOUF.ORGANIZATION_ID = HOUCF.ORGANIZATION_ID(+)
AND HOUF.ORGANIZATION_ID = HOUFT.ORGANIZATION_ID(+)
AND HOUF.EFFECTIVE_START_DATE = HOUFT.EFFECTIVE_START_DATE(+)
AND HOUF.EFFECTIVE_END_DATE = HOUFT.EFFECTIVE_END_DATE(+)
AND (USERENV('LANG')) = HOUFT.LANGUAGE(+)
AND SYSDATE BETWEEN HOUF.EFFECTIVE_START_DATE AND HOUF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN HOUCF.EFFECTIVE_START_DATE(+) AND HOUCF.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN HOUFT.EFFECTIVE_START_DATE(+) AND HOUFT.EFFECTIVE_END_DATE(+)
AND HOUCF.CLASSIFICATION_CODE = 'PA_EXPENDITURE_ORG'
) EXP_ORG,
(
SELECT
PPEB.ELEMENT_NUMBER,
PPEB.PROJ_ELEMENT_ID,
PPAB.SEGMENT1,
PPAB.PROJECT_ID AS PROJECT_ID3
FROM
PJF_PROJ_ELEMENTS_B PPEB,
PJF_PROJECTS_ALL_B PPAB
WHERE
PPEB.PROJECT_ID = PPAB.PROJECT_ID
AND PPAB.PROJECT_CATEGORY = 'FIN_EXEC'
OR PPAB.PROJECT_CATEGORY = 'TODO'
) TASK,
(
SELECT
PECV.EXPENDITURE_CATEGORY_ID,
PECV.EXPENDITURE_CATEGORY_NAME
FROM
PJF_EXP_CATEGORIES_VL PECV
) EXP_CAT
WHERE
COMMIT1.VENDOR_ID = SUP.VEN_ID(+)
AND SUP.VEN_ID = POZC.VENDOR_ID(+)
AND COMMIT1.CMT_EXPENDITURE_TYPE_ID = EXP_TYPE.EXPENDITURE_TYPE_ID
AND COMMIT1.ORGANIZATION_ID = EXP_ORG.ORG_IDF
AND COMMIT1.TASK_ID = TASK.PROJ_ELEMENT_ID
AND COMMIT1.EXPENDITURE_CATEGORY_ID = EXP_CAT.EXPENDITURE_CATEGORY_ID
AND TASK.SEGMENT1 = NVL(:PROJECT_NUMBER,TASK.SEGMENT1)
No comments:
Post a Comment