VIEW: APPS.PO_SGD_MOD_DISTRIBUTIONS_V

Source

View Text - Preformatted

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 ) )

View Text - HTML Formatted

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 ) )