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.WIP_BIS_PERIOD_BAL_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 13


select fou.oracle_username into l_wip_schema
  from FND_PRODUCT_INSTALLATIONS FPI,
       FND_ORACLE_USERID FOU,
       FND_APPLICATION FA
 where fpi.application_id = fa.application_id
   and fpi.oracle_id = fou.oracle_id
   and fa.application_short_name = 'WIP';

Line: 32


insert /*+ APPEND */ into WIP_BIS_PERIOD_BALANCE
( ORGANIZATION_ID,
  ORGANIZATION_NAME,
  LEGAL_ENTITY,
  SCHEDULE_CLOSE_DATE,
  CATEGORY_ID,
  CONCATENATED_SEGMENTS,
  PERIOD_SET_NAME,
  PERIOD_NAME,
  BALANCE
)
SELECT
	WDJ.ORGANIZATION_ID ,
	OOD.ORGANIZATION_NAME ,
	OOD.LEGAL_ENTITY ,
	OAP2.SCHEDULE_CLOSE_DATE ,
	MIC.CATEGORY_ID ,
	MCKFV.CONCATENATED_SEGMENTS ,
	OAP2.PERIOD_SET_NAME ,
	OAP2.PERIOD_NAME ,
	SUM((NVL(WPB.TL_RESOURCE_IN,0)+ NVL(WPB.TL_OVERHEAD_IN,0) +NVL(WPB.TL_OUTSIDE_PROCESSING_IN,0)
		+ NVL(WPB.PL_MATERIAL_IN,0) +NVL(WPB.PL_RESOURCE_IN,0)+ NVL(WPB.PL_OVERHEAD_IN,0)
		+NVL(WPB.PL_OUTSIDE_PROCESSING_IN,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_IN,0))
	  - (NVL(WPB.TL_RESOURCE_OUT,0) +NVL(WPB.TL_OVERHEAD_OUT,0)+ NVL(WPB.TL_OUTSIDE_PROCESSING_OUT,0)
		+ NVL(WPB.PL_MATERIAL_OUT,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_OUT,0)+NVL(WPB.PL_RESOURCE_OUT,0)
		+ NVL(WPB.PL_OVERHEAD_OUT,0) +NVL(WPB.PL_OUTSIDE_PROCESSING_OUT,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_OUT,0)
		+ NVL(WPB.TL_MATERIAL_OUT,0))
      - (NVL(WPB.TL_MATERIAL_VAR,0)+NVL(WPB.TL_RESOURCE_VAR,0)+ NVL(WPB.TL_OVERHEAD_VAR,0)
		+ NVL(WPB.TL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.PL_MATERIAL_VAR,0)+NVL(WPB.PL_RESOURCE_VAR,0)
		+ NVL(WPB.PL_OVERHEAD_VAR,0)+NVL(WPB.PL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_VAR,0)
		+ NVL(WPB.PL_MATERIAL_OVERHEAD_VAR,0)))
FROM
    ORG_ACCT_PERIODS OAP2 ,
	ORG_ORGANIZATION_DEFINITIONS OOD ,
	WIP_DISCRETE_JOBS WDJ ,
	WIP_PERIOD_BALANCES WPB ,
	ORG_ACCT_PERIODS OAP ,
	MTL_CATEGORIES_KFV MCKFV ,
	MTL_ITEM_CATEGORIES MIC ,
	MTL_DEFAULT_CATEGORY_SETS MDCS
WHERE
    WDJ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WPB.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WPB.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WPB.CLASS_TYPE <> 4
AND OAP.ACCT_PERIOD_ID = WPB.ACCT_PERIOD_ID
AND OAP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND OAP2.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND OAP2.SCHEDULE_CLOSE_DATE >= OAP.SCHEDULE_CLOSE_DATE
AND MIC.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
AND MIC.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID
AND MCKFV.CATEGORY_ID = MIC.CATEGORY_ID
AND MDCS.FUNCTIONAL_AREA_ID = 1
GROUP BY
    WDJ.ORGANIZATION_ID ,
	OOD.ORGANIZATION_NAME ,
	OOD.LEGAL_ENTITY ,
	OAP2.PERIOD_SET_NAME ,
	OAP2.PERIOD_NAME ,
	OAP2.SCHEDULE_CLOSE_DATE ,
	MIC.CATEGORY_ID,
	MCKFV.CONCATENATED_SEGMENTS
UNION ALL
SELECT
    WRI.ORGANIZATION_ID ,
	OOD.ORGANIZATION_NAME ,
	OOD.LEGAL_ENTITY ,
	OAP2.SCHEDULE_CLOSE_DATE ,
	MIC.CATEGORY_ID ,
	MCKFV.CONCATENATED_SEGMENTS ,
	OAP2.PERIOD_SET_NAME ,
	OAP2.PERIOD_NAME ,
	SUM((NVL(WPB.TL_RESOURCE_IN,0)+NVL(WPB.TL_OVERHEAD_IN,0)+ NVL(WPB.TL_OUTSIDE_PROCESSING_IN,0)
		+NVL(WPB.PL_MATERIAL_IN,0)+ NVL(WPB.PL_RESOURCE_IN,0)+NVL(WPB.PL_OVERHEAD_IN,0)
		+ NVL(WPB.PL_OUTSIDE_PROCESSING_IN,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_IN,0))
     - (NVL(WPB.TL_RESOURCE_OUT,0)+NVL(WPB.TL_OVERHEAD_OUT,0)+ NVL(WPB.TL_OUTSIDE_PROCESSING_OUT,0)
	    + NVL(WPB.PL_MATERIAL_OUT,0)+ NVL(WPB.PL_MATERIAL_OVERHEAD_OUT,0)+ NVL(WPB.PL_RESOURCE_OUT,0)
		+ NVL(WPB.PL_OVERHEAD_OUT,0)+ NVL(WPB.PL_OUTSIDE_PROCESSING_OUT,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_OUT,0)
		+ NVL(WPB.TL_MATERIAL_OUT,0))
     - (NVL(WPB.TL_MATERIAL_VAR,0)+NVL(WPB.TL_RESOURCE_VAR,0)+ NVL(WPB.TL_OVERHEAD_VAR,0)
	    + NVL(WPB.TL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.PL_MATERIAL_VAR,0)+NVL(WPB.PL_RESOURCE_VAR,0)
		+ NVL(WPB.PL_OVERHEAD_VAR,0)+ NVL(WPB.PL_OUTSIDE_PROCESSING_VAR,0)+ NVL(WPB.TL_MATERIAL_OVERHEAD_VAR,0)
		+ NVL(WPB.PL_MATERIAL_OVERHEAD_VAR,0)) )
FROM
    ORG_ACCT_PERIODS OAP2 ,
    ORG_ORGANIZATION_DEFINITIONS OOD ,
    WIP_REPETITIVE_SCHEDULES WRS ,
    WIP_REPETITIVE_ITEMS WRI ,
    WIP_PERIOD_BALANCES WPB ,
    ORG_ACCT_PERIODS OAP ,
    MTL_CATEGORIES_KFV MCKFV ,
    MTL_ITEM_CATEGORIES MIC , MTL_DEFAULT_CATEGORY_SETS MDCS
WHERE
    WRI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WRS.WIP_ENTITY_ID = WRI.WIP_ENTITY_ID
AND WRS.LINE_ID = WRI.LINE_ID
AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND WPB.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND WPB.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
AND WPB.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND OAP.ACCT_PERIOD_ID = WPB.ACCT_PERIOD_ID
AND OAP.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND OAP2.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND OAP2.SCHEDULE_CLOSE_DATE >= OAP.SCHEDULE_CLOSE_DATE
AND MIC.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = WRI.PRIMARY_ITEM_ID
AND MIC.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID
AND MCKFV.CATEGORY_ID = MIC.CATEGORY_ID
AND MDCS.FUNCTIONAL_AREA_ID = 1
GROUP BY
    WRI.ORGANIZATION_ID ,
	OOD.ORGANIZATION_NAME ,
	OOD.LEGAL_ENTITY ,
	OAP2.PERIOD_SET_NAME ,
	OAP2.PERIOD_NAME ,
	OAP2.SCHEDULE_CLOSE_DATE ,
	MIC.CATEGORY_ID ,
	MCKFV.CONCATENATED_SEGMENTS;