[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
s_dim_select CONSTANT varchar2(1000) := 'select SR_ORDER_LINE_PK, SR_ADJ_ID_PK,
BOOKED_DATE, SR_CUSTOMER_PK, SR_SHIP_TO_LOC_PK, SR_ITEM_PK, SR_SALES_REP_PK,
SR_SALES_CHANNEL_PK, OU_ID, SR_USER_DEFINED1_PK, SR_USER_DEFINED2_PK,
SR_USER_DEFINED3_PK, SR_USER_DEFINED4_PK, SR_USER_DEFINED5_PK, ';
function get_select_meas_sql(p_src_tbl_name varchar2, p_meas_type varchar2)
return varchar2 is
b_first boolean := true;
s_sql := s_dim_select || s_meas_sql || ' from ' || p_src_tbl_name;
t_src_trg_cols.delete;
log_debug('ERROR FORMING MEASURE SELECT...');
end get_select_meas_sql;
log_debug('ERROR ASSIGNING VALUES TO INSERT...');
log_debug('ERROR ASSIGNING VALUES TO UPDATE...');
procedure delete_ins_rec_data is
begin
r_insrecs.ORD_LEVEL_VALUE.delete;
r_insrecs.ADJ_LEVEL_VALUE.delete;
r_insrecs.TIME_LEVEL_VALUE.delete;
r_insrecs.CUS_LEVEL_VALUE.delete;
r_insrecs.GEO_LEVEL_VALUE.delete;
r_insrecs.PRD_LEVEL_VALUE.delete;
r_insrecs.REP_LEVEL_VALUE.delete;
r_insrecs.CHN_LEVEL_VALUE.delete;
r_insrecs.ORG_LEVEL_VALUE.delete;
r_insrecs.USR1_LEVEL_VALUE.delete;
r_insrecs.USR2_LEVEL_VALUE.delete;
r_insrecs.USR3_LEVEL_VALUE.delete;
r_insrecs.USR4_LEVEL_VALUE.delete;
r_insrecs.USR5_LEVEL_VALUE.delete;
r_insrecs.MEASURE1_NUMBER.delete;
r_insrecs.MEASURE2_NUMBER.delete;
r_insrecs.MEASURE3_NUMBER.delete;
r_insrecs.MEASURE4_NUMBER.delete;
r_insrecs.MEASURE5_NUMBER.delete;
r_insrecs.MEASURE6_NUMBER.delete;
r_insrecs.MEASURE7_NUMBER.delete;
r_insrecs.MEASURE8_NUMBER.delete;
r_insrecs.MEASURE9_NUMBER.delete;
r_insrecs.MEASURE10_NUMBER.delete;
r_insrecs.MEASURE11_NUMBER.delete;
r_insrecs.MEASURE12_NUMBER.delete;
r_insrecs.MEASURE13_NUMBER.delete;
r_insrecs.MEASURE14_NUMBER.delete;
r_insrecs.MEASURE15_NUMBER.delete;
r_insrecs.MEASURE16_NUMBER.delete;
r_insrecs.MEASURE17_NUMBER.delete;
r_insrecs.MEASURE18_NUMBER.delete;
r_insrecs.MEASURE19_NUMBER.delete;
r_insrecs.MEASURE20_NUMBER.delete;
r_insrecs.MEASURE21_NUMBER.delete;
r_insrecs.MEASURE22_NUMBER.delete;
r_insrecs.MEASURE23_NUMBER.delete;
r_insrecs.MEASURE24_NUMBER.delete;
r_insrecs.MEASURE25_NUMBER.delete;
r_insrecs.MEASURE26_NUMBER.delete;
r_insrecs.MEASURE27_NUMBER.delete;
r_insrecs.MEASURE28_NUMBER.delete;
r_insrecs.MEASURE29_NUMBER.delete;
r_insrecs.MEASURE30_NUMBER.delete;
r_insrecs.MEASURE1_CHAR.delete;
r_insrecs.MEASURE2_CHAR.delete;
r_insrecs.MEASURE3_CHAR.delete;
r_insrecs.MEASURE4_CHAR.delete;
r_insrecs.MEASURE5_CHAR.delete;
r_insrecs.MEASURE6_CHAR.delete;
r_insrecs.MEASURE7_CHAR.delete;
r_insrecs.MEASURE8_CHAR.delete;
r_insrecs.MEASURE9_CHAR.delete;
r_insrecs.MEASURE10_CHAR.delete;
r_insrecs.MEASURE_UOM.delete;
log_debug('ERROR CLEARING INSERT REC BUFFER...');
end delete_ins_rec_data;
procedure delete_upd_rec_data is
begin
r_updrecs.MEASURE_VALUE_ID.delete;
r_updrecs.MEASURE1_NUMBER.delete;
r_updrecs.MEASURE2_NUMBER.delete;
r_updrecs.MEASURE3_NUMBER.delete;
r_updrecs.MEASURE4_NUMBER.delete;
r_updrecs.MEASURE5_NUMBER.delete;
r_updrecs.MEASURE6_NUMBER.delete;
r_updrecs.MEASURE7_NUMBER.delete;
r_updrecs.MEASURE8_NUMBER.delete;
r_updrecs.MEASURE9_NUMBER.delete;
r_updrecs.MEASURE10_NUMBER.delete;
r_updrecs.MEASURE11_NUMBER.delete;
r_updrecs.MEASURE12_NUMBER.delete;
r_updrecs.MEASURE13_NUMBER.delete;
r_updrecs.MEASURE14_NUMBER.delete;
r_updrecs.MEASURE15_NUMBER.delete;
r_updrecs.MEASURE16_NUMBER.delete;
r_updrecs.MEASURE17_NUMBER.delete;
r_updrecs.MEASURE18_NUMBER.delete;
r_updrecs.MEASURE19_NUMBER.delete;
r_updrecs.MEASURE20_NUMBER.delete;
r_updrecs.MEASURE21_NUMBER.delete;
r_updrecs.MEASURE22_NUMBER.delete;
r_updrecs.MEASURE23_NUMBER.delete;
r_updrecs.MEASURE24_NUMBER.delete;
r_updrecs.MEASURE25_NUMBER.delete;
r_updrecs.MEASURE26_NUMBER.delete;
r_updrecs.MEASURE27_NUMBER.delete;
r_updrecs.MEASURE28_NUMBER.delete;
r_updrecs.MEASURE29_NUMBER.delete;
r_updrecs.MEASURE30_NUMBER.delete;
r_updrecs.MEASURE1_CHAR.delete;
r_updrecs.MEASURE2_CHAR.delete;
r_updrecs.MEASURE3_CHAR.delete;
r_updrecs.MEASURE4_CHAR.delete;
r_updrecs.MEASURE5_CHAR.delete;
r_updrecs.MEASURE6_CHAR.delete;
r_updrecs.MEASURE7_CHAR.delete;
r_updrecs.MEASURE8_CHAR.delete;
r_updrecs.MEASURE9_CHAR.delete;
r_updrecs.MEASURE10_CHAR.delete;
r_updrecs.MEASURE_UOM.delete;
log_debug('ERROR CLEARING UPDATE REC BUFFER...');
end delete_upd_rec_data;
procedure insert_measdata is
begin
forall i in r_insrecs.ORD_LEVEL_VALUE.FIRST..r_insrecs.ORD_LEVEL_VALUE.LAST
insert into QPR_MEASURE_DATA(MEASURE_VALUE_ID,
INSTANCE_ID,
MEASURE_TYPE_CODE,
ORD_LEVEL_VALUE,
ADJ_LEVEL_VALUE,
TIME_LEVEL_VALUE,
CUS_LEVEL_VALUE,
GEO_LEVEL_VALUE,
PRD_LEVEL_VALUE,
REP_LEVEL_VALUE,
CHN_LEVEL_VALUE,
ORG_LEVEL_VALUE,
USR1_LEVEL_VALUE,
USR2_LEVEL_VALUE,
USR3_LEVEL_VALUE,
USR4_LEVEL_VALUE,
USR5_LEVEL_VALUE,
MEASURE1_NUMBER ,
MEASURE2_NUMBER ,
MEASURE3_NUMBER ,
MEASURE4_NUMBER ,
MEASURE5_NUMBER ,
MEASURE6_NUMBER ,
MEASURE7_NUMBER ,
MEASURE8_NUMBER ,
MEASURE9_NUMBER ,
MEASURE10_NUMBER ,
MEASURE11_NUMBER ,
MEASURE12_NUMBER ,
MEASURE13_NUMBER ,
MEASURE14_NUMBER ,
MEASURE15_NUMBER ,
MEASURE16_NUMBER ,
MEASURE17_NUMBER ,
MEASURE18_NUMBER ,
MEASURE19_NUMBER ,
MEASURE20_NUMBER ,
MEASURE21_NUMBER ,
MEASURE22_NUMBER ,
MEASURE23_NUMBER ,
MEASURE24_NUMBER ,
MEASURE25_NUMBER ,
MEASURE26_NUMBER ,
MEASURE27_NUMBER ,
MEASURE28_NUMBER ,
MEASURE29_NUMBER ,
MEASURE30_NUMBER ,
MEASURE1_CHAR ,
MEASURE2_CHAR ,
MEASURE3_CHAR ,
MEASURE4_CHAR ,
MEASURE5_CHAR ,
MEASURE6_CHAR ,
MEASURE7_CHAR ,
MEASURE8_CHAR ,
MEASURE9_CHAR ,
MEASURE10_CHAR ,
MEASURE_UOM,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
REQUEST_ID)
values(QPR_MEASURE_DATA_S.nextval,
g_instance_id,
g_meas_type,
r_insrecs.ORD_LEVEL_VALUE(i),
r_insrecs.ADJ_LEVEL_VALUE(i),
r_insrecs.TIME_LEVEL_VALUE(i),
r_insrecs.CUS_LEVEL_VALUE(i),
r_insrecs.GEO_LEVEL_VALUE(i),
r_insrecs.PRD_LEVEL_VALUE(i),
r_insrecs.REP_LEVEL_VALUE(i),
r_insrecs.CHN_LEVEL_VALUE(i),
r_insrecs.ORG_LEVEL_VALUE(i),
r_insrecs.USR1_LEVEL_VALUE(i),
r_insrecs.USR2_LEVEL_VALUE(i),
r_insrecs.USR3_LEVEL_VALUE(i),
r_insrecs.USR4_LEVEL_VALUE(i),
r_insrecs.USR5_LEVEL_VALUE(i),
r_insrecs.MEASURE1_NUMBER(i),
r_insrecs.MEASURE2_NUMBER(i),
r_insrecs.MEASURE3_NUMBER(i),
r_insrecs.MEASURE4_NUMBER(i),
r_insrecs.MEASURE5_NUMBER(i),
r_insrecs.MEASURE6_NUMBER(i),
r_insrecs.MEASURE7_NUMBER(i),
r_insrecs.MEASURE8_NUMBER(i),
r_insrecs.MEASURE9_NUMBER(i),
r_insrecs.MEASURE10_NUMBER(i),
r_insrecs.MEASURE11_NUMBER(i),
r_insrecs.MEASURE12_NUMBER(i),
r_insrecs.MEASURE13_NUMBER(i),
r_insrecs.MEASURE14_NUMBER(i),
r_insrecs.MEASURE15_NUMBER(i),
r_insrecs.MEASURE16_NUMBER(i),
r_insrecs.MEASURE17_NUMBER(i),
r_insrecs.MEASURE18_NUMBER(i),
r_insrecs.MEASURE19_NUMBER(i),
r_insrecs.MEASURE20_NUMBER(i),
r_insrecs.MEASURE21_NUMBER(i),
r_insrecs.MEASURE22_NUMBER(i),
r_insrecs.MEASURE23_NUMBER(i),
r_insrecs.MEASURE24_NUMBER(i),
r_insrecs.MEASURE25_NUMBER(i),
r_insrecs.MEASURE26_NUMBER(i),
r_insrecs.MEASURE27_NUMBER(i),
r_insrecs.MEASURE28_NUMBER(i),
r_insrecs.MEASURE29_NUMBER(i),
r_insrecs.MEASURE30_NUMBER(i),
r_insrecs.MEASURE1_CHAR(i),
r_insrecs.MEASURE2_CHAR(i),
r_insrecs.MEASURE3_CHAR(i),
r_insrecs.MEASURE4_CHAR(i),
r_insrecs.MEASURE5_CHAR(i),
r_insrecs.MEASURE6_CHAR(i),
r_insrecs.MEASURE7_CHAR(i),
r_insrecs.MEASURE8_CHAR(i),
r_insrecs.MEASURE9_CHAR(i),
r_insrecs.MEASURE10_CHAR(i),
r_insrecs.MEASURE_UOM(i),
g_sys_date,
g_user_id,
g_sys_date,
g_user_id,
g_login_id,
g_prg_appl_id,
g_prg_id,
g_request_id);
log_debug('ERROR INSERTING FACT DATA...');
end insert_measdata;
procedure update_measdata is
begin
forall i in r_updrecs.MEASURE_VALUE_ID.FIRST..r_updrecs.MEASURE_VALUE_ID.LAST
update QPR_MEASURE_DATA set
MEASURE1_NUMBER = r_updrecs.MEASURE1_NUMBER(i),
MEASURE2_NUMBER = r_updrecs.MEASURE2_NUMBER(i),
MEASURE3_NUMBER = r_updrecs.MEASURE3_NUMBER(i),
MEASURE4_NUMBER = r_updrecs.MEASURE4_NUMBER(i),
MEASURE5_NUMBER = r_updrecs.MEASURE5_NUMBER(i),
MEASURE6_NUMBER = r_updrecs.MEASURE6_NUMBER(i),
MEASURE7_NUMBER = r_updrecs.MEASURE7_NUMBER(i),
MEASURE8_NUMBER = r_updrecs.MEASURE8_NUMBER(i),
MEASURE9_NUMBER = r_updrecs.MEASURE9_NUMBER(i),
MEASURE10_NUMBER = r_updrecs.MEASURE10_NUMBER(i),
MEASURE11_NUMBER = r_updrecs.MEASURE11_NUMBER(i),
MEASURE12_NUMBER = r_updrecs.MEASURE12_NUMBER(i),
MEASURE13_NUMBER = r_updrecs.MEASURE13_NUMBER(i),
MEASURE14_NUMBER = r_updrecs.MEASURE14_NUMBER(i),
MEASURE15_NUMBER = r_updrecs.MEASURE15_NUMBER(i),
MEASURE16_NUMBER = r_updrecs.MEASURE16_NUMBER(i),
MEASURE17_NUMBER = r_updrecs.MEASURE17_NUMBER(i),
MEASURE18_NUMBER = r_updrecs.MEASURE18_NUMBER(i),
MEASURE19_NUMBER = r_updrecs.MEASURE19_NUMBER(i),
MEASURE20_NUMBER = r_updrecs.MEASURE20_NUMBER(i),
MEASURE21_NUMBER = r_updrecs.MEASURE21_NUMBER(i),
MEASURE22_NUMBER = r_updrecs.MEASURE22_NUMBER(i),
MEASURE23_NUMBER = r_updrecs.MEASURE23_NUMBER(i),
MEASURE24_NUMBER = r_updrecs.MEASURE24_NUMBER(i),
MEASURE25_NUMBER = r_updrecs.MEASURE25_NUMBER(i),
MEASURE26_NUMBER = r_updrecs.MEASURE26_NUMBER(i),
MEASURE27_NUMBER = r_updrecs.MEASURE27_NUMBER(i),
MEASURE28_NUMBER = r_updrecs.MEASURE28_NUMBER(i),
MEASURE29_NUMBER = r_updrecs.MEASURE29_NUMBER(i),
MEASURE30_NUMBER = r_updrecs.MEASURE30_NUMBER(i),
MEASURE1_CHAR = r_updrecs.MEASURE1_CHAR(i),
MEASURE2_CHAR = r_updrecs.MEASURE2_CHAR(i),
MEASURE3_CHAR = r_updrecs.MEASURE3_CHAR(i),
MEASURE4_CHAR = r_updrecs.MEASURE4_CHAR(i),
MEASURE5_CHAR = r_updrecs.MEASURE5_CHAR(i),
MEASURE6_CHAR = r_updrecs.MEASURE6_CHAR(i),
MEASURE7_CHAR = r_updrecs.MEASURE7_CHAR(i),
MEASURE8_CHAR = r_updrecs.MEASURE8_CHAR(i),
MEASURE9_CHAR = r_updrecs.MEASURE9_CHAR(i),
MEASURE10_CHAR = r_updrecs.MEASURE10_CHAR(i),
MEASURE_UOM = r_updrecs.MEASURE_UOM(i),
LAST_UPDATE_DATE = g_sys_date,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_login_id,
PROGRAM_APPLICATION_ID = g_prg_appl_id,
PROGRAM_ID = g_prg_id,
REQUEST_ID = g_request_id
where MEASURE_VALUE_ID = r_updrecs.MEASURE_VALUE_ID(i);
end update_measdata;
procedure insert_update_meas_data(p_date_from in varchar2,
p_date_to in varchar2,
p_sql in varchar2) is
bfound boolean := false;
bupdate boolean := false;
select ORD_LEVEL_VALUE,
TIME_LEVEL_VALUE, CUS_LEVEL_VALUE,
GEO_LEVEL_VALUE, ORG_LEVEL_VALUE,
REP_LEVEL_VALUE, CHN_LEVEL_VALUE,
PRD_LEVEL_VALUE, ADJ_LEVEL_VALUE,
USR1_LEVEL_VALUE, USR2_LEVEL_VALUE,
USR3_LEVEL_VALUE, USR4_LEVEL_VALUE,
USR5_LEVEL_VALUE, MEASURE_VALUE_ID
from QPR_MEASURE_DATA
where TIME_LEVEL_VALUE between d1 and d2
and INSTANCE_ID = ins_id
and MEASURE_TYPE_CODE = meas_tname
order by ORD_LEVEL_VALUE, ADJ_LEVEL_VALUE,
TIME_LEVEL_VALUE,CUS_LEVEL_VALUE,
GEO_LEVEL_VALUE,
PRD_LEVEL_VALUE, REP_LEVEL_VALUE,
CHN_LEVEL_VALUE, ORG_LEVEL_VALUE,
USR1_LEVEL_VALUE,USR2_LEVEL_VALUE,
USR3_LEVEL_VALUE, USR4_LEVEL_VALUE,
USR5_LEVEL_VALUE;
delete_ins_rec_data();
delete_upd_rec_data();
bupdate := false;
bupdate := true;
bupdate := true;
bupdate := true;
bupdate := true;
if bupdate=true then
r_updrecs.MEASURE_VALUE_ID(l_upd_ctr) :=
r_meas_data.MEASURE_VALUE_ID(j);
r_meas_data.ORD_LEVEL_VALUE.delete(j);
r_meas_data.ADJ_LEVEL_VALUE.delete(j);
r_meas_data.TIME_LEVEL_VALUE.delete(j);
r_meas_data.CUS_LEVEL_VALUE.delete(j);
r_meas_data.GEO_LEVEL_VALUE.delete(j);
r_meas_data.PRD_LEVEL_VALUE.delete(j);
r_meas_data.REP_LEVEL_VALUE.delete(j);
r_meas_data.CHN_LEVEL_VALUE.delete(j);
r_meas_data.ORG_LEVEL_VALUE.delete(j);
r_meas_data.USR1_LEVEL_VALUE.delete(j);
r_meas_data.USR2_LEVEL_VALUE.delete(j);
r_meas_data.USR3_LEVEL_VALUE.delete(j);
r_meas_data.USR4_LEVEL_VALUE.delete(j);
r_meas_data.USR5_LEVEL_VALUE.delete(j);
r_meas_data.MEASURE_VALUE_ID.delete(j);
log_debug('Inserted record count: ' || r_insrecs.ORD_LEVEL_VALUE.count);
insert_measdata;
log_debug('Updated record count: ' || r_updrecs.MEASURE_VALUE_ID.count);
update_measdata;
log_debug('ERROR INSERTING/UPDATING FACT DATA... ');
end insert_update_meas_data;
select column_name bulk collect into t_deal_tbl_def
from all_tab_columns
where table_name = p_tgt_tbl_name
and owner = s_table_owner order by column_id;
s_sql := 'select ';
function insert_update_deal_hdr(
p_header_id in number,
p_sql in varchar2) return number is
b_insert boolean := false;
select PN_INT_HEADER_ID, PN_REQ_HEADER_STATUS_FLAG
into l_req_int_hdr_id, s_status
from qpr_pn_int_headers
where source_ref_header_id = p_header_id
and instance_id = g_instance_id
and source_id = g_source_id
and source_ref_header_short_desc = g_quote_hdr_sd;
b_insert := true;
if b_insert= true then
log_debug('Inserting deal interface header...');
insert into qpr_pn_int_headers(
PN_INT_HEADER_ID,
INSTANCE_ID,
SOURCE_REF_HEADER_ID,
SOURCE_REF_HEADER_SHORT_DESC,
SOURCE_REF_HEADER_LONG_DESC,
SOURCE_ID, SOURCE_SHORT_DESC, SOURCE_LONG_DESC,
CUSTOMER_ID, CUSTOMER_SHORT_DESC,CUSTOMER_LONG_DESC,
INVOICE_TO_PARTY_SITE_ID,
INVOICE_TO_PARTY_SITE_ADDRESS,
SALES_REP_ID, SALES_REP_SHORT_DESC,SALES_REP_LONG_DESC,
SALES_REP_EMAIL_ADDRESS,
SALES_CHANNEL_CODE,SALES_CHANNEL_SHORT_DESC,
SALES_CHANNEL_LONG_DESC,
FREIGHT_TERMS_CODE,FREIGHT_TERMS_SHORT_DESC,
FREIGHT_TERMS_LONG_DESC,
CURRENCY_CODE, CURRENCY_SHORT_DESC,CURRENCY_LONG_DESC,
PN_REQ_EXPIRY_DATE, PN_REQ_HEADER_STATUS_FLAG,
COMMENTS, ADDITIONAL_INFORMATION, PN_REQ_HEADER_CREATION_DATE,
MEASURE1_NUMBER, MEASURE2_NUMBER,MEASURE3_NUMBER,
MEASURE4_NUMBER, MEASURE5_NUMBER,MEASURE6_NUMBER,
MEASURE7_NUMBER, MEASURE8_NUMBER,MEASURE9_NUMBER,
MEASURE10_NUMBER,
MEASURE1_CHAR, MEASURE2_CHAR,MEASURE3_CHAR,
MEASURE4_CHAR, MEASURE5_CHAR,MEASURE6_CHAR,
MEASURE7_CHAR, MEASURE8_CHAR,MEASURE9_CHAR,
MEASURE10_CHAR,
REQUEST_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,PROGRAM_ID)
values(qpr_pn_int_headers_s.nextval,
g_instance_id,
r_hdr.SOURCE_REF_HEADER_ID,
r_hdr.SOURCE_REF_HEADER_SHORT_DESC,
r_hdr.SOURCE_REF_HEADER_LONG_DESC,
r_hdr.SOURCE_ID,
r_hdr.SOURCE_SHORT_DESC,
r_hdr.SOURCE_LONG_DESC,
r_hdr.CUSTOMER_ID, r_hdr.CUSTOMER_SHORT_DESC,
r_hdr.CUSTOMER_LONG_DESC,
r_hdr.INVOICE_TO_PARTY_SITE_ID,
r_hdr.INVOICE_TO_PARTY_SITE_ADDRESS,
r_hdr.SALES_REP_ID, r_hdr.SALES_REP_SHORT_DESC,
r_hdr.SALES_REP_LONG_DESC,
r_hdr.SALES_REP_EMAIL_ADDRESS,
r_hdr.SALES_CHANNEL_CODE,r_hdr.SALES_CHANNEL_SHORT_DESC,
r_hdr.SALES_CHANNEL_LONG_DESC,
r_hdr.FREIGHT_TERMS_CODE,r_hdr.FREIGHT_TERMS_SHORT_DESC,
r_hdr.FREIGHT_TERMS_LONG_DESC,
r_hdr.CURRENCY_CODE, r_hdr.CURRENCY_SHORT_DESC,
r_hdr.CURRENCY_LONG_DESC,
r_hdr.PN_REQ_EXPIRY_DATE, 'I', r_hdr.COMMENTS,
r_hdr.ADDITIONAL_INFORMATION,
/*g_sys_date*/ r_hdr.PN_REQ_HEADER_CREATION_DATE,
r_hdr.MEASURE1_NUMBER, r_hdr.MEASURE2_NUMBER,
r_hdr.MEASURE3_NUMBER,
r_hdr.MEASURE4_NUMBER, r_hdr.MEASURE5_NUMBER,
r_hdr.MEASURE6_NUMBER,
r_hdr.MEASURE7_NUMBER, r_hdr.MEASURE8_NUMBER,
r_hdr.MEASURE9_NUMBER,
r_hdr.MEASURE10_NUMBER,
r_hdr.MEASURE1_CHAR, r_hdr.MEASURE2_CHAR,r_hdr.MEASURE3_CHAR,
r_hdr.MEASURE4_CHAR, r_hdr.MEASURE5_CHAR,r_hdr.MEASURE6_CHAR,
r_hdr.MEASURE7_CHAR, r_hdr.MEASURE8_CHAR,r_hdr.MEASURE9_CHAR,
r_hdr.MEASURE10_CHAR,
g_request_id, g_sys_date, g_user_id, g_sys_date,
g_user_id, g_login_id, g_prg_appl_id, g_prg_id)
returning PN_INT_HEADER_ID into l_req_int_hdr_id;
update qpr_pn_int_headers set
INSTANCE_ID = g_instance_id,
SOURCE_REF_HEADER_SHORT_DESC = r_hdr.SOURCE_REF_HEADER_SHORT_DESC,
SOURCE_REF_HEADER_LONG_DESC = r_hdr.SOURCE_REF_HEADER_LONG_DESC,
CUSTOMER_ID = r_hdr.CUSTOMER_ID,
CUSTOMER_SHORT_DESC= r_hdr.CUSTOMER_SHORT_DESC,
CUSTOMER_LONG_DESC = r_hdr.CUSTOMER_LONG_DESC,
INVOICE_TO_PARTY_SITE_ID = r_hdr.INVOICE_TO_PARTY_SITE_ID,
INVOICE_TO_PARTY_SITE_ADDRESS = r_hdr.INVOICE_TO_PARTY_SITE_ADDRESS,
SALES_REP_ID = r_hdr.SALES_REP_ID,
SALES_REP_SHORT_DESC = r_hdr.SALES_REP_SHORT_DESC,
SALES_REP_LONG_DESC = r_hdr.SALES_REP_LONG_DESC,
SALES_REP_EMAIL_ADDRESS = r_hdr.SALES_REP_EMAIL_ADDRESS,
SALES_CHANNEL_CODE = r_hdr.SALES_CHANNEL_CODE,
SALES_CHANNEL_SHORT_DESC = r_hdr.SALES_CHANNEL_SHORT_DESC,
SALES_CHANNEL_LONG_DESC = r_hdr.SALES_CHANNEL_LONG_DESC,
FREIGHT_TERMS_CODE = r_hdr.FREIGHT_TERMS_CODE,
FREIGHT_TERMS_SHORT_DESC = r_hdr.FREIGHT_TERMS_SHORT_DESC,
FREIGHT_TERMS_LONG_DESC = r_hdr.FREIGHT_TERMS_LONG_DESC,
CURRENCY_CODE = r_hdr.CURRENCY_CODE,
CURRENCY_SHORT_DESC = r_hdr.CURRENCY_SHORT_DESC,
CURRENCY_LONG_DESC = r_hdr.CURRENCY_LONG_DESC,
PN_REQ_EXPIRY_DATE = r_hdr.PN_REQ_EXPIRY_DATE,
COMMENTS = r_hdr.COMMENTS,
ADDITIONAL_INFORMATION = r_hdr.ADDITIONAL_INFORMATION,
MEASURE1_NUMBER = r_hdr.MEASURE1_NUMBER,
MEASURE2_NUMBER = r_hdr.MEASURE2_NUMBER,
MEASURE3_NUMBER = r_hdr.MEASURE3_NUMBER,
MEASURE4_NUMBER = r_hdr.MEASURE4_NUMBER,
MEASURE5_NUMBER = r_hdr.MEASURE5_NUMBER,
MEASURE6_NUMBER = r_hdr.MEASURE6_NUMBER,
MEASURE7_NUMBER = r_hdr.MEASURE7_NUMBER,
MEASURE8_NUMBER = r_hdr.MEASURE8_NUMBER,
MEASURE9_NUMBER = r_hdr.MEASURE9_NUMBER,
MEASURE10_NUMBER = r_hdr.MEASURE10_NUMBER,
MEASURE1_CHAR = r_hdr.MEASURE1_CHAR,
MEASURE2_CHAR = r_hdr.MEASURE2_CHAR,
MEASURE3_CHAR = r_hdr.MEASURE3_CHAR,
MEASURE4_CHAR = r_hdr.MEASURE4_CHAR,
MEASURE5_CHAR = r_hdr.MEASURE5_CHAR,
MEASURE6_CHAR = r_hdr.MEASURE6_CHAR,
MEASURE7_CHAR = r_hdr.MEASURE7_CHAR,
MEASURE8_CHAR = r_hdr.MEASURE8_CHAR,
MEASURE9_CHAR = r_hdr.MEASURE9_CHAR,
MEASURE10_CHAR = r_hdr.MEASURE10_CHAR,
LAST_UPDATE_DATE = g_sys_date,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_login_id,
PROGRAM_APPLICATION_ID = g_prg_appl_id,
PROGRAM_ID = g_prg_id,
REQUEST_ID = g_request_id
where PN_INT_HEADER_ID = l_req_int_hdr_id;
log_debug('Status of the header does not permit update');
log_debug('ERROR IN INSERTING/UPDATING DEAL INTERFACE HEADER');
end insert_update_deal_hdr;
l_sql := 'select QUOTE_LINE_NUMBER from qpr_sr_quote_line_num_v';
log_debug('ERROR ASSIGNING VALUES TO INSERT IN INTERFACE LINE RECORD');
select QUOTE_LINE_NUMBER into l_line_no
from qpr_sr_quote_line_num_v
where quote_header_id = g_t_src_lines(p_src_ctr).SOURCE_REF_HDR_ID
and quote_line_id = g_t_src_lines(p_src_ctr).SOURCE_REF_LINE_ID
and source_id = g_source_id;
log_debug('ERROR ASSIGNING VALUES TO UPDATE INTERFACE LINE RECORD');
g_r_ins_deal.SOURCE_REF_HDR_ID.delete;
g_r_ins_deal.SOURCE_REF_LINE_ID.delete;
g_r_ins_deal.SOURCE_REQ_LINE_NO.delete;
g_r_ins_deal.SOURCE_ID.delete;
g_r_ins_deal.ORG_ID.delete;
g_r_ins_deal.ORG_SHORT_DESC.delete;
g_r_ins_deal.ORG_LONG_DESC.delete;
g_r_ins_deal.INVENTORY_ITEM_ID.delete;
g_r_ins_deal.INVENTORY_ITEM_SHORT_DESC.delete;
g_r_ins_deal.INVENTORY_ITEM_LONG_DESC.delete;
g_r_ins_deal.ITEM_TYPE_CODE.delete;
g_r_ins_deal.TOP_MDL_SRC_LINE_ID.delete;
g_r_ins_deal.PAYMENT_TERM_ID.delete;
g_r_ins_deal.PAYMENT_TERM_SHORT_DESC.delete;
g_r_ins_deal.PAYMENT_TERM_LONG_DESC.delete;
g_r_ins_deal.UOM_CODE.delete;
g_r_ins_deal.UOM_SHORT_DESC.delete;
g_r_ins_deal.CURRENCY_CODE.delete;
g_r_ins_deal.CURRENCY_SHORT_DESC.delete;
g_r_ins_deal.ORDERED_QTY.delete;
g_r_ins_deal.LIST_PRICE.delete;
g_r_ins_deal.PROPOSED_PRICE.delete;
g_r_ins_deal.REVISED_OQ.delete;
g_r_ins_deal.COMPETITOR_NAME.delete;
g_r_ins_deal.COMPETITOR_PRICE.delete;
g_r_ins_deal.COMMENTS.delete;
g_r_ins_deal.ADDITIONAL_INFO.delete;
g_r_ins_deal.SHIP_METHOD_CODE.delete;
g_r_ins_deal.SHIP_METHOD_SHORT_DESC.delete;
g_r_ins_deal.SHIP_METHOD_LONG_DESC.delete;
g_r_ins_deal.FREIGHT_CHARGES.delete;
g_r_ins_deal.GEOGRAPHY_ID.delete;
g_r_ins_deal.GEOGRAPHY_SHORT_DESC.delete;
g_r_ins_deal.GEOGRAPHY_LONG_DESC.delete;
g_r_ins_deal.MEASURE1_NUMBER.delete;
g_r_ins_deal.MEASURE2_NUMBER.delete;
g_r_ins_deal.MEASURE3_NUMBER.delete;
g_r_ins_deal.MEASURE4_NUMBER.delete;
g_r_ins_deal.MEASURE5_NUMBER.delete;
g_r_ins_deal.MEASURE6_NUMBER.delete;
g_r_ins_deal.MEASURE7_NUMBER.delete;
g_r_ins_deal.MEASURE8_NUMBER.delete;
g_r_ins_deal.MEASURE9_NUMBER.delete;
g_r_ins_deal.MEASURE10_NUMBER.delete;
g_r_ins_deal.MEASURE1_CHAR.delete;
g_r_ins_deal.MEASURE2_CHAR.delete;
g_r_ins_deal.MEASURE3_CHAR.delete;
g_r_ins_deal.MEASURE4_CHAR.delete;
g_r_ins_deal.MEASURE5_CHAR.delete;
g_r_ins_deal.MEASURE6_CHAR.delete;
g_r_ins_deal.MEASURE7_CHAR.delete;
g_r_ins_deal.MEASURE8_CHAR.delete;
g_r_ins_deal.MEASURE9_CHAR.delete;
g_r_ins_deal.MEASURE10_CHAR.delete;
g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID.delete;
g_r_upd_deal.SOURCE_REF_HDR_ID.delete;
g_r_upd_deal.SOURCE_REF_LINE_ID.delete;
g_r_upd_deal.SOURCE_REQ_LINE_NO.delete;
g_r_upd_deal.SOURCE_ID.delete;
g_r_upd_deal.ORG_ID.delete;
g_r_upd_deal.ORG_SHORT_DESC.delete;
g_r_upd_deal.ORG_LONG_DESC.delete;
g_r_upd_deal.INVENTORY_ITEM_ID.delete;
g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC.delete;
g_r_upd_deal.INVENTORY_ITEM_LONG_DESC.delete;
g_r_upd_deal.ITEM_TYPE_CODE.delete;
g_r_upd_deal.TOP_MDL_SRC_LINE_ID.delete;
g_r_upd_deal.PAYMENT_TERM_ID.delete;
g_r_upd_deal.PAYMENT_TERM_SHORT_DESC.delete;
g_r_upd_deal.PAYMENT_TERM_LONG_DESC.delete;
g_r_upd_deal.UOM_CODE.delete;
g_r_upd_deal.UOM_SHORT_DESC.delete;
g_r_upd_deal.CURRENCY_CODE.delete;
g_r_upd_deal.CURRENCY_SHORT_DESC.delete;
g_r_upd_deal.ORDERED_QTY.delete;
g_r_upd_deal.LIST_PRICE.delete;
g_r_upd_deal.PROPOSED_PRICE.delete;
g_r_upd_deal.REVISED_OQ.delete;
g_r_upd_deal.COMPETITOR_NAME.delete;
g_r_upd_deal.COMPETITOR_PRICE.delete;
g_r_upd_deal.COMMENTS.delete;
g_r_upd_deal.ADDITIONAL_INFO.delete;
g_r_upd_deal.SHIP_METHOD_CODE.delete;
g_r_upd_deal.SHIP_METHOD_SHORT_DESC.delete;
g_r_upd_deal.SHIP_METHOD_LONG_DESC.delete;
g_r_upd_deal.FREIGHT_CHARGES.delete;
g_r_upd_deal.GEOGRAPHY_ID.delete;
g_r_upd_deal.GEOGRAPHY_SHORT_DESC.delete;
g_r_upd_deal.GEOGRAPHY_LONG_DESC.delete;
g_r_upd_deal.MEASURE1_NUMBER.delete;
g_r_upd_deal.MEASURE2_NUMBER.delete;
g_r_upd_deal.MEASURE3_NUMBER.delete;
g_r_upd_deal.MEASURE4_NUMBER.delete;
g_r_upd_deal.MEASURE5_NUMBER.delete;
g_r_upd_deal.MEASURE6_NUMBER.delete;
g_r_upd_deal.MEASURE7_NUMBER.delete;
g_r_upd_deal.MEASURE8_NUMBER.delete;
g_r_upd_deal.MEASURE9_NUMBER.delete;
g_r_upd_deal.MEASURE10_NUMBER.delete;
g_r_upd_deal.MEASURE1_CHAR.delete;
g_r_upd_deal.MEASURE2_CHAR.delete;
g_r_upd_deal.MEASURE3_CHAR.delete;
g_r_upd_deal.MEASURE4_CHAR.delete;
g_r_upd_deal.MEASURE5_CHAR.delete;
g_r_upd_deal.MEASURE6_CHAR.delete;
g_r_upd_deal.MEASURE7_CHAR.delete;
g_r_upd_deal.MEASURE8_CHAR.delete;
g_r_upd_deal.MEASURE9_CHAR.delete;
g_r_upd_deal.MEASURE10_CHAR.delete;
procedure insert_deal_lines(
p_header_id in number
) is
begin
log_debug('Inserting deal interface lines ....');
insert into qpr_pn_int_lines(PN_INT_LINE_ID,
-- PN_INT_HEADER_ID,
SOURCE_REF_HDR_ID,
SOURCE_REF_LINE_ID,
SOURCE_REQUEST_LINE_NUMBER,
SOURCE_ID,
ORG_ID,
ORG_SHORT_DESC,
ORG_LONG_DESC,
INVENTORY_ITEM_ID,
INVENTORY_ITEM_SHORT_DESC,
INVENTORY_ITEM_LONG_DESC,
ITEM_TYPE_CODE,
TOP_MDL_SRC_LINE_ID,
PAYMENT_TERM_ID,
PAYMENT_TERM_SHORT_DESC,
PAYMENT_TERM_LONG_DESC,
UOM_CODE,
UOM_SHORT_DESC,
CURRENCY_CODE,
CURRENCY_SHORT_DESC,
ORDERED_QTY,
LIST_PRICE,
PROPOSED_PRICE,
REVISED_OQ,
PN_REQ_LINE_STATUS_FLAG,
COMPETITOR_NAME,
COMPETITOR_PRICE,
COMMENTS,
ADDITIONAL_INFORMATION,
SHIP_METHOD_CODE,
SHIP_METHOD_SHORT_DESC,
SHIP_METHOD_LONG_DESC,
FREIGHT_CHARGES,
GEOGRAPHY_ID,
GEOGRAPHY_SHORT_DESC,
GEOGRAPHY_LONG_DESC,
MEASURE1_NUMBER,
MEASURE2_NUMBER,
MEASURE3_NUMBER,
MEASURE4_NUMBER,
MEASURE5_NUMBER,
MEASURE6_NUMBER,
MEASURE7_NUMBER,
MEASURE8_NUMBER,
MEASURE9_NUMBER,
MEASURE10_NUMBER,
MEASURE1_CHAR,
MEASURE2_CHAR,
MEASURE3_CHAR,
MEASURE4_CHAR,
MEASURE5_CHAR,
MEASURE6_CHAR,
MEASURE7_CHAR,
MEASURE8_CHAR,
MEASURE9_CHAR,
MEASURE10_CHAR,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
REQUEST_ID)
values(qpr_pn_int_lines_s.nextval,
-- p_int_hdr_id,
g_r_ins_deal.SOURCE_REF_HDR_ID(i) ,
g_r_ins_deal.SOURCE_REF_LINE_ID(i) ,
g_r_ins_deal.SOURCE_REQ_LINE_NO(i),
g_r_ins_deal.SOURCE_ID(i) ,
g_r_ins_deal.ORG_ID(i) ,
g_r_ins_deal.ORG_SHORT_DESC(i) ,
g_r_ins_deal.ORG_LONG_DESC(i) ,
g_r_ins_deal.INVENTORY_ITEM_ID(i) ,
g_r_ins_deal.INVENTORY_ITEM_SHORT_DESC(i) ,
g_r_ins_deal.INVENTORY_ITEM_LONG_DESC(i) ,
g_r_ins_deal.ITEM_TYPE_CODE(i) ,
g_r_ins_deal.TOP_MDL_SRC_LINE_ID(i) ,
g_r_ins_deal.PAYMENT_TERM_ID(i) ,
g_r_ins_deal.PAYMENT_TERM_SHORT_DESC(i) ,
g_r_ins_deal.PAYMENT_TERM_LONG_DESC(i) ,
g_r_ins_deal.UOM_CODE(i) ,
g_r_ins_deal.UOM_SHORT_DESC(i) ,
g_r_ins_deal.CURRENCY_CODE(i) ,
g_r_ins_deal.CURRENCY_SHORT_DESC(i) ,
g_r_ins_deal.ORDERED_QTY(i) ,
g_r_ins_deal.LIST_PRICE(i) ,
g_r_ins_deal.PROPOSED_PRICE(i) ,
g_r_ins_deal.REVISED_OQ(i) , 'I',
g_r_ins_deal.COMPETITOR_NAME(i) ,
g_r_ins_deal.COMPETITOR_PRICE(i) ,
g_r_ins_deal.COMMENTS(i),
g_r_ins_deal.ADDITIONAL_INFO(i),
g_r_ins_deal.SHIP_METHOD_CODE(i),
g_r_ins_deal.SHIP_METHOD_SHORT_DESC(i),
g_r_ins_deal.SHIP_METHOD_LONG_DESC(i),
g_r_ins_deal.FREIGHT_CHARGES(i),
g_r_ins_deal.GEOGRAPHY_ID(i),
g_r_ins_deal.GEOGRAPHY_SHORT_DESC(i),
g_r_ins_deal.GEOGRAPHY_LONG_DESC(i),
g_r_ins_deal.MEASURE1_NUMBER(i),
g_r_ins_deal.MEASURE2_NUMBER(i),
g_r_ins_deal.MEASURE3_NUMBER(i),
g_r_ins_deal.MEASURE4_NUMBER(i),
g_r_ins_deal.MEASURE5_NUMBER(i),
g_r_ins_deal.MEASURE6_NUMBER(i),
g_r_ins_deal.MEASURE7_NUMBER(i),
g_r_ins_deal.MEASURE8_NUMBER(i),
g_r_ins_deal.MEASURE9_NUMBER(i),
g_r_ins_deal.MEASURE10_NUMBER(i),
g_r_ins_deal.MEASURE1_CHAR(i),
g_r_ins_deal.MEASURE2_CHAR(i),
g_r_ins_deal.MEASURE3_CHAR(i),
g_r_ins_deal.MEASURE4_CHAR(i),
g_r_ins_deal.MEASURE5_CHAR(i),
g_r_ins_deal.MEASURE6_CHAR(i),
g_r_ins_deal.MEASURE7_CHAR(i),
g_r_ins_deal.MEASURE8_CHAR(i),
g_r_ins_deal.MEASURE9_CHAR(i),
g_r_ins_deal.MEASURE10_CHAR(i),
g_sys_date,
g_user_id,
g_sys_date,
g_user_id,
g_login_id, g_prg_appl_id , g_prg_id, g_request_id);
log_debug('ERROR INSERTING VALUES TO DEAL INTERFACE LINE');
end insert_deal_lines;
procedure update_deal_lines is
begin
log_debug('Updating deal interface lines ....');
update qpr_pn_int_lines set
SOURCE_REQUEST_LINE_NUMBER = g_r_upd_deal.SOURCE_REQ_LINE_NO(i),
SOURCE_ID = g_r_upd_deal.SOURCE_ID(i),
ORG_ID = g_r_upd_deal.ORG_ID(i),
ORG_SHORT_DESC = g_r_upd_deal.ORG_SHORT_DESC(i) ,
ORG_LONG_DESC = g_r_upd_deal.ORG_LONG_DESC(i) ,
INVENTORY_ITEM_ID = g_r_upd_deal.INVENTORY_ITEM_ID(i) ,
INVENTORY_ITEM_SHORT_DESC= g_r_upd_deal.INVENTORY_ITEM_SHORT_DESC(i),
INVENTORY_ITEM_LONG_DESC = g_r_upd_deal.INVENTORY_ITEM_LONG_DESC(i),
ITEM_TYPE_CODE = g_r_upd_deal.ITEM_TYPE_CODE(i) ,
TOP_MDL_SRC_LINE_ID = g_r_upd_deal.TOP_MDL_SRC_LINE_ID(i) ,
PAYMENT_TERM_ID = g_r_upd_deal.PAYMENT_TERM_ID(i) ,
PAYMENT_TERM_SHORT_DESC = g_r_upd_deal.PAYMENT_TERM_SHORT_DESC(i),
PAYMENT_TERM_LONG_DESC = g_r_upd_deal.PAYMENT_TERM_LONG_DESC(i),
UOM_CODE = g_r_upd_deal.UOM_CODE(i) ,
UOM_SHORT_DESC = g_r_upd_deal.UOM_SHORT_DESC(i) ,
CURRENCY_CODE = g_r_upd_deal.CURRENCY_CODE(i) ,
CURRENCY_SHORT_DESC = g_r_upd_deal.CURRENCY_SHORT_DESC(i) ,
ORDERED_QTY = g_r_upd_deal.ORDERED_QTY(i) ,
LIST_PRICE = g_r_upd_deal.LIST_PRICE(i) ,
PROPOSED_PRICE = g_r_upd_deal.PROPOSED_PRICE(i) ,
REVISED_OQ = g_r_upd_deal.REVISED_OQ(i) ,
COMPETITOR_NAME = g_r_upd_deal.COMPETITOR_NAME(i),
COMPETITOR_PRICE = g_r_upd_deal.COMPETITOR_PRICE(i) ,
COMMENTS = g_r_upd_deal.COMMENTS(i) ,
ADDITIONAL_INFORMATION = g_r_upd_deal.ADDITIONAL_INFO(i),
SHIP_METHOD_CODE = g_r_upd_deal.SHIP_METHOD_CODE(i),
SHIP_METHOD_SHORT_DESC = g_r_upd_deal.SHIP_METHOD_SHORT_DESC(i),
SHIP_METHOD_LONG_DESC = g_r_upd_deal.SHIP_METHOD_LONG_DESC(i),
FREIGHT_CHARGES = g_r_upd_deal.FREIGHT_CHARGES(i),
GEOGRAPHY_ID = g_r_upd_deal.GEOGRAPHY_ID(i),
GEOGRAPHY_SHORT_DESC = g_r_upd_deal.GEOGRAPHY_SHORT_DESC(i),
GEOGRAPHY_LONG_DESC = g_r_upd_deal.GEOGRAPHY_LONG_DESC(i),
MEASURE1_NUMBER = g_r_upd_deal.MEASURE1_NUMBER(i),
MEASURE2_NUMBER = g_r_upd_deal.MEASURE2_NUMBER(i),
MEASURE3_NUMBER = g_r_upd_deal.MEASURE3_NUMBER(i),
MEASURE4_NUMBER = g_r_upd_deal.MEASURE4_NUMBER(i),
MEASURE5_NUMBER = g_r_upd_deal.MEASURE5_NUMBER(i),
MEASURE6_NUMBER = g_r_upd_deal.MEASURE6_NUMBER(i),
MEASURE7_NUMBER = g_r_upd_deal.MEASURE7_NUMBER(i),
MEASURE8_NUMBER = g_r_upd_deal.MEASURE8_NUMBER(i),
MEASURE9_NUMBER = g_r_upd_deal.MEASURE9_NUMBER(i),
MEASURE10_NUMBER = g_r_upd_deal.MEASURE10_NUMBER(i),
MEASURE1_CHAR = g_r_upd_deal.MEASURE1_CHAR(i),
MEASURE2_CHAR = g_r_upd_deal.MEASURE2_CHAR(i),
MEASURE3_CHAR = g_r_upd_deal.MEASURE3_CHAR(i),
MEASURE4_CHAR = g_r_upd_deal.MEASURE4_CHAR(i),
MEASURE5_CHAR = g_r_upd_deal.MEASURE5_CHAR(i),
MEASURE6_CHAR = g_r_upd_deal.MEASURE6_CHAR(i),
MEASURE7_CHAR = g_r_upd_deal.MEASURE7_CHAR(i),
MEASURE8_CHAR = g_r_upd_deal.MEASURE8_CHAR(i),
MEASURE9_CHAR = g_r_upd_deal.MEASURE9_CHAR(i),
MEASURE10_CHAR = g_r_upd_deal.MEASURE10_CHAR(i),
LAST_UPDATE_DATE = g_sys_date,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_login_id,
PROGRAM_APPLICATION_ID = g_prg_appl_id,
PROGRAM_ID = g_prg_id,
REQUEST_ID = g_request_id
where PN_INT_LINE_ID = g_r_upd_deal.PN_REQ_INTERFACE_LINE_ID(i);
end update_deal_lines;
procedure insert_update_deal_lines(
p_header_id in number,
p_sql in varchar2) is
b_update boolean := false;
select l.SOURCE_REF_LINE_ID, l.PN_INT_LINE_ID, l.PN_REQ_LINE_STATUS_FLAG
bulk collect into t_src_line_id, t_int_line_id, t_status
from qpr_pn_int_lines l, qpr_pn_int_headers h
where h.source_ref_header_id = p_header_id
and h.instance_id = g_instance_id
and h.source_id = g_source_id
and h.source_ref_header_short_desc = g_quote_hdr_sd
and h.source_id = l.source_id
and h.source_ref_header_id = l.source_ref_hdr_id
order by l.source_ref_line_id;
b_update := false;
b_update := true;
t_src_line_id.delete(j);
t_int_line_id.delete(j);
t_status.delete(j);
if b_update = true then
if s_status <> 'P' then
l_qt_uctr := l_qt_uctr + 1;
log_debug('Status of line ' ||g_t_src_lines(i).source_ref_line_id || ' does not permit update.');
insert_deal_lines( p_header_id);
update_deal_lines;
g_t_src_lines.delete;
end insert_update_deal_lines;
procedure insert_deal_adjs(p_header_id in number, p_sql in varchar2) is
l_ctr number:= 0;
select 1 into l_status
from qpr_pn_int_headers h
where h.source_ref_header_id = p_header_id
and h.instance_id = g_instance_id
and h.source_id = g_source_id
and h.pn_req_header_status_flag <> 'P';
delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_header_id
and source_id = g_source_id
and erosion_type = t_adj_rec(1).EROSION_TYPE;
insert into qpr_pn_int_pr_adjs(pn_int_pr_adj_id,
source_ref_hdr_id,
source_ref_line_id,
source_id,
erosion_type,
erosion_name,
erosion_desc,
erosion_per_unit,
erosion_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
request_id)
values(qpr_pn_int_pr_adjs_s.nextval,
t_pr_adj_val.SRC_REF_HDR_ID(i),
t_pr_adj_val.src_ref_line_id(i),
t_pr_adj_val.source_id(i),
t_pr_adj_val.erosion_type(i),
t_pr_adj_val.erosion_name(i),
t_pr_adj_val.erosion_desc(i),
t_pr_adj_val.erosion_per_unit(i),
t_pr_adj_val.erosion_amount(i),
g_sys_date,
g_user_id,
g_sys_date,
g_user_id,
g_login_id, g_prg_appl_id , g_prg_id, g_request_id);
t_pr_adj_val.source_id.delete;
t_pr_adj_val.EROSION_TYPE.delete;
t_pr_adj_val.EROSION_NAME.delete;
t_pr_adj_val.EROSION_DESC.delete;
t_pr_adj_val.EROSION_PER_UNIT.delete;
t_pr_adj_val.EROSION_AMOUNT.delete;
t_pr_adj_val.src_ref_hdr_id.delete;
t_pr_adj_val.src_ref_line_id.delete;
t_adj_rec.delete;
end insert_deal_adjs;
select distinct nvl(USER_SRC_COL_NAME,SRC_COL_NAME) SRC_COL_NAME,
nvl(USER_TGT_COL_NAME, TGT_COL_NAME) TGT_COL_NAME
from QPR_MEASURE_SOURCES
where MEASURE_TYPE_CODE = m_type
and INSTANCE_ID = m_ins_id
and INSTANCE_TYPE = l_inst_type
and nvl(user_src_tbl_name, src_tbl_name ) = m_src_tname
order by TGT_COL_NAME;
select distinct nvl(USER_SRC_TBL_NAME, SRC_TBL_NAME) SRC_TBL_NAME,
TGT_TBL_NAME
from QPR_MEASURE_SOURCES
where MEASURE_TYPE_CODE = m_type
and INSTANCE_ID = m_ins_id
and INSTANCE_TYPE = l_inst_type
order by TGT_TBL_NAME;
select hsecs into l_start_time from v$timer;
select instance_type into l_inst_type
from qpr_instances
where instance_id = p_instance_id;
select count(*) into l_src_count
from QPR_MEASURE_SOURCES
where INSTANCE_ID = p_instance_id
and MEASURE_TYPE_CODE = p_meas_type;
l_req_int_hdr_id := insert_update_deal_hdr(
p_header_id,
s_sql);
insert_update_deal_lines(p_header_id,s_sql);
insert_deal_adjs(p_header_id, s_sql);
s_sql := get_select_meas_sql(src_table, p_meas_type);
insert_update_meas_data(p_date_from, p_date_to, s_sql);
select hsecs into l_end_time from v$timer;
select request_header_id into l_dummy
from qpr_pn_request_hdrs_b
where instance_id = p_instance_id
and source_id = decode(p_src_choice,1, 660, 2, 697, p_src_choice)
and source_ref_hdr_short_desc = (p_quote_number || ' - Ver '|| p_quote_version)
and nvl(request_status, 'ACTIVE') = 'ACTIVE'
and nvl(simulation_flag, 'Y') = 'N'
and rownum < 2;
select distinct src_tbl_name into src_tbl_name
from qpr_measure_sources
where measure_type_code = s_deal_type
and tgt_tbl_name = DEAL_HEADER_TBL
and INSTANCE_TYPE = (select instance_type
from qpr_instances
where instance_id = p_instance_id);
l_sql := 'select quote_header_id, quote_header_sd, source_id from '
|| src_tbl_name||qpr_sr_util.get_dblink(p_instance_id)
|| ' where quote_number = ' || p_quote_number
|| ' and quote_version = ' || p_quote_version ;
l_sql := 'select quote_header_id, quote_header_sd, source_id from '
|| src_tbl_name||qpr_sr_util.get_dblink(p_instance_id)
|| ' where quote_header_id = ' || p_quote_header_id;
select distinct nvl(USER_SRC_COL_NAME,SRC_COL_NAME) SRC_COL_NAME,
nvl(USER_TGT_COL_NAME,TGT_COL_NAME) TGT_COL_NAME
from QPR_MEASURE_SOURCES src, qpr_instances inst
where src.instance_type = inst.instance_type
and inst.instance_id = p_instance_id
and src.measure_type_code = decode(p_source_id, 660, 'OM_DEALINT',
697, 'ASO_DEALINT')
and nvl(src.user_src_tbl_name, src.src_tbl_name ) = m_src_tname
order by TGT_COL_NAME;
select distinct src_tbl_name into l_src_tbl
from qpr_measure_sources src, qpr_instances inst
where src.instance_type = inst.instance_type
and inst.instance_id = p_instance_id
and src.measure_type_code = decode(p_source_id, 660, 'OM_DEALINT',
697, 'ASO_DEALINT')
and tgt_tbl_name = p_tgt_tbl_name;
select req.CUSTOMER_ID, req.SALES_REP_ID, req.SALES_CHANNEL_CODE,
req.request_header_id
into l_resp_cust, l_resp_sales_rep, l_resp_sc, l_request_hdr_id
from qpr_pn_request_hdrs_b req
where instance_id = p_instance_id
and source_id = p_source_id
and source_ref_hdr_id = p_src_quote_header_id
and request_status = 'ACTIVE'
and rownum < 2;
select ORG_ID,INVENTORY_ITEM_ID,ORIG_PAYMENT_TERM_ID,
ORIG_SHIP_METHOD_CODE,
GEOGRAPHY_ID,UOM_CODE,ORDERED_QTY,PROPOSED_PRICE,CURRENCY_CODE
into l_org_id, l_item_id, l_pt_id, l_shm_code,
l_geo_id, l_uom_code, l_ord_qty, l_price, l_curr_code
from qpr_pn_lines
where request_header_id = l_request_hdr_id
and source_ref_line_id = r_lines.SOURCE_REF_LINE_ID
and source_ref_hdr_id = r_lines.SOURCE_REF_HDR_ID
and source_id = r_lines.SOURCE_ID
and item_type_code <> 'DUMMY_PARENT'
and rownum < 2;
select count(*) into l_line_cnt
from qpr_pn_lines
where request_header_id = l_request_hdr_id
and item_type_code <> 'DUMMY_PARENT';