[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
DISTINCT
CSLLC.ORGANIZATION_ID
FROM
CST_SC_LOW_LEVEL_CODES CSLLC
WHERE CSLLC.ROLLUP_ID = P_ROLLUP_ID
AND not exists (
SELECT
'x'
FROM
BOM_PARAMETERS BP
WHERE CSLLC.ORGANIZATION_ID = BP.ORGANIZATION_ID );
SELECT
DISTINCT
CSLLC.ORGANIZATION_ID
FROM
CST_SC_LOW_LEVEL_CODES CSLLC
WHERE CSLLC.ROLLUP_ID = P_ROLLUP_ID
AND not exists (
SELECT
'Alternate designator exists'
FROM
BOM_ALTERNATE_DESIGNATORS BAD
WHERE BAD.ORGANIZATION_ID = CSLLC.ORGANIZATION_ID
AND BAD.ALTERNATE_DESIGNATOR_CODE = I_SNAPSHOT_DESIGNATOR );
SELECT
CSBE.ASSEMBLY_ITEM_ID,
CSBE.ASSEMBLY_ORGANIZATION_ID,
CSBE.COMPONENT_ITEM_ID,
CSBE.COMPONENT_ORGANIZATION_ID
FROM
CST_SC_BOM_EXPLOSION CSBE
WHERE CSBE.ROLLUP_ID = I_ROLLUP_ID
AND CSBE.DELETED_FLAG <> 'Y';
SELECT
DISPLAY_NAME
INTO P_ALT_BOM_DESG_DSP
FROM
BOM_ALTERNATE_DESIGNATORS_VL
WHERE ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
,lp_default_org_id)
AND ALTERNATE_DESIGNATOR_CODE = LP_ALT_BOM_DESG;
SELECT
DISPLAY_NAME
INTO P_ALT_RTG_DESG_DSP
FROM
BOM_ALTERNATE_DESIGNATORS_VL
WHERE ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
,lp_default_org_id)
AND ALTERNATE_DESIGNATOR_CODE = P_ALT_RTG_DESG;
SELECT
NVL(MIN(REQUESTED_BY)
,-1),
NVL(MIN(CONC_LOGIN_ID)
,-1),
NVL(MIN(REQUEST_ID)
,-1),
NVL(MIN(PROGRAM_APPLICATION_ID)
,-1),
NVL(MIN(CONCURRENT_PROGRAM_ID)
,-1)
INTO L_USER_ID,L_LOGIN_ID,L_REQUEST_ID,L_PROG_APPL_ID,L_PROG_ID
FROM
FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_CONC_REQUEST_ID;
SELECT
MIN(TO_CHAR(MCV.CATEGORY_ID))
INTO P_CATEGORY_ID
FROM
MTL_CATEGORIES_KFV MCV,
MTL_CATEGORY_SETS MCS
WHERE SUBSTRB(MCV.CONCATENATED_SEGMENTS
,1
,2000) = P_CATEGORY_ID
AND MCS.CATEGORY_SET_ID = LP_CATEGORY_SET_ID
AND MCV.STRUCTURE_ID = MCS.STRUCTURE_ID;
SELECT
NVL(FND_PROFILE.VALUE('CST_RU_PHANTOM_MATERIAL')
,1)
INTO P_PHANTOM_MAT
FROM
DUAL;
SELECT
ROLLUP_ID,
DESCRIPTION,
COST_TYPE_ID,
BUY_COST_TYPE_ID,
ORGANIZATION_ID,
ASSIGNMENT_SET_ID,
CONVERSION_TYPE,
LP_REPORT_LEVEL,
EXPLOSION_LEVEL,
ROLLUP_OPTION_TYPE,
1,
RANGE_TYPE,
TO_CHAR(REVISION_DATE
,'YYYY/MM/DD HH24:MI:SS'),
INC_UNIMP_ECN_FLAG,
ENG_BILL_FLAG,
QTY_PRECISION,
ITEM_ID,
CATEGORY_SET_ID,
TO_CHAR(CATEGORY_ID),
ALT_BOM_DESG,
ALT_RTG_DESG
INTO P_ROLLUP_ID,LP_DESCRIPTION,LP_COST_TYPE_ID,LP_BUY_COST_TYPE_ID,LP_ORGANIZATION_ID
,LP_ASSIGNMENT_SET_ID,P_CONVERSION_TYPE,LP_REPORT_LEVEL,LP_EXPLOSION_LEVEL
,P_ROLLUP_OPTION_TYPE,LP_REPORT_OPTION_TYPE,LP_RANGE_TYPE,LP_REVISION_DATE,LP_INC_UNIMP_ECN_FLAG,LP_ENG_BILL_FLAG,P_QTY_PRECISION,P_ITEM_ID,LP_CATEGORY_SET_ID,P_CATEGORY_ID,LP_ALT_BOM_DESG,P_ALT_RTG_DESG
FROM
CST_SC_ROLLUP_HISTORY CSRH
WHERE CSRH.ROLLUP_ID = P_ROLLUP_ID;
SELECT
CST_LISTS_S.NEXTVAL
INTO P_ROLLUP_ID
FROM
DUAL;
INSERT INTO CST_SC_ROLLUP_HISTORY
(ROLLUP_ID
,DESCRIPTION
,COST_TYPE_ID
,BUY_COST_TYPE_ID
,ORGANIZATION_ID
,ASSIGNMENT_SET_ID
,CONVERSION_TYPE
,REPORT_LEVEL
,EXPLOSION_LEVEL
,ROLLUP_OPTION_TYPE
,REPORT_OPTION_TYPE
,RANGE_TYPE
,REVISION_DATE
,INC_UNIMP_ECN_FLAG
,ENG_BILL_FLAG
,QTY_PRECISION
,ITEM_ID
,CATEGORY_SET_ID
,CATEGORY_ID
,ALT_BOM_DESG
,ALT_RTG_DESG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE)
VALUES (P_ROLLUP_ID
,LP_DESCRIPTION
,LP_COST_TYPE_ID
,LP_BUY_COST_TYPE_ID
,LP_ORGANIZATION_ID
,LP_ASSIGNMENT_SET_ID
,P_CONVERSION_TYPE
,LP_REPORT_LEVEL
,LP_EXPLOSION_LEVEL
,P_ROLLUP_OPTION_TYPE
,LP_REPORT_OPTION_TYPE
,LP_RANGE_TYPE
,TO_DATE(LP_REVISION_DATE
,'YYYY/MM/DD HH24:MI:SS')
,LP_INC_UNIMP_ECN_FLAG
,LP_ENG_BILL_FLAG
,P_QTY_PRECISION
,P_ITEM_ID
,LP_CATEGORY_SET_ID
,TO_NUMBER(P_CATEGORY_ID)
,LP_ALT_BOM_DESG
,P_ALT_RTG_DESG
,SYSDATE
,L_USER_ID
,L_LOGIN_ID
,SYSDATE
,L_USER_ID
,L_REQUEST_ID
,L_PROG_APPL_ID
,L_PROG_ID
,SYSDATE);
SELECT
DEFAULT_COST_TYPE_ID,
ORGANIZATION_ID
INTO P_DEFAULT_COST_TYPE_ID,L_ORGANIZATION_ID
FROM
CST_COST_TYPES
WHERE COST_TYPE_ID = LP_COST_TYPE_ID;
SELECT
PRIMARY_COST_METHOD
INTO LP_BUY_COST_TYPE_ID
FROM
MTL_PARAMETERS
WHERE ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
,lp_default_org_id);
SELECT
CATEGORY_SET_ID
INTO LP_CATEGORY_SET_ID
FROM
MTL_DEFAULT_CATEGORY_SETS MDCS
WHERE MDCS.FUNCTIONAL_AREA_ID = 5;
SELECT
TO_CHAR(SYSDATE
,'YYYY/MM/DD HH24:MI:ss')
INTO LP_REVISION_DATE
FROM
DUAL;
SELECT
CCT1.COST_TYPE,
DECODE(LP_ASSIGNMENT_SET_ID
,NULL
,' '
,CCT2.COST_TYPE),
GDCT.USER_CONVERSION_TYPE
INTO P_COST_TYPE_NAME,P_BUY_COST_TYPE_NAME,P_CONVERSION_TYPE_NAME
FROM
CST_COST_TYPES CCT1,
CST_COST_TYPES CCT2,
GL_DAILY_CONVERSION_TYPES GDCT
WHERE CCT1.COST_TYPE_ID = LP_COST_TYPE_ID
AND CCT2.COST_TYPE_ID = LP_BUY_COST_TYPE_ID
AND GDCT.CONVERSION_TYPE = P_CONVERSION_TYPE;
SELECT
MAS.ASSIGNMENT_SET_NAME
INTO P_ASSIGNMENT_SET_NAME
FROM
MRP_ASSIGNMENT_SETS MAS
WHERE MAS.ASSIGNMENT_SET_ID = LP_ASSIGNMENT_SET_ID;
SELECT
SUBSTRB(MCV.CONCATENATED_SEGMENTS
,1
,2000)
INTO P_CATEGORY_NAME
FROM
MTL_CATEGORIES_KFV MCV
WHERE CATEGORY_ID = TO_NUMBER(P_CATEGORY_ID);
SELECT
SUBSTRB(CONCATENATED_SEGMENTS
,1
,2000)
INTO P_ITEM_NAME
FROM
MTL_SYSTEM_ITEMS_KFV MSIV
WHERE MSIV.INVENTORY_ITEM_ID = P_ITEM_ID
AND MSIV.ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
,lp_default_org_id);
INSERT INTO CST_SC_LISTS
(ROLLUP_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE)
SELECT
DISTINCT
P_ROLLUP_ID,
MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
sysdate,
L_USER_ID,
sysdate,
L_USER_ID,
L_LOGIN_ID,
L_REQUEST_ID,
L_PROG_APPL_ID,
L_PROG_ID,
sysdate
FROM
MTL_SYSTEM_ITEMS MSI,
BOM_PARAMETERS BP,
CST_ITEM_COSTS CIC,
MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = NVL(LP_ORGANIZATION_ID
,MSI.ORGANIZATION_ID)
AND MSI.INVENTORY_ITEM_ID = P_ITEM_ID
AND MSI.COSTING_ENABLED_FLAG = 'Y'
AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND CIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND CIC.INVENTORY_ITEM_ID = P_ITEM_ID
AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC2
WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC3
WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
AND CIC.BASED_ON_ROLLUP_FLAG = 1
AND BP.organization_id (+) = MSI.ORGANIZATION_ID
AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+);
INSERT INTO CST_SC_LISTS
(ROLLUP_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE)
SELECT
P_ROLLUP_ID,
MIC.INVENTORY_ITEM_ID,
MIC.ORGANIZATION_ID,
sysdate,
L_USER_ID,
sysdate,
L_USER_ID,
L_LOGIN_ID,
L_REQUEST_ID,
L_PROG_APPL_ID,
L_PROG_ID,
sysdate
FROM
MTL_ITEM_CATEGORIES MIC,
MTL_SYSTEM_ITEMS MSI,
BOM_PARAMETERS BP,
MTL_CATEGORIES_KFV MCV,
CST_ITEM_COSTS CIC,
MTL_PARAMETERS MP
WHERE LP_ORGANIZATION_ID is not null
AND MIC.ORGANIZATION_ID = LP_ORGANIZATION_ID
AND MIC.CATEGORY_SET_ID = LP_CATEGORY_SET_ID
AND MIC.CATEGORY_ID = MCV.CATEGORY_ID
AND MCV.CONCATENATED_SEGMENTS >= DECODE(P_CATEGORY_FROM
,NULL
,MCV.CONCATENATED_SEGMENTS
,P_CATEGORY_FROM)
AND MCV.CONCATENATED_SEGMENTS <= DECODE(P_CATEGORY_TO
,NULL
,MCV.CONCATENATED_SEGMENTS
,P_CATEGORY_TO)
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MSI.COSTING_ENABLED_FLAG = 'Y'
AND MP.ORGANIZATION_ID = LP_ORGANIZATION_ID
AND CIC.ORGANIZATION_ID = LP_ORGANIZATION_ID
AND CIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC2
WHERE CIC2.ORGANIZATION_ID = LP_ORGANIZATION_ID
AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC3
WHERE CIC3.ORGANIZATION_ID = LP_ORGANIZATION_ID
AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
AND CIC.BASED_ON_ROLLUP_FLAG = 1
AND BP.organization_id (+) = MSI.ORGANIZATION_ID
AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+)
UNION
SELECT
P_ROLLUP_ID,
MIC.INVENTORY_ITEM_ID,
MIC.ORGANIZATION_ID,
sysdate,
L_USER_ID,
sysdate,
L_USER_ID,
L_LOGIN_ID,
L_REQUEST_ID,
L_PROG_APPL_ID,
L_PROG_ID,
sysdate
FROM
MTL_ITEM_CATEGORIES MIC,
MTL_SYSTEM_ITEMS MSI,
BOM_PARAMETERS BP,
MTL_CATEGORIES_KFV MCV,
CST_ITEM_COSTS CIC,
MTL_PARAMETERS MP
WHERE LP_ORGANIZATION_ID is null
AND MIC.CATEGORY_SET_ID = LP_CATEGORY_SET_ID
AND MIC.CATEGORY_ID = MCV.CATEGORY_ID
AND MCV.CONCATENATED_SEGMENTS >= DECODE(P_CATEGORY_FROM
,NULL
,MCV.CONCATENATED_SEGMENTS
,P_CATEGORY_FROM)
AND MCV.CONCATENATED_SEGMENTS <= DECODE(P_CATEGORY_TO
,NULL
,MCV.CONCATENATED_SEGMENTS
,P_CATEGORY_TO)
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MSI.COSTING_ENABLED_FLAG = 'Y'
AND MP.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND CIC.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND CIC.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC2
WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC3
WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
AND CIC.BASED_ON_ROLLUP_FLAG = 1
AND BP.organization_id (+) = MSI.ORGANIZATION_ID
AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+);
INSERT INTO CST_SC_LISTS
(ROLLUP_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE)
SELECT
P_ROLLUP_ID,
MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
sysdate,
L_USER_ID,
sysdate,
L_USER_ID,
L_LOGIN_ID,
L_REQUEST_ID,
L_PROG_APPL_ID,
L_PROG_ID,
sysdate
FROM
MTL_SYSTEM_ITEMS_KFV MSI,
BOM_PARAMETERS BP,
CST_ITEM_COSTS CIC,
MTL_PARAMETERS MP
WHERE LP_ORGANIZATION_ID is not null
AND MSI.ORGANIZATION_ID = LP_ORGANIZATION_ID
AND MSI.CONCATENATED_SEGMENTS >= DECODE(P_ITEM_FROM
,NULL
,MSI.CONCATENATED_SEGMENTS
,P_ITEM_FROM)
AND MSI.CONCATENATED_SEGMENTS <= DECODE(P_ITEM_TO
,NULL
,MSI.CONCATENATED_SEGMENTS
,P_ITEM_TO)
AND MSI.COSTING_ENABLED_FLAG = 'Y'
AND MP.ORGANIZATION_ID = LP_ORGANIZATION_ID
AND CIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC2
WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC3
WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
AND CIC.BASED_ON_ROLLUP_FLAG = 1
AND NVL(CIC.ITEM_COST
,0) = DECODE(LP_RANGE_TYPE
,4
,0
,NVL(CIC.ITEM_COST
,0))
AND BP.organization_id (+) = MSI.ORGANIZATION_ID
AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+)
UNION
SELECT
P_ROLLUP_ID,
MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
sysdate,
L_USER_ID,
sysdate,
L_USER_ID,
L_LOGIN_ID,
L_REQUEST_ID,
L_PROG_APPL_ID,
L_PROG_ID,
sysdate
FROM
MTL_SYSTEM_ITEMS_KFV MSI,
BOM_PARAMETERS BP,
CST_ITEM_COSTS CIC,
MTL_PARAMETERS MP
WHERE LP_ORGANIZATION_ID is null
AND MSI.CONCATENATED_SEGMENTS >= DECODE(P_ITEM_FROM
,NULL
,MSI.CONCATENATED_SEGMENTS
,P_ITEM_FROM)
AND MSI.CONCATENATED_SEGMENTS <= DECODE(P_ITEM_TO
,NULL
,MSI.CONCATENATED_SEGMENTS
,P_ITEM_TO)
AND MSI.COSTING_ENABLED_FLAG = 'Y'
AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND CIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ( CIC.COST_TYPE_ID = LP_COST_TYPE_ID
OR ( CIC.COST_TYPE_ID = P_DEFAULT_COST_TYPE_ID
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC2
WHERE CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC2.COST_TYPE_ID = LP_COST_TYPE_ID ) )
OR ( CIC.COST_TYPE_ID = MP.PRIMARY_COST_METHOD
AND not exists (
SELECT
'X'
FROM
CST_ITEM_COSTS CIC3
WHERE CIC3.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND CIC3.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND CIC3.COST_TYPE_ID IN ( LP_COST_TYPE_ID , P_DEFAULT_COST_TYPE_ID ) ) ) )
AND CIC.BASED_ON_ROLLUP_FLAG = 1
AND NVL(CIC.ITEM_COST
,0) = DECODE(LP_RANGE_TYPE
,4
,0
,NVL(CIC.ITEM_COST
,0))
AND BP.organization_id (+) = MSI.ORGANIZATION_ID
AND nvl(MSI.INVENTORY_ITEM_STATUS_CODE,
'NOT_' || BP.bom_delete_status_code (+)) <> BP.bom_delete_status_code (+);
SELECT
MEANING
INTO P_LOT_SIZE_OPTION_NAME
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'CST_SC_LOT_OPTION'
AND LOOKUP_CODE = P_LOT_SIZE_OPTION;
SELECT
sysdate
INTO L_TIMESTAMP
FROM
DUAL;
SELECT
sysdate
INTO L_TIMESTAMP
FROM
DUAL;
SELECT
count(*)
INTO L_ROWS_UNEXPLODED
FROM
CST_SC_BOM_EXPLOSION CSBE
WHERE CSBE.ROLLUP_ID = P_ROLLUP_ID
AND CSBE.DELETED_FLAG <> 'Y';
SELECT
count(SOB.CURRENCY_CODE)
INTO P_NUM_CURRENCIES
FROM
CST_SC_LOW_LEVEL_CODES CSLLC,
HR_ORGANIZATION_INFORMATION HOI,
GL_LEDGERS SOB
WHERE CSLLC.ROLLUP_ID = P_ROLLUP_ID
AND HOI.ORGANIZATION_ID = CSLLC.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'Acounting Information'
AND SOB.LEDGER_ID = HOI.ORG_INFORMATION1;
SELECT
ALTERNATE_BOM_DESIGNATOR
INTO L_SNAPSHOT_DESIGNATOR
FROM
CST_COST_TYPES
WHERE COST_TYPE_ID = LP_COST_TYPE_ID;
SELECT
ORGANIZATION_CODE
INTO L_NO_ALT_ORG
FROM
MTL_PARAMETERS
WHERE ORGANIZATION_ID = ORGS.ORGANIZATION_ID;
SELECT
ORGANIZATION_CODE
INTO L_NO_BOM_ORG
FROM
MTL_PARAMETERS
WHERE ORGANIZATION_ID = ORGS.ORGANIZATION_ID;
SELECT
DISTINCT
ORGANIZATION_ID
FROM
CST_SC_LOW_LEVEL_CODES
WHERE ROLLUP_ID = P_ROLLUP_ID;
SELECT
NVL(DEFAULT_MATL_OVHD_COST_ID
,0),
ORGANIZATION_CODE
INTO L_DEFAULT_MATL_OVHD,L_ORGANIZATION_CODE
FROM
MTL_PARAMETERS
WHERE ORGANIZATION_ID = ORGANIZATIONS.ORGANIZATION_ID;