[Home] [Help]
74: i_org_id, b.wip_entity_id,
75: i_close_period_id, 5,
76: oa.schedule_close_date, l_group_id,
77: NULL
78: FROM wip_period_balances b,
79: org_acct_periods oa,
80: wip_discrete_jobs j
81: WHERE b.class_type IN (4,6) -- Bug #2357983.
82: /* modified for EAM (class_type = 6); modified for OSFM (class_type = 7) */
162: TRANSACTION_DATE, WIP_ENTITY_ID,
163: REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
164: TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
165: CONTRA_SET_ID, COST_ELEMENT_ID )
166: SELECT /*+ ORDERED INDEX(WPB WIP_PERIOD_BALANCES_N1) */
167: wcti.transaction_id,
168: decode(cce.cost_element_id,
169: 1, wdj.material_account,
170: 2, wdj.material_overhead_account,
214: 0, max(cce.cost_element_id), NULL)
215: from
216: wip_cost_txn_interface wcti,
217: wip_discrete_jobs wdj,
218: wip_period_balances wpb,
219: cst_cost_elements cce
220: WHERE wcti.group_id = l_group_id
221: AND wcti.line_id IS NULL
222: AND wdj.wip_entity_id = wcti.wip_entity_id
248: TRANSACTION_DATE, WIP_ENTITY_ID,
249: REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
250: TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
251: CONTRA_SET_ID, COST_ELEMENT_ID )
252: SELECT /*+ ORDERED INDEX(WPB WIP_PERIOD_BALANCES_N1) */
253: wcti.transaction_id,
254: decode(cce.cost_element_id,
255: 1, wdj.material_variance_account,
256: 3, wdj.resource_variance_account,
299: 0, max(cce.cost_element_id), NULL)
300: from
301: wip_cost_txn_interface wcti,
302: wip_discrete_jobs wdj,
303: wip_period_balances wpb,
304: cst_cost_elements cce
305: WHERE wcti.group_id = l_group_id
306: AND wcti.line_id IS NULL
307: AND wdj.wip_entity_id = wcti.wip_entity_id
325: * Update variance columns for expense/eam jobs
326: ****************************************************************/
327: where_num := 160;
328: /* Replaced with the query below for bug 12402436
329: UPDATE WIP_PERIOD_BALANCES wpb
330: SET (LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
331: PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
332: PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
333: PL_OVERHEAD_VAR, TL_MATERIAL_VAR,
363: - decode(acct_period_id,i_close_period_id,0,NVL(TL_OUTSIDE_PROCESSING_VAR,0))),
364: SUM( NVL(TL_OVERHEAD_IN,0)
365: - NVL(TL_OVERHEAD_OUT,0)
366: - decode(acct_period_id,i_close_period_id,0,NVL(TL_OVERHEAD_VAR,0)))
367: FROM WIP_PERIOD_BALANCES wpb2
368: WHERE wpb2.wip_entity_id = wpb.wip_entity_id
369: AND wpb2.acct_period_id <= wpb.acct_period_id)
370: /* sum across all periods */
371: /* WHERE (wpb.acct_period_id, wpb.wip_entity_id) IN
373: FROM WIP_COST_TXN_INTERFACE i
374: WHERE i.group_id = l_group_id
375: AND i.line_id IS NULL);*/
376:
377: UPDATE WIP_PERIOD_BALANCES wpb
378: SET LAST_UPDATED_BY = i_user_id, LAST_UPDATE_DATE = SYSDATE, LAST_UPDATE_LOGIN = i_login_id,
379: PL_MATERIAL_VAR =
380: (NVL(PL_MATERIAL_IN,0)
381: - NVL(PL_MATERIAL_OUT,0)),
441: WHERE oa.organization_id = i_org_id
442: AND oa.acct_period_id = i_close_period_id
443: AND (wri.wip_entity_id, wri.line_id) IN
444: (SELECT s.wip_entity_id, s.line_id
445: FROM wip_period_balances b,
446: wip_repetitive_schedules s,
447: org_acct_periods a
448: WHERE b.acct_period_id = i_close_period_id
449: /* only if the schedule has a balance row in this period */
481: b.organization_id,
482: SYSDATE, i_user_id, SYSDATE, i_user_id, i_login_id,
483: 0, 0
484: FROM wip_cost_txn_interface i,
485: wip_period_balances b,
486: wip_repetitive_schedules s,
487: org_acct_periods a
488: WHERE i.group_id = l_group_id
489: AND i.line_id IS NOT NULL
571: 0, max(cce.cost_element_id), NULL)
572: from
573: wip_cost_txn_interface wcti,
574: wip_txn_allocations alloc,
575: wip_period_balances wpb,
576: cst_cost_elements cce,
577: wip_repetitive_schedules wrs
578: WHERE wcti.group_id = l_group_id
579: AND wcti.line_id IS NOT NULL
663: 0, max(cce.cost_element_id), NULL)
664: from
665: wip_cost_txn_interface wcti,
666: wip_txn_allocations alloc,
667: wip_period_balances wpb,
668: cst_cost_elements cce,
669: wip_repetitive_schedules wrs
670: WHERE wcti.group_id = l_group_id
671: AND wcti.line_id IS NOT NULL
695: | Update variance columns for schedules
696: +---------------------------------------------------------------*/
697: where_num := 260;
698: /* Replaced with the query below for bug 12402436
699: UPDATE WIP_PERIOD_BALANCES wpb
700: SET (LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
701: PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
702: PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
703: PL_OVERHEAD_VAR, TL_MATERIAL_VAR,
733: - decode(acct_period_id,i_close_period_id,0,NVL(TL_OUTSIDE_PROCESSING_VAR,0))),
734: SUM( NVL(TL_OVERHEAD_IN,0)
735: - NVL(TL_OVERHEAD_OUT,0)
736: - decode(acct_period_id,i_close_period_id,0,NVL(TL_OVERHEAD_VAR,0)))
737: FROM WIP_PERIOD_BALANCES wpb2
738: WHERE wpb2.wip_entity_id = wpb.wip_entity_id
739: AND wpb2.acct_period_id <= wpb.acct_period_id
740: /* sum across all acct periods */
741: /*AND wpb2.organization_id = wpb.organization_id
750: WHERE i.group_id = l_group_id
751: AND i.transaction_id = alloc.transaction_id
752: AND i.line_id IS NOT NULL);*/
753:
754: UPDATE WIP_PERIOD_BALANCES wpb
755: SET LAST_UPDATED_BY = i_user_id, LAST_UPDATE_DATE = SYSDATE, LAST_UPDATE_LOGIN = i_login_id,
756: PL_MATERIAL_VAR =
757: (NVL(PL_MATERIAL_IN,0)
758: - NVL(PL_MATERIAL_OUT,0)),
851: * completion date
852: **************************************************************/
853: where_num := 600;
854:
855: DELETE FROM wip_period_balances wpb
856: WHERE wpb.acct_period_id > i_close_period_id
857: AND wpb.organization_id = i_org_id
858: AND (wpb.wip_entity_id, wpb.repetitive_schedule_id) IN
859: (SELECT i.wip_entity_id,