[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
asl_summ_load_glbl_pkg.Delete_Rows (
p_table_name => 'ASL_INVENTORY_ITEM_DENORM'
, p_category_set_id => l_category_set_id
, p_organization_id => l_organization_id
, p_category_id => l_category_id
, x_err_msg => l_err_msg
, x_err_code => l_err_code
);
asl_summ_load_glbl_pkg.Delete_Rows (
p_table_name => 'ASL_INVENTORY_PRICING'
, p_category_set_id => l_category_set_id
, p_organization_id => l_organization_id
, p_category_id => l_category_id
, x_err_msg => l_err_msg
, x_err_code => l_err_code
);
l_rows_inserted NUMBER := 0;
DELETE asl_category_summary_info;
INSERT INTO asl_category_summary_info (CATEGORY_ID, CATEGORY_SET_ID , ORGANIZATION_ID,
CREATION_DATE, LANGUAGE_CODE )
SELECT DISTINCT category_id, category_set_id , organization_id, SYSDATE, language_code
FROM asl_inventory_item_denorm ;
l_rows_inserted := SQL%ROWCOUNT;
, p_num_rows => l_rows_inserted
);
l_rows_inserted NUMBER := 0;
INSERT INTO ASL_INVENTORY_ITEM_DENORM
(CATEGORY_SET_ID
,CATEGORY_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,LAST_UPDATE_DATE
,CREATION_DATE
,INVENTORY_ITEM_NUMBER
,ITEM_DESCRIPTION
,LANGUAGE_CODE
,UOM_CODE
,UOM_DESCRIPTION
,INTEREST_TYPE_ID
,INTEREST_TYPE
,PRIMARY_INTEREST_CODE_ID
,PRIMARY_INTEREST_CODE
,SECONDARY_INTEREST_CODE_ID
,SECONDARY_INTEREST_CODE
,SHIPPLE_FLAG
,SERVICE_ITEM_FLAG
,TAXABLE_FLAG
,RETURNABLE_FLAG
,SERVICEABLE_FLAG
,ACTIVE_FLAG
,BOM_ENABLED_FLAG
,VENDOR_WARRANTY_FLAG
,PRIMARY_UOM_CODE
) SELECT /*+ FIRST_ROWS */
MIC.CATEGORY_SET_ID,
MIC.CATEGORY_ID,
ITEM.INVENTORY_ITEM_ID,
ITEM.ORGANIZATION_ID,
SYSDATE, -- For bootstrap, using sysdate temporary.
SYSDATE,
B.CONCATENATED_SEGMENTS,
ITEM.DESCRIPTION,
USERENV ( 'LANG' ),
UOM.UOM_CODE,
UOM.UNIT_OF_MEASURE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
B.SHIPPABLE_ITEM_FLAG ,
B.SERVICE_ITEM_FLAG ,
B.TAXABLE_FLAG ,
B.returnable_flag ,
B.SERVICEABLE_PRODUCT_FLAG ,
'Y', -- Active Flag to be 'Y'
DECODE(B.BOM_ITEM_TYPE,1,'MDL',4,DECODE(B.SERVICE_ITEM_FLAG ,'Y','SRV', DECODE(B.SERVICEABLE_PRODUCT_FLAG,'Y','SVA','STD')),'OPP') ,
B.VENDOR_WARRANTY_FLAG,
B.PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL ITEM,
MTL_ITEM_CATEGORIES MIC,
MTL_UNITS_OF_MEASURE_TL UOM
WHERE MIC.ORGANIZATION_ID = l_inv_org_id
AND MIC.CATEGORY_SET_ID = l_category_set_id
AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND UOM.LANGUAGE = userenv('LANG')
AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEM.LANGUAGE = userenv('LANG');
INSERT INTO ASL_INVENTORY_ITEM_DENORM
(CATEGORY_SET_ID
,CATEGORY_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,LAST_UPDATE_DATE
,CREATION_DATE
,INVENTORY_ITEM_NUMBER
,ITEM_DESCRIPTION
,LANGUAGE_CODE
,UOM_CODE
,UOM_DESCRIPTION
,INTEREST_TYPE_ID
,INTEREST_TYPE
,PRIMARY_INTEREST_CODE_ID
,PRIMARY_INTEREST_CODE
,SECONDARY_INTEREST_CODE_ID
,SECONDARY_INTEREST_CODE
,SHIPPLE_FLAG
,SERVICE_ITEM_FLAG
,TAXABLE_FLAG
,RETURNABLE_FLAG
,SERVICEABLE_FLAG
,ACTIVE_FLAG
,BOM_ENABLED_FLAG
,VENDOR_WARRANTY_FLAG
,PRIMARY_UOM_CODE
) SELECT /*+ FIRST_ROWS */
MIC.CATEGORY_SET_ID,
MIC.CATEGORY_ID,
ITEM.INVENTORY_ITEM_ID,
ITEM.ORGANIZATION_ID,
SYSDATE, -- For bootstrap, using sysdate temporary.
SYSDATE,
B.CONCATENATED_SEGMENTS,
ITEM.DESCRIPTION,
USERENV ( 'LANG' ),
UOM.UOM_CODE,
UOM.UNIT_OF_MEASURE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
B.SHIPPABLE_ITEM_FLAG ,
B.SERVICE_ITEM_FLAG ,
B.TAXABLE_FLAG ,
B.returnable_flag ,
B.SERVICEABLE_PRODUCT_FLAG ,
'Y', -- Active Flag to be 'Y'
DECODE(B.BOM_ITEM_TYPE,1,'MDL',4,DECODE(B.SERVICE_ITEM_FLAG ,'Y','SRV', DECODE(B.SERVICEABLE_PRODUCT_FLAG,'Y','SVA','STD')),'OPP') ,
B.VENDOR_WARRANTY_FLAG,
B.PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL ITEM,
MTL_ITEM_CATEGORIES MIC,
MTL_UNITS_OF_MEASURE_TL UOM
WHERE MIC.ORGANIZATION_ID = l_inv_org_id
AND MIC.CATEGORY_SET_ID = l_category_set_id
AND MIC.CATEGORY_ID = l_category_id
AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND UOM.LANGUAGE = userenv('LANG')
AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEM.LANGUAGE = userenv('LANG');
l_rows_inserted := SQL%ROWCOUNT;
, p_procedure => 'Insert_Inv_Item_Denorm'
, p_num_rows => l_rows_inserted
);
l_rows_updated NUMBER := 0;
SELECT INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES
WHERE CATEGORY_SET_ID = pl_category_set_id
AND ORGANIZATION_ID = pl_inv_org_id
AND CATEGORY_ID = pl_category_id;
UPDATE ASL_INVENTORY_ITEM_DENORM aiid
SET
( LAST_UPDATE_DATE
,INVENTORY_ITEM_NUMBER
,ITEM_DESCRIPTION
,LANGUAGE_CODE
,UOM_CODE
,UOM_DESCRIPTION
-- ,INTEREST_TYPE_ID -- is this required
-- ,INTEREST_TYPE
-- ,PRIMARY_INTEREST_CODE_ID
-- ,PRIMARY_INTEREST_CODE
-- ,SECONDARY_INTEREST_CODE_ID
-- ,SECONDARY_INTEREST_CODE -- is this required
,SHIPPLE_FLAG
,SERVICE_ITEM_FLAG
,TAXABLE_FLAG
,RETURNABLE_FLAG
,SERVICEABLE_FLAG
,ACTIVE_FLAG ) =
(SELECT
SYSDATE,
B.CONCATENATED_SEGMENTS,
ITEM.DESCRIPTION,
USERENV ( 'LANG' ),
UOM.UOM_CODE,
UOM.UNIT_OF_MEASURE,
-- NULL,
-- NULL,
-- NULL,
-- NULL,
-- NULL,
-- NULL,
B.SHIPPABLE_ITEM_FLAG ,
B.SERVICE_ITEM_FLAG ,
B.TAXABLE_FLAG ,
B.returnable_flag ,
B.SERVICEABLE_PRODUCT_FLAG ,
'Y' -- Active Flag to be 'Y'
FROM MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL ITEM,
MTL_ITEM_CATEGORIES MIC,
MTL_UNITS_OF_MEASURE_TL UOM
WHERE MIC.ORGANIZATION_ID = l_inv_org_id
AND MIC.CATEGORY_SET_ID = l_category_set_id
AND MIC.CATEGORY_ID = l_category_id
AND B.INVENTORY_ITEM_ID = l_inv_item_id
AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND UOM.LANGUAGE = userenv('LANG')
AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEM.LANGUAGE = userenv('LANG') )
WHERE aiid.ORGANIZATION_ID = l_inv_org_id
AND aiid.CATEGORY_SET_ID = l_category_set_id
AND aiid.CATEGORY_ID = l_category_id
AND aiid.INVENTORY_ITEM_ID = l_inv_item_id
AND aiid.LANGUAGE_CODE = userenv('LANG')
-- if we do not put this part of code and if select does not get records update will raise ora error
AND EXISTS (SELECT 1 FROM
MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL ITEM,
MTL_ITEM_CATEGORIES MIC,
MTL_UNITS_OF_MEASURE_TL UOM
WHERE MIC.ORGANIZATION_ID = l_inv_org_id
AND MIC.CATEGORY_SET_ID = l_category_set_id
AND MIC.CATEGORY_ID = l_category_id
AND B.INVENTORY_ITEM_ID = l_inv_item_id
AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND UOM.LANGUAGE = userenv('LANG')
AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEM.LANGUAGE = userenv('LANG') );
INSERT INTO ASL_INVENTORY_ITEM_DENORM
(CATEGORY_SET_ID
,CATEGORY_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,LAST_UPDATE_DATE
,CREATION_DATE
,INVENTORY_ITEM_NUMBER
,ITEM_DESCRIPTION
,LANGUAGE_CODE
,UOM_CODE
,UOM_DESCRIPTION
,INTEREST_TYPE_ID
,INTEREST_TYPE
,PRIMARY_INTEREST_CODE_ID
,PRIMARY_INTEREST_CODE
,SECONDARY_INTEREST_CODE_ID
,SECONDARY_INTEREST_CODE
,SHIPPLE_FLAG
,SERVICE_ITEM_FLAG
,TAXABLE_FLAG
,RETURNABLE_FLAG
,SERVICEABLE_FLAG
,ACTIVE_FLAG
,BOM_ENABLED_FLAG
,VENDOR_WARRANTY_FLAG
,PRIMARY_UOM_CODE
) SELECT /*+ FIRST_ROWS */
MIC.CATEGORY_SET_ID,
MIC.CATEGORY_ID,
ITEM.INVENTORY_ITEM_ID,
ITEM.ORGANIZATION_ID,
SYSDATE, -- For bootstrap, using sysdate temporary.
SYSDATE,
B.CONCATENATED_SEGMENTS,
ITEM.DESCRIPTION,
USERENV ( 'LANG' ),
UOM.UOM_CODE,
UOM.UNIT_OF_MEASURE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
B.SHIPPABLE_ITEM_FLAG ,
B.SERVICE_ITEM_FLAG ,
B.TAXABLE_FLAG ,
B.returnable_flag ,
B.SERVICEABLE_PRODUCT_FLAG ,
'Y', -- Active Flag to be 'Y'
DECODE(B.BOM_ITEM_TYPE,1,'MDL',4,DECODE(B.SERVICE_ITEM_FLAG ,'Y','SRV', DECODE(B.SERVICEABLE_PRODUCT_FLAG,'Y','SVA','STD')),'OPP') ,
B.VENDOR_WARRANTY_FLAG,
B.PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL ITEM,
MTL_ITEM_CATEGORIES MIC,
MTL_UNITS_OF_MEASURE_TL UOM
WHERE MIC.ORGANIZATION_ID = l_inv_org_id
AND MIC.CATEGORY_SET_ID = l_category_set_id
AND MIC.CATEGORY_ID = l_category_id
AND ITEM.INVENTORY_ITEM_ID = l_inv_item_id
AND MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND UOM.LANGUAGE = userenv('LANG')
AND B.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEM.LANGUAGE = userenv('LANG')
AND NOT EXISTS
( SELECT 1
FROM ASL_INVENTORY_ITEM_DENORM aiid
WHERE aiid.CATEGORY_SET_ID = l_category_set_id
AND aiid.ORGANIZATION_ID = l_inv_org_id
AND aiid.CATEGORY_ID = l_category_id
AND aiid.INVENTORY_ITEM_ID = l_inv_item_id
AND aiid.LANGUAGE_CODE = userenv('LANG') );
l_rows_updated := 0 ;
l_rows_updated := SQL%ROWCOUNT;
, p_num_rows => l_rows_updated
) ;
l_rows_updated NUMBER := 0;
SELECT CATEGORY_ID
FROM MTL_ITEM_CATEGORIES
WHERE CATEGORY_SET_ID = pl_category_set_id
AND ORGANIZATION_ID = pl_inv_org_id;
l_rows_updated := 0 ;
l_rows_updated := SQL%ROWCOUNT;
, p_num_rows => l_rows_updated
) ;
l_rows_inserted NUMBER := 0;
SELECT QH.LIST_HEADER_ID
FROM QP_LIST_HEADERS_B QH
WHERE QH.LIST_TYPE_CODE = 'PRL'
AND nvl(QH.start_date_active, SYSDATE) <= SYSDATE
AND nvl(QH.end_date_active, SYSDATE) >= SYSDATE
AND QH.mobile_download = 'Y'
AND QH.ACTIVE_FLAG = 'Y'
AND QH.currency_code = p_currency_code;
SELECT INVENTORY_ITEM_ID
FROM ASL_INVENTORY_ITEM_DENORM ITEM
WHERE ITEM.CATEGORY_SET_ID = p_category_set_id
AND ITEM.CATEGORY_ID = p_category_id
AND ITEM.ORGANIZATION_ID = p_inv_org_id
AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
SELECT FND_PROFILE.value('JTF_PROFILE_DEFAULT_CURRENCY')
INTO l_currency_code
FROM DUAL;
INSERT INTO ASL_INVENTORY_PRICING
(LIST_HEADER_ID
,LIST_LINE_ID
,LIST_LINE_TYPE_CODE
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,AUTOMATIC_FLAG
,LIST_PRICE
,LIST_PRICE_UOM_CODE
,PRIMARY_UOM_FLAG
,LIST_LINE_NO
,LAST_UPDATE_DATE
,CREATION_DATE
,LANGUAGE_CODE
,CURRENCY_CODE
)
SELECT *//*+ ORDERED use_nl(QPA QL)
index(QPA QP_PRICING_ATTRIBUTES_N5)
index(QL QP_LIST_LINES_PK)
index(ITEM ASL_INVENTORY_ITEM_DENORM_N1)*/
/*distinct QH.LIST_HEADER_ID,
QL.list_line_id,
QL.list_line_type_code,
ITEM.inventory_item_id,
l_inv_org_id,
QL.AUTOMATIC_FLAG ,
DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
QL.LIST_PRICE_UOM_CODE ,
QL.PRIMARY_UOM_FLAG ,
QL.LIST_LINE_NO ,
SYSDATE,
SYSDATE,
USERENV ( 'LANG' ),
l_currency_code
FROM QP_PRICING_ATTRIBUTES QPA,
QP_LIST_LINES QL,
ASL_INVENTORY_ITEM_DENORM ITEM ,
QP_LIST_HEADERS_B QH
WHERE QPA.LIST_HEADER_ID = QH.LIST_HEADER_ID
AND QH.LIST_TYPE_CODE = 'PRL'
AND nvl(QH.start_date_active, SYSDATE) <= SYSDATE
AND nvl(QH.end_date_active, SYSDATE) >= SYSDATE
AND QH.mobile_download = 'Y'
AND QH.ACTIVE_FLAG = 'Y'
AND QH.currency_code = l_currency_code
-- AND QPA.PRICING_PHASE_ID = 1
AND QPA.product_attribute_context = 'ITEM'
AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
AND to_char(ITEM.inventory_item_id) = QPA.PRODUCT_ATTR_VALUE
-- AND QPA.QUALIFICATION_IND IN (4, 6)
AND QPA.excluder_flag = 'N'
AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
AND QL.LIST_LINE_TYPE_CODE = 'PLL'
AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE'
AND ITEM.CATEGORY_SET_ID = l_category_set_id
AND ITEM.ORGANIZATION_ID = l_inv_org_id
AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
INSERT INTO ASL_INVENTORY_PRICING
(LIST_HEADER_ID
,LIST_LINE_ID
,LIST_LINE_TYPE_CODE
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,AUTOMATIC_FLAG
,LIST_PRICE
,LIST_PRICE_UOM_CODE
,PRIMARY_UOM_FLAG
,LIST_LINE_NO
,LAST_UPDATE_DATE
,CREATION_DATE
,LANGUAGE_CODE
,CURRENCY_CODE
) SELECT*/ /*+ ORDERED use_nl(QPA QL)
index(QPA QP_PRICING_ATTRIBUTES_N5)
index(QL QP_LIST_LINES_PK)*/
/*l_list_header_id,
QL.list_line_id,
QL.list_line_type_code,
l_inventory_item_id,
l_inv_org_id,
QL.AUTOMATIC_FLAG ,
DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
QL.LIST_PRICE_UOM_CODE ,
QL.PRIMARY_UOM_FLAG ,
QL.LIST_LINE_NO ,
SYSDATE,
SYSDATE,
USERENV ( 'LANG' ),
l_currency_code
FROM QP_PRICING_ATTRIBUTES QPA,
QP_LIST_LINES QL
WHERE QPA.LIST_HEADER_ID = l_list_header_id
-- AND QPA.PRICING_PHASE_ID = 1
AND QPA.product_attribute_context = 'ITEM'
AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
-- AND QPA.QUALIFICATION_IND IN (4, 6)
AND QPA.excluder_flag = 'N'
AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
AND QL.LIST_LINE_TYPE_CODE = 'PLL'
AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE' ;
l_rows_inserted := SQL%ROWCOUNT;
, p_num_rows => l_rows_inserted
);
l_rows_updated NUMBER := 0;
SELECT QH.LIST_HEADER_ID
FROM QP_LIST_HEADERS_B QH
WHERE QH.LIST_TYPE_CODE = 'PRL'
AND nvl(QH.start_date_active, SYSDATE) <= SYSDATE
AND nvl(QH.end_date_active, SYSDATE) >= SYSDATE
AND QH.mobile_download = 'Y'
AND QH.ACTIVE_FLAG = 'Y'
AND QH.currency_code = p_currency_code;
SELECT INVENTORY_ITEM_ID
FROM ASL_INVENTORY_ITEM_DENORM ITEM
WHERE ITEM.CATEGORY_SET_ID = p_category_set_id
AND ITEM.CATEGORY_ID = p_category_id
AND ITEM.ORGANIZATION_ID = p_inv_org_id
AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
SELECT FND_PROFILE.value('JTF_PROFILE_DEFAULT_CURRENCY')
INTO l_currency_code
FROM DUAL;
UPDATE ASL_INVENTORY_PRICING aip
SET (LIST_LINE_ID
,LIST_LINE_TYPE_CODE
,AUTOMATIC_FLAG
,LIST_PRICE
,LIST_PRICE_UOM_CODE
,PRIMARY_UOM_FLAG
,LIST_LINE_NO
,LAST_UPDATE_DATE
,LANGUAGE_CODE ) =
( SELECT + ORDERED use_nl(QPA QL)
index(QPA QP_PRICING_ATTRIBUTES_N5)
index(QL QP_LIST_LINES_PK)
QL.list_line_id,
QL.list_line_type_code,
QL.AUTOMATIC_FLAG ,
DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
QL.LIST_PRICE_UOM_CODE ,
QL.PRIMARY_UOM_FLAG ,
QL.LIST_LINE_NO ,
SYSDATE,
USERENV ( 'LANG' )
FROM QP_PRICING_ATTRIBUTES QPA,
QP_LIST_LINES QL
WHERE QPA.LIST_HEADER_ID = l_list_header_id
-- AND QPA.PRICING_PHASE_ID = 1
AND QPA.product_attribute_context = 'ITEM'
AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
-- AND QPA.QUALIFICATION_IND IN (4, 6)
AND QPA.excluder_flag = 'N'
AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
AND QL.LIST_LINE_TYPE_CODE = 'PLL'
AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE' )
WHERE aip.LIST_HEADER_ID = l_list_header_id
AND aip.INVENTORY_ITEM_ID = l_inventory_item_id
AND aip.ORGANIZATION_ID = l_inv_org_id
AND aip.CURRENCY_CODE = l_currency_code
AND EXISTS (SELECT 1
FROM QP_PRICING_ATTRIBUTES QPA,
QP_LIST_LINES QL
WHERE QPA.LIST_HEADER_ID = l_list_header_id
-- AND QPA.PRICING_PHASE_ID = 1
AND QPA.product_attribute_context = 'ITEM'
AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
-- AND QPA.QUALIFICATION_IND IN (4, 6)
AND QPA.excluder_flag = 'N'
AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
AND QL.LIST_LINE_TYPE_CODE = 'PLL'
AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE' );
INSERT INTO ASL_INVENTORY_PRICING
(LIST_HEADER_ID
,LIST_LINE_ID
,LIST_LINE_TYPE_CODE
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,AUTOMATIC_FLAG
,LIST_PRICE
,LIST_PRICE_UOM_CODE
,PRIMARY_UOM_FLAG
,LIST_LINE_NO
,LAST_UPDATE_DATE
,CREATION_DATE
,LANGUAGE_CODE
,CURRENCY_CODE
) SELECT + ORDERED use_nl(QPA QL)
index(QPA QP_PRICING_ATTRIBUTES_N5)
index(QL QP_LIST_LINES_PK)
l_list_header_id,
QL.list_line_id,
QL.list_line_type_code,
l_inventory_item_id,
l_inv_org_id,
QL.AUTOMATIC_FLAG ,
DECODE(QL.OPERAND, NULL, QL.LIST_PRICE, QL.OPERAND),
QL.LIST_PRICE_UOM_CODE ,
QL.PRIMARY_UOM_FLAG ,
QL.LIST_LINE_NO ,
SYSDATE,
SYSDATE,
USERENV ( 'LANG' ),
l_currency_code
FROM QP_PRICING_ATTRIBUTES QPA,
QP_LIST_LINES QL
WHERE QPA.LIST_HEADER_ID = l_list_header_id
-- AND QPA.PRICING_PHASE_ID = 1
AND QPA.product_attribute_context = 'ITEM'
AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
AND QPA.PRODUCT_ATTR_VALUE = to_char(l_inventory_item_id)
-- AND QPA.QUALIFICATION_IND IN (4, 6)
AND QPA.excluder_flag = 'N'
AND QPA.LIST_LINE_ID = QL.LIST_LINE_ID
AND QL.LIST_LINE_TYPE_CODE = 'PLL'
AND QL.ARITHMETIC_OPERATOR = 'UNIT_PRICE'
AND NOT EXISTS
( SELECT 1
FROM ASL_INVENTORY_PRICING aip
WHERE aip.LIST_HEADER_ID = l_list_header_id
AND aip.INVENTORY_ITEM_ID = l_inventory_item_id
AND aip.ORGANIZATION_ID = l_inv_org_id
AND aip.CURRENCY_CODE = l_currency_code);
l_rows_updated := 0 ;
l_rows_updated := SQL%ROWCOUNT;
, p_num_rows => l_rows_updated
) ;
l_rows_updated NUMBER := 0;
SELECT distinct CATEGORY_ID
FROM ASL_INVENTORY_ITEM_DENORM ITEM
WHERE ITEM.CATEGORY_SET_ID = pl_category_set_id
AND ITEM.ORGANIZATION_ID = pl_inv_org_id
AND ITEM.LANGUAGE_CODE = USERENV ( 'LANG' );
l_rows_updated := 0 ;
l_rows_updated := SQL%ROWCOUNT;
, p_num_rows => l_rows_updated
) ;