[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT_CALENDAR_DEFAULTS(P_ORG_ID
,CAL_CODE
,EXC_SET_ID);
SELECT
sysdate
INTO P_CURRENT_DATE
FROM
DUAL;
SELECT
EMPLOYEE_ID
INTO P_EMPLOYEE_ID
FROM
FND_USER
WHERE USER_ID = P_USER_ID;
SELECT
OPERATING_UNIT
INTO P_PO_ORG_ID
FROM
ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = P_ORG_ID;
SELECT
NVL(REQ_ENCUMBRANCE_FLAG
,'N')
INTO P_ENCUM_FLAG
FROM
FINANCIALS_SYSTEM_PARAMS_ALL
WHERE NVL(ORG_ID
,-11) = NVL(P_PO_ORG_ID
,-11);
SELECT
NVL(PO.CUSTOMER_ID
,0)
INTO P_CUSTOMER_ID
FROM
PO_LOCATION_ASSOCIATIONS_ALL PO,
HR_LOCATIONS HR
WHERE HR.LOCATION_ID = P_DEFAULT_DELIVERY_TO
AND HR.LOCATION_ID = po.location_id (+)
AND NVL(PO.ORG_ID
,-11) = NVL(P_PO_ORG_ID
,-11);
SELECT
WIP_JOB_SCHEDULE_INTERFACE_S.NEXTVAL
INTO P_WIP_BATCH_ID
FROM
DUAL;
SELECT
NVL(MAX(S1.SAFETY_STOCK_QUANTITY)
,0)
INTO SAFETY_STOCK
FROM
MTL_SAFETY_STOCKS S1
WHERE S1.ORGANIZATION_ID = P_ORG_ID
AND S1.INVENTORY_ITEM_ID = C_ITEM_ID
AND ( S1.EFFECTIVITY_DATE <= ( sysdate + C_ORD_LEAD_TIME )
AND S1.EFFECTIVITY_DATE >= (
SELECT
NVL(MAX(S2.EFFECTIVITY_DATE)
,SYSDATE)
FROM
MTL_SAFETY_STOCKS S2
WHERE S2.ORGANIZATION_ID = P_ORG_ID
AND S2.INVENTORY_ITEM_ID = C_ITEM_ID
AND S2.EFFECTIVITY_DATE <= sysdate ) );
SELECT
MEANING
INTO SORT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
AND LOOKUP_CODE = P_FIRST_SORT;
SELECT
MEANING
INTO SORT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
AND LOOKUP_CODE = P_SECOND_SORT;
SELECT
MEANING
INTO SORT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
AND LOOKUP_CODE = P_THIRD_SORT;
SELECT
CATEGORY_SET_NAME
INTO CAT_SET
FROM
MTL_CATEGORY_SETS
WHERE CATEGORY_SET_ID = P_CATEGORY_SET;
SELECT
MEANING
INTO TEXT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND LOOKUP_CODE = P_DISPLAY_DESCRIPTION;
SELECT
MEANING
INTO TEXT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND LOOKUP_CODE = P_RESTOCK;
FND_MSG_PUB.DELETE_MSG(I);
SELECT
ASSIGNMENT_GROUP_NAME
INTO ABC_ASSGN
FROM
MTL_ABC_ASSIGNMENT_GROUPS
WHERE ASSIGNMENT_GROUP_ID = P_ABC_ASSGN
AND ORGANIZATION_ID = P_ORG_ID;
SELECT
ABC_CLASS_NAME
INTO ABC_CLASS
FROM
MTL_ABC_CLASSES
WHERE ABC_CLASS_ID = P_ABC_CLASS
AND ORGANIZATION_ID = P_ORG_ID;
SELECT
LOCATION_CODE
INTO LOCATION_NAME
FROM
HR_LOCATIONS
WHERE LOCATION_ID = P_DEFAULT_DELIVERY_TO;
FUNCTION C_ITEM_SELECTION_PFORMULA RETURN VARCHAR2 IS
BEGIN
DECLARE
ITEM_SEL VARCHAR2(80);
SELECT
MEANING
INTO ITEM_SEL
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MTL_REORDER_RPT'
AND LOOKUP_CODE = P_ITEM_SELECTION;
END C_ITEM_SELECTION_PFORMULA;
SELECT
MEANING
INTO TEXT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND LOOKUP_CODE = P_INCLUDE_PO;
SELECT
MEANING
INTO TEXT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND LOOKUP_CODE = P_INCLUDE_WIP;
SELECT
MEANING
INTO TEXT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND LOOKUP_CODE = P_INCLUDE_IF;
SELECT
MEANING
INTO TEXT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND LOOKUP_CODE = P_INCLUDE_NONNET;
SELECT
MEANING
INTO TEXT
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'SYS_YES_NO'
AND LOOKUP_CODE = P_DISPLAY_ADD_INFO;
SELECT
FULL_NAME
INTO VAR_NAME
FROM
MTL_EMPLOYEES_VIEW
WHERE EMPLOYEE_ID = P_LOW_BUYER
AND ORGANIZATION_ID = P_ORG_ID;
SELECT
FULL_NAME
INTO VAR_NAME
FROM
MTL_EMPLOYEES_VIEW
WHERE EMPLOYEE_ID = P_HIGH_BUYER
AND ORGANIZATION_ID = P_ORG_ID;
PROCEDURE SELECT_CALENDAR_DEFAULTS(ARG_ORG_ID IN NUMBER
,ARG_CALENDAR_CODE OUT NOCOPY VARCHAR2
,ARG_EXCEPTION_SET_ID OUT NOCOPY NUMBER) IS
BEGIN
/*STPROC.INIT('begin MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(:ARG_ORG_ID, :ARG_CALENDAR_CODE, :ARG_EXCEPTION_SET_ID); end;');
MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ARG_ORG_ID, ARG_CALENDAR_CODE, ARG_EXCEPTION_SET_ID);
END SELECT_CALENDAR_DEFAULTS;
MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ORG_ID
,CAL_CODE
,EXC_SET_ID);
SELECT
CAL1.NEXT_SEQ_NUM,
CAL2.NEXT_SEQ_NUM
INTO START_SEQ_NUM,END_SEQ_NUM
FROM
BOM_CALENDAR_DATES CAL1,
BOM_CALENDAR_DATES CAL2
WHERE CAL1.CALENDAR_CODE = CAL2.CALENDAR_CODE
AND CAL1.CALENDAR_CODE = CAL_CODE
AND CAL1.EXCEPTION_SET_ID = CAL2.EXCEPTION_SET_ID
AND CAL1.EXCEPTION_SET_ID = EXC_SET_ID
AND CAL1.CALENDAR_DATE = START_DATE
AND CAL2.CALENDAR_DATE = END_DATE;
SELECT
NVL(SUM(FC.ORIGINAL_FORECAST_QUANTITY * (DECODE(SIGN(END_SEQ_NUM - CAL2.NEXT_SEQ_NUM)
,-1
,END_SEQ_NUM
,CAL2.NEXT_SEQ_NUM) - DECODE(SIGN(START_SEQ_NUM - CAL1.NEXT_SEQ_NUM)
,-1
,CAL1.NEXT_SEQ_NUM
,START_SEQ_NUM)))
,0)
INTO DAY_FC_QTY
FROM
BOM_CALENDAR_DATES CAL1,
BOM_CALENDAR_DATES CAL2,
MRP_FORECAST_DATES FC,
MRP_FORECAST_DESIGNATORS DESIG1,
MRP_FORECAST_DESIGNATORS DESIG2
WHERE DESIG2.FORECAST_DESIGNATOR = FORECAST_DESIG
AND DESIG1.ORGANIZATION_ID = FC.ORGANIZATION_ID
AND DESIG2.ORGANIZATION_ID = FC.ORGANIZATION_ID
AND FC.ORGANIZATION_ID = ORG_ID
AND DESIG1.FORECAST_SET = NVL(DESIG2.FORECAST_SET
,FORECAST_DESIG)
AND DESIG1.FORECAST_DESIGNATOR = DECODE(DESIG2.FORECAST_SET
,NULL
,DESIG1.FORECAST_DESIGNATOR
,FORECAST_DESIG)
AND NVL(DESIG1.DISABLE_DATE
,SYSDATE) >= sysdate
AND FC.FORECAST_DATE < END_DATE
AND FC.INVENTORY_ITEM_ID = ITEM_ID
AND FC.FORECAST_DESIGNATOR = DESIG1.FORECAST_DESIGNATOR
AND FC.BUCKET_TYPE = 1
AND CAL1.CALENDAR_CODE = CAL_CODE
AND CAL1.EXCEPTION_SET_ID = EXC_SET_ID
AND CAL1.CALENDAR_DATE = FC.FORECAST_DATE
AND CAL2.CALENDAR_CODE = CAL1.CALENDAR_CODE
AND CAL2.EXCEPTION_SET_ID = CAL1.EXCEPTION_SET_ID
AND CAL2.CALENDAR_DATE = NVL(FC.RATE_END_DATE
,FC.FORECAST_DATE) + 1
AND CAL2.CALENDAR_DATE > START_DATE;
SELECT
NVL(SUM(FC.ORIGINAL_FORECAST_QUANTITY / (CAL2.NEXT_SEQ_NUM - CAL1.NEXT_SEQ_NUM) * (DECODE(SIGN(END_SEQ_NUM - CAL2.NEXT_SEQ_NUM)
,-1
,END_SEQ_NUM
,CAL2.NEXT_SEQ_NUM) - DECODE(SIGN(START_SEQ_NUM - CAL1.NEXT_SEQ_NUM)
,-1
,CAL1.NEXT_SEQ_NUM
,START_SEQ_NUM)))
,0)
INTO WEEK_FC_QTY
FROM
BOM_CALENDAR_DATES CAL1,
BOM_CALENDAR_DATES CAL2,
BOM_CAL_WEEK_START_DATES WEEK,
MRP_FORECAST_DATES FC,
MRP_FORECAST_DESIGNATORS DESIG1,
MRP_FORECAST_DESIGNATORS DESIG2
WHERE DESIG2.FORECAST_DESIGNATOR = FORECAST_DESIG
AND DESIG1.ORGANIZATION_ID = FC.ORGANIZATION_ID
AND DESIG2.ORGANIZATION_ID = FC.ORGANIZATION_ID
AND FC.ORGANIZATION_ID = ORG_ID
AND DESIG1.FORECAST_SET = NVL(DESIG2.FORECAST_SET
,FORECAST_DESIG)
AND DESIG1.FORECAST_DESIGNATOR = DECODE(DESIG2.FORECAST_SET
,NULL
,DESIG1.FORECAST_DESIGNATOR
,FORECAST_DESIG)
AND NVL(DESIG1.DISABLE_DATE
,SYSDATE) >= sysdate
AND FC.FORECAST_DATE < END_DATE
AND FC.INVENTORY_ITEM_ID = ITEM_ID
AND FC.FORECAST_DESIGNATOR = DESIG1.FORECAST_DESIGNATOR
AND FC.BUCKET_TYPE = 2
AND WEEK.CALENDAR_CODE = CAL_CODE
AND WEEK.EXCEPTION_SET_ID = EXC_SET_ID
AND ( WEEK.WEEK_START_DATE >= FC.FORECAST_DATE
AND WEEK.WEEK_START_DATE < END_DATE
AND WEEK.WEEK_START_DATE <= NVL(FC.RATE_END_DATE
,FC.FORECAST_DATE) )
AND WEEK.NEXT_DATE > START_DATE
AND CAL1.CALENDAR_CODE = WEEK.CALENDAR_CODE
AND CAL2.CALENDAR_CODE = WEEK.CALENDAR_CODE
AND CAL1.EXCEPTION_SET_ID = WEEK.EXCEPTION_SET_ID
AND CAL2.EXCEPTION_SET_ID = WEEK.EXCEPTION_SET_ID
AND CAL1.CALENDAR_DATE = WEEK.WEEK_START_DATE
AND CAL2.CALENDAR_DATE = WEEK.NEXT_DATE;
SELECT
NVL(SUM(FC.ORIGINAL_FORECAST_QUANTITY / (CAL2.NEXT_SEQ_NUM - CAL1.NEXT_SEQ_NUM) * (DECODE(SIGN(END_SEQ_NUM - CAL2.NEXT_SEQ_NUM)
,-1
,END_SEQ_NUM
,CAL2.NEXT_SEQ_NUM) - DECODE(SIGN(START_SEQ_NUM - CAL1.NEXT_SEQ_NUM)
,-1
,CAL1.NEXT_SEQ_NUM
,START_SEQ_NUM)))
,0)
INTO PERIOD_FC_QTY
FROM
BOM_CALENDAR_DATES CAL1,
BOM_CALENDAR_DATES CAL2,
BOM_PERIOD_START_DATES PER,
MRP_FORECAST_DATES FC,
MRP_FORECAST_DESIGNATORS DESIG1,
MRP_FORECAST_DESIGNATORS DESIG2
WHERE DESIG2.FORECAST_DESIGNATOR = FORECAST_DESIG
AND DESIG1.ORGANIZATION_ID = FC.ORGANIZATION_ID
AND DESIG2.ORGANIZATION_ID = FC.ORGANIZATION_ID
AND FC.ORGANIZATION_ID = ORG_ID
AND DESIG1.FORECAST_SET = NVL(DESIG2.FORECAST_SET
,FORECAST_DESIG)
AND DESIG1.FORECAST_DESIGNATOR = DECODE(DESIG2.FORECAST_SET
,NULL
,DESIG1.FORECAST_DESIGNATOR
,FORECAST_DESIG)
AND NVL(DESIG1.DISABLE_DATE
,SYSDATE) >= sysdate
AND FC.FORECAST_DATE < END_DATE
AND FC.INVENTORY_ITEM_ID = ITEM_ID
AND FC.FORECAST_DESIGNATOR = DESIG1.FORECAST_DESIGNATOR
AND FC.BUCKET_TYPE = 3
AND PER.CALENDAR_CODE = CAL_CODE
AND PER.EXCEPTION_SET_ID = EXC_SET_ID
AND ( PER.PERIOD_START_DATE >= FC.FORECAST_DATE
AND PER.PERIOD_START_DATE < END_DATE
AND PER.PERIOD_START_DATE <= NVL(FC.RATE_END_DATE
,FC.FORECAST_DATE) )
AND PER.NEXT_DATE > START_DATE
AND CAL1.CALENDAR_CODE = PER.CALENDAR_CODE
AND CAL2.CALENDAR_CODE = PER.CALENDAR_CODE
AND CAL1.EXCEPTION_SET_ID = PER.EXCEPTION_SET_ID
AND CAL2.EXCEPTION_SET_ID = PER.EXCEPTION_SET_ID
AND CAL1.CALENDAR_DATE = PER.PERIOD_START_DATE
AND CAL2.CALENDAR_DATE = PER.NEXT_DATE;
MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ORG_ID
,CAL_CODE
,EXC_SET_ID);
SELECT
NVL(PROCESS_ENABLED_FLAG
,'N')
INTO C_PROCESS_ENABLED
FROM
MTL_PARAMETERS
WHERE ORGANIZATION_ID = ORG_ID;
SELECT
DECODE(NVL(CST.ITEM_COST
,0) * NVL(SYS.CARRYING_COST / 100
,0)
,0
,0
,NVL(SYS.ORDER_COST
,0) / (CST.ITEM_COST * (SYS.CARRYING_COST / 100)))
INTO COST_RATIO
FROM
MTL_SYSTEM_ITEMS SYS,
CST_ITEM_COSTS_FOR_GL_VIEW CST
WHERE cst.organization_id (+) = SYS.ORGANIZATION_ID
AND SYS.ORGANIZATION_ID = ORG_ID
AND cst.inventory_item_id (+) = SYS.INVENTORY_ITEM_ID
AND SYS.INVENTORY_ITEM_ID = ITEM_ID;
SELECT
DECODE(NVL(GMP_APS_OUTPUT_PKG.RETRIEVE_ITEM_COST(ITEM_ID
,ORG_ID)
,0) * NVL(SYS.CARRYING_COST / 100
,0)
,0
,0
,NVL(SYS.ORDER_COST
,0) / (GMP_APS_OUTPUT_PKG.RETRIEVE_ITEM_COST(ITEM_ID
,ORG_ID) * (SYS.CARRYING_COST / 100)))
INTO COST_RATIO
FROM
MTL_SYSTEM_ITEMS SYS
WHERE SYS.ORGANIZATION_ID = ORG_ID
AND SYS.INVENTORY_ITEM_ID = ITEM_ID;
SELECT
NVL(MAX(PER.PERIOD_START_DATE)
,TRUNC(SYSDATE)),
NVL(MAX(PER.NEXT_DATE)
,TRUNC(SYSDATE))
INTO PERIOD_START_DATE,PERIOD_END_DATE
FROM
BOM_PERIOD_START_DATES PER
WHERE PER.CALENDAR_CODE = CAL_CODE
AND PER.EXCEPTION_SET_ID = EXC_SET_ID
AND PER.PERIOD_START_DATE <= TRUNC(SYSDATE);
SELECT
DECODE(CAL.QUARTERLY_CALENDAR_TYPE
,4
,(FC_QTY * 13)
,(FC_QTY * 12))
INTO ANNUAL_DEMAND
FROM
BOM_CALENDARS CAL,
MTL_PARAMETERS PARAM
WHERE PARAM.ORGANIZATION_ID = ORG_ID
AND CAL.CALENDAR_CODE = PARAM.CALENDAR_CODE;
SELECT
SQRT(2 * ANNUAL_DEMAND * COST_RATIO)
INTO REORDER_QTY
FROM
DUAL;
SELECT
ROUND(REORDER_QTY / FIX_LOT_MULT
,0)
INTO QUOTIENT
FROM
DUAL;
SELECT
ROUNDING_CONTROL_TYPE
INTO L_ROUND
FROM
MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = ORG_ID
AND INVENTORY_ITEM_ID = ITEM_ID;
MRP_CALENDAR.SELECT_CALENDAR_DEFAULTS(ORG_ID
,CAL_CODE
,EXC_SET_ID);
SELECT
TRUNC(SYSDATE),
CAL2.CALENDAR_DATE
INTO START_DATE,END_DATE
FROM
BOM_CALENDAR_DATES CAL1,
BOM_CALENDAR_DATES CAL2
WHERE CAL1.CALENDAR_CODE = CAL_CODE
AND CAL1.EXCEPTION_SET_ID = EXC_SET_ID
AND CAL1.CALENDAR_CODE = CAL2.CALENDAR_CODE
AND CAL1.EXCEPTION_SET_ID = CAL2.EXCEPTION_SET_ID
AND CAL1.CALENDAR_DATE = TRUNC(SYSDATE)
AND CAL2.SEQ_NUM = ROUND(CAL1.NEXT_SEQ_NUM + ORD_LEAD_TIME);
SELECT
SUM(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY
,0)
,COMPLETED_QUANTITY))
INTO QTY
FROM
MTL_DEMAND
WHERE RESERVATION_TYPE = 2
AND DEMAND_SOURCE_TYPE NOT IN ( 2 , 8 , 12 )
AND ORGANIZATION_ID = ORG_ID
AND INVENTORY_ITEM_ID = ITEM_ID
AND PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY
,0)
,COMPLETED_QUANTITY)
AND REQUIREMENT_DATE <= DEMAND_CUTOFF_DATE
AND ( NVL(SUBINVENTORY
,'x') = DECODE(SUBINV
,NULL
,NVL(SUBINVENTORY
,'x')
,SUBINV)
OR EXISTS (
SELECT
1
FROM
MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = ORG_ID
AND S.SECONDARY_INVENTORY_NAME = NVL(SUBINV
,SUBINVENTORY)
AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,S.AVAILABILITY_TYPE
,1) ) )
AND ( LOCATOR_ID IS NULL
OR EXISTS (
SELECT
1
FROM
MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = ORG_ID
AND MIL.INVENTORY_LOCATION_ID = LOCATOR_ID
AND MIL.SUBINVENTORY_CODE = NVL(SUBINVENTORY
,MIL.SUBINVENTORY_CODE)
AND MIL.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,MIL.AVAILABILITY_TYPE
,1) ) )
AND ( LOT_NUMBER IS NULL
OR EXISTS (
SELECT
1
FROM
MTL_LOT_NUMBERS MLN
WHERE MLN.ORGANIZATION_ID = ORG_ID
AND MLN.LOT_NUMBER = LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID = ITEM_ID
AND MLN.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,MLN.AVAILABILITY_TYPE
,1) ) );
SELECT
SUM(DECODE(OOL.ORDERED_QUANTITY
,NULL
,0
,INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(OOL.SHIP_FROM_ORG_ID
,OOL.INVENTORY_ITEM_ID
,OOL.ORDER_QUANTITY_UOM
,OOL.ORDERED_QUANTITY)))
INTO QTY
FROM
OE_ORDER_LINES_ALL OOL
WHERE OPEN_FLAG = 'Y'
AND VISIBLE_DEMAND_FLAG = 'Y'
AND SHIPPED_QUANTITY IS NULL
AND SHIP_FROM_ORG_ID = LV_ORG_ID
AND INVENTORY_ITEM_ID = ITEM_ID
AND SCHEDULE_SHIP_DATE <= DEMAND_CUTOFF_DATE
AND ( NVL(SUBINVENTORY
,1) = DECODE(SUBINV
,NULL
,NVL(SUBINVENTORY
,1)
,SUBINV)
OR EXISTS (
SELECT
1
FROM
MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = LV_ORG_ID
AND S.SECONDARY_INVENTORY_NAME = NVL(SUBINV
,SUBINVENTORY)
AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,S.AVAILABILITY_TYPE
,1) ) );
SELECT
NVL(PROCESS_ENABLED_FLAG
,'N')
INTO C_PROCESS_ENABLED
FROM
MTL_PARAMETERS
WHERE ORGANIZATION_ID = ORG_ID;
SELECT
SUM((NVL((NVL(D.WIP_PLAN_QTY
,D.PLAN_QTY) - D.ACTUAL_QTY)
,0) * (D.ORIGINAL_PRIMARY_QTY / D.ORIGINAL_QTY)) - NVL(MTR.PRIMARY_RESERVATION_QUANTITY
,0))
INTO QTY
FROM
GME_MATERIAL_DETAILS D,
GME_BATCH_HEADER H,
MTL_RESERVATIONS MTR
WHERE H.BATCH_TYPE IN ( 0 , 10 )
AND H.BATCH_STATUS IN ( 1 , 2 )
AND H.BATCH_ID = D.BATCH_ID
AND D.LINE_TYPE = - 1
AND NVL(D.ORIGINAL_QTY
,0) <> 0
AND D.ORGANIZATION_ID = ORG_ID
AND D.INVENTORY_ITEM_ID = ITEM_ID
AND D.BATCH_ID = mtr.demand_source_header_id (+)
AND D.MATERIAL_DETAIL_ID = mtr.demand_source_line_id (+)
AND D.INVENTORY_ITEM_ID = mtr.inventory_item_id (+)
AND D.ORGANIZATION_ID = mtr.organization_id (+)
AND ( ( NVL((NVL(D.WIP_PLAN_QTY
,D.PLAN_QTY) - D.ACTUAL_QTY)
,0) * ( D.ORIGINAL_PRIMARY_QTY / D.ORIGINAL_QTY ) ) - NVL(MTR.PRIMARY_RESERVATION_QUANTITY
,0) ) > 0
AND NVL(MTR.DEMAND_SOURCE_TYPE_ID
,5) = 5
AND D.MATERIAL_REQUIREMENT_DATE <= DEMAND_CUTOFF_DATE
AND ( MTR.SUBINVENTORY_CODE IS NULL
OR EXISTS (
SELECT
1
FROM
MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = ORG_ID
AND S.SECONDARY_INVENTORY_NAME = MTR.SUBINVENTORY_CODE
AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,S.AVAILABILITY_TYPE
,1) ) )
AND ( MTR.LOCATOR_ID IS NULL
OR EXISTS (
SELECT
1
FROM
MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = ORG_ID
AND MIL.INVENTORY_LOCATION_ID = MTR.LOCATOR_ID
AND MIL.SUBINVENTORY_CODE = NVL(MTR.SUBINVENTORY_CODE
,MIL.SUBINVENTORY_CODE)
AND MIL.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,MIL.AVAILABILITY_TYPE
,1) ) )
AND ( MTR.LOT_NUMBER IS NULL
OR EXISTS (
SELECT
1
FROM
MTL_LOT_NUMBERS MLN
WHERE MLN.ORGANIZATION_ID = ORG_ID
AND MLN.LOT_NUMBER = MTR.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID = ITEM_ID
AND MLN.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,MLN.AVAILABILITY_TYPE
,1) ) );
SELECT
SUM(O.REQUIRED_QUANTITY - O.QUANTITY_ISSUED)
INTO QTY
FROM
WIP_DISCRETE_JOBS D,
WIP_REQUIREMENT_OPERATIONS O
WHERE O.WIP_ENTITY_ID = D.WIP_ENTITY_ID
AND O.ORGANIZATION_ID = D.ORGANIZATION_ID
AND D.ORGANIZATION_ID = ORG_ID
AND O.INVENTORY_ITEM_ID = ITEM_ID
AND O.DATE_REQUIRED <= DEMAND_CUTOFF_DATE
AND O.REQUIRED_QUANTITY > 0
AND O.OPERATION_SEQ_NUM > 0
AND D.STATUS_TYPE in ( 1 , 3 , 4 , 6 )
AND O.WIP_SUPPLY_TYPE NOT IN ( 5 , 6 )
AND NVL(O.SUPPLY_SUBINVENTORY
,1) = DECODE(SUBINV
,NULL
,NVL(O.SUPPLY_SUBINVENTORY
,1)
,SUBINV)
AND NOT EXISTS (
SELECT
WIP.WIP_ENTITY_ID
FROM
WIP_SO_ALLOCATIONS WIP,
MTL_DEMAND MTL
WHERE WIP_ENTITY_ID = O.WIP_ENTITY_ID
AND WIP.ORGANIZATION_ID = ORG_ID
AND WIP.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND WIP.DEMAND_SOURCE_HEADER_ID = MTL.DEMAND_SOURCE_HEADER_ID
AND WIP.DEMAND_SOURCE_LINE = MTL.DEMAND_SOURCE_LINE
AND WIP.DEMAND_SOURCE_DELIVERY = MTL.DEMAND_SOURCE_DELIVERY
AND MTL.INVENTORY_ITEM_ID = ITEM_ID );
SELECT
SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED
,0))
INTO QTY
FROM
MTL_TXN_REQUEST_LINES MTRL,
MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
AND MTRL.ORGANIZATION_ID = ORG_ID
AND MTRL.INVENTORY_ITEM_ID = ITEM_ID
AND MTRL.LINE_STATUS NOT IN ( 5 , 6 )
AND MTT.TRANSACTION_ACTION_ID = 1
AND ( P_LEVEL = 1
OR MTRL.FROM_SUBINVENTORY_CODE = SUBINV )
AND ( MTRL.FROM_SUBINVENTORY_CODE IS NULL
OR P_LEVEL = 2
OR EXISTS (
SELECT
1
FROM
MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = ORG_ID
AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
AND S.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,S.AVAILABILITY_TYPE
,1) ) )
AND MTRL.DATE_REQUIRED <= DEMAND_CUTOFF_DATE
AND ( MTRL.FROM_LOCATOR_ID IS NULL
OR EXISTS (
SELECT
1
FROM
MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = ORG_ID
AND MIL.INVENTORY_LOCATION_ID = MTRL.FROM_LOCATOR_ID
AND MIL.SUBINVENTORY_CODE = NVL(MTRL.FROM_SUBINVENTORY_CODE
,MIL.SUBINVENTORY_CODE)
AND MIL.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,MIL.AVAILABILITY_TYPE
,1) ) )
AND ( MTRL.LOT_NUMBER IS NULL
OR EXISTS (
SELECT
1
FROM
MTL_LOT_NUMBERS MLN
WHERE MLN.ORGANIZATION_ID = ORG_ID
AND MLN.LOT_NUMBER = MTRL.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID = ITEM_ID
AND MLN.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,MLN.AVAILABILITY_TYPE
,1) ) );
L_STMT := 'SELECT to_char(nvl(sum(to_org_primary_quantity), 0))
INTO :char_qty
FROM mtl_supply sup, mtl_system_items items
WHERE sup.supply_type_code in (''PO'',''REQ'',''ASN'',''SHIPMENT'',''RECEIVING'')
AND sup.destination_type_code =''INVENTORY''
AND sup.to_organization_id =' || TO_CHAR(ORG_ID) || '
AND sup.item_id =' || TO_CHAR(CURRENT_ITEM_ID) || '
AND items.organization_id = sup.to_organization_id' || '
AND items.inventory_item_id = sup.item_id' || '
AND TRUNC(DECODE(NVL(items.postprocessing_lead_time,0),0,MRP_CALENDAR.NEXT_WORK_DAY(items.organization_id,1,
DECODE(sup.supply_type_code,''PO'',sup.need_by_date,
''REQ'',sup.need_by_date,
''ASN'',sup.need_by_date,''RECEIVING'',sup.receipt_date,''SHIPMENT'',
sup.receipt_date)),' || ' MRP_CALENDAR.DATE_OFFSET(items.organization_id,1,DECODE(sup.supply_type_code,''PO'',sup.need_by_date,
''REQ'',sup.need_by_date,''ASN'',sup.need_by_date,''RECEIVING'',sup.receipt_date,''SHIPMENT'',sup.receipt_date),
items.postprocessing_lead_time))) <=
TO_DATE(''' || SCD || ''',''DD-MON-RRRR'')' || ' AND (NVL(sup.FROM_organization_id,-1) <>' || TO_CHAR(ORG_ID) || '
OR (sup.FROM_organization_id =' || TO_CHAR(ORG_ID) || ' AND ' || TO_CHAR(INCLUDE_NONNET) || '= 2' || ' AND EXISTS (SELECT ''x''
FROM mtl_secondary_inventories sub1
WHERE sub1.organization_id = sup.FROM_organization_id
AND sup.FROM_subinventory = sub1.secondary_inventory_name
AND sub1.availability_type <> 1)))' || ' AND NOT EXISTS (select ''y''
from oe_drop_ship_sources odss
where sup.po_header_id is null and sup.req_line_id = odss.requisition_line_id ) ' || ' AND NOT EXISTS (select ''y''
from oe_drop_ship_sources odss
where sup.req_line_id is null and sup.po_line_location_id = odss.line_location_id)';
OR EXISTS (SELECT ''x''
FROM po_line_locations_all lilo
WHERE lilo.line_location_id = sup.po_line_location_id
AND NVL(lilo.vmi_flag,''N'') =''N''
)
)
AND (sup.req_line_id IS NULL
OR EXISTS (SELECT ''x''
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = sup.req_line_id
AND NVL(prl.vmi_flag,''N'') =''N''
)
)';
SELECT
NVL(PROCESS_ENABLED_FLAG
,'N')
INTO C_PROCESS_ENABLED
FROM
MTL_PARAMETERS
WHERE ORGANIZATION_ID = ORG_ID;
SELECT
SUM(NVL((NVL(D.WIP_PLAN_QTY
,D.PLAN_QTY) - D.ACTUAL_QTY)
,0) * (ORIGINAL_PRIMARY_QTY / ORIGINAL_QTY))
INTO QTY
FROM
GME_MATERIAL_DETAILS D,
GME_BATCH_HEADER H
WHERE H.BATCH_TYPE IN ( 0 , 10 )
AND H.BATCH_STATUS IN ( 1 , 2 )
AND H.BATCH_ID = D.BATCH_ID
AND D.INVENTORY_ITEM_ID = CURRENT_ITEM_ID
AND D.ORGANIZATION_ID = ORG_ID
AND D.MATERIAL_REQUIREMENT_DATE <= SUPPLY_CUTOFF_DATE
AND D.LINE_TYPE > 0;
SELECT
SUM(NVL(START_QUANTITY
,0) - NVL(QUANTITY_COMPLETED
,0) - NVL(QUANTITY_SCRAPPED
,0))
INTO QTY
FROM
WIP_DISCRETE_JOBS
WHERE ORGANIZATION_ID = ORG_ID
AND PRIMARY_ITEM_ID = CURRENT_ITEM_ID
AND STATUS_TYPE in ( 1 , 3 , 4 , 6 )
AND JOB_TYPE in ( 1 , 3 )
AND SCHEDULED_COMPLETION_DATE <= TO_DATE(TO_CHAR(SUPPLY_CUTOFF_DATE)
,'DD-MON-RR')
AND NVL(COMPLETION_SUBINVENTORY
,1) = DECODE(SUBINV
,NULL
,NVL(COMPLETION_SUBINVENTORY
,1)
,SUBINV);
SELECT
SUM(DAILY_PRODUCTION_RATE * LEAST(0
,GREATEST(PROCESSING_WORK_DAYS
,SUPPLY_CUTOFF_DATE - FIRST_UNIT_COMPLETION_DATE)) - QUANTITY_COMPLETED)
INTO QTY
FROM
WIP_REPETITIVE_SCHEDULES WRS,
WIP_REPETITIVE_ITEMS WRI
WHERE WRS.ORGANIZATION_ID = ORG_ID
AND WRS.STATUS_TYPE IN ( 1 , 3 , 4 , 6 )
AND WRI.ORGANIZATION_ID = ORG_ID
AND WRI.PRIMARY_ITEM_ID = CURRENT_ITEM_ID
AND WRI.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND WRI.LINE_ID = WRS.LINE_ID
AND NVL(WRI.COMPLETION_SUBINVENTORY
,1) = DECODE(SUBINV
,NULL
,NVL(WRI.COMPLETION_SUBINVENTORY
,1)
,SUBINV);
SELECT
SUM(QUANTITY)
INTO QTY
FROM
PO_REQUISITIONS_INTERFACE_ALL
WHERE ITEM_ID = CURRENT_ITEM_ID
AND DESTINATION_ORGANIZATION_ID = ORG_ID1
AND INCLUDE_PO = 1
AND ( PROCESS_FLAG <> 'ERROR'
OR PROCESS_FLAG IS NULL )
AND NEED_BY_DATE <= SUPPLY_CUTOFF_DATE
AND ( NVL(DESTINATION_SUBINVENTORY
,1) = DECODE(SUBINV
,NULL
,NVL(DESTINATION_SUBINVENTORY
,1)
,SUBINV)
OR EXISTS (
SELECT
1
FROM
MTL_SECONDARY_INVENTORIES SUB2
WHERE SECONDARY_INVENTORY_NAME = DESTINATION_SUBINVENTORY
AND DESTINATION_SUBINVENTORY = NVL(SUBINV
,DESTINATION_SUBINVENTORY)
AND SUB2.ORGANIZATION_ID = ORG_ID1
AND SUB2.AVAILABILITY_TYPE = DECODE(INCLUDE_NONNET
,1
,SUB2.AVAILABILITY_TYPE
,1) ) );
SELECT
SUM(START_QUANTITY)
INTO QTY
FROM
WIP_JOB_SCHEDULE_INTERFACE
WHERE PRIMARY_ITEM_ID = CURRENT_ITEM_ID
AND ORGANIZATION_ID = ORG_ID
AND INCLUDE_WIP = 1
AND PROCESS_STATUS <> 3
AND LAST_UNIT_COMPLETION_DATE <= SUPPLY_CUTOFF_DATE;
SELECT
sysdate
INTO L_SYSDATE
FROM
SYS.DUAL;