[Home] [Help]
1625: system_linkage_function,
1626: system_reference1 txn_accum_id,
1627: system_reference2 project_id,
1628: system_reference3 task_id
1629: FROM PA_MAPPABLE_TXNS_TMP pmt
1630: WHERE NOT EXISTS
1631: (SELECT 'Yes'
1632: FROM pa_resource_accum_details rad
1633: WHERE resource_list_id = l_resource_list_id
1652: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1653: pa_debug.debug('map_txns: ' || x_err_stage);
1654: END IF;
1655:
1656: delete from pa_mappable_txns_tmp;
1657:
1658: x_err_stage := 'Inserting into pa_mappable_txns_tmp';
1659: /* Bug 5552602/ 5571792: Split the insert based on x_mode = I (Incremental-Update Process) or F (Full-Refresh process)*/
1660: If nvl(x_mode,'F') = 'I' then
1654: END IF;
1655:
1656: delete from pa_mappable_txns_tmp;
1657:
1658: x_err_stage := 'Inserting into pa_mappable_txns_tmp';
1659: /* Bug 5552602/ 5571792: Split the insert based on x_mode = I (Incremental-Update Process) or F (Full-Refresh process)*/
1660: If nvl(x_mode,'F') = 'I' then
1661: INSERT INTO PA_MAPPABLE_TXNS_TMP (
1662: txn_id,
1657:
1658: x_err_stage := 'Inserting into pa_mappable_txns_tmp';
1659: /* Bug 5552602/ 5571792: Split the insert based on x_mode = I (Incremental-Update Process) or F (Full-Refresh process)*/
1660: If nvl(x_mode,'F') = 'I' then
1661: INSERT INTO PA_MAPPABLE_TXNS_TMP (
1662: txn_id,
1663: person_id,
1664: job_id,
1665: organization_id,
1678: system_reference2,
1679: system_reference3
1680: )
1681: SELECT
1682: pa_mappable_txns_tmp_s.NEXTVAL,
1683: pta.person_id,
1684: pta.job_id,
1685: pta.organization_id,
1686: pta.vendor_id,
1726: AND resource_list_assignment_id = l_resource_list_assignment_id
1727: )
1728: );
1729: ELSE /* 5571792*/
1730: INSERT INTO PA_MAPPABLE_TXNS_TMP (
1731: txn_id,
1732: person_id,
1733: job_id,
1734: organization_id,
1747: system_reference2,
1748: system_reference3
1749: )
1750: SELECT
1751: pa_mappable_txns_tmp_s.NEXTVAL,
1752: pta.person_id,
1753: pta.job_id,
1754: pta.organization_id,
1755: pta.vendor_id,
1790: /*Code Changes for Bug No.2984871 end */
1791:
1792: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1793: /* Commented for Bug 2984871
1794: pa_debug.debug('map_txns: ' || 'Inserted ' || sql%rowcount|| ' rows in pa_mappable_txns_tmp ');*/
1795: /*Code Changes for Bug No.2984871 start */
1796: pa_debug.debug('map_txns: ' || 'Inserted ' || l_rowcount || ' rows in pa_mappable_txns_tmp ');
1797: /*Code Changes for Bug No.2984871 end*/
1798: END IF;
1792: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1793: /* Commented for Bug 2984871
1794: pa_debug.debug('map_txns: ' || 'Inserted ' || sql%rowcount|| ' rows in pa_mappable_txns_tmp ');*/
1795: /*Code Changes for Bug No.2984871 start */
1796: pa_debug.debug('map_txns: ' || 'Inserted ' || l_rowcount || ' rows in pa_mappable_txns_tmp ');
1797: /*Code Changes for Bug No.2984871 end*/
1798: END IF;
1799: /* Commented for Bug 2984871
1800: IF sql%rowcount = 0 THEN*/
1805: pa_debug.debug('NEW_MAP_TXNS is not called for this resource list since there ' ||
1806: 'arent any records to process');
1807: END IF;
1808:
1809: ELSE /* PA_MAPPABLE_TXNS_TMP contains records to be processed */
1810:
1811: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1812: pa_debug.debug('map_txns: ' || 'Calling new mapping api @ ' ||
1813: to_CHAR(sysdate,'DD-MON-RR::HH:MI:SS'));
1864: x_last_update_login,
1865: x_request_id,
1866: x_program_application_id,
1867: x_program_id
1868: FROM PA_MAPPABLE_TXNS_TMP pmt
1869: WHERE NOT EXISTS
1870: (SELECT 'Yes'
1871: FROM pa_resource_accum_details rad
1872: WHERE resource_list_id = l_resource_list_id
1910: pa_debug.debug('map_txns: ' || 'Inserted ' || sql%rowcount || ' rows into PA_RESOURCE_ACCUM_DETAILS');
1911: END IF;
1912:
1913: COMMIT;
1914: END IF; /* PA_MAPPABLE_TXNS_TMP%rowcount check */
1915:
1916: END LOOP;
1917: EXCEPTION
1918: WHEN OTHERS THEN
1926: END map_txns;
1927:
1928: /* MAP_TRANSACTIONS Procedure : Created for bug# 1889671
1929: This process will update the RESOURCE_LIST_MEMBER_ID and RESOURCE_ID in
1930: table PA_MAPPABLE_TXNS_TMP table. Following needs to be done before a call
1931: to this API is made.
1932:
1933: PA_MAPPABLE_TXNS_TMP table should have been populated for a single RESOURCE_LIST
1934: with all the transaction attributes with TXN_ID populated with a unique id.
1929: This process will update the RESOURCE_LIST_MEMBER_ID and RESOURCE_ID in
1930: table PA_MAPPABLE_TXNS_TMP table. Following needs to be done before a call
1931: to this API is made.
1932:
1933: PA_MAPPABLE_TXNS_TMP table should have been populated for a single RESOURCE_LIST
1934: with all the transaction attributes with TXN_ID populated with a unique id.
1935:
1936: Populate SYSTEM_REFERENCE1-5 columns are populated with unique identifiers
1937: which will be used by the calling program after the completion of the
1958: TYPE l_resource_member_id_tbl_typ IS TABLE OF
1959: PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1960: TYPE l_resource_id_tbl_typ IS TABLE OF
1961: PA_RESOURCE_LIST_MEMBERS.RESOURCE_ID%TYPE;
1962: TYPE l_txn_id_table_typ IS TABLE OF PA_MAPPABLE_TXNS_TMP.TXN_ID%TYPE;
1963:
1964: l_resource_member_id_tbl L_RESOURCE_MEMBER_ID_TBL_TYP := L_RESOURCE_MEMBER_ID_TBL_TYP();
1965: l_resource_id_tbl L_RESOURCE_ID_TBL_TYP := L_RESOURCE_ID_TBL_TYP();
1966: l_txn_id_tbl L_TXN_ID_TABLE_TYP := L_TXN_ID_TABLE_TYP();
1974: resource list to be grouped only by organization, expenditure_category
1975: and revenue_category. Hence PA_RESOURCE_LIST_PARENTS_TMP table has only
1976: these three attributes.
1977:
1978: 1.2 Now assign parents to each transaction in PA_MAPPABLE_TXNS_TMP table. This can
1979: be done by matching organization, expenditure_category or revenue_category
1980: of the txn with that in PA_RESOURCE_LIST_PARENTS_TMP.
1981:
1982: 1.3 At this point if parent could not be assigned then the txn should be
1986: their ranks in this table. This is done by matching all attributes of
1987: transactions with corresponding attribute of the child resource.
1988:
1989: 1.5 Fetch all the resources with highest (lowest in magnitude) rank in pl/sql
1990: tables and update PA_MAPPABLE_TXNS_TMP table with the resource id.
1991:
1992: 1.6 At this stage if no resource is assigned to any txn but parent is assigned
1993: then assign parent level unclassified resource to these transactions.
1994:
2023: CURSOR C2 IS
2024: SELECT pmt.txn_id
2025: ,prlm.resource_list_member_id
2026: ,prlm.resource_id
2027: FROM pa_mappable_txns_tmp pmt
2028: ,pa_resource_list_members prlm
2029: WHERE pmt.resource_list_member_id is null /* A resource is not already assigned */
2030: AND pmt.parent_member_id is not null /* But a parent is assigned */
2031: AND pmt.parent_member_id = prlm.parent_member_id
2071: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2072: pa_debug.debug('new_map_txns: ' || 'Resource List is UNCATEGORIZED');
2073: END IF;
2074:
2075: UPDATE pa_mappable_txns_tmp PMT
2076: SET resource_list_member_id = l_unclassified_rlm_id
2077: ,resource_id = l_unclassified_res_id
2078: WHERE PMT.resource_list_id = x_resource_list_id;
2079: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2102: and will improve the process throughput also.
2103: */
2104:
2105:
2106: UPDATE pa_mappable_txns_tmp PMT
2107: SET resource_list_member_id = l_unclassified_rlm_id
2108: ,resource_id = l_unclassified_res_id
2109: WHERE pmt.parent_member_id is null;
2110:
2139:
2140: /* following bulk update logic is irrespective of whether resource list is categorized or not */
2141:
2142: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2143: pa_debug.debug ('new_map_txns: ' || 'Update PA_MAPPABLE_TXNS_TMP table with resources assigned');
2144: END IF;
2145:
2146: /* Bulk update has very consistent time for updations. It takes precisely 30 secs for 50000
2147: updates. The time does not vary whether we do bulk updates in batches of 200 records or in
2154: l_txn_id_tbl.extend(l_plsql_max_array_size);
2155: l_resource_member_id_tbl.extend(l_plsql_max_array_size);
2156: l_resource_id_tbl.extend(l_plsql_max_array_size);
2157:
2158: /* Point 1.5 and 2.2 : Just update PA_MAPPABLE_TXNS_TMP with records
2159: in plsql table and handle for every 200 records */
2160:
2161: l_prev_txn_id := NULL;
2162: l_counter := 1;
2174: l_prev_txn_id := c1rec.txn_id;
2175:
2176: IF l_counter > l_plsql_max_array_size THEN
2177:
2178: x_error_stage := 'Updating pa_mappable_txns_tmp with records in pl/sql tables';
2179:
2180: FORALL i in l_resource_member_id_tbl.first..l_resource_member_id_tbl.last
2181: UPDATE pa_mappable_txns_tmp
2182: SET resource_list_member_id = l_resource_member_id_tbl(i)
2177:
2178: x_error_stage := 'Updating pa_mappable_txns_tmp with records in pl/sql tables';
2179:
2180: FORALL i in l_resource_member_id_tbl.first..l_resource_member_id_tbl.last
2181: UPDATE pa_mappable_txns_tmp
2182: SET resource_list_member_id = l_resource_member_id_tbl(i)
2183: ,resource_id = l_resource_id_tbl(i)
2184: WHERE txn_id = l_txn_id_tbl(i);
2185:
2198:
2199: x_error_stage := 'Updating if any more records left';
2200:
2201: FORALL i in l_resource_member_id_tbl.first..(l_counter-1)
2202: UPDATE pa_mappable_txns_tmp
2203: SET resource_list_member_id = l_resource_member_id_tbl(i)
2204: ,resource_id = l_resource_id_tbl(i)
2205: WHERE txn_id = l_txn_id_tbl(i);
2206:
2243: LIMIT l_plsql_max_array_size;
2244:
2245: IF nvl(l_txn_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
2246:
2247: x_error_stage := 'Doing bulk update of pa_mappable_txns_tmp from data fetched from c2';
2248:
2249: FORALL i in l_resource_member_id_tbl.first..l_resource_member_id_tbl.last
2250: UPDATE pa_mappable_txns_tmp
2251: SET resource_list_member_id = l_resource_member_id_tbl(i)
2246:
2247: x_error_stage := 'Doing bulk update of pa_mappable_txns_tmp from data fetched from c2';
2248:
2249: FORALL i in l_resource_member_id_tbl.first..l_resource_member_id_tbl.last
2250: UPDATE pa_mappable_txns_tmp
2251: SET resource_list_member_id = l_resource_member_id_tbl(i)
2252: ,resource_id = l_resource_id_tbl(i)
2253: WHERE txn_id = l_txn_id_tbl(i);
2254: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2272: PARENT_MEMBER_ID as the RESOURCE_LIST_MEMBER_ID. Reason being, this is a
2273: categorized resource, and since there arent any children, assign the parent
2274: itself as the resource */
2275:
2276: UPDATE pa_mappable_txns_tmp pmt
2277: set resource_list_member_id = parent_member_id
2278: ,resource_id = parent_resource_id
2279: WHERE pmt.resource_list_member_id is null
2280: AND pmt.parent_member_id is not null;
2294: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2295: pa_debug.debug('new_map_txns: ' || x_error_stage);
2296: END IF;
2297:
2298: UPDATE pa_mappable_txns_tmp PMT
2299: SET resource_list_member_id = l_unclassified_rlm_id
2300: ,resource_id = l_unclassified_res_id
2301: WHERE pmt.resource_list_member_id is null;
2302:
2373: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2374: pa_debug.debug('update_parents_mem_id: ' || 'Inserted ' || sql%rowcount || ' rows into pa_resource_list_parents_tmp');
2375: END IF;
2376:
2377: /* Determine parent for each transaction in PA_MAPPABLE_TXNS_TMP.
2378: As parents can be only organizations, expenditure category or revenue category,
2379: and only one of the three attributes will be populated in the parents tables,
2380: one single update will do for resource lists grouped by any of these three
2381: */
2381: */
2382:
2383: /* Point 1.2 */
2384:
2385: x_err_stage := 'Updating the parent member details in PA_MAPPABLE_TXNS_TMP';
2386: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2387: pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
2388: END IF;
2389:
2386: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2387: pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
2388: END IF;
2389:
2390: UPDATE pa_mappable_txns_tmp PMT
2391: SET (parent_member_id, parent_resource_id) =
2392: (SELECT resource_list_member_id, resource_id
2393: FROM pa_resource_list_parents_tmp PRLP
2394: WHERE (pmt.expenditure_category = prlp.expenditure_category
2396: OR pmt.revenue_category = prlp.revenue_category)
2397: AND pmt.resource_list_id = prlp.resource_list_id);
2398:
2399: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2400: pa_debug.debug('update_parents_mem_id: ' || 'Updated ' || sql%rowcount || ' rows in pa_mappable_txns_tmp with parent member details');
2401: END IF;
2402:
2403:
2404: EXCEPTION
2443: Later only those resources will be picked up which have highest rank
2444: (lowest in magnitude).
2445:
2446: Since resource list is grouped then we use the parent_member_id info already
2447: stamped on PA_MAPPABLE_TXNS_TMP table else we do not.
2448: */
2449:
2450: FOR C3REC in C3 LOOP
2451: IF C3REC.RESOURCE_TYPE_CODE = 'EMPLOYEE' THEN
2465: (SELECT txn_id
2466: , prlm.resource_list_member_id
2467: , prlm.resource_id,
2468: prfr.rank
2469: FROM pa_mappable_txns_tmp temp
2470: ,pa_resource_list_members prlm
2471: ,pa_resource_format_ranks prfr
2472: WHERE temp.parent_member_id = prlm.parent_member_id
2473: AND prlm.person_id = temp.person_id
2495: (SELECT txn_id
2496: , prlm.resource_list_member_id
2497: , prlm.resource_id,
2498: prfr.rank
2499: FROM pa_mappable_txns_tmp temp
2500: ,pa_resource_list_members prlm
2501: ,pa_resource_format_ranks prfr
2502: WHERE temp.parent_member_id = prlm.parent_member_id
2503: AND prlm.job_id = PA_Cross_Business_Grp.IsMappedToJob(temp.job_id,x_rl_job_grp_id)
2525: (SELECT txn_id
2526: , prlm.resource_list_member_id
2527: , prlm.resource_id
2528: , prfr.rank
2529: FROM pa_mappable_txns_tmp TEMP
2530: ,pa_resource_list_members PRLM
2531: ,pa_resource_format_ranks prfr
2532: WHERE temp.parent_member_id = prlm.parent_member_id
2533: AND prlm.organization_id = temp.organization_id
2555: (SELECT txn_id
2556: , prlm.resource_list_member_id
2557: , prlm.resource_id
2558: , prfr.rank
2559: FROM pa_mappable_txns_tmp TEMP
2560: ,pa_resource_list_members PRLM
2561: ,pa_resource_format_ranks prfr
2562: WHERE temp.parent_member_id = prlm.parent_member_id
2563: AND prlm.revenue_category = temp.revenue_category
2585: (SELECT txn_id
2586: , prlm.resource_list_member_id
2587: , prlm.resource_id
2588: , prfr.rank
2589: FROM pa_mappable_txns_tmp TEMP
2590: ,pa_resource_list_members PRLM
2591: ,pa_resource_format_ranks prfr
2592: WHERE temp.parent_member_id = prlm.parent_member_id
2593: AND prlm.vendor_id = temp.vendor_id
2615: (SELECT txn_id
2616: , prlm.resource_list_member_id
2617: , prlm.resource_id
2618: , prfr.rank
2619: FROM pa_mappable_txns_tmp TEMP
2620: ,pa_resource_list_members PRLM
2621: ,pa_resource_format_ranks prfr
2622: WHERE temp.parent_member_id = prlm.parent_member_id
2623: AND prlm.expenditure_type = temp.expenditure_type
2645: (SELECT txn_id
2646: , prlm.resource_list_member_id
2647: , prlm.resource_id
2648: , prfr.rank
2649: FROM pa_mappable_txns_tmp TEMP
2650: ,pa_resource_list_members PRLM
2651: ,pa_resource_format_ranks prfr
2652: WHERE temp.parent_member_id = prlm.parent_member_id
2653: AND prlm.expenditure_category = temp.expenditure_category
2675: (SELECT txn_id
2676: , prlm.resource_list_member_id
2677: , prlm.resource_id
2678: , prfr.rank
2679: FROM pa_mappable_txns_tmp TEMP
2680: ,pa_resource_list_members PRLM
2681: ,pa_resource_format_ranks prfr
2682: WHERE temp.parent_member_id = prlm.parent_member_id
2683: AND prlm.event_type = temp.event_type
2705: (SELECT txn_id
2706: , prlm.resource_list_member_id
2707: , prlm.resource_id
2708: , prfr.rank
2709: FROM pa_mappable_txns_tmp TEMP
2710: ,pa_resource_list_members PRLM
2711: ,pa_resource_format_ranks prfr
2712: WHERE temp.parent_member_id = prlm.parent_member_id
2713: AND prlm.project_role_id = temp.project_role_id
2784: (SELECT txn_id
2785: , prlm.resource_list_member_id
2786: , prlm.resource_id
2787: , prfr.rank
2788: FROM pa_mappable_txns_tmp TEMP
2789: ,pa_resource_list_members PRLM
2790: ,pa_resource_format_ranks prfr
2791: WHERE prlm.person_id = temp.person_id
2792: AND prlm.resource_format_id = prfr.resource_format_id
2813: (SELECT txn_id
2814: , prlm.resource_list_member_id
2815: , prlm.resource_id
2816: , prfr.rank
2817: FROM pa_mappable_txns_tmp TEMP
2818: ,pa_resource_list_members PRLM
2819: ,pa_resource_format_ranks prfr
2820: WHERE prlm.job_id = PA_Cross_Business_Grp.IsMappedToJob(temp.job_id,x_rl_job_grp_id)
2821: AND prlm.resource_format_id = prfr.resource_format_id
2842: (SELECT txn_id
2843: , prlm.resource_list_member_id
2844: , prlm.resource_id
2845: , prfr.rank
2846: FROM pa_mappable_txns_tmp TEMP
2847: ,pa_resource_list_members PRLM
2848: ,pa_resource_format_ranks prfr
2849: WHERE prlm.organization_id = temp.organization_id
2850: AND prlm.resource_format_id = prfr.resource_format_id
2871: (SELECT txn_id
2872: , prlm.resource_list_member_id
2873: , prlm.resource_id
2874: , prfr.rank
2875: FROM pa_mappable_txns_tmp TEMP
2876: ,pa_resource_list_members PRLM
2877: ,pa_resource_format_ranks prfr
2878: WHERE prlm.revenue_category = temp.revenue_category
2879: AND prlm.resource_format_id = prfr.resource_format_id
2900: (SELECT txn_id
2901: , prlm.resource_list_member_id
2902: , prlm.resource_id
2903: , prfr.rank
2904: FROM pa_mappable_txns_tmp TEMP
2905: ,pa_resource_list_members PRLM
2906: ,pa_resource_format_ranks prfr
2907: WHERE prlm.vendor_id = temp.vendor_id
2908: AND prlm.resource_format_id = prfr.resource_format_id
2929: (SELECT txn_id
2930: , prlm.resource_list_member_id
2931: , prlm.resource_id
2932: , prfr.rank
2933: FROM pa_mappable_txns_tmp TEMP
2934: ,pa_resource_list_members PRLM
2935: ,pa_resource_format_ranks prfr
2936: WHERE prlm.expenditure_type = temp.expenditure_type
2937: AND prlm.resource_format_id = prfr.resource_format_id
2958: (SELECT txn_id
2959: , prlm.resource_list_member_id
2960: , prlm.resource_id
2961: , prfr.rank
2962: FROM pa_mappable_txns_tmp TEMP
2963: ,pa_resource_list_members PRLM
2964: ,pa_resource_format_ranks prfr
2965: WHERE prlm.expenditure_category = temp.expenditure_category
2966: AND prlm.resource_format_id = prfr.resource_format_id
2987: (SELECT txn_id
2988: , prlm.resource_list_member_id
2989: , prlm.resource_id
2990: , prfr.rank
2991: FROM pa_mappable_txns_tmp TEMP
2992: ,pa_resource_list_members PRLM
2993: ,pa_resource_format_ranks prfr
2994: WHERE prlm.event_type = temp.event_type
2995: AND prlm.resource_format_id = prfr.resource_format_id
3016: (SELECT txn_id
3017: , prlm.resource_list_member_id
3018: , prlm.resource_id
3019: , prfr.rank
3020: FROM pa_mappable_txns_tmp TEMP
3021: ,pa_resource_list_members PRLM
3022: ,pa_resource_format_ranks prfr
3023: WHERE prlm.project_role_id = temp.project_role_id
3024: AND prlm.resource_format_id = prfr.resource_format_id