VIEW: APPS.PO_DISTRIBUTIONS_DIFF_V

Source

View Text - Preformatted

SELECT po_header_id pk1_value, -1 pk2_value, po_line_id pk3_value, line_location_id pk4_value, po_distribution_id pk5_value, col_name , col_value, revision_num from ( SELECT pd.po_header_id, -1 draft_id, pd.po_line_id, pd.line_location_id, pd.po_distribution_id, to_char(pd.accrual_account_id) accrual_account_id, pd.accrued_flag, pd.accrue_on_receipt_flag, pd.acrn, to_char(pd.amount_billed) amount_billed, to_char(pd.amount_cancelled) amount_cancelled, to_char(pd.amount_delivered) amount_delivered, to_char(pd.amount_financed) amount_financed, to_char(pd.amount_funded) amount_funded, to_char(pd.amount_ordered) amount_ordered, to_char(pd.amount_recouped) amount_recouped, to_char(pd.amount_to_encumber) amount_to_encumber, pd.attribute1, pd.attribute10, pd.attribute11, pd.attribute12, pd.attribute13, pd.attribute14, pd.attribute15, pd.attribute2, pd.attribute3, pd.attribute4, pd.attribute5, pd.attribute6, pd.attribute7, pd.attribute8, pd.attribute9, pd.attribute_category, to_char(pd.award_id) award_id, to_char(pd.bom_resource_id) bom_resource_id, to_char(pd.budget_account_id) budget_account_id, to_char(pd.change_in_funded_value) change_in_funded_value, pd.clm_agency_acct_identifier, pd.clm_defence_funding, pd.clm_fms_case_number, pd.clm_misc_loa, to_char(pd.clm_payment_sequence_num) clm_payment_sequence_num, to_char(pd.code_combination_id) code_combination_id, to_char(pd.deliver_to_location_id) deliver_to_location_id, to_char(pd.deliver_to_person_id) deliver_to_person_id, pd.destination_context, to_char(pd.destination_organization_id) destination_organization_id, pd.destination_subinventory, pd.destination_type_code, to_char(pd.dest_charge_account_id) dest_charge_account_id, to_char(pd.dest_variance_account_id) dest_variance_account_id, to_char(pd.distribution_num) distribution_num, pd.distribution_type, to_char(pd.encumbered_amount) encumbered_amount, pd.encumbered_flag, pd.end_item_unit_number, to_char(pd.expenditure_item_date) expenditure_item_date, to_char(pd.expenditure_organization_id) expenditure_organization_id, pd.expenditure_type, pd.failed_funds_lookup_code, to_char(pd.funded_value) funded_value, to_char(pd.gl_cancelled_date) gl_cancelled_date, to_char(pd.gl_closed_date) gl_closed_date, to_char(pd.gl_encumbered_date) gl_encumbered_date, pd.gl_encumbered_period_name, pd.government_context, to_char(pd.group_line_id) group_line_id, to_char(pd.invoiced_val_in_ntfn) invoiced_val_in_ntfn, pd.invoice_adjustment_flag, to_char(pd.kanban_card_id) kanban_card_id, pd.mrc_encumbered_amount, pd.mrc_rate, pd.mrc_rate_date, pd.mrc_unencumbered_amount, to_char(pd.nonrecoverable_tax) nonrecoverable_tax, to_char(pd.oke_contract_deliverable_id) oke_contract_deliverable_id, to_char(pd.oke_contract_line_id) oke_contract_line_id, to_char(pd.org_id) org_id, pd.partial_funded_flag, to_char(pd.po_release_id) po_release_id, pd.prevent_encumbrance_flag, to_char(pd.program_application_id) program_application_id, to_char(pd.program_id) program_id, to_char(pd.program_update_date) program_update_date, pd.project_accounting_context, to_char(pd.project_id) project_id, to_char(pd.quantity_billed) quantity_billed, to_char(pd.quantity_cancelled) quantity_cancelled, to_char(pd.quantity_delivered) quantity_delivered, to_char(pd.quantity_financed) quantity_financed, to_char(pd.quantity_funded) quantity_funded, to_char(pd.quantity_ordered) quantity_ordered, to_char(pd.quantity_recouped) quantity_recouped, to_char(pd.rate) rate, to_char(pd.rate_date) rate_date, to_char(pd.recoverable_tax) recoverable_tax, to_char(pd.recovery_rate) recovery_rate, to_char(pd.request_id) request_id, to_char(pd.req_distribution_id) req_distribution_id, pd.req_header_reference_num, pd.req_line_reference_num, to_char(pd.retainage_released_amount) retainage_released_amount, to_char(pd.retainage_withheld_amount) retainage_withheld_amount, to_char(pd.revision_num) revision_num, to_char(pd.set_of_books_id) set_of_books_id, to_char(pd.source_distribution_id) source_distribution_id, to_char(pd.task_id) task_id, pd.tax_attribute_update_code, pd.tax_recovery_override_flag, to_char(pd.uda_template_id) uda_template_id, to_char(pd.unencumbered_amount) unencumbered_amount, to_char(pd.unencumbered_quantity) unencumbered_quantity, pd.ussgl_transaction_code, to_char(pd.variance_account_id) variance_account_id, to_char(pd.wip_entity_id) wip_entity_id, to_char(pd.wip_line_id) wip_line_id, to_char(pd.wip_operation_seq_num) wip_operation_seq_num, to_char(pd.wip_repetitive_schedule_id) wip_repetitive_schedule_id, to_char(pd.wip_resource_seq_num) wip_resource_seq_num, null as change_status FROM po_distributions_archive_all pd WHERE po_header_id = PO_GEN_DIFF_PKG.getBasePK1) po_distribution_unpivot_data unpivot include nulls ( col_value for col_name in ( ACCRUAL_ACCOUNT_ID, ACCRUED_FLAG, ACCRUE_ON_RECEIPT_FLAG, ACRN, AMOUNT_BILLED, AMOUNT_CANCELLED, AMOUNT_DELIVERED, AMOUNT_FINANCED, AMOUNT_FUNDED, AMOUNT_ORDERED, AMOUNT_RECOUPED, AMOUNT_TO_ENCUMBER, ATTRIBUTE1, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE_CATEGORY, AWARD_ID, BOM_RESOURCE_ID, BUDGET_ACCOUNT_ID, CHANGE_IN_FUNDED_VALUE, CLM_AGENCY_ACCT_IDENTIFIER, CLM_DEFENCE_FUNDING, CLM_FMS_CASE_NUMBER, CLM_MISC_LOA, CLM_PAYMENT_SEQUENCE_NUM, CODE_COMBINATION_ID, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, DESTINATION_CONTEXT, DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY, DESTINATION_TYPE_CODE, DEST_CHARGE_ACCOUNT_ID, DEST_VARIANCE_ACCOUNT_ID, DISTRIBUTION_NUM, DISTRIBUTION_TYPE, ENCUMBERED_AMOUNT, ENCUMBERED_FLAG, END_ITEM_UNIT_NUMBER, EXPENDITURE_ITEM_DATE, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_TYPE, FAILED_FUNDS_LOOKUP_CODE, FUNDED_VALUE, GL_CANCELLED_DATE, GL_CLOSED_DATE, GL_ENCUMBERED_DATE, GL_ENCUMBERED_PERIOD_NAME, GOVERNMENT_CONTEXT, GROUP_LINE_ID, INVOICED_VAL_IN_NTFN, INVOICE_ADJUSTMENT_FLAG, KANBAN_CARD_ID, MRC_ENCUMBERED_AMOUNT, MRC_RATE, MRC_RATE_DATE, MRC_UNENCUMBERED_AMOUNT, NONRECOVERABLE_TAX, OKE_CONTRACT_DELIVERABLE_ID, OKE_CONTRACT_LINE_ID, ORG_ID, PARTIAL_FUNDED_FLAG, PO_RELEASE_ID, PREVENT_ENCUMBRANCE_FLAG, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, PROJECT_ACCOUNTING_CONTEXT, PROJECT_ID, QUANTITY_BILLED, QUANTITY_CANCELLED, QUANTITY_DELIVERED, QUANTITY_FINANCED, QUANTITY_FUNDED, QUANTITY_ORDERED, QUANTITY_RECOUPED, RATE, RATE_DATE, RECOVERABLE_TAX, RECOVERY_RATE, REQUEST_ID, REQ_DISTRIBUTION_ID, REQ_HEADER_REFERENCE_NUM, REQ_LINE_REFERENCE_NUM, RETAINAGE_RELEASED_AMOUNT, RETAINAGE_WITHHELD_AMOUNT, SET_OF_BOOKS_ID, SOURCE_DISTRIBUTION_ID, TASK_ID, TAX_ATTRIBUTE_UPDATE_CODE, TAX_RECOVERY_OVERRIDE_FLAG, UDA_TEMPLATE_ID, UNENCUMBERED_AMOUNT, UNENCUMBERED_QUANTITY, USSGL_TRANSACTION_CODE, VARIANCE_ACCOUNT_ID, WIP_ENTITY_ID, WIP_LINE_ID, WIP_OPERATION_SEQ_NUM, WIP_REPETITIVE_SCHEDULE_ID, WIP_RESOURCE_SEQ_NUM, CHANGE_STATUS ) )

View Text - HTML Formatted

SELECT PO_HEADER_ID PK1_VALUE
, -1 PK2_VALUE
, PO_LINE_ID PK3_VALUE
, LINE_LOCATION_ID PK4_VALUE
, PO_DISTRIBUTION_ID PK5_VALUE
, COL_NAME
, COL_VALUE
, REVISION_NUM
FROM ( SELECT PD.PO_HEADER_ID
, -1 DRAFT_ID
, PD.PO_LINE_ID
, PD.LINE_LOCATION_ID
, PD.PO_DISTRIBUTION_ID
, TO_CHAR(PD.ACCRUAL_ACCOUNT_ID) ACCRUAL_ACCOUNT_ID
, PD.ACCRUED_FLAG
, PD.ACCRUE_ON_RECEIPT_FLAG
, PD.ACRN
, TO_CHAR(PD.AMOUNT_BILLED) AMOUNT_BILLED
, TO_CHAR(PD.AMOUNT_CANCELLED) AMOUNT_CANCELLED
, TO_CHAR(PD.AMOUNT_DELIVERED) AMOUNT_DELIVERED
, TO_CHAR(PD.AMOUNT_FINANCED) AMOUNT_FINANCED
, TO_CHAR(PD.AMOUNT_FUNDED) AMOUNT_FUNDED
, TO_CHAR(PD.AMOUNT_ORDERED) AMOUNT_ORDERED
, TO_CHAR(PD.AMOUNT_RECOUPED) AMOUNT_RECOUPED
, TO_CHAR(PD.AMOUNT_TO_ENCUMBER) AMOUNT_TO_ENCUMBER
, PD.ATTRIBUTE1
, PD.ATTRIBUTE10
, PD.ATTRIBUTE11
, PD.ATTRIBUTE12
, PD.ATTRIBUTE13
, PD.ATTRIBUTE14
, PD.ATTRIBUTE15
, PD.ATTRIBUTE2
, PD.ATTRIBUTE3
, PD.ATTRIBUTE4
, PD.ATTRIBUTE5
, PD.ATTRIBUTE6
, PD.ATTRIBUTE7
, PD.ATTRIBUTE8
, PD.ATTRIBUTE9
, PD.ATTRIBUTE_CATEGORY
, TO_CHAR(PD.AWARD_ID) AWARD_ID
, TO_CHAR(PD.BOM_RESOURCE_ID) BOM_RESOURCE_ID
, TO_CHAR(PD.BUDGET_ACCOUNT_ID) BUDGET_ACCOUNT_ID
, TO_CHAR(PD.CHANGE_IN_FUNDED_VALUE) CHANGE_IN_FUNDED_VALUE
, PD.CLM_AGENCY_ACCT_IDENTIFIER
, PD.CLM_DEFENCE_FUNDING
, PD.CLM_FMS_CASE_NUMBER
, PD.CLM_MISC_LOA
, TO_CHAR(PD.CLM_PAYMENT_SEQUENCE_NUM) CLM_PAYMENT_SEQUENCE_NUM
, TO_CHAR(PD.CODE_COMBINATION_ID) CODE_COMBINATION_ID
, TO_CHAR(PD.DELIVER_TO_LOCATION_ID) DELIVER_TO_LOCATION_ID
, TO_CHAR(PD.DELIVER_TO_PERSON_ID) DELIVER_TO_PERSON_ID
, PD.DESTINATION_CONTEXT
, TO_CHAR(PD.DESTINATION_ORGANIZATION_ID) DESTINATION_ORGANIZATION_ID
, PD.DESTINATION_SUBINVENTORY
, PD.DESTINATION_TYPE_CODE
, TO_CHAR(PD.DEST_CHARGE_ACCOUNT_ID) DEST_CHARGE_ACCOUNT_ID
, TO_CHAR(PD.DEST_VARIANCE_ACCOUNT_ID) DEST_VARIANCE_ACCOUNT_ID
, TO_CHAR(PD.DISTRIBUTION_NUM) DISTRIBUTION_NUM
, PD.DISTRIBUTION_TYPE
, TO_CHAR(PD.ENCUMBERED_AMOUNT) ENCUMBERED_AMOUNT
, PD.ENCUMBERED_FLAG
, PD.END_ITEM_UNIT_NUMBER
, TO_CHAR(PD.EXPENDITURE_ITEM_DATE) EXPENDITURE_ITEM_DATE
, TO_CHAR(PD.EXPENDITURE_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID
, PD.EXPENDITURE_TYPE
, PD.FAILED_FUNDS_LOOKUP_CODE
, TO_CHAR(PD.FUNDED_VALUE) FUNDED_VALUE
, TO_CHAR(PD.GL_CANCELLED_DATE) GL_CANCELLED_DATE
, TO_CHAR(PD.GL_CLOSED_DATE) GL_CLOSED_DATE
, TO_CHAR(PD.GL_ENCUMBERED_DATE) GL_ENCUMBERED_DATE
, PD.GL_ENCUMBERED_PERIOD_NAME
, PD.GOVERNMENT_CONTEXT
, TO_CHAR(PD.GROUP_LINE_ID) GROUP_LINE_ID
, TO_CHAR(PD.INVOICED_VAL_IN_NTFN) INVOICED_VAL_IN_NTFN
, PD.INVOICE_ADJUSTMENT_FLAG
, TO_CHAR(PD.KANBAN_CARD_ID) KANBAN_CARD_ID
, PD.MRC_ENCUMBERED_AMOUNT
, PD.MRC_RATE
, PD.MRC_RATE_DATE
, PD.MRC_UNENCUMBERED_AMOUNT
, TO_CHAR(PD.NONRECOVERABLE_TAX) NONRECOVERABLE_TAX
, TO_CHAR(PD.OKE_CONTRACT_DELIVERABLE_ID) OKE_CONTRACT_DELIVERABLE_ID
, TO_CHAR(PD.OKE_CONTRACT_LINE_ID) OKE_CONTRACT_LINE_ID
, TO_CHAR(PD.ORG_ID) ORG_ID
, PD.PARTIAL_FUNDED_FLAG
, TO_CHAR(PD.PO_RELEASE_ID) PO_RELEASE_ID
, PD.PREVENT_ENCUMBRANCE_FLAG
, TO_CHAR(PD.PROGRAM_APPLICATION_ID) PROGRAM_APPLICATION_ID
, TO_CHAR(PD.PROGRAM_ID) PROGRAM_ID
, TO_CHAR(PD.PROGRAM_UPDATE_DATE) PROGRAM_UPDATE_DATE
, PD.PROJECT_ACCOUNTING_CONTEXT
, TO_CHAR(PD.PROJECT_ID) PROJECT_ID
, TO_CHAR(PD.QUANTITY_BILLED) QUANTITY_BILLED
, TO_CHAR(PD.QUANTITY_CANCELLED) QUANTITY_CANCELLED
, TO_CHAR(PD.QUANTITY_DELIVERED) QUANTITY_DELIVERED
, TO_CHAR(PD.QUANTITY_FINANCED) QUANTITY_FINANCED
, TO_CHAR(PD.QUANTITY_FUNDED) QUANTITY_FUNDED
, TO_CHAR(PD.QUANTITY_ORDERED) QUANTITY_ORDERED
, TO_CHAR(PD.QUANTITY_RECOUPED) QUANTITY_RECOUPED
, TO_CHAR(PD.RATE) RATE
, TO_CHAR(PD.RATE_DATE) RATE_DATE
, TO_CHAR(PD.RECOVERABLE_TAX) RECOVERABLE_TAX
, TO_CHAR(PD.RECOVERY_RATE) RECOVERY_RATE
, TO_CHAR(PD.REQUEST_ID) REQUEST_ID
, TO_CHAR(PD.REQ_DISTRIBUTION_ID) REQ_DISTRIBUTION_ID
, PD.REQ_HEADER_REFERENCE_NUM
, PD.REQ_LINE_REFERENCE_NUM
, TO_CHAR(PD.RETAINAGE_RELEASED_AMOUNT) RETAINAGE_RELEASED_AMOUNT
, TO_CHAR(PD.RETAINAGE_WITHHELD_AMOUNT) RETAINAGE_WITHHELD_AMOUNT
, TO_CHAR(PD.REVISION_NUM) REVISION_NUM
, TO_CHAR(PD.SET_OF_BOOKS_ID) SET_OF_BOOKS_ID
, TO_CHAR(PD.SOURCE_DISTRIBUTION_ID) SOURCE_DISTRIBUTION_ID
, TO_CHAR(PD.TASK_ID) TASK_ID
, PD.TAX_ATTRIBUTE_UPDATE_CODE
, PD.TAX_RECOVERY_OVERRIDE_FLAG
, TO_CHAR(PD.UDA_TEMPLATE_ID) UDA_TEMPLATE_ID
, TO_CHAR(PD.UNENCUMBERED_AMOUNT) UNENCUMBERED_AMOUNT
, TO_CHAR(PD.UNENCUMBERED_QUANTITY) UNENCUMBERED_QUANTITY
, PD.USSGL_TRANSACTION_CODE
, TO_CHAR(PD.VARIANCE_ACCOUNT_ID) VARIANCE_ACCOUNT_ID
, TO_CHAR(PD.WIP_ENTITY_ID) WIP_ENTITY_ID
, TO_CHAR(PD.WIP_LINE_ID) WIP_LINE_ID
, TO_CHAR(PD.WIP_OPERATION_SEQ_NUM) WIP_OPERATION_SEQ_NUM
, TO_CHAR(PD.WIP_REPETITIVE_SCHEDULE_ID) WIP_REPETITIVE_SCHEDULE_ID
, TO_CHAR(PD.WIP_RESOURCE_SEQ_NUM) WIP_RESOURCE_SEQ_NUM
, NULL AS CHANGE_STATUS
FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PD
WHERE PO_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1) PO_DISTRIBUTION_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( ACCRUAL_ACCOUNT_ID
, ACCRUED_FLAG
, ACCRUE_ON_RECEIPT_FLAG
, ACRN
, AMOUNT_BILLED
, AMOUNT_CANCELLED
, AMOUNT_DELIVERED
, AMOUNT_FINANCED
, AMOUNT_FUNDED
, AMOUNT_ORDERED
, AMOUNT_RECOUPED
, AMOUNT_TO_ENCUMBER
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, AWARD_ID
, BOM_RESOURCE_ID
, BUDGET_ACCOUNT_ID
, CHANGE_IN_FUNDED_VALUE
, CLM_AGENCY_ACCT_IDENTIFIER
, CLM_DEFENCE_FUNDING
, CLM_FMS_CASE_NUMBER
, CLM_MISC_LOA
, CLM_PAYMENT_SEQUENCE_NUM
, CODE_COMBINATION_ID
, DELIVER_TO_LOCATION_ID
, DELIVER_TO_PERSON_ID
, DESTINATION_CONTEXT
, DESTINATION_ORGANIZATION_ID
, DESTINATION_SUBINVENTORY
, DESTINATION_TYPE_CODE
, DEST_CHARGE_ACCOUNT_ID
, DEST_VARIANCE_ACCOUNT_ID
, DISTRIBUTION_NUM
, DISTRIBUTION_TYPE
, ENCUMBERED_AMOUNT
, ENCUMBERED_FLAG
, END_ITEM_UNIT_NUMBER
, EXPENDITURE_ITEM_DATE
, EXPENDITURE_ORGANIZATION_ID
, EXPENDITURE_TYPE
, FAILED_FUNDS_LOOKUP_CODE
, FUNDED_VALUE
, GL_CANCELLED_DATE
, GL_CLOSED_DATE
, GL_ENCUMBERED_DATE
, GL_ENCUMBERED_PERIOD_NAME
, GOVERNMENT_CONTEXT
, GROUP_LINE_ID
, INVOICED_VAL_IN_NTFN
, INVOICE_ADJUSTMENT_FLAG
, KANBAN_CARD_ID
, MRC_ENCUMBERED_AMOUNT
, MRC_RATE
, MRC_RATE_DATE
, MRC_UNENCUMBERED_AMOUNT
, NONRECOVERABLE_TAX
, OKE_CONTRACT_DELIVERABLE_ID
, OKE_CONTRACT_LINE_ID
, ORG_ID
, PARTIAL_FUNDED_FLAG
, PO_RELEASE_ID
, PREVENT_ENCUMBRANCE_FLAG
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PROJECT_ACCOUNTING_CONTEXT
, PROJECT_ID
, QUANTITY_BILLED
, QUANTITY_CANCELLED
, QUANTITY_DELIVERED
, QUANTITY_FINANCED
, QUANTITY_FUNDED
, QUANTITY_ORDERED
, QUANTITY_RECOUPED
, RATE
, RATE_DATE
, RECOVERABLE_TAX
, RECOVERY_RATE
, REQUEST_ID
, REQ_DISTRIBUTION_ID
, REQ_HEADER_REFERENCE_NUM
, REQ_LINE_REFERENCE_NUM
, RETAINAGE_RELEASED_AMOUNT
, RETAINAGE_WITHHELD_AMOUNT
, SET_OF_BOOKS_ID
, SOURCE_DISTRIBUTION_ID
, TASK_ID
, TAX_ATTRIBUTE_UPDATE_CODE
, TAX_RECOVERY_OVERRIDE_FLAG
, UDA_TEMPLATE_ID
, UNENCUMBERED_AMOUNT
, UNENCUMBERED_QUANTITY
, USSGL_TRANSACTION_CODE
, VARIANCE_ACCOUNT_ID
, WIP_ENTITY_ID
, WIP_LINE_ID
, WIP_OPERATION_SEQ_NUM
, WIP_REPETITIVE_SCHEDULE_ID
, WIP_RESOURCE_SEQ_NUM
, CHANGE_STATUS ) )