The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_ORDER_TYPE in NUMBER,
X_LANG_CODE in VARCHAR2,
X_INVEN_TRANS_TYPE in NUMBER,
X_SHIPMENT_IND in NUMBER,
X_INVOICE_IND in NUMBER,
X_PICKINGLST_IND in NUMBER,
X_EXPORTDOC_IND in NUMBER,
X_TOTALORD_IND in NUMBER,
X_UPDATE_SALESHIST in NUMBER,
X_CREDITCHK_TYPE in NUMBER,
X_PRICE_ORDER in NUMBER,
X_CALC_COMM_IND in NUMBER,
X_CALC_TAX_IND in NUMBER,
X_PRODORDR_IND in NUMBER,
X_SHIPTO_REQUIRED in NUMBER,
X_UPDATE_AUDITTRAIL in NUMBER,
X_ORDER_LEADTIME in NUMBER,
X_TRANS_CNT in NUMBER,
X_TEXT_CODE in NUMBER,
X_DELETE_MARK in NUMBER,
X_ORDER_TYPE_CODE in VARCHAR2,
X_ORDER_TYPE_DESC in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from OP_ORDR_TYP_B
where ORDER_TYPE = X_ORDER_TYPE
;
insert into OP_ORDR_TYP_B (
ORDER_TYPE,
ORDER_TYPE_CODE,
LANG_CODE,
INVEN_TRANS_TYPE,
SHIPMENT_IND,
INVOICE_IND,
PICKINGLST_IND,
EXPORTDOC_IND,
TOTALORD_IND,
UPDATE_SALESHIST,
CREDITCHK_TYPE,
PRICE_ORDER,
CALC_COMM_IND,
CALC_TAX_IND,
PRODORDR_IND,
SHIPTO_REQUIRED,
UPDATE_AUDITTRAIL,
ORDER_LEADTIME,
TRANS_CNT,
TEXT_CODE,
DELETE_MARK,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_ORDER_TYPE,
X_ORDER_TYPE_CODE,
X_LANG_CODE,
X_INVEN_TRANS_TYPE,
X_SHIPMENT_IND,
X_INVOICE_IND,
X_PICKINGLST_IND,
X_EXPORTDOC_IND,
X_TOTALORD_IND,
X_UPDATE_SALESHIST,
X_CREDITCHK_TYPE,
X_PRICE_ORDER,
X_CALC_COMM_IND,
X_CALC_TAX_IND,
X_PRODORDR_IND,
X_SHIPTO_REQUIRED,
X_UPDATE_AUDITTRAIL,
X_ORDER_LEADTIME,
X_TRANS_CNT,
X_TEXT_CODE,
X_DELETE_MARK,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into OP_ORDR_TYP_TL (
ORDER_TYPE,
ORDER_TYPE_CODE,
ORDER_TYPE_DESC,
CREATED_BY,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
X_ORDER_TYPE,
X_ORDER_TYPE_CODE,
X_ORDER_TYPE_DESC,
X_CREATED_BY,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from OP_ORDR_TYP_TL T
where T.ORDER_TYPE = X_ORDER_TYPE
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
X_UPDATE_SALESHIST in NUMBER,
X_CREDITCHK_TYPE in NUMBER,
X_PRICE_ORDER in NUMBER,
X_CALC_COMM_IND in NUMBER,
X_CALC_TAX_IND in NUMBER,
X_PRODORDR_IND in NUMBER,
X_SHIPTO_REQUIRED in NUMBER,
X_UPDATE_AUDITTRAIL in NUMBER,
X_ORDER_LEADTIME in NUMBER,
X_TRANS_CNT in NUMBER,
X_TEXT_CODE in NUMBER,
X_DELETE_MARK in NUMBER,
X_ORDER_TYPE_CODE in VARCHAR2,
X_ORDER_TYPE_DESC in VARCHAR2
) is
cursor c is select
LANG_CODE,
INVEN_TRANS_TYPE,
SHIPMENT_IND,
INVOICE_IND,
PICKINGLST_IND,
EXPORTDOC_IND,
TOTALORD_IND,
UPDATE_SALESHIST,
CREDITCHK_TYPE,
PRICE_ORDER,
CALC_COMM_IND,
CALC_TAX_IND,
PRODORDR_IND,
SHIPTO_REQUIRED,
UPDATE_AUDITTRAIL,
ORDER_LEADTIME,
TRANS_CNT,
TEXT_CODE,
DELETE_MARK
from OP_ORDR_TYP_B
where ORDER_TYPE = X_ORDER_TYPE
for update of ORDER_TYPE nowait;
cursor c1 is select
ORDER_TYPE_CODE,
ORDER_TYPE_DESC,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from OP_ORDR_TYP_TL
where ORDER_TYPE = X_ORDER_TYPE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of ORDER_TYPE nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
AND (recinfo.UPDATE_SALESHIST = X_UPDATE_SALESHIST)
AND (recinfo.CREDITCHK_TYPE = X_CREDITCHK_TYPE)
AND (recinfo.PRICE_ORDER = X_PRICE_ORDER)
AND (recinfo.CALC_COMM_IND = X_CALC_COMM_IND)
AND (recinfo.CALC_TAX_IND = X_CALC_TAX_IND)
AND (recinfo.PRODORDR_IND = X_PRODORDR_IND)
AND (recinfo.SHIPTO_REQUIRED = X_SHIPTO_REQUIRED)
AND (recinfo.UPDATE_AUDITTRAIL = X_UPDATE_AUDITTRAIL)
AND (recinfo.ORDER_LEADTIME = X_ORDER_LEADTIME)
AND (recinfo.TRANS_CNT = X_TRANS_CNT)
AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
AND (recinfo.DELETE_MARK = X_DELETE_MARK)
) then
null;
procedure UPDATE_ROW (
X_ORDER_TYPE in NUMBER,
X_LANG_CODE in VARCHAR2,
X_INVEN_TRANS_TYPE in NUMBER,
X_SHIPMENT_IND in NUMBER,
X_INVOICE_IND in NUMBER,
X_PICKINGLST_IND in NUMBER,
X_EXPORTDOC_IND in NUMBER,
X_TOTALORD_IND in NUMBER,
X_UPDATE_SALESHIST in NUMBER,
X_CREDITCHK_TYPE in NUMBER,
X_PRICE_ORDER in NUMBER,
X_CALC_COMM_IND in NUMBER,
X_CALC_TAX_IND in NUMBER,
X_PRODORDR_IND in NUMBER,
X_SHIPTO_REQUIRED in NUMBER,
X_UPDATE_AUDITTRAIL in NUMBER,
X_ORDER_LEADTIME in NUMBER,
X_TRANS_CNT in NUMBER,
X_TEXT_CODE in NUMBER,
X_DELETE_MARK in NUMBER,
X_ORDER_TYPE_CODE in VARCHAR2,
X_ORDER_TYPE_DESC in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update OP_ORDR_TYP_B set
LANG_CODE = X_LANG_CODE,
INVEN_TRANS_TYPE = X_INVEN_TRANS_TYPE,
SHIPMENT_IND = X_SHIPMENT_IND,
INVOICE_IND = X_INVOICE_IND,
PICKINGLST_IND = X_PICKINGLST_IND,
EXPORTDOC_IND = X_EXPORTDOC_IND,
TOTALORD_IND = X_TOTALORD_IND,
UPDATE_SALESHIST = X_UPDATE_SALESHIST,
CREDITCHK_TYPE = X_CREDITCHK_TYPE,
PRICE_ORDER = X_PRICE_ORDER,
CALC_COMM_IND = X_CALC_COMM_IND,
CALC_TAX_IND = X_CALC_TAX_IND,
PRODORDR_IND = X_PRODORDR_IND,
SHIPTO_REQUIRED = X_SHIPTO_REQUIRED,
UPDATE_AUDITTRAIL = X_UPDATE_AUDITTRAIL,
ORDER_LEADTIME = X_ORDER_LEADTIME,
TRANS_CNT = X_TRANS_CNT,
TEXT_CODE = X_TEXT_CODE,
DELETE_MARK = X_DELETE_MARK,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where ORDER_TYPE = X_ORDER_TYPE;
update OP_ORDR_TYP_TL set
ORDER_TYPE_CODE = X_ORDER_TYPE_CODE,
ORDER_TYPE_DESC = X_ORDER_TYPE_DESC,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where ORDER_TYPE = X_ORDER_TYPE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure DELETE_ROW (
X_ORDER_TYPE in NUMBER
) is
begin
delete from OP_ORDR_TYP_TL
where ORDER_TYPE = X_ORDER_TYPE;
delete from OP_ORDR_TYP_B
where ORDER_TYPE = X_ORDER_TYPE;
end DELETE_ROW;
delete from OP_ORDR_TYP_TL T
where not exists
(select NULL
from OP_ORDR_TYP_B B
where B.ORDER_TYPE = T.ORDER_TYPE
);
update OP_ORDR_TYP_TL T set (
ORDER_TYPE_CODE,
ORDER_TYPE_DESC
) = (select
B.ORDER_TYPE_CODE,
B.ORDER_TYPE_DESC
from OP_ORDR_TYP_TL B
where B.ORDER_TYPE = T.ORDER_TYPE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ORDER_TYPE,
T.LANGUAGE
) in (select
SUBT.ORDER_TYPE,
SUBT.LANGUAGE
from OP_ORDR_TYP_TL SUBB, OP_ORDR_TYP_TL SUBT
where SUBB.ORDER_TYPE = SUBT.ORDER_TYPE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.ORDER_TYPE_CODE <> SUBT.ORDER_TYPE_CODE
or SUBB.ORDER_TYPE_DESC <> SUBT.ORDER_TYPE_DESC
));
insert into OP_ORDR_TYP_TL (
ORDER_TYPE,
ORDER_TYPE_CODE,
ORDER_TYPE_DESC,
CREATED_BY,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.ORDER_TYPE,
B.ORDER_TYPE_CODE,
B.ORDER_TYPE_DESC,
B.CREATED_BY,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from OP_ORDR_TYP_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from OP_ORDR_TYP_TL T
where T.ORDER_TYPE = B.ORDER_TYPE
and T.LANGUAGE = L.LANGUAGE_CODE);
update OP_ORDR_TYP_TL set
ORDER_TYPE_CODE = X_ORDER_TYPE_CODE,
ORDER_TYPE_DESC = X_ORDER_TYPE_DESC,
SOURCE_LANG = userenv('LANG'),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = 0,
LAST_UPDATE_LOGIN = 0
where
ORDER_TYPE = X_ORDER_TYPE
and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
X_UPDATE_SALESHIST in NUMBER,
X_CREDITCHK_TYPE in NUMBER,
X_PRICE_ORDER in NUMBER,
X_CALC_COMM_IND in NUMBER,
X_CALC_TAX_IND in NUMBER,
X_PRODORDR_IND in NUMBER,
X_SHIPTO_REQUIRED in NUMBER,
X_UPDATE_AUDITTRAIL in NUMBER,
X_ORDER_LEADTIME in NUMBER,
X_TRANS_CNT in NUMBER,
X_TEXT_CODE in NUMBER,
X_DELETE_MARK in NUMBER,
X_ORDER_TYPE_DESC in VARCHAR2
) IS
l_user_id number :=0;
GML_OP_ORDR_TYP_PKG.UPDATE_ROW (
X_ORDER_TYPE => X_ORDER_TYPE ,
X_LANG_CODE => X_LANG_CODE,
X_INVEN_TRANS_TYPE => X_INVEN_TRANS_TYPE,
X_SHIPMENT_IND => X_SHIPMENT_IND,
X_INVOICE_IND => X_INVOICE_IND,
X_PICKINGLST_IND => X_PICKINGLST_IND,
X_EXPORTDOC_IND => X_EXPORTDOC_IND,
X_TOTALORD_IND => X_TOTALORD_IND ,
X_UPDATE_SALESHIST => X_UPDATE_SALESHIST,
X_CREDITCHK_TYPE => X_CREDITCHK_TYPE,
X_PRICE_ORDER => X_CREDITCHK_TYPE ,
X_CALC_COMM_IND => X_CALC_COMM_IND,
X_CALC_TAX_IND => X_CALC_TAX_IND ,
X_PRODORDR_IND => X_PRODORDR_IND ,
X_SHIPTO_REQUIRED => X_SHIPTO_REQUIRED ,
X_UPDATE_AUDITTRAIL => X_UPDATE_AUDITTRAIL,
X_ORDER_LEADTIME => X_UPDATE_AUDITTRAIL ,
X_TRANS_CNT => X_TRANS_CNT ,
X_TEXT_CODE => X_TEXT_CODE ,
X_DELETE_MARK => X_DELETE_MARK,
X_ORDER_TYPE_CODE => X_ORDER_TYPE_CODE,
X_ORDER_TYPE_DESC => X_ORDER_TYPE_DESC,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0
);
GML_OP_ORDR_TYP_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_ORDER_TYPE => X_ORDER_TYPE ,
X_LANG_CODE => X_LANG_CODE,
X_INVEN_TRANS_TYPE => X_INVEN_TRANS_TYPE,
X_SHIPMENT_IND => X_SHIPMENT_IND,
X_INVOICE_IND => X_INVOICE_IND,
X_PICKINGLST_IND => X_PICKINGLST_IND,
X_EXPORTDOC_IND => X_EXPORTDOC_IND,
X_TOTALORD_IND => X_TOTALORD_IND ,
X_UPDATE_SALESHIST => X_UPDATE_SALESHIST,
X_CREDITCHK_TYPE => X_CREDITCHK_TYPE,
X_PRICE_ORDER => X_CREDITCHK_TYPE ,
X_CALC_COMM_IND => X_CALC_COMM_IND,
X_CALC_TAX_IND => X_CALC_TAX_IND ,
X_PRODORDR_IND => X_PRODORDR_IND ,
X_SHIPTO_REQUIRED => X_SHIPTO_REQUIRED ,
X_UPDATE_AUDITTRAIL => X_UPDATE_AUDITTRAIL,
X_ORDER_LEADTIME => X_UPDATE_AUDITTRAIL ,
X_TRANS_CNT => X_TRANS_CNT ,
X_TEXT_CODE => X_TEXT_CODE ,
X_DELETE_MARK => X_DELETE_MARK,
X_ORDER_TYPE_CODE => X_ORDER_TYPE_CODE,
X_ORDER_TYPE_DESC => X_ORDER_TYPE_DESC,
X_CREATION_DATE => sysdate,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0
);