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
6368: sum(tmp2.TOTAL_HRS_A) QUANTITY,
6369: sum(tmp2.BILL_HRS_A) BILL_QUANTITY
6370: from
6371: (
6372: select 'GL' INVERT_ID from DUAL union all
6373: select 'PA' INVERT_ID from DUAL
6374: ) invert,
6375: PJI_FM_AGGR_FIN2 tmp2
6376: where
6369: sum(tmp2.BILL_HRS_A) BILL_QUANTITY
6370: from
6371: (
6372: select 'GL' INVERT_ID from DUAL union all
6373: select 'PA' INVERT_ID from DUAL
6374: ) invert,
6375: PJI_FM_AGGR_FIN2 tmp2
6376: where
6377: tmp2.WORKER_ID = p_worker_id and
6962: 'PA', tmp2.GP2_AR_AMOUNT_OVERDUE))
6963: G2_AR_AMOUNT_OVERDUE
6964: from
6965: (
6966: select 'GL' INVERT_ID from DUAL union all
6967: select 'PA' INVERT_ID from DUAL
6968: ) invert,
6969: PJI_FM_AGGR_ACT2 tmp2
6970: where
6963: G2_AR_AMOUNT_OVERDUE
6964: from
6965: (
6966: select 'GL' INVERT_ID from DUAL union all
6967: select 'PA' INVERT_ID from DUAL
6968: ) invert,
6969: PJI_FM_AGGR_ACT2 tmp2
6970: where
6971: tmp2.WORKER_ID = p_worker_id