114: 0 MAU,
115: 0 RATE2,
116: 0 MAU2
117: from
118: dual
119: union all
120: select
121: p_worker_id WORKER_ID,
122: PF_CURRENCY_CODE PF_CURRENCY_CODE,
167: WORKER_ID = p_worker_id
168: ) tmp1,
169: PJI_ORG_EXTR_INFO res_info,
170: (
171: select 'PRVDR_GL' INVERT_ID from dual union all
172: select 'RECVR_GL' INVERT_ID from dual union all
173: select 'PRVDR_PA' INVERT_ID from dual union all
174: select 'RECVR_PA' INVERT_ID from dual union all
175: select 'EXP' INVERT_ID from dual
168: ) tmp1,
169: PJI_ORG_EXTR_INFO res_info,
170: (
171: select 'PRVDR_GL' INVERT_ID from dual union all
172: select 'RECVR_GL' INVERT_ID from dual union all
173: select 'PRVDR_PA' INVERT_ID from dual union all
174: select 'RECVR_PA' INVERT_ID from dual union all
175: select 'EXP' INVERT_ID from dual
176: ) invert
169: PJI_ORG_EXTR_INFO res_info,
170: (
171: select 'PRVDR_GL' INVERT_ID from dual union all
172: select 'RECVR_GL' INVERT_ID from dual union all
173: select 'PRVDR_PA' INVERT_ID from dual union all
174: select 'RECVR_PA' INVERT_ID from dual union all
175: select 'EXP' INVERT_ID from dual
176: ) invert
177: where
170: (
171: select 'PRVDR_GL' INVERT_ID from dual union all
172: select 'RECVR_GL' INVERT_ID from dual union all
173: select 'PRVDR_PA' INVERT_ID from dual union all
174: select 'RECVR_PA' INVERT_ID from dual union all
175: select 'EXP' INVERT_ID from dual
176: ) invert
177: where
178: tmp1.PROJECT_ORG_ID = prj_info.ORG_ID and
171: select 'PRVDR_GL' INVERT_ID from dual union all
172: select 'RECVR_GL' INVERT_ID from dual union all
173: select 'PRVDR_PA' INVERT_ID from dual union all
174: select 'RECVR_PA' INVERT_ID from dual union all
175: select 'EXP' INVERT_ID from dual
176: ) invert
177: where
178: tmp1.PROJECT_ORG_ID = prj_info.ORG_ID and
179: tmp1.EXPENDITURE_ORG_ID = res_info.ORG_ID
217: RECORD_TYPE = 'A'
218: ) tmp2,
219: PJI_ORG_EXTR_INFO res_info,
220: (
221: select 'PRVDR_GL' INVERT_ID from dual union all
222: select 'RECVR_GL' INVERT_ID from dual union all
223: select 'PRVDR_PA' INVERT_ID from dual union all
224: select 'RECVR_PA' INVERT_ID from dual union all
225: select 'EXP' INVERT_ID from dual
218: ) tmp2,
219: PJI_ORG_EXTR_INFO res_info,
220: (
221: select 'PRVDR_GL' INVERT_ID from dual union all
222: select 'RECVR_GL' INVERT_ID from dual union all
223: select 'PRVDR_PA' INVERT_ID from dual union all
224: select 'RECVR_PA' INVERT_ID from dual union all
225: select 'EXP' INVERT_ID from dual
226: ) invert
219: PJI_ORG_EXTR_INFO res_info,
220: (
221: select 'PRVDR_GL' INVERT_ID from dual union all
222: select 'RECVR_GL' INVERT_ID from dual union all
223: select 'PRVDR_PA' INVERT_ID from dual union all
224: select 'RECVR_PA' INVERT_ID from dual union all
225: select 'EXP' INVERT_ID from dual
226: ) invert
227: where
220: (
221: select 'PRVDR_GL' INVERT_ID from dual union all
222: select 'RECVR_GL' INVERT_ID from dual union all
223: select 'PRVDR_PA' INVERT_ID from dual union all
224: select 'RECVR_PA' INVERT_ID from dual union all
225: select 'EXP' INVERT_ID from dual
226: ) invert
227: where
228: map.WORKER_ID = p_worker_id and
221: select 'PRVDR_GL' INVERT_ID from dual union all
222: select 'RECVR_GL' INVERT_ID from dual union all
223: select 'PRVDR_PA' INVERT_ID from dual union all
224: select 'RECVR_PA' INVERT_ID from dual union all
225: select 'EXP' INVERT_ID from dual
226: ) invert
227: where
228: map.WORKER_ID = p_worker_id and
229: map.PROJECT_ID = tmp2.PROJECT_ID and
253: where
254: WORKER_ID = p_worker_id
255: ) tmp1,
256: (
257: select 'GL' INVERT_ID from dual union all
258: select 'PA' INVERT_ID from dual
259: ) invert
260: where
261: tmp1.PROJECT_ORG_ID = prj_info.ORG_ID
254: WORKER_ID = p_worker_id
255: ) tmp1,
256: (
257: select 'GL' INVERT_ID from dual union all
258: select 'PA' INVERT_ID from dual
259: ) invert
260: where
261: tmp1.PROJECT_ORG_ID = prj_info.ORG_ID
262: union
284: where
285: WORKER_ID = 0
286: ) tmp2,
287: (
288: select 'GL' INVERT_ID from dual union all
289: select 'PA' INVERT_ID from dual
290: ) invert
291: where
292: map.WORKER_ID = p_worker_id and
285: WORKER_ID = 0
286: ) tmp2,
287: (
288: select 'GL' INVERT_ID from dual union all
289: select 'PA' INVERT_ID from dual
290: ) invert
291: where
292: map.WORKER_ID = p_worker_id and
293: map.PROJECT_ID = tmp2.PROJECT_ID and
324: where
325: l_extract_commitments = 'Y'
326: ) tmp2,
327: (
328: select 'GL' INVERT_ID from dual union all
329: select 'PA' INVERT_ID from dual
330: ) invert
331: where
332: l_extract_commitments = 'Y' and
325: l_extract_commitments = 'Y'
326: ) tmp2,
327: (
328: select 'GL' INVERT_ID from dual union all
329: select 'PA' INVERT_ID from dual
330: ) invert
331: where
332: l_extract_commitments = 'Y' and
333: map.WORKER_ID = p_worker_id and
340: 'J')) GL_TIME_ID,
341: to_number(to_char(trunc(sysdate),
342: 'J')) PA_TIME_ID
343: from
344: dual
345: ) tmp2,
346: (
347: select 'GL' INVERT_ID from dual union all
348: select 'PA' INVERT_ID from dual
343: from
344: dual
345: ) tmp2,
346: (
347: select 'GL' INVERT_ID from dual union all
348: select 'PA' INVERT_ID from dual
349: ) invert
350: where
351: l_extract_commitments = 'Y' and
344: dual
345: ) tmp2,
346: (
347: select 'GL' INVERT_ID from dual union all
348: select 'PA' INVERT_ID from dual
349: ) invert
350: where
351: l_extract_commitments = 'Y' and
352: map.WORKER_ID = p_worker_id and
6378: sum(tmp2.TOTAL_HRS_A) QUANTITY,
6379: sum(tmp2.BILL_HRS_A) BILL_QUANTITY
6380: from
6381: (
6382: select 'GL' INVERT_ID from DUAL union all
6383: select 'PA' INVERT_ID from DUAL
6384: ) invert,
6385: ( -- Modified for bug 10071936
6386: select /*+ ordered
6379: sum(tmp2.BILL_HRS_A) BILL_QUANTITY
6380: from
6381: (
6382: select 'GL' INVERT_ID from DUAL union all
6383: select 'PA' INVERT_ID from DUAL
6384: ) invert,
6385: ( -- Modified for bug 10071936
6386: select /*+ ordered
6387: full(fin2) use_hash(fin2) parallel(fin2)
7342: 'PA', tmp2.GP2_AR_AMOUNT_OVERDUE))
7343: G2_AR_AMOUNT_OVERDUE
7344: from
7345: (
7346: select 'GL' INVERT_ID from DUAL union all
7347: select 'PA' INVERT_ID from DUAL
7348: ) invert,
7349: PJI_FM_AGGR_ACT2 tmp2
7350: where
7343: G2_AR_AMOUNT_OVERDUE
7344: from
7345: (
7346: select 'GL' INVERT_ID from DUAL union all
7347: select 'PA' INVERT_ID from DUAL
7348: ) invert,
7349: PJI_FM_AGGR_ACT2 tmp2
7350: where
7351: tmp2.WORKER_ID = p_worker_id