[Home] [Help]
39: made changes in change_reason derivation. This is done as part of merging the MRUP3 changes
40: done in 11i into R12.
41: */
42: CURSOR inf_tbl_data_csr
43: (c_run_id pa_fp_webadi_upload_inf.run_id%TYPE,
44: c_allow_qty_flag VARCHAR2,
45: c_allow_raw_cost_flag VARCHAR2,
46: c_allow_burd_cost_flag VARCHAR2,
47: c_allow_revenue_flag VARCHAR2,
189: DECODE (inf.prd50, g_hidden_col_num, NULL, DECODE(inf.prd50, NULL, l_fnd_miss_num, inf.prd50)) prd50,
190: DECODE (inf.prd51, g_hidden_col_num, NULL, DECODE(inf.prd51, NULL, l_fnd_miss_num, inf.prd51)) prd51,
191: DECODE (inf.prd52, g_hidden_col_num, NULL, DECODE(inf.prd52, NULL, l_fnd_miss_num, inf.prd52)) prd52,
192: DECODE (inf.sd_prd, g_hidden_col_num, NULL, DECODE(inf.sd_prd, NULL, l_fnd_miss_num, inf.sd_prd)) sd_prd
193: FROM pa_fp_webadi_upload_inf inf
194: WHERE inf.run_id=c_run_id
195: AND Nvl(inf.val_error_flag, 'N') <> 'Y'
196: AND inf.val_error_code IS NULL
197: AND (inf.amount_type_name IS NULL OR
304: END log1;
305: */
306:
307: PROCEDURE delete_xface
308: ( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE
309: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
310: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
311: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
312: ,p_calling_module IN VARCHAR2 DEFAULT NULL
359: SELECT 'Y'
360: INTO l_preserve_rows_flag
361: FROM DUAL
362: WHERE EXISTS (SELECT 'X'
363: FROM pa_fp_webadi_upload_inf inf,
364: pa_budget_versions bv
365: WHERE inf.run_id = p_run_id
366: AND inf.budget_version_id = bv.budget_version_id
367: AND Nvl(inf.request_id, -99) = Nvl(bv.request_id, -99)
384: END;
385: END IF;
386:
387: IF l_preserve_rows_flag <> 'Y' THEN
388: DELETE FROM PA_FP_WEBADI_UPLOAD_INF
389: WHERE run_id = p_run_id;
390:
391: IF l_debug_mode = 'Y' THEN
392: l_del_record_count := SQL%ROWCOUNT;
389: WHERE run_id = p_run_id;
390:
391: IF l_debug_mode = 'Y' THEN
392: l_del_record_count := SQL%ROWCOUNT;
393: pa_debug.g_err_stage := ':Deleted '||l_del_record_count||' records from PA_FP_WEBADI_UPLOAD_INF';
394: pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
395: pa_debug.reset_curr_function;
396: END IF;
397: END IF;
438: -- Bug 3986129: FP.M Web ADI Dev changes. Added the followings:
439:
440: PROCEDURE validate_header_info
441: ( p_calling_mode IN VARCHAR2,
442: p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
443: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
444: p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
445: p_pm_rec_version_number IN pa_period_masks_b.record_version_number%TYPE,
446: p_submit_flag IN VARCHAR2,
585: SELECT 'Y'
586: INTO is_periodic_layout
587: FROM DUAL
588: WHERE EXISTS(SELECT 'X'
589: FROM pa_fp_webadi_upload_inf
590: WHERE run_id = p_run_id
591: AND amount_type_name IS NOT NULL
592: AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
593: EXCEPTION
801: pa_debug.g_err_stage := 'Updating tmp table with error code: PA_FP_WEBADI_VER_MODIFIED';
802: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
803: END IF;
804:
805: UPDATE pa_fp_webadi_upload_inf
806: SET val_error_flag = 'Y',
807: val_error_code = 'PA_FP_WEBADI_VER_MODIFIED',
808: err_task_name = nvl(task_name,'-98'),
809: err_task_number = nvl(task_number,'-98'),
823: pa_debug.g_err_stage := 'Updating tmp table with error code: PA_FP_WEBADI_TP_MODIFIED';
824: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
825: END IF;
826:
827: UPDATE pa_fp_webadi_upload_inf
828: SET val_error_flag = 'Y',
829: val_error_code = 'PA_FP_WEBADI_TP_MODIFIED',
830: err_task_name = nvl(task_name,'-98'),
831: err_task_number = nvl(task_number,'-98'),
845: pa_debug.g_err_stage := 'Updating tmp table with error code: PA_FP_WEBADI_NO_BV_MAINT_PVLG';
846: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
847: END IF;
848:
849: UPDATE pa_fp_webadi_upload_inf
850: SET val_error_flag = 'Y',
851: val_error_code = 'PA_FP_WEBADI_NO_BV_MAINT_PVLG',
852: err_task_name = nvl(task_name,'-98'),
853: err_task_number = nvl(task_number,'-98'),
867: pa_debug.g_err_stage := 'Updating tmp table with error code: ' || l_val_err_code;
868: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
869: END IF;
870:
871: UPDATE pa_fp_webadi_upload_inf
872: SET val_error_flag = 'Y',
873: val_error_code = l_val_err_code,
874: err_task_name = nvl(task_name,'-98'),
875: err_task_number = nvl(task_number,'-98'),
889: pa_debug.g_err_stage := 'Updating tmp table with error code: FP_WEBADI_CI_VER_NON_EDITABLE';
890: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
891: END IF;
892:
893: UPDATE pa_fp_webadi_upload_inf
894: SET val_error_flag = 'Y',
895: val_error_code = 'PA_FP_WA_CI_VER_NON_EDITABLE',
896: err_task_name = nvl(task_name,'-98'),
897: err_task_number = nvl(task_number,'-98'),
911: pa_debug.g_err_stage := 'Updating tmp table with error code: FP_WEBADI_BV_NOT_CURR_WORKING';
912: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
913: END IF;
914:
915: UPDATE pa_fp_webadi_upload_inf
916: SET val_error_flag = 'Y',
917: val_error_code = 'PA_FP_WA_BV_NOT_CURR_WORKING',
918: err_task_name = nvl(task_name,'-98'),
919: err_task_number = nvl(task_number,'-98'),
933: pa_debug.g_err_stage := 'Updating tmp table with error code: FP_WEBADI_CI_VER_SUBMIT_FLAG';
934: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
935: END IF;
936:
937: UPDATE pa_fp_webadi_upload_inf
938: SET val_error_flag = 'Y',
939: val_error_code = 'PA_FP_WA_CI_VER_SUBMIT_FLAG',
940: err_task_name = nvl(task_name,'-98'),
941: err_task_number = nvl(task_number,'-98'),
1005: -- other DML performed in the course of validation processing till the point
1006: -- a validation failure occurs.
1007:
1008: PROCEDURE process_errors
1009: ( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
1010: p_context IN VARCHAR2,
1011: p_periodic_flag IN VARCHAR2,
1012: p_error_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
1013: p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
1077: SELECT 'Y'
1078: INTO l_periodic_flag
1079: FROM DUAL
1080: WHERE EXISTS (SELECT 1
1081: FROM pa_fp_webadi_upload_inf
1082: WHERE run_id=p_run_id
1083: AND amount_type_name IS NOT NULL
1084: AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
1085:
1095:
1096:
1097: IF p_context IS NOT NULL THEN
1098: IF p_context = 'INV_TASK' THEN
1099: UPDATE pa_fp_webadi_upload_inf
1100: SET val_error_flag = 'Y',
1101: val_error_code = 'INVALID_TASK_INFO',
1102: err_task_name = nvl(task_name,'-98'),
1103: err_task_number = nvl(task_number,'-98'),
1107: AND task_id IS NULL
1108: AND val_error_flag IS NULL
1109: AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
1110: ELSIF p_context = 'INV_RESOURCE' THEN
1111: UPDATE pa_fp_webadi_upload_inf
1112: SET val_error_flag = 'Y',
1113: val_error_code = 'INVALID_RESOURCE_INFO',
1114: err_task_name = nvl(task_name,'-98'),
1115: err_task_number = nvl(task_number,'-98'),
1136: p_error_code_tbl.COUNT = p_rlm_id_tbl.COUNT AND
1137: p_error_code_tbl.COUNT = p_amount_type_tbl.COUNT AND
1138: p_error_code_tbl.COUNT = p_txn_curr_tbl.COUNT THEN
1139: FORALL i IN p_task_id_tbl.FIRST .. p_task_id_tbl.LAST
1140: UPDATE pa_fp_webadi_upload_inf
1141: SET val_error_flag = 'Y',
1142: val_error_code = p_error_code_tbl(i),
1143: err_task_name = nvl(task_name,'-98'),
1144: err_task_number = nvl(task_number,'-98'),
1165: IF p_error_code_tbl.COUNT = p_task_id_tbl.COUNT AND
1166: p_error_code_tbl.COUNT = p_rlm_id_tbl.COUNT AND
1167: p_error_code_tbl.COUNT = p_txn_curr_tbl.COUNT THEN
1168: FORALL i IN p_task_id_tbl.FIRST .. p_task_id_tbl.LAST
1169: UPDATE pa_fp_webadi_upload_inf inf
1170: SET inf.val_error_flag = 'Y',
1171: inf.val_error_code = p_error_code_tbl(i),
1172: inf.err_task_name = nvl(inf.task_name,'-98'),
1173: inf.err_task_number = nvl(inf.task_number,'-98'),
1263: * rec type returned from calculate api and then to call process_errors api
1264: * to report the errors in interface table.
1265: */
1266: PROCEDURE read_global_var_to_report_err
1267: ( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
1268: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1269: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1270: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1271: )
1534: -- these attribute can be defaulted to null values or version
1535: -- level attributes accordingly.
1536:
1537: FUNCTION conv_attributes_displayed
1538: (p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE)
1539: RETURN VARCHAR2
1540: IS
1541:
1542: l_debug_mode VARCHAR2(1);
1545: l_debug_level5 CONSTANT NUMBER := PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5;
1546:
1547: conv_attrb_displayed VARCHAR2(1);
1548:
1549: l_projfunc_rate_type pa_fp_webadi_upload_inf.projfunc_rate_type%TYPE;
1550: l_projfunc_rate_date_type pa_fp_webadi_upload_inf.projfunc_rate_date_type%TYPE;
1551: l_projfunc_rate_date pa_fp_webadi_upload_inf.projfunc_rate_date%TYPE;
1552: l_project_rate_type pa_fp_webadi_upload_inf.project_rate_type%TYPE;
1553: l_project_rate_date_type pa_fp_webadi_upload_inf.project_rate_date_type%TYPE;
1546:
1547: conv_attrb_displayed VARCHAR2(1);
1548:
1549: l_projfunc_rate_type pa_fp_webadi_upload_inf.projfunc_rate_type%TYPE;
1550: l_projfunc_rate_date_type pa_fp_webadi_upload_inf.projfunc_rate_date_type%TYPE;
1551: l_projfunc_rate_date pa_fp_webadi_upload_inf.projfunc_rate_date%TYPE;
1552: l_project_rate_type pa_fp_webadi_upload_inf.project_rate_type%TYPE;
1553: l_project_rate_date_type pa_fp_webadi_upload_inf.project_rate_date_type%TYPE;
1554: l_project_rate_date pa_fp_webadi_upload_inf.project_rate_date%TYPE;
1547: conv_attrb_displayed VARCHAR2(1);
1548:
1549: l_projfunc_rate_type pa_fp_webadi_upload_inf.projfunc_rate_type%TYPE;
1550: l_projfunc_rate_date_type pa_fp_webadi_upload_inf.projfunc_rate_date_type%TYPE;
1551: l_projfunc_rate_date pa_fp_webadi_upload_inf.projfunc_rate_date%TYPE;
1552: l_project_rate_type pa_fp_webadi_upload_inf.project_rate_type%TYPE;
1553: l_project_rate_date_type pa_fp_webadi_upload_inf.project_rate_date_type%TYPE;
1554: l_project_rate_date pa_fp_webadi_upload_inf.project_rate_date%TYPE;
1555:
1548:
1549: l_projfunc_rate_type pa_fp_webadi_upload_inf.projfunc_rate_type%TYPE;
1550: l_projfunc_rate_date_type pa_fp_webadi_upload_inf.projfunc_rate_date_type%TYPE;
1551: l_projfunc_rate_date pa_fp_webadi_upload_inf.projfunc_rate_date%TYPE;
1552: l_project_rate_type pa_fp_webadi_upload_inf.project_rate_type%TYPE;
1553: l_project_rate_date_type pa_fp_webadi_upload_inf.project_rate_date_type%TYPE;
1554: l_project_rate_date pa_fp_webadi_upload_inf.project_rate_date%TYPE;
1555:
1556:
1549: l_projfunc_rate_type pa_fp_webadi_upload_inf.projfunc_rate_type%TYPE;
1550: l_projfunc_rate_date_type pa_fp_webadi_upload_inf.projfunc_rate_date_type%TYPE;
1551: l_projfunc_rate_date pa_fp_webadi_upload_inf.projfunc_rate_date%TYPE;
1552: l_project_rate_type pa_fp_webadi_upload_inf.project_rate_type%TYPE;
1553: l_project_rate_date_type pa_fp_webadi_upload_inf.project_rate_date_type%TYPE;
1554: l_project_rate_date pa_fp_webadi_upload_inf.project_rate_date%TYPE;
1555:
1556:
1557: BEGIN
1550: l_projfunc_rate_date_type pa_fp_webadi_upload_inf.projfunc_rate_date_type%TYPE;
1551: l_projfunc_rate_date pa_fp_webadi_upload_inf.projfunc_rate_date%TYPE;
1552: l_project_rate_type pa_fp_webadi_upload_inf.project_rate_type%TYPE;
1553: l_project_rate_date_type pa_fp_webadi_upload_inf.project_rate_date_type%TYPE;
1554: l_project_rate_date pa_fp_webadi_upload_inf.project_rate_date%TYPE;
1555:
1556:
1557: BEGIN
1558: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1593: l_projfunc_rate_date,
1594: l_project_rate_type,
1595: l_project_rate_date_type,
1596: l_project_rate_date
1597: FROM pa_fp_webadi_upload_inf
1598: WHERE run_id = p_run_id
1599: AND ROWNUM = 1;
1600:
1601: IF l_projfunc_rate_type <> g_hidden_col_char OR
1658: RAISE;
1659: END conv_attributes_displayed;
1660:
1661: PROCEDURE prepare_val_input
1662: ( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
1663: p_request_id IN pa_budget_versions.request_id%TYPE DEFAULT NULL,
1664: p_version_info_rec IN pa_fp_gen_amount_utils.fp_cols,
1665: p_prd_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE,
1666: p_prd_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE,
2068: --log1('----- STAGE 3.2------- '||l_uncategorized_flag);
2069: IF l_uncategorized_flag = 'N' THEN
2070: IF l_rl_control_flag = 'N' THEN
2071: -- updating the rlm ids for categorized resource lists with control_fl as N
2072: UPDATE pa_fp_webadi_upload_inf inf
2073: SET inf.task_id = (SELECT pt.task_id
2074: FROM (SELECT pt.name task_name, /* Bug 4929163. Modified the select statement to refer to pa_proj_elements instead of pa_tasks*/
2075: pt.element_number task_number,
2076: pt.proj_element_id task_id
2191: AND Nvl(p_request_id, -99) = Nvl(inf.request_id, -99);
2192:
2193: ELSE -- control_flag = Y
2194: -- updating the rlm ids for categorized resource lists with control_fl as Y
2195: UPDATE pa_fp_webadi_upload_inf inf
2196: SET inf.task_id = (SELECT pt.task_id
2197: FROM (SELECT pt.name task_name, /* Bug 4929163. Modified the select statement to refer to pa_proj_elements instead of pa_tasks*/
2198: pt.element_number task_number,
2199: pt.proj_element_id task_id
2329: AND object_type = 'RESOURCE_LIST'
2330: AND object_id = p_version_info_rec.x_resource_list_id;
2331:
2332: --log1('----- STAGE 3.4------- '||l_unct_rlm_id);
2333: UPDATE pa_fp_webadi_upload_inf inf
2334: SET inf.task_id = (SELECT pt.task_id
2335: FROM (SELECT pt.name task_name, /* Bug 4929163. Modidfied the select statement to refer to pa_proj_elements instead of pa_tasks*/
2336: pt.element_number task_number,
2337: pt.proj_element_id task_id
2471: SELECT 'Y'
2472: INTO is_periodic_setup
2473: FROM DUAL
2474: WHERE EXISTS(SELECT 'X'
2475: FROM pa_fp_webadi_upload_inf
2476: WHERE amount_type_name IS NOT NULL
2477: AND run_id = p_run_id
2478: AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
2479: EXCEPTION
2502:
2503: END IF;
2504:
2505:
2506: UPDATE pa_fp_webadi_upload_inf inf
2507: SET amount_type_code = (SELECT pl.lookup_code
2508: FROM pa_lookups pl,pa_fp_proj_xl_amt_types xlt
2509: WHERE lookup_type=l_lookup_type
2510: AND meaning=inf.amount_type_name
2532: AND Nvl(p_request_id, -99) = Nvl(request_id, -99);
2533:
2534: ELSE --Non Periodic layout
2535:
2536: UPDATE pa_fp_webadi_upload_inf inf
2537: SET val_error_flag = NVL(val_error_flag,
2538: DECODE(task_id,
2539: NULL,'Y',
2540: DECODE(resource_list_member_id,
5879: RAISE;
5880:
5881: END prepare_val_input;
5882:
5883: --This is a private function used by prepare_pbl_input. This function expects p_rec, record in pa_fp_webadi_upload_inf
5884: --and p_prd that indicates the period in p_rec from which the amount should be returned.
5885: --valid values for prd are ('SD,'PD', '1'..'52')
5886:
5887: FUNCTION get_amount_in_prd_x
6652:
6653: END get_total_fcst_amounts;
6654:
6655: --This is a private API called from Switcher API. This API assumes that the data exists
6656: --in pa_fp_webadi_upload_inf table and this data has been already validated.
6657:
6658: --Note: p_budget_lines_tbl contains records ordered by task id, resource alias and curr. This
6659: --API assumes that there exists only one record for an RA/TXN Currency Code in p_budget_lines_tbl
6660:
6666: --and it will be used as the start date if the amounts are entered in preceeding bucket
6667:
6668: PROCEDURE prepare_pbl_input
6669: (p_context IN VARCHAR2,
6670: p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
6671: p_request_id IN pa_budget_versions.request_id%TYPE DEFAULT NULL,
6672: p_inf_tbl_rec_tbl IN inf_cur_tbl_typ,
6673: p_version_info_rec IN pa_fp_gen_amount_utils.fp_cols,
6674: p_project_id IN pa_projects_all.project_id%TYPE,
10071: --This procedure will update the budget version and its corresponding data in the interface table with
10072: --the latest request id and plan processing code(XLUP/XLUE).
10073: PROCEDURE update_xl_data_for_new_req
10074: (p_request_id IN pa_budget_versions.request_id%TYPE,
10075: p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
10076: p_plan_processing_code IN pa_budget_versions.plan_processing_code%TYPE,
10077: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
10078: p_null_out_cols IN VARCHAR2)
10079: IS
10087: WHERE budget_version_id = p_budget_version_id;
10088:
10089: --NULL out the IDS and values derived in the previous call to Switcher API do that all the validations
10090: --are done again
10091: UPDATE pa_fp_webadi_upload_inf
10092: SET task_id =DECODE(p_null_out_cols,'Y',NULL,task_id),
10093: resource_list_member_id=DECODE(p_null_out_cols,'Y',NULL,resource_list_member_id),
10094: val_error_flag =DECODE(p_null_out_cols,'Y',NULL,val_error_flag),
10095: val_error_code =DECODE(p_null_out_cols,'Y',NULL,val_error_code),
10107: (x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
10108: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
10109: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
10110: p_submit_budget_flag IN VARCHAR2,
10111: p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
10112: x_success_msg OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
10113: p_submit_forecast_flag IN VARCHAR2,
10114: p_request_id IN pa_budget_versions.request_id%TYPE,
10115: p_calling_mode IN VARCHAR2)
10298: l_rollback_flag := 'N';
10299: COMMIT; /* Added this for bug 3736220
10300:
10301: Reason: To display budget client extension error messages customized by the Customers
10302: we need to stamp the val_error_code and val_error_flag of PA_FP_WEBADI_UPLOAD_INF table
10303: Only then these error messages will appear in Excel Sheet itself when any failure happens
10304: during upload.
10305:
10306: Code flow is designed as follows:
10304: during upload.
10305:
10306: Code flow is designed as follows:
10307: 1. When user clicks on Oracle Upload Toolbar Menu item in Excel
10308: the WEB ADI related procedures will populate PA_FP_WEBADI_UPLOAD_INF table and does not commit
10309:
10310: 2. Switcher API will be invoked after WEB ADI related code flow is completed.
10311:
10312: 3. Swicher API internally invokes PA_FP_CALC_PLAN_PKG.CALCULATE which inturn
10313: invokes the actual budget client extension
10314:
10315: 4. In client extension, as part of bug fix 3736220 added code to Stamp client extension errors
10316: in xface table (we stamp the val_error_code and val_error_flag of
10317: PA_FP_WEBADI_UPLOAD_INF table)
10318:
10319: 5. But the client extension is designed in such a way that when any error occurs in
10320: client extension we roll back to the calling API PA_FP_CALC_PLAN_PKG. So the stamped data
10321: gets rolled back. Ulimately the customers customized errors cannot be displayed in excel
10321: gets rolled back. Ulimately the customers customized errors cannot be displayed in excel
10322: due to this rollback.
10323:
10324: 6. Hence in order to preserve the stamped val_error_code and val_error_flag
10325: of PA_FP_WEBADI_UPLOAD_INF table,introduced an autonomous procedure Stamp_Client_Extn_Errors
10326:
10327: 7. As a result if we dont commit in the beginning when SWITCHER API is called, then when
10328: Stamp_Client_Extn_Errors APi is invoked there will be no records in PA_FP_WEBADI_UPLOAD_INF.
10329: */
10324: 6. Hence in order to preserve the stamped val_error_code and val_error_flag
10325: of PA_FP_WEBADI_UPLOAD_INF table,introduced an autonomous procedure Stamp_Client_Extn_Errors
10326:
10327: 7. As a result if we dont commit in the beginning when SWITCHER API is called, then when
10328: Stamp_Client_Extn_Errors APi is invoked there will be no records in PA_FP_WEBADI_UPLOAD_INF.
10329: */
10330:
10331: fnd_profile.get('PA_DEBUG_MODE', l_debug_mode);
10332:
10380: prd_mask_rec_ver_number
10381: INTO l_budget_version_id,
10382: l_rec_version_number,
10383: l_pm_rec_version_number
10384: FROM pa_fp_webadi_upload_inf
10385: WHERE run_id = p_run_id
10386: AND Nvl(p_request_id, -99) = Nvl(request_id, -99)
10387: AND rownum = 1;
10388: EXCEPTION
10401: SELECT 'Y'
10402: INTO is_periodic_setup
10403: FROM DUAL
10404: WHERE EXISTS(SELECT 'X'
10405: FROM pa_fp_webadi_upload_inf
10406: WHERE amount_type_name IS NOT NULL
10407: AND run_id = p_run_id
10408: AND Nvl(p_request_id, -99) = Nvl(request_id, -99));
10409: EXCEPTION
10455: IF l_debug_mode = 'Y' THEN
10456: pa_debug.g_err_stage := 'No data found while reading project id ..';
10457: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
10458: END IF;
10459: UPDATE pa_fp_webadi_upload_inf
10460: SET val_error_flag = 'Y',
10461: val_error_code = 'PA_FP_WEBADI_VER_MODIFIED',
10462: err_task_name = nvl(task_name,'-98'),
10463: err_task_number = nvl(task_number,'-98'),
10767: SELECT 'X'
10768: INTO l_error_indicator_flag
10769: FROM DUAL
10770: WHERE EXISTS (SELECT 'Y'
10771: FROM pa_fp_webadi_upload_inf inf
10772: WHERE run_id = p_run_id
10773: AND Nvl(p_request_id, -99) = Nvl(request_id, -99)
10774: AND (inf.val_error_flag = 'Y'
10775: OR inf.val_error_code IS NOT NULL));
10887: SET plan_processing_code = 'XLUP',
10888: request_id = l_request_id
10889: WHERE budget_version_id = l_budget_version_id;
10890:
10891: UPDATE pa_fp_webadi_upload_inf
10892: SET request_id = l_request_id
10893: WHERE run_id = p_run_id
10894: AND request_id IS NULL;
10895:
11043: pa_debug.g_err_stage := 'Calling process_budget_lines';
11044: pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
11045: END IF;
11046:
11047: --Till this point all the DMLs would have been executed on PA_FP_WEBADI_UPLOAD_INF. In case of any error
11048: --those changes need not be rolled back. process_budget_lines API will update the core B/F tables and hence
11049: --those updates should be reverted in case of any error. Hence establishing a savepoint now.
11050: SAVEPOINT SWITCHER;
11051: l_rollback_flag := 'Y';
11296: PROCEDURE process_MSExcel_data
11297: (errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
11298: retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
11299: p_submit_ver_flag IN VARCHAR2,
11300: p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE)
11301: IS
11302: --Start of variables used for debugging
11303: l_return_status VARCHAR2(1);
11304: l_msg_count NUMBER := 0;
11320: -- the following cursor would query the interface table for any validation failure
11321: -- error messages present in the interface table corresponding to a set of run_id
11322: -- for the upload session.
11323:
11324: CURSOR l_get_error_msg_to_report_csr (c_run_id pa_fp_webadi_upload_inf.run_id%TYPE,
11325: c_plan_class pa_fin_plan_types_b.plan_class_code%TYPE,
11326: c_version_type pa_budget_versions.version_type%TYPE,
11327: c_request_id pa_budget_versions.request_id%TYPE)
11328: IS
11331: inf.resource_alias resource_info,
11332: inf.txn_currency_code currency,
11333: plu1.meaning amount_type,
11334: plu2.meaning error
11335: FROM pa_fp_webadi_upload_inf inf,
11336: pa_lookups plu1,
11337: pa_lookups plu2
11338: WHERE inf.run_id = c_run_id
11339: AND Nvl(inf.val_error_flag, 'N') = 'Y'
11376:
11377: -- 4497323.Perf Fix:The SELECT query is splitted into two queries in order to improve performance.
11378: SELECT budget_version_id
11379: INTO l_budget_version_id
11380: FROM pa_fp_webadi_upload_inf inf
11381: WHERE inf.run_id = p_run_id
11382: AND Nvl(l_request_id,-99) = Nvl(request_id,-99)
11383: AND rownum=1;
11384: