[Home] [Help]
100: log.ATTRIBUTE18,
101: log.ATTRIBUTE19,
102: log.ATTRIBUTE20
103: from
104: PJI_PJP_PROJ_BATCH_MAP map,
105: PA_PJI_PROJ_EVENTS_LOG log,
106: PA_RBS_VERSIONS_B ver
107: where
108: map.WORKER_ID = p_worker_id and
142: log.ATTRIBUTE18,
143: log.ATTRIBUTE19,
144: log.ATTRIBUTE20
145: from
146: PJI_PJP_PROJ_BATCH_MAP map,
147: PA_PJI_PROJ_EVENTS_LOG log
148: where
149: map.WORKER_ID = p_worker_id and
150: log.EVENT_TYPE = 'PRG_CHANGE' and
186: select /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N3) */
187: distinct
188: ver.PRG_GROUP
189: from
190: PJI_PJP_PROJ_BATCH_MAP map,
191: PA_PROJ_ELEMENT_VERSIONS ver
192: where
193: map.WORKER_ID = p_worker_id and
194: map.PROJECT_ID = ver.PROJECT_ID and
237: distinct
238: asg.RBS_HEADER_ID,
239: asg.RBS_VERSION_ID
240: from
241: PJI_PJP_PROJ_BATCH_MAP map,
242: PA_RBS_PRJ_ASSIGNMENTS asg
243: where
244: map.WORKER_ID = p_worker_id and
245: asg.PROJECT_ID = map.PROJECT_ID and
281: log.ATTRIBUTE18,
282: log.ATTRIBUTE19,
283: log.ATTRIBUTE20
284: from
285: PJI_PJP_PROJ_BATCH_MAP map,
286: PA_RBS_PRJ_ASSIGNMENTS asg,
287: PA_PJI_PROJ_EVENTS_LOG log
288: where
289: map.WORKER_ID = p_worker_id and
386: log.ATTRIBUTE19,
387: log.ATTRIBUTE20
388: from
389: PA_PJI_PROJ_EVENTS_LOG log,
390: PJI_PJP_PROJ_BATCH_MAP map
391: where
392: map.WORKER_ID = p_worker_id and
393: log.EVENT_TYPE = 'PRG_CHANGE' and
394: log.EVENT_OBJECT = -1 and
1188: cursor required_rbs (p_worker_id in number) is
1189: select
1190: asg.RBS_VERSION_ID
1191: from
1192: PJI_PJP_PROJ_BATCH_MAP map,
1193: PA_RBS_PRJ_ASSIGNMENTS asg
1194: where
1195: map.WORKER_ID = p_worker_id and
1196: asg.PROJECT_ID = map.PROJECT_ID and
1364: l_creation_date,
1365: l_created_by,
1366: l_last_update_login
1367: from
1368: PJI_PJP_PROJ_BATCH_MAP map,
1369: PA_XBS_DENORM pa
1370: where
1371: map.WORKER_ID = p_worker_id and
1372: pa.SUP_PROJECT_ID = map.PROJECT_ID and
1423: l_creation_date,
1424: l_created_by,
1425: l_last_update_login
1426: from
1427: PJI_PJP_PROJ_BATCH_MAP map,
1428: PA_XBS_DENORM pa
1429: where
1430: map.WORKER_ID = p_worker_id and
1431: pa.SUP_PROJECT_ID = map.PROJECT_ID and
1444:
1445: delete
1446: from PJI_REP_XBS_DENORM;
1447: -- where PROJECT_ID in (select map.PROJECT_ID
1448: -- from PJI_PJP_PROJ_BATCH_MAP map
1449: -- where map.WORKER_ID = p_worker_id);
1450:
1451: end if;
1452:
1588: select
1589: distinct
1590: prj_emt.PROJECT_ID
1591: from
1592: PJI_PJP_PROJ_BATCH_MAP map,
1593: PA_PROJ_ELEMENTS prj_emt
1594: where
1595: map.WORKER_ID = p_worker_id and
1596: prj_emt.OBJECT_TYPE = 'PA_STRUCTURES' and
1612: wbs_hdr.LAST_UPDATED_BY = l_last_updated_by,
1613: wbs_hdr.LAST_UPDATE_LOGIN = l_last_update_login
1614: where wbs_hdr.PROJECT_ID in
1615: (select map.PROJECT_ID
1616: from PJI_PJP_PROJ_BATCH_MAP map
1617: where map.WORKER_ID = p_worker_id) and
1618: wbs_hdr.PLAN_VERSION_ID = -1 and
1619: wbs_hdr.WBS_VERSION_ID in
1620: (select log.EVENT_OBJECT
1848: 'N') PROG_REP_USAGE_FLAG,
1849: 'Y' REPORTING_USAGE_FLAG,
1850: 'N' FP_USAGE_FLAG
1851: from
1852: PJI_PJP_PROJ_BATCH_MAP map,
1853: PA_RBS_PRJ_ASSIGNMENTS asg,
1854: PA_XBS_DENORM den1,
1855: PA_XBS_DENORM den2
1856: where
2083: l_created_by,
2084: l_last_update_login,
2085: 'A'
2086: from
2087: PJI_PJP_PROJ_BATCH_MAP map,
2088: PA_RBS_PRJ_ASSIGNMENTS asg,
2089: PJI_PJP_RBS_HEADER rbs_hdr
2090: where
2091: map.WORKER_ID = p_worker_id and
2124: l_created_by,
2125: l_last_update_login,
2126: 'A'
2127: from
2128: PJI_PJP_PROJ_BATCH_MAP map
2129: where
2130: map.WORKER_ID = p_worker_id;
2131:
2132: elsif (l_extraction_type = 'INCREMENTAL' or
3747: to_number(null) G2_OTH_COMMITTED_COST,
3748: bal.QUANTITY,
3749: bal.BILL_QUANTITY
3750: from
3751: PJI_PJP_PROJ_BATCH_MAP map,
3752: PJI_FP_TXN_ACCUM bal,
3753: PJI_PJP_RBS_HEADER rbs_hdr
3754: where
3755: l_extraction_type in ('FULL', 'PARTIAL') and
3827: bal.G2_OTH_COMMITTED_COST,
3828: to_number(null) QUANTITY,
3829: to_number(null) BILL_QUANTITY
3830: from
3831: PJI_PJP_PROJ_BATCH_MAP map,
3832: PJI_FP_TXN_ACCUM1 bal,
3833: PJI_PJP_RBS_HEADER rbs_hdr
3834: where
3835: l_extraction_type in ('FULL', 'PARTIAL') and
4258: src1.TXN_CURRENCY_CODE
4259: ) src2
4260: ) src,
4261: PA_RBS_TXN_ACCUM_MAP rbs,
4262: PJI_PJP_PROJ_BATCH_MAP map,
4263: PJI_ORG_EXTR_INFO info,
4264: PJI_PJP_WBS_HEADER wbs_hdr,
4265: PA_PROJ_ELEMENT_VERSIONS ver,
4266: (
4955: prj.PROJECT_CURRENCY_CODE,
4956: src.TXN_CURRENCY_CODE
4957: )
4958: ) src,
4959: PJI_PJP_PROJ_BATCH_MAP map,
4960: PJI_PJP_WBS_HEADER wbs_hdr,
4961: PA_PROJ_ELEMENT_VERSIONS ver,
4962: (
4963: select 1 INVERT_ID from dual
5062:
5063: if (l_extraction_type = 'PARTIAL' or
5064: l_extraction_type = 'RBS') then
5065:
5066: update PJI_PJP_PROJ_BATCH_MAP
5067: set PJI_PROJECT_STATUS = 'Y'
5068: where WORKER_ID = p_worker_id and
5069: PJI_PROJECT_STATUS is null;
5070:
5071: elsif (l_extraction_type = 'FULL' or
5072: l_extraction_type = 'INCREMENTAL') then
5073:
5074: /*bug#5349102 added the check for FIN8 */
5075: update PJI_PJP_PROJ_BATCH_MAP
5076: set PJI_PROJECT_STATUS = 'Y'
5077: where WORKER_ID = p_worker_id and
5078: PJI_PROJECT_STATUS is null and
5079: PROJECT_ID in (select distinct
5097: end if;
5098:
5099: if (l_extraction_type = 'INCREMENTAL') then
5100:
5101: update PJI_PJP_PROJ_BATCH_MAP map
5102: set map.EXTRACTION_TYPE = 'M'
5103: where map.WORKER_ID = p_worker_id and
5104: map.PROJECT_ID in (select fin7.PROJECT_ID
5105: from PJI_PJP_RMAP_FPR fin7_r,
5107: where fin7_r.WORKER_ID = p_worker_id and
5108: fin7_r.RECORD_TYPE = 'M' and
5109: fin7.ROWID = fin7_r.STG_ROWID);
5110:
5111: update PJI_PJP_PROJ_BATCH_MAP map
5112: set map.EXTRACTION_TYPE = 'M'
5113: where map.WORKER_ID = p_worker_id and
5114: map.PROJECT_ID not in (select cmt.PROJECT_ID
5115: from PA_COMMITMENT_TXNS cmt);
5204: select
5205: map.worker_id,bv.project_id,bv.budget_version_id,
5206: nvl(fpo.all_time_phased_code,nvl(fpo.cost_time_phased_code,fpo.revenue_time_phased_code)) time_phased_code
5207: from
5208: PJI_PJP_PROJ_BATCH_MAP map,
5209: PA_PROJ_FP_OPTIONS fpo,
5210: PA_BUDGET_VERSIONS bv
5211: where
5212: map.WORKER_ID = p_worker_id and
5252: UNION ALL --Pull Reversals for Actuals , CB,CO when l_refresh_code=-1,-3,-4
5253: select
5254: map.worker_id,map.project_id, plan_version_id, 'G' time_phased_code
5255: from
5256: PJI_PJP_PROJ_BATCH_MAP map,
5257: (
5258: select decode (bitand (l_refresh_code,1),1,-1,-999) plan_version_id from dual where l_plan_type_id is null
5259: union all
5260: select decode (bitand (l_refresh_code,g_cb_plans),g_cb_plans,-3,-999) plan_version_id from dual
5655: to_number(null) CUSTOM13,
5656: to_number(null) CUSTOM14,
5657: to_number(null) CUSTOM15
5658: from
5659: PJI_PJP_PROJ_BATCH_MAP map,
5660: PJI_FP_XBS_ACCUM_F fpr
5661: where
5662: l_extraction_type = 'INCREMENTAL' and
5663: map.WORKER_ID = p_worker_id and
6179: - acr.CUSTOM13 CUSTOM13,
6180: - acr.CUSTOM14 CUSTOM14,
6181: - acr.CUSTOM15 CUSTOM15
6182: from
6183: PJI_PJP_PROJ_BATCH_MAP map,
6184: PJI_AC_XBS_ACCUM_F acr
6185: where
6186: l_extraction_type = 'PARTIAL' and
6187: map.WORKER_ID = p_worker_id and
6597: to_number(null) CUSTOM13,
6598: to_number(null) CUSTOM14,
6599: to_number(null) CUSTOM15
6600: from
6601: PJI_PJP_PROJ_BATCH_MAP map,
6602: PJI_AC_XBS_ACCUM_F acr,
6603: PJI_AC_AGGR_PJP0 pjp0, -- Bug 6689297
6604: PJI_TIME_RPT_STRUCT cal,
6605: PJI_TIME_ENT_PERIOD_V ent,
6740: to_number(null) CUSTOM13,
6741: to_number(null) CUSTOM14,
6742: to_number(null) CUSTOM15
6743: from
6744: PJI_PJP_PROJ_BATCH_MAP map,
6745: PJI_AC_XBS_ACCUM_F acr,
6746: PJI_AC_AGGR_PJP0 pjp0, -- Bug 6689297
6747: PJI_TIME_CAL_RPT_STRUCT cal,
6748: PJI_ORG_EXTR_INFO info,
6887: to_number(null) CUSTOM13,
6888: to_number(null) CUSTOM14,
6889: to_number(null) CUSTOM15
6890: from
6891: PJI_PJP_PROJ_BATCH_MAP map,
6892: PJI_AC_XBS_ACCUM_F acr,
6893: PJI_AC_AGGR_PJP0 pjp0, -- Bug 6689297
6894: PJI_TIME_CAL_RPT_STRUCT cal,
6895: PJI_ORG_EXTR_INFO info,
7616: nvl(max(den.SUP_LEVEL), 0)
7617: into
7618: l_level
7619: from
7620: PJI_PJP_PROJ_BATCH_MAP map,
7621: PJI_XBS_DENORM den
7622: where
7623: map.WORKER_ID = p_worker_id and
7624: den.STRUCT_TYPE = 'PRG' and
8862: 'LW', 'Y',
8863: 'LF', 'N') WP_FLAG,
8864: 'Y' PUSHUP_FLAG
8865: from
8866: PJI_PJP_PROJ_BATCH_MAP map,
8867: PJI_XBS_DENORM prg,
8868: (
8869: select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
8870: select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
12083: nvl(max(den.SUP_LEVEL), 0)
12084: into
12085: l_level
12086: from
12087: PJI_PJP_PROJ_BATCH_MAP map,
12088: PJI_XBS_DENORM den
12089: where
12090: map.WORKER_ID = p_worker_id and
12091: den.STRUCT_TYPE = 'PRG' and
12658: prg.SUB_ROLLUP_ID,
12659: prg.RELATIONSHIP_TYPE RELATIONSHIP_TYPE,
12660: 'Y' PUSHUP_FLAG
12661: from
12662: PJI_PJP_PROJ_BATCH_MAP map,
12663: PJI_XBS_DENORM prg,
12664: PJI_XBS_DENORM_DELTA prg_delta
12665: where
12666: prg.STRUCT_TYPE = 'PRG' and
13468: select
13469: SUP_LEVEL
13470: from
13471: PA_XBS_DENORM den,
13472: PJI_PJP_PROJ_BATCH_MAP map
13473: where
13474: map.WORKER_ID = p_worker_id and
13475: den.STRUCT_TYPE = 'PRG' and
13476: den.SUP_PROJECT_ID = map.PROJECT_ID
14504: 'STRUCTURE_PUBLISHED' = sup_ver.STATUS_CODE (+) and
14505: wbs_hdr.CB_FLAG = fin_plan.CB (+) and
14506: wbs_hdr.CO_FLAG = fin_plan.CO (+)
14507: ) pjp,
14508: PJI_PJP_PROJ_BATCH_MAP map,
14509: PA_PROJ_WORKPLAN_ATTR sup_wpa,
14510: PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
14511: PA_XBS_DENORM wbs,
14512: (
14706: select
14707: SUP_LEVEL
14708: from
14709: PA_XBS_DENORM den,
14710: PJI_PJP_PROJ_BATCH_MAP map
14711: where
14712: map.WORKER_ID = p_worker_id and
14713: den.STRUCT_TYPE = 'PRG' and
14714: den.SUP_PROJECT_ID = map.PROJECT_ID
15053: wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
15054: wbs_hdr.PROJECT_ID = pjp1.PROJECT_ID and
15055: wbs_hdr.PLAN_VERSION_ID = -1
15056: ) pjp,
15057: PJI_PJP_PROJ_BATCH_MAP map,
15058: PA_PROJ_WORKPLAN_ATTR sup_wpa,
15059: PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
15060: PA_XBS_DENORM wbs,
15061: (
16572:
16573: BEGIN
16574: select distinct ver.wbs_version_id bulk collect
16575: into l_wbs_version_id_tbl
16576: from PJI_PJP_PROJ_BATCH_MAP map,Pji_pjp_wbs_header ver
16577: where map.PROJECT_ID=ver.project_id
16578: and map.WORKER_ID = p_worker_id;
16579: EXCEPTION
16580: WHEN NO_DATA_FOUND then null;
16582:
16583: delete
16584: from PJI_XBS_DENORM
16585: where SUP_PROJECT_ID in (select map.PROJECT_ID
16586: from PJI_PJP_PROJ_BATCH_MAP map
16587: where WORKER_ID = p_worker_id);
16588:
16589: l_count := l_count + sql%rowcount;
16590:
16630: l_creation_date,
16631: l_created_by,
16632: l_last_update_login
16633: from
16634: PJI_PJP_PROJ_BATCH_MAP map,
16635: PA_XBS_DENORM den
16636: where
16637: map.WORKER_ID = p_worker_id and
16638: den.SUP_PROJECT_ID = map.PROJECT_ID;
16645:
16646: -- delete from PJI_REP_XBS_DENORM;
16647: pji_rep_util.Log_Struct_Change_Event(l_wbs_version_id_tbl);
16648: -- where PROJECT_ID in (select map.PROJECT_ID
16649: -- from PJI_PJP_PROJ_BATCH_MAP map
16650: -- where map.WORKER_ID = p_worker_id);
16651:
16652: end if;
16653:
17145: select
17146: ROWNUM,
17147: PROJECT_ID
17148: from
17149: PJI_PJP_PROJ_BATCH_MAP
17150: where
17151: WORKER_ID = p_worker_id and
17152: PJI_PROJECT_STATUS = 'Y' and
17153: rownum <= G_PROGRESS_COMMIT_SIZE;
17222: -- condition. It is to be commented at all times in checked
17223: -- in code.
17224:
17225: forall j in 1..l_project_id_tbl.COUNT
17226: update PJI_PJP_PROJ_BATCH_MAP
17227: set pji_project_status =
17228: decode(l_return_status
17229: , FND_API.G_RET_STS_SUCCESS
17230: , 'S' -- Success
17245: END LOOP;
17246:
17247: if l_success <> 'Y' then
17248:
17249: update pji_pjp_proj_batch_map -- Process the pending projects in the next sumz run.
17250: set pji_project_status = 'Y'
17251: where
17252: WORKER_ID = p_worker_id and
17253: pji_project_status = 'P';
17258: end if;
17259:
17260: -- Reset flags. 'N' means actuals don't exist for this project.
17261: -- Reset flags for only those projects for which actuals exist.
17262: update pji_pjp_proj_batch_map
17263: set pji_project_status = 'Y'
17264: where
17265: WORKER_ID = p_worker_id and
17266: pji_project_status <> 'N';
17296: cursor project_list (p_worker_id in number) is
17297: SELECT nvl(ver.PRG_LEVEL,1) PRG_LEVEL,
17298: ver.PROJECT_ID
17299: FROM
17300: pji_pjp_proj_batch_map map,
17301: pa_proj_element_versions ver,
17302: pa_proj_structure_types typ,
17303: pa_proj_elem_ver_structure str
17304: WHERE
17337:
17338: if (l_extraction_type = 'FULL' or
17339: l_extraction_type = 'INCREMENTAL') then
17340:
17341: update PJI_PJP_PROJ_BATCH_MAP
17342: set PJI_PROJECT_STATUS = 'Y'
17343: where WORKER_ID = p_worker_id and
17344: PJI_PROJECT_STATUS is null and
17345: PROJECT_ID in (select distinct
17423: set LOCK_FLAG = null
17424: where LOCK_FLAG is not null
17425: and project_id IN
17426: ( SELECT project_id
17427: FROM pji_pjp_proj_batch_map
17428: WHERE worker_id = p_worker_id );
17429:
17430: -- SELECT DECODE(l_extraction_type, 'PARTIAL', 'PLANTYPE', l_extraction_type)
17431: -- INTO l_extraction_type