[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp, --File.Sql.39 bug 4440895
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_COMMITMENT_AMOUNTS.GEN_COMMITMENT_AMOUNTS';
SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
P.RESOURCE_ASSIGNMENT_ID
,TMP.TXN_TASK_ID
,TMP.RESOURCE_LIST_MEMBER_ID
,DECODE(c_multi_curr_flag, 'Y',
CT.DENOM_CURRENCY_CODE,
CT.PROJECT_CURRENCY_CODE) currency_code
,MAX(P.planning_start_date) planning_start_date
,MAX(P.planning_end_date) planning_end_date
,MIN(NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE))
,MAX(NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE))
,SUM(DECODE(c_multi_curr_flag, 'Y',
CT.DENOM_RAW_COST,
CT.PROJ_RAW_COST)) tot_raw_cost
,SUM(DECODE(c_multi_curr_flag, 'Y',
CT.DENOM_BURDENED_COST,
CT.PROJ_BURDENED_COST)) tot_burdened_cost
,SUM(CT.proj_raw_cost)
,SUM(CT.proj_burdened_cost)
,SUM(CT.acct_raw_cost)
,SUM(CT.acct_burdened_cost)
,SUM(NVL(CT.TOT_CMT_QUANTITY,
DECODE(c_multi_curr_flag, 'Y',
CT.DENOM_RAW_COST,
CT.PROJ_RAW_COST)) ) tot_quantity
FROM PA_COMMITMENT_TXNS CT,
PA_RES_LIST_MAP_TMP4 TMP,
PA_RESOURCE_ASSIGNMENTS P
WHERE TMP.TXN_SOURCE_ID = CT.CMT_LINE_ID
AND CT.PROJECT_ID = P_PROJECT_ID
AND NVL(CT.generation_error_flag,'N') = 'N'
AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
GROUP BY P.RESOURCE_ASSIGNMENT_ID,
TMP.TXN_TASK_ID
,TMP.RESOURCE_LIST_MEMBER_ID
,DECODE(c_multi_curr_flag, 'Y',
CT.DENOM_CURRENCY_CODE,
CT.PROJECT_CURRENCY_CODE);
l_DELETED_RES_ASG_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
DELETE FROM PA_RES_LIST_MAP_TMP1;
SELECT RESOURCE_CLASS_ID
INTO l_resource_class_id
FROM PA_RESOURCE_CLASSES_B
WHERE RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS';
SELECT ct.CMT_LINE_ID,
'OPEN_COMMITMENTS',
ct.ORGANIZATION_ID,
ct.VENDOR_ID,
ct.EXPENDITURE_TYPE,
ct.REVENUE_CATEGORY,
ct.TASK_ID
,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
,SYSTEM_LINKAGE_FUNCTION
,INVENTORY_ITEM_ID
,DECODE(EXPENDITURE_TYPE,null,
DECODE(EXPENDITURE_CATEGORY,null,NULL,
'EXPENDITURE_CATEGORY'),'EXPENDITURE_TYPE'),
NVL(ct.RESOURCE_CLASS,'FINANCIAL_ELEMENTS')
BULK COLLECT
INTO l_TXN_SOURCE_ID_tab,
l_TXN_SOURCE_TYPE_CODE_tab,
l_ORGANIZATION_ID_tab,
l_VENDOR_ID_tab,
l_EXPENDITURE_TYPE_tab,
l_REVENUE_CATEGORY_CODE_tab,
l_TXN_TASK_ID_tab,
l_TXN_PLAN_START_DATE_tab,
l_TXN_PLAN_END_DATE_tab,
l_SYS_LINK_FUNCTION_tab,
l_INVENTORY_ITEM_ID_tab,
l_FC_RES_TYPE_CODE_tab,
l_RESOURCE_CLASS_CODE_tab
FROM PA_COMMITMENT_TXNS ct, PA_RESOURCE_CLASSES_B rc
WHERE ct.PROJECT_ID = P_PROJECT_ID
AND NVL(CT.generation_error_flag,'N') = 'N'
AND ct.RESOURCE_CLASS = rc.RESOURCE_CLASS_CODE(+);
SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
count(*) INTO l_count1
FROM PA_RES_LIST_MAP_TMP4
WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
(p_msg => 'Before calling pa_fp_gen_budget_amt_pub.update_res_asg',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_STRU_SHARING_CODE => l_stru_sharing_code,
P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
P_FP_COLS_REC => P_FP_COLS_REC,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
(p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.update_res_asg'
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
INSERT INTO PA_FP_CALC_AMT_TMP1(
RESOURCE_ASSIGNMENT_ID,
BUDGET_VERSION_ID,
PROJECT_ID,
TASK_ID,
RESOURCE_LIST_MEMBER_ID,
UNIT_OF_MEASURE,
PLANNING_START_DATE,
PLANNING_END_DATE,
FC_RES_TYPE_CODE,
RESOURCE_CLASS_CODE,
ORGANIZATION_ID,
JOB_ID,
PERSON_ID,
EXPENDITURE_TYPE,
EXPENDITURE_CATEGORY,
EVENT_TYPE,
PROJECT_ROLE_ID,
PERSON_TYPE_CODE,
NON_LABOR_RESOURCE,
BOM_RESOURCE_ID,
INVENTORY_ITEM_ID,
ITEM_CATEGORY_ID,
TRANSACTION_SOURCE_CODE,
MFC_COST_TYPE_ID,
INCURRED_BY_RES_FLAG,
RATE_BASED_FLAG,
NAMED_ROLE,
ETC_METHOD_CODE,
TARGET_RLM_ID,
MAPPED_FIN_TASK_ID)
SELECT /*+ leading(tmp4) */ -- SQL Repository Bug 4884824; SQL ID 14901250.
INSERT INTO PA_FP_CALC_AMT_TMP2(
TARGET_RES_ASG_ID
, TXN_CURRENCY_CODE
, TOTAL_PLAN_QUANTITY
, TOTAL_TXN_RAW_COST
, TOTAL_TXN_BURDENED_COST
, TOTAL_PC_RAW_COST
, TOTAL_PC_BURDENED_COST
, TOTAL_PFC_RAW_COST
, TOTAL_PFC_BURDENED_COST
--, TARGET_RES_ASG_ID
,TRANSACTION_SOURCE_CODE
)
VALUES(l_res_asg_id(i),
l_currency_code(i),
l_quantity_sum_tab(i),
l_raw_cost_sum(i),
l_burdened_cost_sum(i),
l_pc_raw_cost_sum(i),
l_pc_burdened_cost_sum(i),
l_pfc_raw_cost_sum(i),
l_pfc_burdened_cost_sum(i),
'OPEN_COMMITMENTS'
);