ECC Projects, Projects Costing: Transaction Source

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Data set for information about pending source transactions
Dataset Key: pa-ds-cost-source-imp
Query Procedure: PA_ECC_COST_PROJ_PKG.LOAD_FULL_TXN_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
Run ECC Projects, Projects Costing: Transaction Source and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
 
select * from (
SELECT
 RECORD_TYPE           ,
 ECC_SPEC_ID           ,
 TRANSACTION_ID        ,
 DISTRIBUTION_ID       ,
 SOURCE_CODE		   ,
 SOURCE				   ,
 TRANSACTION_SOURCE    ,
 BATCH_NAME ,
 DOCUMENT_DISTRIBUTION_TYPE	,
 DOCUMENT_TYPE		,
 PROJECT_ID            ,
 PROJECT_NAME          ,
 PROJECT_NUMBER        ,
 PROJECT_TYPE          ,
 PROJECT_TYPE_CLASS_CODE	,
 PROJECT_TYPE_CLASS		,
 TASK_ID               ,
 TASK_NUMBER           ,
 TASK_NAME             ,
EXPENDITURE_ITEM_DATE ,
EXPENDITURE_TYPE       ,
QUANTITY               ,
UNIT_OF_MEASURE        ,
 UNIT_OF_MEASURE_M	   ,
 AMOUNT        ,
 TRANSACTION_REFERENCE,
 TRANSACTION_STATUS_CODE,
 TRANSACTION_STATUS,
 EXPENDITURE_ORGANIZATION_ID,
 EXPENDITURE_ORG_NAME,
 OVERRIDE_TO_ORGANIZATION_NAME,
 PREDEFINED_FLAG,
 COSTED_FLAG          ,
 ALLOW_ADJUSTMENTS_FLAG ,
 GL_ACCOUNTED_FLAG	 ,
 COST_BURDENED_FLAG	 ,
 ALLOW_BURDEN_FLAG	 ,
 DEBIT_ACCOUNT 	 ,
 CREDIT_ACCOUNT 	 ,
 WORK_TYPE 			,
 INVENTORY_ITEM     ,
 LOCATION_CODE      ,
 ASSIGNMENT_NAME    ,
 EMPLOYEE_NUMBER    ,
 EMPLOYEE_NAME      ,
 WIP_RESOURCE       ,
 SUPPLIER_NAME      ,
 PO_NUMBER  		,
 PO_LINE_NUMBER 	,
 PO_DIST_LINE_NUMBER,
 RECEIPT_NUMBER 	,
 RECEIPT_LINE_NUMBER,
 INVOICE_NO 		,
 INVOICE_LINE_NUMBER,
 INV_DIST_LINE_NUMBER,
 ALERT_TYPE,
 ALERT_TEXT,
 LANGUAGE,
 TOP_TASK_ID,
 TOP_TASK_NUMBER,
 TOP_TASK_NAME,
 FUNDING_TASK_ID,
 BILLPROJ_DISPLAY_FLAG,
 PROJECT_ID||':'||FUNDING_TASK_ID DS_ASSOC_PRJ_TSK_ATTR,
 PROJECT_ID||':'||FUNDING_TASK_ID||':'||PERIOD_NAME DS_ASSOC_PRJ_TSK_PER_ATTR,
 PERIOD_NAME,
 GL_DATE,
 GL_PERIOD_NAME_SRC,
 PERIOD_DISP_FLAG,
 ACCT_AMOUNT,
 ACCT_CURRENCY,
 TRX_SOURCE_NAME_CODE,
  CASE WHEN TRX_SOURCE_NAME_CODE IN ('PO RECEIPT', 'PO RECEIPT NRTAX', 'PO RECEIPT PRICE ADJ')
           THEN 'PEND_PO'
      WHEN TRX_SOURCE_NAME_CODE IN ('AP NRTAX', 'AP VARIANCE', 'INTERPROJECT_AP_INVOICES', 'AP EXPENSE', 'AP INVOICE', 'AP DISCOUNTS')
	       THEN 'PEND_VI'
      WHEN TRX_SOURCE_NAME_CODE <> 'ORACLE TIME AND LABOR'
	       THEN 'PEND_SRC'
	  ELSE NULL
 END TRX_SRC_TYPE_CODE,
 CASE WHEN TRX_SOURCE_NAME_CODE IN ('PO RECEIPT', 'PO RECEIPT NRTAX', 'PO RECEIPT PRICE ADJ')
           THEN pa_utils.get_lookup_values('PA_ECC_SRC_TYPE_CODE', 'PEND_PO', language)
      WHEN TRX_SOURCE_NAME_CODE IN ('AP NRTAX', 'AP VARIANCE', 'INTERPROJECT_AP_INVOICES', 'AP EXPENSE', 'AP INVOICE', 'AP DISCOUNTS')
	       THEN pa_utils.get_lookup_values('PA_ECC_SRC_TYPE_CODE', 'PEND_VI', language)
      WHEN TRX_SOURCE_NAME_CODE <> 'ORACLE TIME AND LABOR'
	       THEN pa_utils.get_lookup_values('PA_ECC_SRC_TYPE_CODE', 'PEND_SRC', language)
	  ELSE NULL
 END TRX_SRC_TYPE
FROM pa_ecc_cost_txns where language in ('US') ) PIVOT(
max(SOURCE) as SOURCE,
max(PROJECT_TYPE_CLASS) as PROJECT_TYPE_CLASS,
max(EXPENDITURE_ORG_NAME) as EXPENDITURE_ORG_NAME,
max(UNIT_OF_MEASURE_M) as UNIT_OF_MEASURE_M,
max(TRANSACTION_STATUS) as TRANSACTION_STATUS,
max(PREDEFINED_FLAG) as PREDEFINED_FLAG,
max(COSTED_FLAG) as COSTED_FLAG,
max(ALLOW_ADJUSTMENTS_FLAG) as ALLOW_ADJUSTMENTS_FLAG,
max(GL_ACCOUNTED_FLAG) as GL_ACCOUNTED_FLAG,
max(COST_BURDENED_FLAG) as COST_BURDENED_FLAG,
max(ALLOW_BURDEN_FLAG) as ALLOW_BURDEN_FLAG,
max(ALERT_TYPE) as ALERT_TYPE,
max(ALERT_TEXT) as ALERT_TEXT,
max(TRX_SRC_TYPE) as TRX_SRC_TYPE
for LANGUAGE in ('US' "US")) 
) x
where
2=2