[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM op_prce_mst
WHERE pricelist_id = p_old_pricelist_id;
SELECT *
FROM op_prce_itm
WHERE pricelist_id = p_old_pricelist_id;
SELECT *
FROM op_prce_brk
WHERE price_id = V_old_price_id;
INSERT INTO OP_PRCE_MST
( pricelist_id ,
pricelist_desc1 ,
pricesort_name ,
comments ,
currency_code ,
creation_date ,
pricelist_code ,
last_update_date , last_updated_by ,
created_by , last_update_login,
delete_mark , in_use ,
text_code ,
attribute1 , attribute2 ,
attribute3 , attribute4 ,
attribute5 , attribute6 ,
attribute7 , attribute8 ,
attribute9 , attribute10 ,
attribute11 , attribute12 ,
attribute13 , attribute14 ,
attribute15 , attribute16 ,
attribute17 , attribute18 ,
attribute19 , attribute20 ,
attribute21 , attribute22 ,
attribute23 , attribute24 ,
attribute25 , attribute26 ,
attribute27 , attribute28 ,
attribute29 , attribute30 ,
attribute_category
)
SELECT
X_pricelist_id ,
NVL(P_pricelist_desc1, v_prce_mst.pricelist_desc1) ,
NVL(P_pricesort_name , v_prce_mst.pricesort_name ) ,
P_comments ,
NVL( P_currency_code, v_prce_mst.currency_code ) ,
sysdate , P_pricelist_code ,
sysdate , P_user_id ,
P_user_id , NULL ,
v_prce_mst.delete_mark , 0 ,
X_prce_mst_text_code ,
v_prce_mst.attribute1 , v_prce_mst.attribute2 ,
v_prce_mst.attribute3 , v_prce_mst.attribute4 ,
v_prce_mst.attribute5 , v_prce_mst.attribute6 ,
v_prce_mst.attribute7 , v_prce_mst.attribute8 ,
v_prce_mst.attribute9 , v_prce_mst.attribute10,
v_prce_mst.attribute11 , v_prce_mst.attribute12,
v_prce_mst.attribute13 , v_prce_mst.attribute14,
v_prce_mst.attribute15 , v_prce_mst.attribute16,
v_prce_mst.attribute17 , v_prce_mst.attribute18,
v_prce_mst.attribute19 , v_prce_mst.attribute20,
v_prce_mst.attribute21 , v_prce_mst.attribute22,
v_prce_mst.attribute23 , v_prce_mst.attribute24,
v_prce_mst.attribute25 , v_prce_mst.attribute26,
v_prce_mst.attribute27 , v_prce_mst.attribute28,
v_prce_mst.attribute29 , v_prce_mst.attribute30,
v_prce_mst.attribute_category
FROM DUAL;
/* Now insert a new row in OP_PRCE_ITM */
INSERT INTO OP_PRCE_ITM
( base_price , pricelist_id ,
price_id , price_type ,
break_type , creation_date ,
last_update_date , last_updated_by ,
created_by , last_update_login,
price_class ,
delete_mark , trans_cnt ,
text_code , item_id ,
whse_code , qc_grade ,
price_um , frtbill_mthd ,
line_no ,
attribute1 , attribute2 ,
attribute3 , attribute4 ,
attribute5 , attribute6 ,
attribute7 , attribute8 ,
attribute9 , attribute10 ,
attribute11 , attribute12 ,
attribute13 , attribute14 ,
attribute15 , attribute16 ,
attribute17 , attribute18 ,
attribute19 , attribute20 ,
attribute21 , attribute22 ,
attribute23 , attribute24 ,
attribute25 , attribute26 ,
attribute27 , attribute28 ,
attribute29 , attribute30 ,
attribute_category
)
SELECT
X_base_price , X_pricelist_id ,
X_price_id , v_prce_itm.price_type ,
v_prce_itm.break_type , SYSDATE ,
SYSDATE , P_user_id ,
P_user_id , NULL ,
v_prce_itm.price_class ,
v_prce_itm.delete_mark , 0 ,
X_prce_itm_text_code , v_prce_itm.item_id ,
v_prce_itm.whse_code , v_prce_itm.qc_grade ,
v_prce_itm.price_um , v_prce_itm.frtbill_mthd,
v_prce_itm.line_no ,
v_prce_itm.attribute1 , v_prce_itm.attribute2 ,
v_prce_itm.attribute3 , v_prce_itm.attribute4 ,
v_prce_itm.attribute5 , v_prce_itm.attribute6 ,
v_prce_itm.attribute7 , v_prce_itm.attribute8 ,
v_prce_itm.attribute9 , v_prce_itm.attribute10 ,
v_prce_itm.attribute11 , v_prce_itm.attribute12 ,
v_prce_itm.attribute13 , v_prce_itm.attribute14 ,
v_prce_itm.attribute15 , v_prce_itm.attribute16 ,
v_prce_itm.attribute17 , v_prce_itm.attribute18 ,
v_prce_itm.attribute19 , v_prce_itm.attribute20 ,
v_prce_itm.attribute21 , v_prce_itm.attribute22 ,
v_prce_itm.attribute23 , v_prce_itm.attribute24 ,
v_prce_itm.attribute25 , v_prce_itm.attribute26 ,
v_prce_itm.attribute27 , v_prce_itm.attribute28 ,
v_prce_itm.attribute29 , v_prce_itm.attribute30 ,
v_prce_itm.attribute_category
FROM DUAL ;
/* Now insert corresponding break rows into OP_PRCE_BRK */
FOR v_prce_brk IN cur_prce_brk(v_prce_itm.price_id) LOOP
X_breaktype_id := create_breaktype_id;
INSERT INTO OP_PRCE_BRK
( price_id , breakpoint_factor,
breakpoint_price , creation_date ,
last_update_date , last_updated_by ,
created_by , last_update_login,
delete_mark , trans_cnt ,
breaktype_id , line_no ,
qty_breakpoint , value_breakpoint
)
SELECT
X_price_id , v_prce_brk.breakpoint_factor,
X_breakpoint_price , sysdate ,
sysdate , P_user_id ,
P_user_id , NULL ,
v_prce_brk.delete_mark , 0 ,
X_breaktype_id , v_prce_brk.line_no ,
v_prce_brk.qty_breakpoint , v_prce_brk.value_breakpoint
FROM DUAL ;
SELECT *
FROM op_cntr_hdr
WHERE contract_id = p_old_contract_id;
SELECT *
FROM op_cntr_dtl
WHERE contract_id = p_old_contract_id;
SELECT *
FROM op_cntr_brk
WHERE price_id = V_old_price_id;
INSERT INTO OP_CNTR_HDR
( contract_id , presales_ord_id ,
contract_desc1 ,
contract_desc2 ,
comments ,
contract_currency ,
exchange_rate ,
mul_div_sign ,
contract_no , order_discount ,
value_ordered , creation_date ,
created_by , last_update_date ,
last_updated_by , last_update_login,
text_code , delete_mark ,
in_use , base_currency ,
attribute1 , attribute2 ,
attribute3 , attribute4 ,
attribute5 , attribute6 ,
attribute7 , attribute8 ,
attribute9 , attribute10 ,
attribute11 , attribute12 ,
attribute13 , attribute14 ,
attribute15 , attribute16 ,
attribute17 , attribute18 ,
attribute19 , attribute20 ,
attribute21 , attribute22 ,
attribute23 , attribute24 ,
attribute25 , attribute26 ,
attribute27 , attribute28 ,
attribute29 , attribute30 ,
attribute_category
)
SELECT
X_contract_id , P_presales_ord_id ,
NVL( P_contract_desc1, v_cntr_hdr.contract_desc1) ,
NVL( P_contract_desc2, v_cntr_hdr.contract_desc2) ,
P_comments ,
NVL( P_contract_currency, v_cntr_hdr.contract_currency) ,
NVL( P_exchange_rate, v_cntr_hdr.exchange_rate) ,
NVL( P_mul_div_sign, v_cntr_hdr.mul_div_sign) ,
P_contract_no , v_cntr_hdr.order_discount,
0 , SYSDATE ,
P_user_id , sysdate ,
P_user_id , NULL ,
X_cntr_hdr_text_code , v_cntr_hdr.delete_mark ,
0 , v_cntr_hdr.base_currency ,
v_cntr_hdr.attribute1 , v_cntr_hdr.attribute2 ,
v_cntr_hdr.attribute3 , v_cntr_hdr.attribute4 ,
v_cntr_hdr.attribute5 , v_cntr_hdr.attribute6 ,
v_cntr_hdr.attribute7 , v_cntr_hdr.attribute8 ,
v_cntr_hdr.attribute9 , v_cntr_hdr.attribute10 ,
v_cntr_hdr.attribute11 , v_cntr_hdr.attribute12 ,
v_cntr_hdr.attribute13 , v_cntr_hdr.attribute14 ,
v_cntr_hdr.attribute15 , v_cntr_hdr.attribute16 ,
v_cntr_hdr.attribute17 , v_cntr_hdr.attribute18 ,
v_cntr_hdr.attribute19 , v_cntr_hdr.attribute20 ,
v_cntr_hdr.attribute21 , v_cntr_hdr.attribute22 ,
v_cntr_hdr.attribute23 , v_cntr_hdr.attribute24 ,
v_cntr_hdr.attribute25 , v_cntr_hdr.attribute26 ,
v_cntr_hdr.attribute27 , v_cntr_hdr.attribute28 ,
v_cntr_hdr.attribute29 , v_cntr_hdr.attribute30 ,
v_cntr_hdr.attribute_category
FROM DUAL ;
/* Now insert a new row in OP_CNTR_DTL */
INSERT INTO OP_CNTR_DTL
( price_id , contract_id ,
base_price , price_type ,
break_type , creation_date ,
last_update_date , last_updated_by ,
created_by , last_update_login,
price_class ,
delete_mark , trans_cnt ,
text_code , item_id ,
whse_code , qc_grade ,
price_um , frtbill_mthd ,
line_no ,
attribute1 , attribute2 ,
attribute3 , attribute4 ,
attribute5 , attribute6 ,
attribute7 , attribute8 ,
attribute9 , attribute10 ,
attribute11 , attribute12 ,
attribute13 , attribute14 ,
attribute15 , attribute16 ,
attribute17 , attribute18 ,
attribute19 , attribute20 ,
attribute21 , attribute22 ,
attribute23 , attribute24 ,
attribute25 , attribute26 ,
attribute27 , attribute28 ,
attribute29 , attribute30 ,
attribute_category
)
VALUES
( X_price_id , X_contract_id ,
X_base_price , v_cntr_dtl.price_type ,
v_cntr_dtl.break_type , SYSDATE ,
SYSDATE , P_user_id ,
P_user_id , NULL ,
v_cntr_dtl.price_class ,
v_cntr_dtl.delete_mark , 0 ,
X_cntr_dtl_text_code , v_cntr_dtl.item_id ,
v_cntr_dtl.whse_code , v_cntr_dtl.qc_grade ,
v_cntr_dtl.price_um , v_cntr_dtl.frtbill_mthd,
v_cntr_dtl.line_no ,
v_cntr_dtl.attribute1 , v_cntr_dtl.attribute2 ,
v_cntr_dtl.attribute3 , v_cntr_dtl.attribute4 ,
v_cntr_dtl.attribute5 , v_cntr_dtl.attribute6 ,
v_cntr_dtl.attribute7 , v_cntr_dtl.attribute8 ,
v_cntr_dtl.attribute9 , v_cntr_dtl.attribute10 ,
v_cntr_dtl.attribute11 , v_cntr_dtl.attribute12 ,
v_cntr_dtl.attribute13 , v_cntr_dtl.attribute14 ,
v_cntr_dtl.attribute15 , v_cntr_dtl.attribute16 ,
v_cntr_dtl.attribute17 , v_cntr_dtl.attribute18 ,
v_cntr_dtl.attribute19 , v_cntr_dtl.attribute20 ,
v_cntr_dtl.attribute21 , v_cntr_dtl.attribute22 ,
v_cntr_dtl.attribute23 , v_cntr_dtl.attribute24 ,
v_cntr_dtl.attribute25 , v_cntr_dtl.attribute26 ,
v_cntr_dtl.attribute27 , v_cntr_dtl.attribute28 ,
v_cntr_dtl.attribute29 , v_cntr_dtl.attribute30 ,
v_cntr_dtl.attribute_category
) ;
/* Now insert corresponding break rows into OP_CNTR_BRK */
FOR v_cntr_brk IN cur_cntr_brk(v_cntr_dtl.price_id) LOOP
X_breaktype_id := create_breaktype_id;
INSERT INTO OP_CNTR_BRK
( breakpoint_factor , breakpoint_price ,
creation_date , last_updated_by ,
last_update_date , created_by ,
price_id , last_update_login,
delete_mark , trans_cnt ,
breaktype_id , line_no ,
qty_breakpoint , value_breakpoint
)
VALUES
( v_cntr_brk.breakpoint_factor , X_breakpoint_price ,
SYSDATE , P_user_id ,
sysdate , P_user_id ,
X_price_id , NULL ,
v_cntr_brk.delete_mark , 0 ,
X_breaktype_id , v_cntr_brk.line_no ,
v_cntr_brk.qty_breakpoint , v_cntr_brk.value_breakpoint
) ;
SELECT *
FROM op_chrg_mst
WHERE charge_id = p_old_charge_id;
SELECT *
FROM op_chrg_brk
WHERE charge_id = V_old_charge_id;
INSERT INTO OP_CHRG_MST
( charge_id , charge_code ,
charge_desc ,
charge_type ,
currency_code ,
chgtax_class ,
base_rate , base_amount ,
creation_date , last_update_date ,
created_by , last_updated_by ,
last_update_login , trans_cnt ,
text_code , delete_mark ,
base_per_unit , charge_uom ,
breakprice_type , break_type ,
allocation_method , calculation_type ,
linecharge_ind ,
billable_ind ,
attribute1 , attribute2 ,
attribute3 , attribute4 ,
attribute5 , attribute6 ,
attribute7 , attribute8 ,
attribute9 , attribute10 ,
attribute11 , attribute12 ,
attribute13 , attribute14 ,
attribute15 , attribute16 ,
attribute17 , attribute18 ,
attribute19 , attribute20 ,
attribute21 , attribute22 ,
attribute23 , attribute24 ,
attribute25 , attribute26 ,
attribute27 , attribute28 ,
attribute29 , attribute30 ,
attribute_category
)
SELECT
X_charge_id , P_charge_code ,
NVL( P_charge_desc, v_chrg_mst.charge_desc ) ,
NVL( P_charge_type, v_chrg_mst.charge_type ) ,
NVL( P_currency_code, v_chrg_mst.currency_code) ,
NVL( P_chgtax_class, v_chrg_mst.chgtax_class) ,
X_base_rate , X_base_amount ,
SYSDATE , SYSDATE ,
P_user_id , P_user_id ,
NULL , 0 ,
X_chrg_mst_text_code , v_chrg_mst.delete_mark ,
X_base_per_unit , v_chrg_mst.charge_uom ,
v_chrg_mst.breakprice_type , v_chrg_mst.break_type ,
v_chrg_mst.allocation_method , v_chrg_mst.calculation_type ,
v_chrg_mst.linecharge_ind ,
NVL( P_billable_ind, v_chrg_mst.billable_ind ) ,
v_chrg_mst.attribute1 , v_chrg_mst.attribute2 ,
v_chrg_mst.attribute3 , v_chrg_mst.attribute4 ,
v_chrg_mst.attribute5 , v_chrg_mst.attribute6 ,
v_chrg_mst.attribute7 , v_chrg_mst.attribute8 ,
v_chrg_mst.attribute9 , v_chrg_mst.attribute10 ,
v_chrg_mst.attribute11 , v_chrg_mst.attribute12 ,
v_chrg_mst.attribute13 , v_chrg_mst.attribute14 ,
v_chrg_mst.attribute15 , v_chrg_mst.attribute16 ,
v_chrg_mst.attribute17 , v_chrg_mst.attribute18 ,
v_chrg_mst.attribute19 , v_chrg_mst.attribute20 ,
v_chrg_mst.attribute21 , v_chrg_mst.attribute22 ,
v_chrg_mst.attribute23 , v_chrg_mst.attribute24 ,
v_chrg_mst.attribute25 , v_chrg_mst.attribute26 ,
v_chrg_mst.attribute27 , v_chrg_mst.attribute28 ,
v_chrg_mst.attribute29 , v_chrg_mst.attribute30 ,
v_chrg_mst.attribute_category
FROM DUAL ;
/* Now insert corresponding break rows into OP_CHRG_BRK */
FOR v_chrg_brk IN cur_chrg_brk(P_old_charge_id ) LOOP
X_chrgbreak_id := create_chargebreak_id;
INSERT INTO OP_CHRG_BRK
( chrgbreak_id , charge_id ,
qty_breakpoint , value_breakpoint ,
breakpoint_factor , breakpoint_price ,
created_by , last_update_date ,
creation_date , last_updated_by ,
last_update_login , delete_mark ,
trans_cnt , line_no
)
SELECT
X_chrgbreak_id , X_charge_id ,
v_chrg_brk.qty_breakpoint , v_chrg_brk.value_breakpoint ,
v_chrg_brk.breakpoint_factor , X_breakpoint_price ,
P_user_id , SYSDATE ,
sysdate , P_user_id ,
NULL , v_chrg_brk.delete_mark ,
0 , v_chrg_brk.line_no
FROM DUAL ;
SELECT *
FROM op_prce_eff
WHERE priceff_id = p_old_priceff_id ;
SELECT linecharge_ind
FROM op_chrg_mst
WHERE charge_id = p_list_id ;
SELECT COUNT(1)
FROM op_prce_eff
WHERE ( ( orgn_code = v_prce_eff.orgn_code)
OR ( orgn_code IS NULL AND v_prce_eff.orgn_code IS NULL ) )
AND ( ( custprice_class = v_prce_eff.custprice_class )
OR ( custprice_class IS NULL AND v_prce_eff.custprice_class IS NULL ) )
AND ( ( cust_id = v_prce_eff.cust_id )
OR ( cust_id IS NULL AND v_prce_eff.cust_id IS NULL ) )
AND effectivity_type = v_prce_eff.effectivity_type
AND preference = p_new_preference
AND ( p_new_start_date BETWEEN start_date AND end_date
OR p_new_end_date BETWEEN start_date AND end_date
OR start_date BETWEEN p_new_start_date AND p_new_end_date
OR end_date BETWEEN p_new_start_date AND p_new_end_date
);
INSERT INTO OP_PRCE_EFF(PRICEFF_ID ,
ORGN_CODE ,
PROMOTION_ID ,
CUST_ID ,
INACTIVE_IND ,
LISTPRICE_IND ,
CUSTPRICE_CLASS ,
DELETE_MARK ,
TEXT_CODE ,
TRANS_CNT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TERRITORY ,
START_DATE ,
END_DATE ,
PREFERENCE ,
EFFECTIVITY_TYPE ,
PRICELIST_ID ,
CONTRACT_ID ,
CHARGE_ID
)
SELECT X_priceff_id ,
v_prce_eff.orgn_code ,
NULL ,
v_prce_eff.cust_id ,
v_prce_eff.inactive_ind ,
v_prce_eff.listprice_ind ,
v_prce_eff.custprice_class ,
v_prce_eff.delete_mark ,
X_prce_eff_text_code ,
0 ,
SYSDATE ,
P_user_id ,
SYSDATE ,
P_user_id ,
NULL ,
v_prce_eff.territory ,
p_new_start_date ,
p_new_end_date ,
p_new_preference ,
v_prce_eff.effectivity_type ,
DECODE (v_prce_eff.effectivity_type, 0, p_list_id,
NULL ) ,
DECODE (v_prce_eff.effectivity_type, 1, p_list_id,
NULL ) ,
DECODE (v_prce_eff.effectivity_type, 3, p_list_id,
NULL )
FROM DUAL;
SELECT *
FROM op_chrg_itm
WHERE chargeitem_id = p_old_chargeitem_id;
SELECT linecharge_ind
FROM op_chrg_mst
WHERE charge_id = p_charge_id ;
INSERT INTO OP_CHRG_ITM
( chargeitem_id , charge_id ,
created_by , last_updated_by ,
creation_date , last_update_date ,
last_update_login , delete_mark ,
trans_cnt , text_code ,
cust_id , item_id ,
icprice_class ,
attribute1 , attribute2 ,
attribute3 , attribute4 ,
attribute5 , attribute6 ,
attribute7 , attribute8 ,
attribute9 , attribute10 ,
attribute11 , attribute12 ,
attribute13 , attribute14 ,
attribute15 , attribute16 ,
attribute17 , attribute18 ,
attribute19 , attribute20 ,
attribute21 , attribute22 ,
attribute23 , attribute24 ,
attribute25 , attribute26 ,
attribute27 , attribute28 ,
attribute29 , attribute30 ,
attribute_category
)
SELECT
X_chargeitem_id , P_charge_id ,
P_user_id , P_user_id ,
SYSDATE , SYSDATE ,
NULL , v_chrg_itm.delete_mark,
0 , X_chrg_itm_text_code ,
v_chrg_itm.cust_id , v_chrg_itm.item_id ,
v_chrg_itm.icprice_class ,
v_chrg_itm.attribute1 , v_chrg_itm.attribute2 ,
v_chrg_itm.attribute3 , v_chrg_itm.attribute4 ,
v_chrg_itm.attribute5 , v_chrg_itm.attribute6 ,
v_chrg_itm.attribute7 , v_chrg_itm.attribute8 ,
v_chrg_itm.attribute9 , v_chrg_itm.attribute10 ,
v_chrg_itm.attribute11 , v_chrg_itm.attribute12 ,
v_chrg_itm.attribute13 , v_chrg_itm.attribute14 ,
v_chrg_itm.attribute15 , v_chrg_itm.attribute16 ,
v_chrg_itm.attribute17 , v_chrg_itm.attribute18 ,
v_chrg_itm.attribute19 , v_chrg_itm.attribute20 ,
v_chrg_itm.attribute21 , v_chrg_itm.attribute22 ,
v_chrg_itm.attribute23 , v_chrg_itm.attribute24 ,
v_chrg_itm.attribute25 , v_chrg_itm.attribute26 ,
v_chrg_itm.attribute27 , v_chrg_itm.attribute28 ,
v_chrg_itm.attribute29 , v_chrg_itm.attribute30 ,
v_chrg_itm.attribute_category
FROM DUAL ;
SELECT *
FROM op_text_tbl_tl
WHERE text_code = p_old_text_code;
INSERT INTO OP_TEXT_HDR
( text_code , creation_date ,
created_by , last_update_date ,
last_updated_by , last_update_login
)
SELECT
P_new_text_code , SYSDATE ,
P_user_id , sysdate ,
P_user_id , NULL
FROM DUAL ;
/* Now insert a new row in OP_TEXT_TBL_TL */
INSERT INTO OP_TEXT_TBL_TL
( text_code ,
lang_code ,
paragraph_code ,
sub_paracode ,
line_no ,
text ,
language ,
source_lang ,
last_updated_by ,
created_by ,
last_update_date ,
creation_date ,
last_update_login
)
SELECT p_new_text_code ,
v_text_tbl_tl.lang_code ,
v_text_tbl_tl.paragraph_code,
v_text_tbl_tl.sub_paracode ,
v_text_tbl_tl.line_no ,
v_text_tbl_tl.text ,
v_text_tbl_tl.language ,
v_text_tbl_tl.source_lang ,
P_user_id ,
P_user_id ,
SYSDATE ,
SYSDATE ,
NULL
FROM DUAL ;
SELECT GMO_PRICELIST_ID_S.NEXTVAL
FROM SYS.DUAL;
SELECT gmo_price_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT gmo_breaktype_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT gmo_priceff_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT GMO_CHARGE_ID_S.NEXTVAL
FROM SYS.DUAL;
SELECT gem5_chargeitem_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT gmo_chrgbreak_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT GMO_CHARGE_ID_S.NEXTVAL
FROM SYS.DUAL;
SELECT GEM5_TEXT_CODE_S.NEXTVAL
FROM SYS.DUAL;