PJC_PRJ_REQ_DIST_V

Details

• Schema: FUSION

• Object owner: PJC

• Object type: VIEW

Columns

Name

REQ_NUMBER APPROVED_FLAG REQ_LINE ITEM_DESCRIPTION DOCUMENT_TYPE NEED_BY_DATE CREATION_DATE VENDOR_NAME VENDOR_ID REQUESTOR_PERSON_ID REQUESTOR_NAME DENOM_CURRENCY_CODE DENOM_AMOUNT ACCT_CURRENCY_CODE ACCT_RATE_DATE ACCT_RATE_TYPE ACCT_EXCHANGE_RATE UNIT UNIT_PRICE QUANTITY AMOUNT PROJECT_NUMBER PROJECT_NAME TASK_NUMBER TASK_NAME EXPENDITURE_TYPE EXPENDITURE_CATEGORY REVENUE_CATEGORY EXPENDITURE_ITEM_DATE EXPENDITURE_ORGANIZATION REQUISITION_HEADER_ID REQUISITION_LINE_ID REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_NUMBER PROJECT_ID PROJECT_TYPE_ID PROJECT_CURRENCY_CODE PROJ_CURR_CONV_DATE_TYPE_CODE PROJ_CURRENCY_CONV_DATE PROJ_CURR_RATE_TYPE TASK_ID EXPENDITURE_ORGANIZATION_ID RESOURCE_CLASS ORG_ID AWARD_SET_ID EXPENDITURE_TYPE_ID EXP_TYPE_COST_RATE_FLAG EXPENDITURE_CATEGORY_ID BILLABLE_FLAG CAPITALIZABLE_FLAG WORK_TYPE_ID CONTRACT_ID FUNDING_SOURCE_ID FUNDING_ALLOCATION_ID CONTRACT_LINE_ID

Query

SQL_Statement

SELECT RH.REQUISITION_NUMBER REQ_NUMBER , DECODE(DOCUMENT_STATUS,'APPROVED','Y','PENDING APPROVAL','N','RETURNED','N','WITHDRAWN','N') APPROVED_FLAG , RL.LINE_NUMBER REQ_LINE , RL.ITEM_DESCRIPTION ITEM_DESCRIPTION , NULL DOCUMENT_TYPE , RL.NEED_BY_DATE NEED_BY_DATE , RL.CREATION_DATE CREATION_DATE , RL.SUGGESTED_VENDOR_NAME VENDOR_NAME , RL.VENDOR_ID VENDOR_ID , REQ.PERSON_ID REQUESTOR_PERSON_ID , REQ.FULL_NAME REQUESTOR_NAME , NVL(RL.CURRENCY_CODE,G.CURRENCY_CODE) DENOM_CURRENCY_CODE , RD.DISTRIBUTION_CURRENCY_AMOUNT + ( NVL(RD.NONRECOVERABLE_TAX,0) * ( RD.DISTRIBUTION_CURRENCY_AMOUNT/RD.DISTRIBUTION_AMOUNT ) ) DENOM_AMOUNT , G.CURRENCY_CODE ACCT_CURRENCY_CODE , decode(NVL(RL.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_DATE(NULL),RL.RATE_DATE) ACCT_RATE_DATE , decode(NVL(RL.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),NULL,RL.RATE_TYPE) ACCT_RATE_TYPE , decode(NVL(RL.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_NUMBER(NULL),RL.RATE) ACCT_EXCHANGE_RATE , DECODE(LT.ORDER_TYPE_LOOKUP_CODE,'AMOUNT',NULL, RL.UOM_CODE) UNIT , TO_NUMBER(DECODE(LT.ORDER_TYPE_LOOKUP_CODE,'AMOUNT',NULL, RL.UNIT_PRICE)) UNIT_PRICE , RD.DISTRIBUTION_QUANTITY QUANTITY , DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', NVL (rd.DISTRIBUTION_AMOUNT, 0) + NVL (rd.nonrecoverable_tax, 0), 'QUANTITY', (rd.DISTRIBUTION_QUANTITY * rl.unit_price) + NVL (rd.nonrecoverable_tax, 0)) AMOUNT , P.SEGMENT1 PROJECT_NUMBER , P.NAME PROJECT_NAME , T.TASK_NUMBER TASK_NUMBER , T.TASK_NAME TASK_NAME , ET.EXPENDITURE_TYPE_NAME EXPENDITURE_TYPE , CAT.EXPENDITURE_CATEGORY_NAME EXPENDITURE_CATEGORY , ET.REVENUE_CATEGORY_CODE REVENUE_CATEGORY , RD.PJC_EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE , O.NAME EXPENDITURE_ORGANIZATION , RH.REQUISITION_HEADER_ID REQUISITION_HEADER_ID , RL.REQUISITION_LINE_ID REQUISITION_LINE_ID , RD.DISTRIBUTION_ID REQ_DISTRIBUTION_ID , RD.DISTRIBUTION_NUMBER REQ_DISTRIBUTION_NUMBER , P.PROJECT_ID PROJECT_ID , P.PROJECT_TYPE_ID PROJECT_TYPE_ID , P.PROJECT_CURRENCY_CODE PROJECT_CURRENCY_CODE , P.CURRENCY_CONV_DATE_TYPE_CODE PROJ_CURR_CONV_DATE_TYPE_CODE , P.CURRENCY_CONV_DATE PROJ_CURRENCY_CONV_DATE , P.CURRENCY_CONV_RATE_TYPE PROJ_CURR_RATE_TYPE , T.TASK_ID TASK_ID , O.ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID , 'FINANCIAL_ELEMENTS' RESOURCE_CLASS , RH.REQ_BU_ID ORG_ID , NULL AWARD_SET_ID , RD.PJC_EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID , ET.COST_RATE_FLAG EXP_TYPE_COST_RATE_FLAG , CAT.EXPENDITURE_CATEGORY_ID EXPENDITURE_CATEGORY_ID , RD.PJC_BILLABLE_FLAG BILLABLE_FLAG , RD.PJC_CAPITALIZABLE_FLAG CAPITALIZABLE_FLAG , RD.PJC_WORK_TYPE_ID WORK_TYPE_ID , RD.PJC_CONTRACT_ID CONTRACT_ID/*Added for grants uptake*/ , RD.PJC_RESERVED_ATTRIBUTE1 FUNDING_SOURCE_ID/*Added for grants uptake*/ , RD.PJC_FUNDING_ALLOCATION_ID FUNDING_ALLOCATION_ID/*Added for grants uptake*/ , RD.PJC_CONTRACT_LINE_ID CONTRACT_LINE_ID/*Added for grants uptake*/ FROM GL_LEDGERS G, PER_PERSON_NAMES_F_V REQ, PO_LINE_TYPES LT, POR_REQUISITION_LINES_ALL RL, POR_REQUISITION_HEADERS_ALL RH, PJF_TASKS_V T, HR_ORGANIZATION_V O, PJF_EXP_TYPES_VL ET, POR_REQ_DISTRIBUTIONS_ALL RD, PJF_PROJECTS_ALL_VL P, PJF_EXP_CATEGORIES_VL CAT, FUN_ALL_BUSINESS_UNITS_V FABUV WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID AND RH.DOCUMENT_STATUS IN ('APPROVED','PENDING APPROVAL','RETURNED','WITHDRAWN') AND ((RL.PO_LINE_ID IS NULL) OR (RL.PO_LINE_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL WHERE NVL(POH.DOCUMENT_STATUS,'INCOMPLETE') IN ('CANCELED','CLOSED','FINALLY CLOSED','PENDING APPROVAL','OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','ON HOLD','PENDING CATALOG AUTHORING','PENDING ACKNOWLEDGMENT' ) AND NVL(PLL.SCHEDULE_STATUS,'INCOMPLETE') IN ('CANCELED','CLOSED','FINALLY CLOSED','PENDING APPROVAL','OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','ON HOLD','PENDING CATALOG AUTHORING','PENDING ACKNOWLEDGMENT' ) AND POL.PO_HEADER_ID = POH.PO_HEADER_ID AND POL.PO_LINE_ID = PLL.PO_LINE_ID AND POL.PO_LINE_ID = RL.PO_LINE_ID ) ) ) AND NVL(RL.LINE_STATUS,'APPROVED') IN ('APPROVED','PENDING APPROVAL','RETURNED','ON_HOLD','WITHDRAWN') AND NVL(RL.MODIFIED_BY_BUYER_FLAG,'N') = 'N' AND REQ.PERSON_ID = RL.REQUESTER_ID AND TRUNC(SYSDATE) BETWEEN NVL(REQ.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(REQ.EFFECTIVE_END_DATE,TRUNC(SYSDATE)) AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID AND RL.DESTINATION_TYPE_CODE = 'EXPENSE' AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID AND RD.PJC_PROJECT_ID = P.PROJECT_ID AND RD.PJC_TASK_ID = T.TASK_ID AND RD.PJC_ORGANIZATION_ID = O.ORGANIZATION_ID AND RD.PJC_EXPENDITURE_TYPE_ID = ET.EXPENDITURE_TYPE_ID AND RD.DISTRIBUTION_AMOUNT <> 0 /*Added For bug 17840243*/ AND RH.REQ_BU_ID = FABUV.BU_ID AND G.LEDGER_ID = FABUV.PRIMARY_LEDGER_ID AND NVL(LT.MATCHING_BASIS,'QUANTITY') IN ('QUANTITY', 'AMOUNT') AND CAT.EXPENDITURE_CATEGORY_ID = ET.EXPENDITURE_CATEGORY_ID AND O.CLASSIFICATION_CODE = 'PA_EXPENDITURE_ORG' AND NVL(RD.PJC_EXPENDITURE_ITEM_DATE, TRUNC(SYSDATE)) BETWEEN NVL(O.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(O.EFFECTIVE_END_DATE,TRUNC(SYSDATE))