eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_R_PROJECT_RESOURCES_PVT dependencies on PA_RESOURCES

Line 1125: This procedure inserts records into pa_resources and

1121: RAISE;
1122: END INSERT_INTO_CRM;
1123:
1124: /*Procedure : PA Insert
1125: This procedure inserts records into pa_resources and
1126: pa_resource_txn_attributes table. This procedure calls the table
1127: handler package PA_RESOURCE_PKG.INSERT_ROW1 and PA_RESOURCE_PKG.INSERT_ROW2 . */
1128:
1129: PROCEDURE INSERT_INTO_PA(

Line 1131: P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,

1127: handler package PA_RESOURCE_PKG.INSERT_ROW1 and PA_RESOURCE_PKG.INSERT_ROW2 . */
1128:
1129: PROCEDURE INSERT_INTO_PA(
1130: P_RESOURCE_TYPE_ID IN PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE,
1131: P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
1132: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,

Line 1132: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895

1128:
1129: PROCEDURE INSERT_INTO_PA(
1130: P_RESOURCE_TYPE_ID IN PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE,
1131: P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
1132: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
1136: P_NAME IN PA_RESOURCES.NAME%TYPE,

Line 1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,

1129: PROCEDURE INSERT_INTO_PA(
1130: P_RESOURCE_TYPE_ID IN PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE,
1131: P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
1132: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
1136: P_NAME IN PA_RESOURCES.NAME%TYPE,
1137: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,

Line 1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,

1130: P_RESOURCE_TYPE_ID IN PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE,
1131: P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
1132: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
1136: P_NAME IN PA_RESOURCES.NAME%TYPE,
1137: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,
1138: X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895

Line 1136: P_NAME IN PA_RESOURCES.NAME%TYPE,

1132: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1133: P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
1134: P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
1135: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
1136: P_NAME IN PA_RESOURCES.NAME%TYPE,
1137: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,
1138: X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1139: IS
1140: l_resource_txn_attribute_id PA_RESOURCE_TXN_ATTRIBUTES.RESOURCE_TXN_ATTRIBUTE_ID%TYPE;

Line 1146: l_unit_of_measure pa_resources.unit_of_measure%type;

1142: l_rowid ROWID;
1143: l_check_char VARCHAR2(1);
1144:
1145: -- added for bug 3921534
1146: l_unit_of_measure pa_resources.unit_of_measure%type;
1147: l_rollup_quantity_flag pa_resources.rollup_quantity_flag%type;
1148: l_track_as_labor_flag pa_resources.track_as_labor_flag%type;
1149:
1150: CURSOR c_dup_resource_id(l_resource_id IN pa_resources.resource_id%type)

Line 1147: l_rollup_quantity_flag pa_resources.rollup_quantity_flag%type;

1143: l_check_char VARCHAR2(1);
1144:
1145: -- added for bug 3921534
1146: l_unit_of_measure pa_resources.unit_of_measure%type;
1147: l_rollup_quantity_flag pa_resources.rollup_quantity_flag%type;
1148: l_track_as_labor_flag pa_resources.track_as_labor_flag%type;
1149:
1150: CURSOR c_dup_resource_id(l_resource_id IN pa_resources.resource_id%type)
1151: IS

Line 1148: l_track_as_labor_flag pa_resources.track_as_labor_flag%type;

1144:
1145: -- added for bug 3921534
1146: l_unit_of_measure pa_resources.unit_of_measure%type;
1147: l_rollup_quantity_flag pa_resources.rollup_quantity_flag%type;
1148: l_track_as_labor_flag pa_resources.track_as_labor_flag%type;
1149:
1150: CURSOR c_dup_resource_id(l_resource_id IN pa_resources.resource_id%type)
1151: IS
1152: SELECT 'X'

Line 1150: CURSOR c_dup_resource_id(l_resource_id IN pa_resources.resource_id%type)

1146: l_unit_of_measure pa_resources.unit_of_measure%type;
1147: l_rollup_quantity_flag pa_resources.rollup_quantity_flag%type;
1148: l_track_as_labor_flag pa_resources.track_as_labor_flag%type;
1149:
1150: CURSOR c_dup_resource_id(l_resource_id IN pa_resources.resource_id%type)
1151: IS
1152: SELECT 'X'
1153: FROM pa_resources
1154: WHERE resource_id = l_resource_id;

Line 1153: FROM pa_resources

1149:
1150: CURSOR c_dup_resource_id(l_resource_id IN pa_resources.resource_id%type)
1151: IS
1152: SELECT 'X'
1153: FROM pa_resources
1154: WHERE resource_id = l_resource_id;
1155:
1156: CURSOR c_dup_txn_attribute_id(l_resource_txn_attribute_id IN
1157: pa_resource_txn_attributes.

Line 1164: CURSOR c_pa_resources( l_rowid IN ROWID ) IS

1160: SELECT 'X'
1161: FROM pa_resource_txn_attributes
1162: WHERE resource_txn_attribute_id = l_resource_txn_attribute_id;
1163:
1164: CURSOR c_pa_resources( l_rowid IN ROWID ) IS
1165: SELECT 'Y'
1166: FROM pa_resources
1167: WHERE ROWID = l_rowid;
1168:

Line 1166: FROM pa_resources

1162: WHERE resource_txn_attribute_id = l_resource_txn_attribute_id;
1163:
1164: CURSOR c_pa_resources( l_rowid IN ROWID ) IS
1165: SELECT 'Y'
1166: FROM pa_resources
1167: WHERE ROWID = l_rowid;
1168:
1169: CURSOR c_pa_resource_txn_attributes( l_rowid IN ROWID ) IS
1170: SELECT 'Y'

Line 1185: Select pa_resources_s.nextval

1181: X_RETURN_STATUS := fnd_api.g_ret_sts_success;
1182:
1183: --Fetch unique resource_id from sequence.
1184: LOOP
1185: Select pa_resources_s.nextval
1186: into x_resource_id
1187: from dual;
1188:
1189: OPEN c_dup_resource_id(x_resource_id);

Line 1232: --Insert into pa_resources table

1228: l_rollup_quantity_flag := 'N';
1229: l_track_as_labor_flag := 'Y';
1230: end if;
1231:
1232: --Insert into pa_resources table
1233: pa_debug.g_err_stage := 'Log: Calling Insert_row1 to insert into PA_RESOURCES table';
1234: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1235: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1236: END IF;

Line 1233: pa_debug.g_err_stage := 'Log: Calling Insert_row1 to insert into PA_RESOURCES table';

1229: l_track_as_labor_flag := 'Y';
1230: end if;
1231:
1232: --Insert into pa_resources table
1233: pa_debug.g_err_stage := 'Log: Calling Insert_row1 to insert into PA_RESOURCES table';
1234: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1235: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1236: END IF;
1237: log_message('Calling insert_row1 procedure to do the actual inserts into PA_RESOURCES table');

Line 1237: log_message('Calling insert_row1 procedure to do the actual inserts into PA_RESOURCES table');

1233: pa_debug.g_err_stage := 'Log: Calling Insert_row1 to insert into PA_RESOURCES table';
1234: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1235: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1236: END IF;
1237: log_message('Calling insert_row1 procedure to do the actual inserts into PA_RESOURCES table');
1238:
1239: PA_RESOURCE_PKG.INSERT_ROW1 (
1240: X_ROWID => l_rowid,
1241: X_RESOURCE_ID => x_resource_id,

Line 1323: P_DEFAULT_OU IN PA_RESOURCES_DENORM.RESOURCE_ORG_ID%TYPE,

1319: Also if the default OU is NULL for a resource
1320: then gives an expected error. */
1321:
1322: PROCEDURE CHECK_OU(
1323: P_DEFAULT_OU IN PA_RESOURCES_DENORM.RESOURCE_ORG_ID%TYPE,
1324: P_EXP_ORG IN VARCHAR2,
1325: X_EXP_OU OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1326: X_RETURN_STATUS OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1327: IS

Line 1408: P_NAME IN PA_RESOURCES.NAME%TYPE,

1404: details. */
1405:
1406: PROCEDURE CREATE_INTERNAL_RESOURCE(
1407: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE,
1408: P_NAME IN PA_RESOURCES.NAME%TYPE,
1409: P_ORGANIZATION_ID IN PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE,
1410: P_ASSIGNMENT_START_DATE IN DATE,
1411: P_ASSIGNMENT_END_DATE IN DATE,
1412: P_START_DATE IN DATE,

Line 1420: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895

1416: P_USER_TYPE IN PER_PERSON_TYPES.USER_PERSON_TYPE%TYPE,
1417: P_RES_EXISTS IN VARCHAR2,
1418: P_COUNT IN NUMBER,
1419: P_RESOURCE_TYPE IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
1420: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1421: X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1422: P_START_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_start_date%TYPE,
1423: P_END_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_end_date%TYPE,
1424: P_SOURCE_NUMBER IN pa_r_project_resources_ind_v.per_emp_number%TYPE,

Line 1463: FROM pa_resources

1459:
1460: CURSOR c_jtf_id_exist(l_res_id IN NUMBER)
1461: IS
1462: SELECT jtf_resource_id
1463: FROM pa_resources
1464: WHERE resource_id = l_res_id;
1465:
1466: BEGIN
1467: PA_DEBUG.set_err_stack('Create_Internal_Resource');

Line 1714: pa_debug.g_err_stage := 'Log: PA_RESOURCES.JTF_RESOURCE_ID does not exist or not equal to the CRM resource_id';

1710: FETCH c_jtf_id_exist INTO l_jtf_id;
1711: CLOSE c_jtf_id_exist;
1712:
1713: IF (l_jtf_id is null) or (l_jtf_id <> x_crm_resource_id) THEN
1714: pa_debug.g_err_stage := 'Log: PA_RESOURCES.JTF_RESOURCE_ID does not exist or not equal to the CRM resource_id';
1715: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1716: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1717: END IF;
1718:

Line 1719: update pa_resources

1715: IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1716: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
1717: END IF;
1718:
1719: update pa_resources
1720: set jtf_resource_id = x_crm_resource_id,
1721: request_id = G_request_id,
1722: program_id = G_program_id,
1723: program_update_date = sysdate,

Line 1807: to insert the resource into pa_resources and pa_resource_txn_attributes

1803: /*Procedure : Create External Resource
1804: This procedure is the main procedure called by the create_resource
1805: procedure for external resources. First the procedure checks to see if
1806: the resource exists in PA, if it does not then it calls the insert_into_pa
1807: to insert the resource into pa_resources and pa_resource_txn_attributes
1808: If the resource does exist in PA, the procedure just returns the resource_id
1809: of the external resource */
1810:
1811: PROCEDURE CREATE_EXTERNAL_RESOURCE(

Line 1814: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895

1810:
1811: PROCEDURE CREATE_EXTERNAL_RESOURCE(
1812: P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE,
1813: P_RESOURCE_TYPE IN PA_RESOURCE_TYPES.RESOURCE_TYPE_CODE%TYPE,
1814: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
1815: X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1816: IS
1817: l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
1818: l_valid VARCHAR2(1) := 'N';

Line 1821: l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;

1817: l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
1818: l_valid VARCHAR2(1) := 'N';
1819: l_res_exists VARCHAR2(20);
1820: l_party_id PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE := P_PARTY_ID;
1821: l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;
1822: l_name PA_RESOURCES.NAME%TYPE;
1823: l_start_date DATE;
1824: l_end_date DATE;
1825: l_return_status VARCHAR2(1);

Line 1822: l_name PA_RESOURCES.NAME%TYPE;

1818: l_valid VARCHAR2(1) := 'N';
1819: l_res_exists VARCHAR2(20);
1820: l_party_id PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE := P_PARTY_ID;
1821: l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;
1822: l_name PA_RESOURCES.NAME%TYPE;
1823: l_start_date DATE;
1824: l_end_date DATE;
1825: l_return_status VARCHAR2(1);
1826:

Line 1865: pa_resources.name is only VARCHAR2(100) */

1861:
1862: -- get party_name, start date, end date
1863: /* bug2498092 - need to truncate the party name from hz_parties
1864: because HZ_PARTIES.party_name is VARCHAR2(360) while
1865: pa_resources.name is only VARCHAR2(100) */
1866: /*Bug 3612182:Modified the substr to substrb*/
1867: select substrb(party_name,1,100), start_date, end_date
1868: into l_name, l_start_date, l_end_date
1869: from pa_party_resource_details_v

Line 1962: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE) --File.Sql.39 bug 4440895

1958: P_ORG_STR_VERSION_ID IN NUMBER DEFAULT NULL,
1959: P_START_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
1960: -- End of parameters added for PJR Resource Pull Enhancements - Bug 5130414
1961: X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1962: X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE) --File.Sql.39 bug 4440895
1963:
1964: IS
1965: L_API_VERSION CONSTANT NUMBER := 1.0;
1966: L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';

Line 1974: l_name PA_RESOURCES.NAME%TYPE;

1970: l_resource_type JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE := P_RESOURCE_TYPE;
1971:
1972: x_resource_number NUMBER;
1973:
1974: l_name PA_RESOURCES.NAME%TYPE;
1975: l_organization_id PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE;
1976: l_assignment_start_date DATE;
1977: l_assignment_end_date DATE;
1978: l_start_date DATE;

Line 2031: l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;

2027: G_person_id NUMBER := 0;
2028: l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
2029: -- l_org_type VARCHAR2(20); --Bug 4363092
2030: l_crm_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
2031: l_res_id PA_RESOURCES.RESOURCE_ID%TYPE;
2032: l_per_success VARCHAR2(1);
2033: l_asgmt_success VARCHAR2(1) := 'N';
2034:
2035: -- variables for fix in message stack in individual pull

Line 2056: l_resource_id PA_RESOURCES.RESOURCE_ID%TYPE;

2052: l_invol_term VARCHAR2(1); --bug 5683340
2053:
2054: /*Added 2 new params for bug 4087022*/
2055: l_excep VARCHAR2(1) := 'N' ;
2056: l_resource_id PA_RESOURCES.RESOURCE_ID%TYPE;
2057:
2058: PERSON_ERROR_EXCEPTION EXCEPTION;
2059: TERM_DATE_EXCEPTION EXCEPTION;
2060:

Line 2121: from pa_resources_denorm

2117: and rownum = 1 );
2118:
2119: CURSOR check_res_denorm IS
2120: select 'Y'
2121: from pa_resources_denorm
2122: where person_id = l_person_id
2123: and rownum=1;
2124:
2125: /*Cursor added for Bug 6943551*/

Line 2130: FROM pa_resources_denorm prd,

2126: CURSOR cur_denorm_del(p_person_id IN NUMBER ) IS
2127: SELECT prd.person_id,
2128: prd.resource_effective_start_date,
2129: prd.resource_effective_end_date
2130: FROM pa_resources_denorm prd,
2131: per_all_assignments_f paf,
2132: per_assignment_status_types past
2133: WHERE prd.person_id = paf.person_id
2134: AND prd.person_id = p_person_id

Line 2147: FROM pa_resources_denorm prd

2143: CURSOR cur_denorm_del_redundant(p_person_id IN NUMBER, l_prof_date IN DATE) IS
2144: SELECT prd.person_id,
2145: prd.resource_effective_start_date,
2146: prd.resource_effective_end_date
2147: FROM pa_resources_denorm prd
2148: WHERE prd.resource_effective_end_date < sysdate
2149: AND prd.resource_effective_end_date < l_prof_date
2150: AND prd.resource_effective_end_date is not null
2151: AND prd.person_id = p_person_id

Line 2248: -- For Non-Scheduled Member, we will only insert into PA_RESOURCES and

2244: l_user_type := eRec.user_type;
2245:
2246:
2247: ------------------------------------------------------------------------
2248: -- For Non-Scheduled Member, we will only insert into PA_RESOURCES and
2249: -- PA_RESOURCE_TXN_ATTRIBUTES. No need for calendar assignment, timeline
2250: -- generation and we also do not populate the pa_resources_denorm table
2251: ------------------------------------------------------------------------
2252:

Line 2250: -- generation and we also do not populate the pa_resources_denorm table

2246:
2247: ------------------------------------------------------------------------
2248: -- For Non-Scheduled Member, we will only insert into PA_RESOURCES and
2249: -- PA_RESOURCE_TXN_ATTRIBUTES. No need for calendar assignment, timeline
2250: -- generation and we also do not populate the pa_resources_denorm table
2251: ------------------------------------------------------------------------
2252:
2253: IF (p_scheduled_member_flag = 'N') THEN
2254:

Line 2559: from pa_resources_denorm

2555: -----------------------------------------------------------------------
2556: BEGIN
2557: select 'Y'
2558: into l_exists
2559: from pa_resources_denorm
2560: where person_id = l_person_id
2561: and l_assignment_end_date BETWEEN resource_effective_start_date
2562: AND resource_effective_end_date;
2563:

Line 3382: --Here we are going to delete all rows for pa_resources_denorm for

3378:
3379: /* Changes start for Bug 5662589 */
3380: ---------------------------------------------------------------------
3381: --This IF tells us the last record for a person as from cur_resources.
3382: --Here we are going to delete all rows for pa_resources_denorm for
3383: --which the resource_effective_end_date is greater than
3384: --max(assignment_end_date) from pa_r_project_resources_term_v res
3385: ---------------------------------------------------------------------
3386: IF (trunc(l_end_date) = trunc(l_assignment_end_date))

Line 3395: DELETE FROM PA_RESOURCES_DENORM

3391: IF P_DEBUG_MODE = 'Y' THEN
3392: pa_debug.write_file('start_crm_workflow: ' || 'LOG',pa_debug.g_err_stage);
3393: END IF;
3394:
3395: DELETE FROM PA_RESOURCES_DENORM
3396: WHERE person_id = l_person_id
3397: AND resource_effective_start_date > l_end_date;
3398:
3399: log_message('right after calling deleting denorm data');

Line 3417: /* Removing data from pa_resources_demorn as per 7336526 */

3413: /*2749061: Do not create FI for past employees*/
3414: IF (trunc(l_end_date) = trunc(l_assignment_end_date)) THEN
3415: -- AND trunc(l_end_date) >= sysdate) THEN
3416:
3417: /* Removing data from pa_resources_demorn as per 7336526 */
3418: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL) THEN
3419: FOR delrec2 IN cur_denorm_del_redundant(l_person_id, l_prof_date) LOOP
3420:
3421: DELETE FROM pa_resources_denorm

Line 3421: DELETE FROM pa_resources_denorm

3417: /* Removing data from pa_resources_demorn as per 7336526 */
3418: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL) THEN
3419: FOR delrec2 IN cur_denorm_del_redundant(l_person_id, l_prof_date) LOOP
3420:
3421: DELETE FROM pa_resources_denorm
3422: WHERE RESOURCE_EFFECTIVE_START_DATE = delrec2.RESOURCE_EFFECTIVE_START_DATE
3423: AND PERSON_ID = delrec2.PERSON_ID;
3424:
3425: END LOOP ;

Line 3432: DELETE FROM pa_resources_denorm

3428:
3429: /*Code starts for Bug 6943551*/
3430: FOR delrec IN cur_denorm_del(l_person_id) LOOP
3431:
3432: DELETE FROM pa_resources_denorm
3433: WHERE RESOURCE_EFFECTIVE_START_DATE = delrec.RESOURCE_EFFECTIVE_START_DATE;
3434:
3435: END LOOP ;
3436: /*Code ends for Bug 6943551*/

Line 3669: /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/

3665:
3666: END;
3667:
3668: END LOOP;
3669: /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/
3670: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL AND p_pull_term_res = 'Y') THEN
3671: FOR delrec3 IN cur_denorm_del_term(l_prof_date) LOOP
3672:
3673: DELETE FROM pa_resources_denorm

Line 3673: DELETE FROM pa_resources_denorm

3669: /* Placing this outside the loop so as to only make it execute once. removing data from pa_resources_denorm as per Bug 7336526*/
3670: IF (l_refresh = 'Y' AND l_prof_date IS NOT NULL AND p_pull_term_res = 'Y') THEN
3671: FOR delrec3 IN cur_denorm_del_term(l_prof_date) LOOP
3672:
3673: DELETE FROM pa_resources_denorm
3674: WHERE person_id = delrec3.person_id
3675: AND resource_effective_start_date = delrec3.assignment_start_date
3676: AND resource_organization_id = l_p_org_id;
3677: