View: PO_SGD_MOD_DISTRIBUTIONS_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.PO_SGD_MOD_DISTRIBUTIONS_V

View Text

SELECT PO_HEADER_ID PK1_VALUE
, DRAFT_ID PK2_VALUE
, PO_LINE_ID PK3_VALUE
, LINE_LOCATION_ID PK4_VALUE
, PO_DISTRIBUTION_ID PK5_VALUE
, COL_NAME
, COL_VALUE
, DECODE(COL_NAME
, 'CLM_DEFENCE_FUNDING'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_FUND_INDICATOR'
AND LANGUAGE = USERENV('LANG'))
, 'DESTINATION_TYPE_CODE'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'DESTINATION TYPE'
AND LANGUAGE = USERENV('LANG'))
, 'DELIVER_TO_LOCATION_ID'
, (SELECT LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = COL_VALUE)
, 'DELIVER_TO_PERSON_ID'
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = COL_VALUE
AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE)
, 'CODE_COMBINATION_ID'
, (SELECT CONCATENATED_SEGMENTS
FROM GL_CODE_COMBINATIONS_KFV
WHERE CODE_COMBINATION_ID = COL_VALUE)
, 'ACCRUAL_ACCOUNT_ID'
, (SELECT CONCATENATED_SEGMENTS
FROM GL_CODE_COMBINATIONS_KFV
WHERE CODE_COMBINATION_ID = COL_VALUE)
, 'VARIANCE_ACCOUNT_ID'
, (SELECT CONCATENATED_SEGMENTS
FROM GL_CODE_COMBINATIONS_KFV
WHERE CODE_COMBINATION_ID = COL_VALUE)
, 'PROJECT_ID'
, (SELECT NAME
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = COL_VALUE)
, 'TASK_ID'
, (SELECT TASK_NAME
FROM PA_TASKS
WHERE TASK_ID = COL_VALUE)
, 'EXPENDITURE_ORGANIZATION_ID'
, (SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = COL_VALUE)
, 'OKE_CONTRACT_LINE_ID'
, (SELECT LINE_NUMBER
FROM OKC_K_LINES_B
WHERE ID = COL_VALUE)
, NULL) COL_DESC
FROM ( SELECT PD.PO_HEADER_ID
, PD.DRAFT_ID
, PD.PO_LINE_ID
, PD.LINE_LOCATION_ID
, PD.PO_DISTRIBUTION_ID
, TO_CHAR(PD.DISTRIBUTION_NUM) DISTRIBUTION_NUM
, TO_CHAR(PD.QUANTITY_ORDERED) QUANTITY_ORDERED
, TO_CHAR(PD.QUANTITY_DELIVERED) QUANTITY_DELIVERED
, TO_CHAR(PD.QUANTITY_BILLED) QUANTITY_BILLED
, TO_CHAR(PD.QUANTITY_CANCELLED) QUANTITY_CANCELLED
, TO_CHAR(PD.QUANTITY_FINANCED) QUANTITY_FINANCED
, TO_CHAR(PD.QUANTITY_RECOUPED) QUANTITY_RECOUPED
, TO_CHAR(PD.AMOUNT_ORDERED) AMOUNT_ORDERED
, TO_CHAR(PD.AMOUNT_DELIVERED) AMOUNT_DELIVERED
, TO_CHAR(PD.AMOUNT_BILLED) AMOUNT_BILLED
, TO_CHAR(PD.AMOUNT_CANCELLED) AMOUNT_CANCELLED
, TO_CHAR(PD.AMOUNT_FINANCED) AMOUNT_FINANCED
, TO_CHAR(PD.AMOUNT_RECOUPED) AMOUNT_RECOUPED
, PD.CLM_DEFENCE_FUNDING
, TO_CHAR(PD.GL_ENCUMBERED_DATE) GL_ENCUMBERED_DATE
, PD.DESTINATION_TYPE_CODE
, TO_CHAR(PD.DESTINATION_ORGANIZATION_ID) DESTINATION_ORGANIZATION_ID
, TO_CHAR(PD.DELIVER_TO_LOCATION_ID) DELIVER_TO_LOCATION_ID
, TO_CHAR(PD.DELIVER_TO_PERSON_ID) DELIVER_TO_PERSON_ID
, PD.REQ_HEADER_REFERENCE_NUM
, PD.REQ_LINE_REFERENCE_NUM
, PD.CLM_MISC_LOA
, TO_CHAR(PD.CODE_COMBINATION_ID) CODE_COMBINATION_ID
, TO_CHAR(PD.ACCRUAL_ACCOUNT_ID) ACCRUAL_ACCOUNT_ID
, TO_CHAR(PD.VARIANCE_ACCOUNT_ID) VARIANCE_ACCOUNT_ID
, PD.CLM_FMS_CASE_NUMBER
, PD.CLM_AGENCY_ACCT_IDENTIFIER
, TO_CHAR(PD.PROJECT_ID) PROJECT_ID
, TO_CHAR(PD.TASK_ID) TASK_ID
, PD.EXPENDITURE_TYPE
, PD.PROJECT_ACCOUNTING_CONTEXT
, TO_CHAR(PD.EXPENDITURE_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID
, TO_CHAR(PD.EXPENDITURE_ITEM_DATE) EXPENDITURE_ITEM_DATE
, TO_CHAR(PD.OKE_CONTRACT_LINE_ID) OKE_CONTRACT_LINE_ID
, TO_CHAR(PD.OKE_CONTRACT_DELIVERABLE_ID) OKE_CONTRACT_DELIVERABLE_ID
, TO_CHAR(PD.AMOUNT_FUNDED) AMOUNT_FUNDED
, TO_CHAR(PD.QUANTITY_FUNDED) QUANTITY_FUNDED
, TO_CHAR(PD.FUNDED_VALUE) FUNDED_VALUE
, PD.PARTIAL_FUNDED_FLAG
, PD.CHANGE_STATUS
FROM PO_DISTRIBUTIONS_DRAFT_ALL PD
WHERE PD.CHANGE_STATUS IN ('UPDATE')
AND PD.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETMODPK1
AND PD.DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2
AND PD.PO_LINE_ID = NVL(PO_GEN_DIFF_PKG.GETMODPK3
, PD.PO_LINE_ID)
AND PD.LINE_LOCATION_ID = NVL(PO_GEN_DIFF_PKG.GETMODPK4
, PD.LINE_LOCATION_ID)
AND PD.PO_DISTRIBUTION_ID = NVL(PO_GEN_DIFF_PKG.GETMODPK5
, PD.PO_DISTRIBUTION_ID) ) PO_DISTRIBUTION_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( DISTRIBUTION_NUM
, QUANTITY_ORDERED
, QUANTITY_DELIVERED
, QUANTITY_BILLED
, QUANTITY_CANCELLED
, QUANTITY_FINANCED
, QUANTITY_RECOUPED
, AMOUNT_ORDERED
, AMOUNT_DELIVERED
, AMOUNT_BILLED
, AMOUNT_CANCELLED
, AMOUNT_FINANCED
, AMOUNT_RECOUPED
, CLM_DEFENCE_FUNDING
, GL_ENCUMBERED_DATE
, DESTINATION_TYPE_CODE
, DESTINATION_ORGANIZATION_ID
, DELIVER_TO_LOCATION_ID
, DELIVER_TO_PERSON_ID
, REQ_HEADER_REFERENCE_NUM
, REQ_LINE_REFERENCE_NUM
, CLM_MISC_LOA
, CODE_COMBINATION_ID
, ACCRUAL_ACCOUNT_ID
, VARIANCE_ACCOUNT_ID
, CLM_FMS_CASE_NUMBER
, CLM_AGENCY_ACCT_IDENTIFIER
, PROJECT_ID
, TASK_ID
, EXPENDITURE_TYPE
, PROJECT_ACCOUNTING_CONTEXT
, EXPENDITURE_ORGANIZATION_ID
, EXPENDITURE_ITEM_DATE
, OKE_CONTRACT_LINE_ID
, OKE_CONTRACT_DELIVERABLE_ID
, AMOUNT_FUNDED
, FUNDED_VALUE
, PARTIAL_FUNDED_FLAG
, QUANTITY_FUNDED
, CHANGE_STATUS ) )

Columns