VIEW: APPS.CST_PM_PENDING_TXN_V

Source

View Text - Preformatted

SELECT cpmtv.project_id, cpmtv.task_id, cpmtv.organization_id, cpmtv.transaction_id, to_char(NULL), cpmtv.transaction_date, 1, pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'R'), pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'A'), 'CCNR' exception_code FROM cst_pm_matl_txn_v cpmtv WHERE cpmtv.pm_cost_collected = 'N' AND cpmtv.project_id IS NOT NULL UNION SELECT cpmtv.to_project_id, cpmtv.to_task_id, cpmtv.organization_id, cpmtv.transaction_id, to_char(NULL), cpmtv.transaction_date, 1, pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'R '), pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'A '), 'CCNR' exception_code FROM cst_pm_matl_txn_v cpmtv WHERE cpmtv.pm_cost_collected = 'N' AND cpmtv.transaction_action_id IN (2,3) AND cpmtv.to_project_id IS NOT NULL UNION SELECT cpmtv.source_project_id, cpmtv.source_task_id, cpmtv.organization_id, cpmtv.transaction_id, to_char(NULL), cpmtv.transaction_date, 1, pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'R '), pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'A '), 'CCNR' exception_code FROM cst_pm_matl_txn_v cpmtv WHERE cpmtv.pm_cost_collected = 'N' AND ( (cpmtv.transaction_action_id IN (1,27,31,32,33,34) AND cpmtv.transaction_source_type_id = 5) OR (cpmtv.type_class = 1) ) AND cpmtv.source_project_id IS NOT NULL UNION SELECT mmt.project_id, mmt.task_id, mmt.organization_id, mmt.transaction_id, to_char(NULL), mmt.transaction_date, 1, pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'R'), pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'A'), 'CCERR' exception_code FROM mtl_material_transactions mmt WHERE mmt.pm_cost_collected = 'E' AND mmt.project_id IS NOT NULL AND mmt.actual_cost <> 0 UNION SELECT mmt.source_project_id, mmt.source_task_id, mmt.organization_id, mmt.transaction_id, to_char(NULL), mmt.transaction_date, 1, pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'R'), pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'A'), 'CCERR' exception_code FROM mtl_material_transactions mmt WHERE mmt.pm_cost_collected = 'E' AND mmt.source_project_id IS NOT NULL AND mmt.actual_cost <> 0 UNION SELECT mmt.to_project_id, mmt.to_task_id, mmt.organization_id, mmt.transaction_id, to_char(NULL), mmt.transaction_date, 1, pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'R'), pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'A'), 'CCERR' exception_code FROM mtl_material_transactions mmt WHERE mmt.pm_cost_collected = 'E' AND mmt.to_project_id IS NOT NULL AND mmt.actual_cost <> 0 UNION SELECT wt.project_id, wt.task_id, wt.organization_id, wt.transaction_id, to_char(NULL), wt.transaction_date, 2, pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'R '), pa_exception_reasons_pub.get_exception_text('MFG_EXCP','CCNR',NULL,'A '), 'CCNR' exception_code FROM wip_transactions wt WHERE wt.pm_cost_collected = 'N' AND wt.transaction_type in (1,2,3) AND wt.project_id is NOT NULL AND EXISTS ( SELECT null FROM wip_transaction_accounts wta WHERE wta.transaction_id = wt.transaction_id ) UNION SELECT wt.project_id, wt.task_id, wt.organization_id, wt.transaction_id, to_char(NULL), wt.transaction_date, 2, pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'R'), pa_exception_reasons_pub.get_exception_text('MFG_EXCP', 'CCERR',NULL,'A'), 'CCERR' exception_code FROM wip_transactions wt WHERE wt.pm_cost_collected = 'E'

View Text - HTML Formatted

SELECT CPMTV.PROJECT_ID
, CPMTV.TASK_ID
, CPMTV.ORGANIZATION_ID
, CPMTV.TRANSACTION_ID
, TO_CHAR(NULL)
, CPMTV.TRANSACTION_DATE
, 1
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'R')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'A')
, 'CCNR' EXCEPTION_CODE
FROM CST_PM_MATL_TXN_V CPMTV
WHERE CPMTV.PM_COST_COLLECTED = 'N'
AND CPMTV.PROJECT_ID IS NOT NULL UNION SELECT CPMTV.TO_PROJECT_ID
, CPMTV.TO_TASK_ID
, CPMTV.ORGANIZATION_ID
, CPMTV.TRANSACTION_ID
, TO_CHAR(NULL)
, CPMTV.TRANSACTION_DATE
, 1
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'R ')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'A ')
, 'CCNR' EXCEPTION_CODE
FROM CST_PM_MATL_TXN_V CPMTV
WHERE CPMTV.PM_COST_COLLECTED = 'N'
AND CPMTV.TRANSACTION_ACTION_ID IN (2
, 3)
AND CPMTV.TO_PROJECT_ID IS NOT NULL UNION SELECT CPMTV.SOURCE_PROJECT_ID
, CPMTV.SOURCE_TASK_ID
, CPMTV.ORGANIZATION_ID
, CPMTV.TRANSACTION_ID
, TO_CHAR(NULL)
, CPMTV.TRANSACTION_DATE
, 1
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'R ')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'A ')
, 'CCNR' EXCEPTION_CODE
FROM CST_PM_MATL_TXN_V CPMTV
WHERE CPMTV.PM_COST_COLLECTED = 'N'
AND ( (CPMTV.TRANSACTION_ACTION_ID IN (1
, 27
, 31
, 32
, 33
, 34)
AND CPMTV.TRANSACTION_SOURCE_TYPE_ID = 5) OR (CPMTV.TYPE_CLASS = 1) )
AND CPMTV.SOURCE_PROJECT_ID IS NOT NULL UNION SELECT MMT.PROJECT_ID
, MMT.TASK_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSACTION_ID
, TO_CHAR(NULL)
, MMT.TRANSACTION_DATE
, 1
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'R')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'A')
, 'CCERR' EXCEPTION_CODE
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.PM_COST_COLLECTED = 'E'
AND MMT.PROJECT_ID IS NOT NULL
AND MMT.ACTUAL_COST <> 0 UNION SELECT MMT.SOURCE_PROJECT_ID
, MMT.SOURCE_TASK_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSACTION_ID
, TO_CHAR(NULL)
, MMT.TRANSACTION_DATE
, 1
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'R')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'A')
, 'CCERR' EXCEPTION_CODE
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.PM_COST_COLLECTED = 'E'
AND MMT.SOURCE_PROJECT_ID IS NOT NULL
AND MMT.ACTUAL_COST <> 0 UNION SELECT MMT.TO_PROJECT_ID
, MMT.TO_TASK_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSACTION_ID
, TO_CHAR(NULL)
, MMT.TRANSACTION_DATE
, 1
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'R')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'A')
, 'CCERR' EXCEPTION_CODE
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.PM_COST_COLLECTED = 'E'
AND MMT.TO_PROJECT_ID IS NOT NULL
AND MMT.ACTUAL_COST <> 0 UNION SELECT WT.PROJECT_ID
, WT.TASK_ID
, WT.ORGANIZATION_ID
, WT.TRANSACTION_ID
, TO_CHAR(NULL)
, WT.TRANSACTION_DATE
, 2
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'R ')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCNR'
, NULL
, 'A ')
, 'CCNR' EXCEPTION_CODE
FROM WIP_TRANSACTIONS WT
WHERE WT.PM_COST_COLLECTED = 'N'
AND WT.TRANSACTION_TYPE IN (1
, 2
, 3)
AND WT.PROJECT_ID IS NOT NULL
AND EXISTS ( SELECT NULL
FROM WIP_TRANSACTION_ACCOUNTS WTA
WHERE WTA.TRANSACTION_ID = WT.TRANSACTION_ID ) UNION SELECT WT.PROJECT_ID
, WT.TASK_ID
, WT.ORGANIZATION_ID
, WT.TRANSACTION_ID
, TO_CHAR(NULL)
, WT.TRANSACTION_DATE
, 2
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'R')
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('MFG_EXCP'
, 'CCERR'
, NULL
, 'A')
, 'CCERR' EXCEPTION_CODE
FROM WIP_TRANSACTIONS WT
WHERE WT.PM_COST_COLLECTED = 'E'