VIEW: APPS.PO_CLMREQ_DIST_DETAILS_V

Source

View Text - Preformatted

SELECT prh.requisition_header_id , prh.segment1 requisition_number , prl.requisition_line_id , nvl(prl.line_num_display, prl.line_num) line_number , prl.at_sourcing_flag , prl.reqs_in_pool_flag , prl.line_location_id , prl.po_line_id , prl.auction_header_id , prl.auction_line_number , prl.destination_type_code , prl.destination_subinventory , prl.wip_entity_id , prl.wip_line_id , prl.wip_repetitive_schedule_id , prl.wip_operation_seq_num , prl.wip_resource_seq_num , prl.bom_resource_id , prl.destination_context , prl.deliver_to_location_id , prl.destination_organization_id , prl.unit_meas_lookup_code , prl.item_id , prl.job_id , prl.currency_code , prl.unit_price , prl.item_description , prd.distribution_id , prd.set_of_books_id , prd.code_combination_id , prd.req_line_quantity , prd.encumbered_flag , prd.gl_encumbered_date , prd.gl_encumbered_period_name , prd.gl_cancelled_date , prd.failed_funds_lookup_code , prd.encumbered_amount , prd.budget_account_id , prd.accrual_account_id , prd.variance_account_id , prd.prevent_encumbrance_flag , prd.ussgl_transaction_code , prd.government_context , prd.project_id , prd.task_id , prd.expenditure_type , prd.project_accounting_context , prd.expenditure_organization_id , prd.gl_closed_date , prd.source_req_distribution_id , prd.distribution_num , prd.project_related_flag , prd.expenditure_item_date , prd.org_id , prd.allocation_type , prd.allocation_value , prd.award_id , prd.end_item_unit_number , prd.recoverable_tax , prd.nonrecoverable_tax , prd.recovery_rate , prd.tax_recovery_override_flag , prd.oke_contract_line_id , prd.oke_contract_deliverable_id , prd.req_line_amount , prd.req_line_currency_amount , prd.req_award_id , prd.event_id , prd.conformed_dist_id , prd.amendment_type , prd.amendment_status , prd.uda_template_id , prd.info_line_id , prd.amount_funded , prd.funded_value , prd.partial_funded_flag , prd.quantity_funded , prd.clm_misc_loa , prd.clm_defence_funding , prd.clm_fms_case_number , prd.clm_agency_acct_identifier , prd.change_in_funded_value , prd.unencumbered_amount , nvl(poref.funds_used,0) funds_used , (nvl(prd.funded_value,0) - NVL(PRD.FUNDS_LIQUIDATED,0) - nvl(poref.funds_used,0)) funds_remaining, (case when(nvl(prd.funded_value,0) > 0 and (nvl(poref.funds_used,0) + NVL(PRD.FUNDS_LIQUIDATED,0) ) < nvl(prd.funded_value,0)) then 'Y' else 'N' end) is_funds_remaining, NVL(PRD.FUNDS_LIQUIDATED,0) FUNDS_LIQUIDATED from po_requisition_headers_clm_v prh, po_requisition_lines_all prl , po_req_distributions_all prd , po_clmreq_dist_funds_used_v poref where prh.requisition_header_id = prl.requisition_header_id and prh.conformed_header_id is null and nvl(prh.active_shopping_cart_flag, 'N') <> 'Y' and prl.requisition_line_id = prd.requisition_line_id and prd.distribution_id = poref.req_distribution_id(+)

View Text - HTML Formatted

SELECT PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1 REQUISITION_NUMBER
, PRL.REQUISITION_LINE_ID
, NVL(PRL.LINE_NUM_DISPLAY
, PRL.LINE_NUM) LINE_NUMBER
, PRL.AT_SOURCING_FLAG
, PRL.REQS_IN_POOL_FLAG
, PRL.LINE_LOCATION_ID
, PRL.PO_LINE_ID
, PRL.AUCTION_HEADER_ID
, PRL.AUCTION_LINE_NUMBER
, PRL.DESTINATION_TYPE_CODE
, PRL.DESTINATION_SUBINVENTORY
, PRL.WIP_ENTITY_ID
, PRL.WIP_LINE_ID
, PRL.WIP_REPETITIVE_SCHEDULE_ID
, PRL.WIP_OPERATION_SEQ_NUM
, PRL.WIP_RESOURCE_SEQ_NUM
, PRL.BOM_RESOURCE_ID
, PRL.DESTINATION_CONTEXT
, PRL.DELIVER_TO_LOCATION_ID
, PRL.DESTINATION_ORGANIZATION_ID
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.ITEM_ID
, PRL.JOB_ID
, PRL.CURRENCY_CODE
, PRL.UNIT_PRICE
, PRL.ITEM_DESCRIPTION
, PRD.DISTRIBUTION_ID
, PRD.SET_OF_BOOKS_ID
, PRD.CODE_COMBINATION_ID
, PRD.REQ_LINE_QUANTITY
, PRD.ENCUMBERED_FLAG
, PRD.GL_ENCUMBERED_DATE
, PRD.GL_ENCUMBERED_PERIOD_NAME
, PRD.GL_CANCELLED_DATE
, PRD.FAILED_FUNDS_LOOKUP_CODE
, PRD.ENCUMBERED_AMOUNT
, PRD.BUDGET_ACCOUNT_ID
, PRD.ACCRUAL_ACCOUNT_ID
, PRD.VARIANCE_ACCOUNT_ID
, PRD.PREVENT_ENCUMBRANCE_FLAG
, PRD.USSGL_TRANSACTION_CODE
, PRD.GOVERNMENT_CONTEXT
, PRD.PROJECT_ID
, PRD.TASK_ID
, PRD.EXPENDITURE_TYPE
, PRD.PROJECT_ACCOUNTING_CONTEXT
, PRD.EXPENDITURE_ORGANIZATION_ID
, PRD.GL_CLOSED_DATE
, PRD.SOURCE_REQ_DISTRIBUTION_ID
, PRD.DISTRIBUTION_NUM
, PRD.PROJECT_RELATED_FLAG
, PRD.EXPENDITURE_ITEM_DATE
, PRD.ORG_ID
, PRD.ALLOCATION_TYPE
, PRD.ALLOCATION_VALUE
, PRD.AWARD_ID
, PRD.END_ITEM_UNIT_NUMBER
, PRD.RECOVERABLE_TAX
, PRD.NONRECOVERABLE_TAX
, PRD.RECOVERY_RATE
, PRD.TAX_RECOVERY_OVERRIDE_FLAG
, PRD.OKE_CONTRACT_LINE_ID
, PRD.OKE_CONTRACT_DELIVERABLE_ID
, PRD.REQ_LINE_AMOUNT
, PRD.REQ_LINE_CURRENCY_AMOUNT
, PRD.REQ_AWARD_ID
, PRD.EVENT_ID
, PRD.CONFORMED_DIST_ID
, PRD.AMENDMENT_TYPE
, PRD.AMENDMENT_STATUS
, PRD.UDA_TEMPLATE_ID
, PRD.INFO_LINE_ID
, PRD.AMOUNT_FUNDED
, PRD.FUNDED_VALUE
, PRD.PARTIAL_FUNDED_FLAG
, PRD.QUANTITY_FUNDED
, PRD.CLM_MISC_LOA
, PRD.CLM_DEFENCE_FUNDING
, PRD.CLM_FMS_CASE_NUMBER
, PRD.CLM_AGENCY_ACCT_IDENTIFIER
, PRD.CHANGE_IN_FUNDED_VALUE
, PRD.UNENCUMBERED_AMOUNT
, NVL(POREF.FUNDS_USED
, 0) FUNDS_USED
, (NVL(PRD.FUNDED_VALUE
, 0) - NVL(PRD.FUNDS_LIQUIDATED
, 0) - NVL(POREF.FUNDS_USED
, 0)) FUNDS_REMAINING
, (CASE WHEN(NVL(PRD.FUNDED_VALUE
, 0) > 0
AND (NVL(POREF.FUNDS_USED
, 0) + NVL(PRD.FUNDS_LIQUIDATED
, 0) ) < NVL(PRD.FUNDED_VALUE
, 0)) THEN 'Y' ELSE 'N' END) IS_FUNDS_REMAINING
, NVL(PRD.FUNDS_LIQUIDATED
, 0) FUNDS_LIQUIDATED
FROM PO_REQUISITION_HEADERS_CLM_V PRH
, PO_REQUISITION_LINES_ALL PRL
, PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_CLMREQ_DIST_FUNDS_USED_V POREF
WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRH.CONFORMED_HEADER_ID IS NULL
AND NVL(PRH.ACTIVE_SHOPPING_CART_FLAG
, 'N') <> 'Y'
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PRD.DISTRIBUTION_ID = POREF.REQ_DISTRIBUTION_ID(+)