VIEW: APPS.OKC_LAUNCH_KGRID_V

Source

View Text - Preformatted

SELECT /*+ ORDERED USE_NL(CHRB CHRT) */ chrb.rowid ROW_ID, CGP.CGP_PARENT_ID parent_group_id, CHRB.ID CONTRACT_ID, CHRB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, CHRB.STS_CODE STATUS_CODE, STS.MEANING STATUS_MEANING, CHRB.SCS_CODE SUBCLASS_CODE, SC.MEANING subclass_meaning, CHRB.QCL_ID QCL_ID, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER, CHRB.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER, CHRB.estimated_amount Contract_Amount, chrb.currency_code currency_code, CHRB.CUST_PO_NUMBER_REQ_YN CUST_PO_NUMBER_REQ_YN, CHRB.PRE_PAY_REQ_YN PRE_PAY_REQ_YN, CHRB.CUST_PO_NUMBER CUST_PO_NUMBER, CHRT.SHORT_DESCRIPTION SHORT_DESCRIPTION, CHRT.COMMENTS COMMENTS, CHRT.DESCRIPTION DESCRIPTION, CHRB.DPAS_RATING DPAS_RATING, CHRT.COGNOMEN KNOWN_AS, CHRB.TEMPLATE_YN TEMPLATE_YN, CHRB.TEMPLATE_USED TEMPLATE_USED, CHRB.DATE_APPROVED DATE_APPROVED, CHRB.DATETIME_CANCELLED DATETIME_CANCELLED, CHRB.AUTO_RENEW_DAYS AUTO_RENEW_DAYS, CHRB.DATE_SIGNED DATE_SIGNED, CHRB.DATE_TERMINATED DATE_TERMINATED, CHRB.DATE_RENEWED DATE_RENEWED, CHRB.TRN_CODE TRN_CODE, CHRB.START_DATE START_DATE, CHRB.END_DATE END_DATE, CHRB.AUTHORING_ORG_ID AUTHORING_ORG_ID, chrb.application_id application_id, CHRB.CREATED_BY CREATED_BY, CHRB.CREATION_DATE CREATION_DATE, CHRB.LAST_UPDATED_BY LAST_UPDATED_BY, CHRB.LAST_UPDATE_DATE LAST_UPDATE_DATE, CHRB.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CHRB.ORG_ID ORG_ID, HR.NAME OPERATING_UNIT FROM OKC_K_GRPINGS CGP, OKC_K_HEADERS_B CHRB, OKC_K_HEADERS_TL CHRT, OKC_SUBCLASSES_TL SC, OKC_STATUSES_TL STS, HR_ALL_ORGANIZATION_UNITS_TL HR WHERE CGP.SCS_CODE in (SELECT ras.scs_code FROM okc_subclass_resps ras WHERE ras.resp_id = fnd_global.resp_id AND sysdate BETWEEN ras.start_date AND nvl(ras.end_date, sysdate) ) AND exists (select NULL from OKC_APP_FUNCTION_ACCESSES afa where afa.responsibility_application_id=fnd_global.resp_appl_id and afa.object_application_id=chrb.application_id and afa.function='LAUNCHPAD' and afa.object_type='CONTRACT' and afa.object_access_flag='Y') AND CGP.INCLUDED_CHR_ID = CHRB.ID AND CHRT.ID = CHRB.ID AND CHRT.LANGUAGE = USERENV('LANG') AND sts.code = chrb.sts_code and sts.language = USERENV('LANG') AND sc.code = chrb.scs_code and sc.language = USERENV('LANG') AND HR.ORGANIZATION_ID = CHRB.ORG_ID AND HR.LANGUAGE = USERENV('LANG') union all select /* + ORDERED */ chrb.rowid ROW_ID, CGP.CGP_PARENT_ID parent_group_id, CHRB.ID CONTRACT_ID, CHRB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, CHRB.STS_CODE STATUS_CODE, STS.MEANING STATUS_MEANING, CHRB.SCS_CODE SUBCLASS_CODE, SC.MEANING subclass_meaning, CHRB.QCL_ID QCL_ID, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER, CHRB.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER, CHRB.estimated_amount Contract_Amount, chrb.currency_code currency_code, CHRB.CUST_PO_NUMBER_REQ_YN CUST_PO_NUMBER_REQ_YN, CHRB.PRE_PAY_REQ_YN PRE_PAY_REQ_YN, CHRB.CUST_PO_NUMBER CUST_PO_NUMBER, CHRT.SHORT_DESCRIPTION SHORT_DESCRIPTION, CHRT.COMMENTS COMMENTS, CHRT.DESCRIPTION DESCRIPTION, CHRB.DPAS_RATING DPAS_RATING, CHRT.COGNOMEN KNOWN_AS, CHRB.TEMPLATE_YN TEMPLATE_YN, CHRB.TEMPLATE_USED TEMPLATE_USED, CHRB.DATE_APPROVED DATE_APPROVED, CHRB.DATETIME_CANCELLED DATETIME_CANCELLED, CHRB.AUTO_RENEW_DAYS AUTO_RENEW_DAYS, CHRB.DATE_SIGNED DATE_SIGNED, CHRB.DATE_TERMINATED DATE_TERMINATED, CHRB.DATE_RENEWED DATE_RENEWED, CHRB.TRN_CODE TRN_CODE, CHRB.START_DATE START_DATE, CHRB.END_DATE END_DATE, CHRB.AUTHORING_ORG_ID AUTHORING_ORG_ID, chrb.application_id application_id, CHRB.CREATED_BY CREATED_BY, CHRB.CREATION_DATE CREATION_DATE, CHRB.LAST_UPDATED_BY LAST_UPDATED_BY, CHRB.LAST_UPDATE_DATE LAST_UPDATE_DATE, CHRB.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CHRB.ORG_ID ORG_ID, HR.NAME OPERATING_UNIT FROM OKC_K_HEADERS_B CHRB, OKC_K_GRPINGS CGP, OKC_K_HEADERS_TL CHRT, OKC_SUBCLASSES_TL SC, OKC_STATUSES_TL STS, HR_ALL_ORGANIZATION_UNITS_TL HR WHERE CHRB.ID in ( SELECT cas.chr_id FROM okc_k_accesses cas WHERE cas.resource_id in ( SELECT res.resource_id FROM jtf_rs_resource_extns res, jtf_rs_role_relations rrr, jtf_rs_roles_b rr WHERE res.user_id = fnd_global.user_id AND res.resource_id = rrr.role_resource_id AND rrr.role_resource_type = 'RS_INDIVIDUAL' AND rrr.role_id = rr.role_id AND rr.role_type_code = 'CONTRACTS') OR cas.group_id in ( SELECT rgm.group_id FROM jtf_rs_group_members rgm, jtf_rs_resource_extns res, jtf_rs_role_relations rrr, jtf_rs_roles_b rr WHERE res.user_id = fnd_global.user_id AND rgm.resource_id = res.resource_id AND rgm.group_id = rrr.role_resource_id AND rrr.role_resource_type = 'RS_GROUP' AND rrr.role_id = rr.role_id AND rr.role_type_code = 'CONTRACTS') OR cas.group_id in ( SELECT rgd.parent_group_id FROM jtf_rs_group_members rgm, jtf_rs_resource_extns res, jtf_rs_groups_denorm rgd, jtf_rs_role_relations rrr, jtf_rs_roles_b rr WHERE res.user_id = fnd_global.user_id AND rgm.resource_id = res.resource_id AND rgd.group_id = rgm.group_id AND rgd.parent_group_id = rrr.role_resource_id AND rrr.role_resource_type = 'RS_GROUP' AND rrr.role_id = rr.role_id AND rr.role_type_code = 'CONTRACTS') ) AND exists (select NULL from OKC_APP_FUNCTION_ACCESSES afa where afa.responsibility_application_id=fnd_global.resp_appl_id and afa.object_application_id=chrb.application_id and afa.function='LAUNCHPAD' and afa.object_type='CONTRACT' and afa.object_access_flag='Y') AND CGP.INCLUDED_CHR_ID = CHRB.ID and not exists (select null from okc_subclass_resps ras WHERE ras.resp_id = fnd_global.resp_id and ras.scs_code = CGP.SCS_CODE and sysdate BETWEEN ras.start_date AND nvl(ras.end_date, sysdate) ) AND CHRT.ID = CHRB.ID AND CHRT.LANGUAGE = USERENV('LANG') AND sts.code = chrb.sts_code and sts.language = USERENV('LANG') AND sc.code = chrb.scs_code and sc.language = USERENV('LANG') AND HR.ORGANIZATION_ID = CHRB.ORG_ID AND HR.LANGUAGE=USERENV('LANG')

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(CHRB CHRT) */ CHRB.ROWID ROW_ID
, CGP.CGP_PARENT_ID PARENT_GROUP_ID
, CHRB.ID CONTRACT_ID
, CHRB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, CHRB.STS_CODE STATUS_CODE
, STS.MEANING STATUS_MEANING
, CHRB.SCS_CODE SUBCLASS_CODE
, SC.MEANING SUBCLASS_MEANING
, CHRB.QCL_ID QCL_ID
, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER
, CHRB.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER
, CHRB.ESTIMATED_AMOUNT CONTRACT_AMOUNT
, CHRB.CURRENCY_CODE CURRENCY_CODE
, CHRB.CUST_PO_NUMBER_REQ_YN CUST_PO_NUMBER_REQ_YN
, CHRB.PRE_PAY_REQ_YN PRE_PAY_REQ_YN
, CHRB.CUST_PO_NUMBER CUST_PO_NUMBER
, CHRT.SHORT_DESCRIPTION SHORT_DESCRIPTION
, CHRT.COMMENTS COMMENTS
, CHRT.DESCRIPTION DESCRIPTION
, CHRB.DPAS_RATING DPAS_RATING
, CHRT.COGNOMEN KNOWN_AS
, CHRB.TEMPLATE_YN TEMPLATE_YN
, CHRB.TEMPLATE_USED TEMPLATE_USED
, CHRB.DATE_APPROVED DATE_APPROVED
, CHRB.DATETIME_CANCELLED DATETIME_CANCELLED
, CHRB.AUTO_RENEW_DAYS AUTO_RENEW_DAYS
, CHRB.DATE_SIGNED DATE_SIGNED
, CHRB.DATE_TERMINATED DATE_TERMINATED
, CHRB.DATE_RENEWED DATE_RENEWED
, CHRB.TRN_CODE TRN_CODE
, CHRB.START_DATE START_DATE
, CHRB.END_DATE END_DATE
, CHRB.AUTHORING_ORG_ID AUTHORING_ORG_ID
, CHRB.APPLICATION_ID APPLICATION_ID
, CHRB.CREATED_BY CREATED_BY
, CHRB.CREATION_DATE CREATION_DATE
, CHRB.LAST_UPDATED_BY LAST_UPDATED_BY
, CHRB.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CHRB.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CHRB.ORG_ID ORG_ID
, HR.NAME OPERATING_UNIT
FROM OKC_K_GRPINGS CGP
, OKC_K_HEADERS_B CHRB
, OKC_K_HEADERS_TL CHRT
, OKC_SUBCLASSES_TL SC
, OKC_STATUSES_TL STS
, HR_ALL_ORGANIZATION_UNITS_TL HR
WHERE CGP.SCS_CODE IN (SELECT RAS.SCS_CODE
FROM OKC_SUBCLASS_RESPS RAS
WHERE RAS.RESP_ID = FND_GLOBAL.RESP_ID
AND SYSDATE BETWEEN RAS.START_DATE
AND NVL(RAS.END_DATE
, SYSDATE) )
AND EXISTS (SELECT NULL
FROM OKC_APP_FUNCTION_ACCESSES AFA
WHERE AFA.RESPONSIBILITY_APPLICATION_ID=FND_GLOBAL.RESP_APPL_ID
AND AFA.OBJECT_APPLICATION_ID=CHRB.APPLICATION_ID
AND AFA.FUNCTION='LAUNCHPAD'
AND AFA.OBJECT_TYPE='CONTRACT'
AND AFA.OBJECT_ACCESS_FLAG='Y')
AND CGP.INCLUDED_CHR_ID = CHRB.ID
AND CHRT.ID = CHRB.ID
AND CHRT.LANGUAGE = USERENV('LANG')
AND STS.CODE = CHRB.STS_CODE
AND STS.LANGUAGE = USERENV('LANG')
AND SC.CODE = CHRB.SCS_CODE
AND SC.LANGUAGE = USERENV('LANG')
AND HR.ORGANIZATION_ID = CHRB.ORG_ID
AND HR.LANGUAGE = USERENV('LANG') UNION ALL SELECT /* + ORDERED */ CHRB.ROWID ROW_ID
, CGP.CGP_PARENT_ID PARENT_GROUP_ID
, CHRB.ID CONTRACT_ID
, CHRB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, CHRB.STS_CODE STATUS_CODE
, STS.MEANING STATUS_MEANING
, CHRB.SCS_CODE SUBCLASS_CODE
, SC.MEANING SUBCLASS_MEANING
, CHRB.QCL_ID QCL_ID
, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER
, CHRB.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER
, CHRB.ESTIMATED_AMOUNT CONTRACT_AMOUNT
, CHRB.CURRENCY_CODE CURRENCY_CODE
, CHRB.CUST_PO_NUMBER_REQ_YN CUST_PO_NUMBER_REQ_YN
, CHRB.PRE_PAY_REQ_YN PRE_PAY_REQ_YN
, CHRB.CUST_PO_NUMBER CUST_PO_NUMBER
, CHRT.SHORT_DESCRIPTION SHORT_DESCRIPTION
, CHRT.COMMENTS COMMENTS
, CHRT.DESCRIPTION DESCRIPTION
, CHRB.DPAS_RATING DPAS_RATING
, CHRT.COGNOMEN KNOWN_AS
, CHRB.TEMPLATE_YN TEMPLATE_YN
, CHRB.TEMPLATE_USED TEMPLATE_USED
, CHRB.DATE_APPROVED DATE_APPROVED
, CHRB.DATETIME_CANCELLED DATETIME_CANCELLED
, CHRB.AUTO_RENEW_DAYS AUTO_RENEW_DAYS
, CHRB.DATE_SIGNED DATE_SIGNED
, CHRB.DATE_TERMINATED DATE_TERMINATED
, CHRB.DATE_RENEWED DATE_RENEWED
, CHRB.TRN_CODE TRN_CODE
, CHRB.START_DATE START_DATE
, CHRB.END_DATE END_DATE
, CHRB.AUTHORING_ORG_ID AUTHORING_ORG_ID
, CHRB.APPLICATION_ID APPLICATION_ID
, CHRB.CREATED_BY CREATED_BY
, CHRB.CREATION_DATE CREATION_DATE
, CHRB.LAST_UPDATED_BY LAST_UPDATED_BY
, CHRB.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CHRB.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CHRB.ORG_ID ORG_ID
, HR.NAME OPERATING_UNIT
FROM OKC_K_HEADERS_B CHRB
, OKC_K_GRPINGS CGP
, OKC_K_HEADERS_TL CHRT
, OKC_SUBCLASSES_TL SC
, OKC_STATUSES_TL STS
, HR_ALL_ORGANIZATION_UNITS_TL HR
WHERE CHRB.ID IN ( SELECT CAS.CHR_ID
FROM OKC_K_ACCESSES CAS
WHERE CAS.RESOURCE_ID IN ( SELECT RES.RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_ROLE_RELATIONS RRR
, JTF_RS_ROLES_B RR
WHERE RES.USER_ID = FND_GLOBAL.USER_ID
AND RES.RESOURCE_ID = RRR.ROLE_RESOURCE_ID
AND RRR.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
AND RRR.ROLE_ID = RR.ROLE_ID
AND RR.ROLE_TYPE_CODE = 'CONTRACTS') OR CAS.GROUP_ID IN ( SELECT RGM.GROUP_ID
FROM JTF_RS_GROUP_MEMBERS RGM
, JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_ROLE_RELATIONS RRR
, JTF_RS_ROLES_B RR
WHERE RES.USER_ID = FND_GLOBAL.USER_ID
AND RGM.RESOURCE_ID = RES.RESOURCE_ID
AND RGM.GROUP_ID = RRR.ROLE_RESOURCE_ID
AND RRR.ROLE_RESOURCE_TYPE = 'RS_GROUP'
AND RRR.ROLE_ID = RR.ROLE_ID
AND RR.ROLE_TYPE_CODE = 'CONTRACTS') OR CAS.GROUP_ID IN ( SELECT RGD.PARENT_GROUP_ID
FROM JTF_RS_GROUP_MEMBERS RGM
, JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_GROUPS_DENORM RGD
, JTF_RS_ROLE_RELATIONS RRR
, JTF_RS_ROLES_B RR
WHERE RES.USER_ID = FND_GLOBAL.USER_ID
AND RGM.RESOURCE_ID = RES.RESOURCE_ID
AND RGD.GROUP_ID = RGM.GROUP_ID
AND RGD.PARENT_GROUP_ID = RRR.ROLE_RESOURCE_ID
AND RRR.ROLE_RESOURCE_TYPE = 'RS_GROUP'
AND RRR.ROLE_ID = RR.ROLE_ID
AND RR.ROLE_TYPE_CODE = 'CONTRACTS') )
AND EXISTS (SELECT NULL
FROM OKC_APP_FUNCTION_ACCESSES AFA
WHERE AFA.RESPONSIBILITY_APPLICATION_ID=FND_GLOBAL.RESP_APPL_ID
AND AFA.OBJECT_APPLICATION_ID=CHRB.APPLICATION_ID
AND AFA.FUNCTION='LAUNCHPAD'
AND AFA.OBJECT_TYPE='CONTRACT'
AND AFA.OBJECT_ACCESS_FLAG='Y')
AND CGP.INCLUDED_CHR_ID = CHRB.ID
AND NOT EXISTS (SELECT NULL
FROM OKC_SUBCLASS_RESPS RAS
WHERE RAS.RESP_ID = FND_GLOBAL.RESP_ID
AND RAS.SCS_CODE = CGP.SCS_CODE
AND SYSDATE BETWEEN RAS.START_DATE
AND NVL(RAS.END_DATE
, SYSDATE) )
AND CHRT.ID = CHRB.ID
AND CHRT.LANGUAGE = USERENV('LANG')
AND STS.CODE = CHRB.STS_CODE
AND STS.LANGUAGE = USERENV('LANG')
AND SC.CODE = CHRB.SCS_CODE
AND SC.LANGUAGE = USERENV('LANG')
AND HR.ORGANIZATION_ID = CHRB.ORG_ID
AND HR.LANGUAGE=USERENV('LANG')