PROCEDURE load_purchase_quotation
as
lv_currency_code fnd_currencies_vl.currency_code%type;
lv_verify_flag varchar2 (1);
lv_error_message varchar2 (5000);
ln_vendor_id po_vendors.vendor_id%type;
ln_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
ln_ship_to_locn_id hr_locations.location_id%type;
ln_bill_to_locn_id hr_locations.location_id%type;
ln_inventory_item_id mtl_system_items_b.inventory_item_id%type;
lv_legacy_quotenum varchar2 (30);
ln_batch_id number (3);
ln_term_id number;
lv_freight_code org_freight.freight_code%type;
lv_fob_code po_lookup_codes.lookup_code%type;
lv_freight_terms po_lookup_codes.lookup_code%type;
ln_org_id number;
ln_agent_id number;
ln_vendor_contact_id number;
ln_line_type_id number;
ln_item_id number;
lv_unit_of_measure mtl_units_of_measure_tl.unit_of_measure%type;
ln_interface_header_id number;
ln_interface_line_id number;
ln_intf_line_locn_id number;
ln_po_header_id number;
ln_ou_id number ;
ln_ship_to_location_id number;
lv_ship_to_location_code varchar2 (30);
lv_unit_of_measure1 mtl_units_of_measure_tl.unit_of_measure%type;
lv_shipment_type varchar2 (30);
ln_ship_to_organization_id number;
lc_ship_to_org_code varchar2 (30);
ln_hazard_class_id number;
ln_term_id1 number;
lv_freight_code1 org_freight.freight_code%type;
lv_fob_code1 po_lookup_codes.lookup_code%type;
lv_freight_terms1 po_lookup_codes.lookup_code%type;
ln_category_id number;
lv_item_revision mtl_item_revisions.revision%type;
lv_supplier_number varchar2 (30);
lv_exists varchar2 (1);
cursor cur_po_header
is
select distinct organization_name, quote_number, quote_type_code,
supplier_name, supplier_name_alt, supplier_site_code, vendor_contact,
ship_to_location, bill_to_location, vendor_doc_num, reply_date,
agent_name, currency_code, quote_desc,quote_creation_date,
approval_required_flag, eff_start_date, eff_end_date,
payment_terms, freight_terms, freight_carrier, fob,
note_from_vendor, quote_warning_delay, reply_method, hdr_attribute1, hdr_attribute2, hdr_attribute3,
hdr_attribute4, hdr_attribute5, hdr_attribute6, hdr_attribute7, hdr_attribute15
from xx_purc_quot_stg_tbl where nvl (record_status, ‘ni’) = ‘ni’ and error_message is null;-
cursor cur_po_lines (p_quote_num varchar2)
is
select distinct organization_name, quote_number, quote_line_num, quote_line_type, quote_line_item,
qt_line_item_category, qt_line_item_desc, qt_line_uom_code, qt_line_qty, qt_line_unit_price,
vendor_product_num, un_number, hazard_class, min_order_quantity, max_order_quantity,
line_note_fm_vendor, line_attribute1, line_attribute2, ship_to_location_code,
line_freight_carrier, line_payment_terms, line_fob, line_freight_terms, shp_attribute2,
shp_attribute3, line_eff_start_dt, line_eff_end_dt, 1
shipment_uom_code, ship_to_organization_code, lead_time_uom, lead_time
from xx_purc_quot_stg_tbl where trim (quote_number) = trim (p_quote_num) and quote_line_num is not null;
cursor cur_po_location_lines (p_quote_num number, p_line_num number) is
select * from xx_purc_quot_stg_tbl
where trim (quote_number) = trim (p_quote_num)
and trim (quote_line_num) = p_line_num
and shipment_num is not null;
ln_valid_records number := 0;
ln_exists number;
ln_request_id number;
lb_complete boolean;
lc_phase varchar2 (20);
lc_status varchar2 (20);
lc_dev_phase varchar2 (20);
lc_dev_status varchar2 (20);
lc_message varchar2 (100);
ln_interval number := 2;
ln_max_wait number := 9999;
lc_status_code varchar2 (50);
lc_phase_code varchar2 (50);
begin
for rec_po_header in cur_po_header
loop
lv_verify_flag := ‘y’;
lv_error_message := null;
ln_org_id := null;
ln_agent_id := null;
lv_currency_code := null;
ln_vendor_id := null;
ln_vendor_site_id := null;
ln_ship_to_locn_id := null;
ln_bill_to_locn_id := null;
ln_vendor_contact_id := null;
ln_term_id := null;
lv_freight_code := null;
lv_fob_code := null;
lv_freight_terms := null;
lv_supplier_number := null;
–validation of vendor number
if rec_po_header.supplier_name is not null
then
begin
select distinct supplier_number
into lv_supplier_number
from xx_purc_quot_stg_tbl
where supplier_name = rec_po_header.supplier_name;
exception
when no_data_found
then
lv_supplier_number := null;
end;
if lv_supplier_number is not null
then
begin
select vendor_id
into ln_vendor_id
from ap_suppliers
where segment1 = lv_supplier_number
and (end_date_active is null or end_date_active > sysdate
);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘ error in getting the vendor id from vendor number’;
end;
else
–validation of vendor number
begin
select vendor_id
into ln_vendor_id
from ap_suppliers
where ( upper (vendor_name) =
upper (trim (rec_po_header.supplier_name))
or upper (vendor_name_alt) =
upper (trim (rec_po_header.supplier_name_alt))
)
and (end_date_active is null or end_date_active > sysdate
);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘vendor is not available’;
end;
end if;
else
lv_verify_flag := ‘n’;
lv_error_message := lv_error_message || ‘supplier cannot be null’;
–validation of vendor site
begin
select vendor_site_id
into ln_vendor_site_id
from ap_supplier_sites_all
where upper (vendor_site_code) =
upper (trim (rec_po_header.supplier_site_code))
and vendor_id = ln_vendor_id
and org_id = ln_ou_id
and (inactive_date is null or inactive_date > sysdate);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘vendor site is not available.’;
end;
lv_legacy_quotenum := trim (rec_po_header.quote_number);
if (ln_vendor_id is not null and ln_vendor_site_id is not null)
then
begin
select ‘y’
into lv_exists
from po_headers_all
where vendor_id = ln_vendor_id
and vendor_site_id = ln_vendor_site_id
and comments = lv_legacy_quotenum
and rownum = 1;
exception
when no_data_found
then
lv_exists := ‘n’;
end;
if lv_exists = ‘y’
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘vendor quote for the vendor,vendor site and legacy quote number already exists.’;
update xx_purc_quot_stg_tbl
set record_status = ‘ni’,
error_message = ‘error in header-‘ || lv_error_message
where quote_number = lv_legacy_quotenum;
else
if rec_po_header.quote_type_code is null
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘quote type cannot be null’;
end if;
–validation of organization
begin
select organization_id
into ln_org_id
from org_organization_definitions
where organization_code = rec_po_header.organization_name;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘not a valid organization.’;
end; –validation of buyer name
if rec_po_header.agent_name is not null
then
begin
select agent_id
into ln_agent_id
from po_agents_v
where upper (agent_name) =
upper (rec_po_header.agent_name)
and ( end_date_active is null
or end_date_active > sysdate
);
exception
when no_data_found
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘buyer is not valid.’;
end;
else
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘buyer cannot be null.’;
end if;
— validation of currency code
begin
select currency_code
into lv_currency_code
from fnd_currencies_vl
where enabled_flag = ‘y’
and currency_flag = ‘y’
and upper (currency_code) =
upper (trim (rec_po_header.currency_code));
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘currency code is not valid…’;
end;
— validation of ship to location/ deliver_loc_code
begin
select location_id
into ln_ship_to_locn_id
from hr_locations
where upper (location_code) =
upper (trim (rec_po_header.ship_to_location))
and (inactive_date is null or inactive_date > sysdate)
and nvl (ship_to_site_flag, ‘n’) = ‘y’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘ship to location is not available.’;
end;
–6) validation of bill to location code
— validation of ship to location/ deliver_loc_code
begin
select location_id
into ln_bill_to_locn_id
from hr_locations
where upper (location_code) =
upper (trim (rec_po_header.bill_to_location))
and (inactive_date is null or inactive_date > sysdate)
and nvl (bill_to_site_flag, ‘n’) = ‘y’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘bill to location is not available.’;
end;
–5) validation of vendor contact
if rec_po_header.vendor_contact is not null
then
begin
select distinct ascs.vendor_contact_id
into ln_vendor_contact_id
from hz_parties hp,
hz_party_sites hps,
hz_relationships hzr,
ap_supplier_contacts ascs,
hz_party_usg_assignments hpua,
hz_contact_points hcpp,
hz_contact_points hcpe,
hz_contact_points hcpf,
ap_suppliers aps,
ap_supplier_sites_all apssa
where 1 = 1
and nvl (hps.end_date_active, sysdate) >=
sysdate
and hzr.relationship_type = ‘contact’
and hzr.relationship_code = ‘contact_of’
and hzr.subject_type = ‘person’
and hzr.subject_table_name = ‘hz_parties’
and hzr.object_type = ‘organization’
and hzr.object_table_name = ‘hz_parties’
and hzr.status = ‘a’
and hps.party_id = hzr.object_id
and aps.vendor_id = apssa.vendor_id
and apssa.party_site_id =
ascs.org_party_site_id
and hzr.relationship_id =
ascs.relationship_id
and hzr.party_id = ascs.rel_party_id
and hps.party_site_id =
ascs.org_party_site_id
and hzr.subject_id = ascs.per_party_id
and hp.party_id = hzr.subject_id
and hpua.party_id = hp.party_id
and hpua.status_flag in (‘a’, ‘i’)
and hpua.party_usage_code =
‘supplier_contact’
and hcpp.owner_table_name(+) = ‘hz_parties’
and hcpp.owner_table_id(+) = hzr.party_id
and hcpp.phone_line_type(+) = ‘gen’
and hcpp.contact_point_type(+) = ‘phone’
and hcpp.primary_flag(+) = ‘y’
and hcpe.owner_table_name(+) = ‘hz_parties’
and hcpe.owner_table_id(+) = hzr.party_id
and hcpe.contact_point_type(+) = ’email’
and hcpe.primary_flag(+) = ‘y’
and ( hcpe.status is null
or hcpe.status in (‘a’, ‘i’)
)
and hcpf.owner_table_name(+) = ‘hz_parties’
and hcpf.owner_table_id(+) = hzr.party_id
and hcpf.contact_point_type(+) = ‘phone’
and hcpf.phone_line_type(+) = ‘fax’
and ( hcpf.status is null
or hcpf.status in (‘a’, ‘i’)
)
and ( hcpp.status is null
or hcpp.status in (‘a’, ‘i’)
)
and ( aps.vendor_name =
rec_po_header.supplier_name
or aps.segment1 = lv_supplier_number
)
and upper ( hp.person_last_name
|| ‘, ‘
|| hp.person_first_name
)
— || ‘ ‘ || middle_name || ‘ ‘
–|| last_name)
= upper (rec_po_header.vendor_contact);
exception
when no_data_found
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘vendor contact is not available.’;
when too_many_rows
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘multiple vendor contacts.’;
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting vendor contacts.’;
end;
end if;
if rec_po_header.payment_terms is not null
then
begin
select term_id
into ln_term_id
from ap_terms
where name = rec_po_header.payment_terms
and ( end_date_active is null
or end_date_active > sysdate
)
and nvl (enabled_flag, ‘y’) = ‘y’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘payment terms is not available.’;
end;
end if;
if rec_po_header.freight_carrier is not null
then
begin
select freight_code
into lv_freight_code
from org_freight ofr
where freight_code = rec_po_header.freight_carrier
–and (disable_date is null or disable_date > sysdate);
and nvl (ofr.disable_date, sysdate + 1) > sysdate
and ofr.organization_id = ln_org_id;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting freight carriers.’;
end;
end if;
if rec_po_header.fob is not null
then
begin
select lookup_code
into lv_fob_code
from po_lookup_codes
where lookup_type = ‘fob’
and lookup_code = rec_po_header.fob
and sysdate < nvl (inactive_date, sysdate + 1);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘error in getting fob.’;
end;
end if;
if rec_po_header.freight_terms is not null
then
begin
select lookup_code
into lv_freight_terms
from po_lookup_codes
where lookup_type = ‘freight terms’
and lookup_code = rec_po_header.freight_terms
and sysdate < nvl (inactive_date, sysdate + 1);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting freight terms.’;
end;
end if;
if rec_po_header.eff_start_date > trunc (sysdate)
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘effective start date must not be greater than system date’;
end if;
end if;–need to uncomment
end if;
if lv_verify_flag <> ‘n’
then
ln_interface_header_id := po_headers_interface_s.nextval;
insert into po_headers_interface
(interface_header_id, batch_id, action,
org_id, — po_header_id,
document_type_code, document_subtype,
document_num, currency_code, agent_id, vendor_id,
vendor_site_id, vendor_contact_id,
ship_to_location_id, bill_to_location_id, terms_id,
freight_carrier, fob, freight_terms,
note_to_vendor, comments,
effective_date,
expiration_date,
reply_date,
reply_method,
quote_warning_delay,
vendor_doc_num,
approval_required_flag,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute15, acceptance_required_flag, created_by,
creation_date, last_updated_by, last_update_date,
last_update_login
)
values (ln_interface_header_id, ln_batch_id, ‘original’,
ln_ou_id, — ln_po_header_id,
‘quotation’, ‘standard’,
null, –rec_po_header.quote_number,
lv_currency_code, ln_agent_id, ln_vendor_id,
ln_vendor_site_id, ln_vendor_contact_id,
ln_ship_to_locn_id, ln_bill_to_locn_id, ln_term_id,
lv_freight_code, lv_fob_code, lv_freight_terms,
rec_po_header.note_from_vendor, lv_legacy_quotenum,
–rec_po_header.quote_desc,
rec_po_header.eff_start_date,
rec_po_header.eff_end_date,
rec_po_header.reply_date,
rec_po_header.reply_method,
rec_po_header.quote_warning_delay,
rec_po_header.vendor_doc_num,
rec_po_header.approval_required_flag,
rec_po_header.hdr_attribute1,
rec_po_header.hdr_attribute2,
rec_po_header.hdr_attribute3,
rec_po_header.hdr_attribute4,
rec_po_header.hdr_attribute5,
rec_po_header.hdr_attribute6,
rec_po_header.hdr_attribute7,
rec_po_header.hdr_attribute15, ‘n’, gn_user_id,
sysdate, gn_user_id, sysdate,
gn_user_id
);
update xx_purc_quot_stg_tbl
set record_status = ‘i’,
error_message =
‘record inserted into po header interface table’
–lv_error_message
where quote_number = lv_legacy_quotenum;
commit;
for rec_po_lines in cur_po_lines (lv_legacy_quotenum)
loop
dbms_output.put_line (‘inside line loop’);
ln_line_type_id := null;
ln_line_type_id := null;
lv_unit_of_measure := null;
lv_verify_flag := ‘y’;
ln_interface_line_id := null;
ln_category_id := null;
ln_hazard_class_id := null;
lv_error_message := null;
ln_item_id := null;
lv_item_revision := null;
–1) validation of line type
begin
select line_type_id
into ln_line_type_id
from po_line_types_val_v
where upper (line_type) =
upper (rec_po_lines.quote_line_type);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘line type is not available.’;
end;
–2) validation of item number
if rec_po_lines.quote_line_item is not null
then
begin
select inventory_item_id
into ln_item_id
from mtl_system_items_b
where upper (segment1) =
upper (rec_po_lines.quote_line_item)
and organization_id = ln_org_id
and purchasing_enabled_flag = ‘y’
and purchasing_item_flag = ‘y’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘inventory item is not available.’;
end;
begin
select revision
into lv_item_revision
from apps.mtl_item_revisions
where 1 = 1
and organization_id = ln_org_id
and inventory_item_id = ln_item_id;
exception
when no_data_found
then
null;
end;
end if;
–3) validation of uom_code
begin
select uom_code –unit_of_measure
into lv_unit_of_measure
from mtl_units_of_measure_tl
where upper (unit_of_measure) =
upper (rec_po_lines.qt_line_uom_code)
and language = ‘us’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘uom is not available.’;
end;
if rec_po_lines.qt_line_item_desc is null
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘item description cannot be null.’;
end if;
if rec_po_lines.hazard_class is not null
then
begin
select hazard_class_id
into ln_hazard_class_id
from po_hazard_classes_vl
where hazard_class = rec_po_lines.hazard_class;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting the hazard class.’;
end;
end if;
if ln_item_id is not null
then
rec_po_lines.qt_line_item_category := null;
else
if rec_po_lines.qt_line_item_category is not null
then
begin
select category_id
into ln_category_id
from mtl_categories_v
where structure_name = ‘dmc po item category’
and category_concat_segs =
rec_po_lines.qt_line_item_category
and nvl (enabled_flag, ‘n’) = ‘y’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting the po item category.’;
end;
end if;
end if;
ln_ship_to_location_id := null;
lv_ship_to_location_code := null;
lv_unit_of_measure1 := null;
ln_ship_to_organization_id := null;
lc_ship_to_org_code := null;
ln_intf_line_locn_id := null;
ln_term_id1 := null;
lv_freight_code1 := null;
lv_fob_code1 := null;
lv_freight_terms1 := null;
–1) ship_to_location_code
begin
select location_id, location_code
into ln_ship_to_location_id, lv_ship_to_location_code
from hr_locations_all
where upper (location_code) =
upper (rec_po_lines.ship_to_location_code);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting ship to line locations’;
end;
–2) unit_of_measure
/* begin
select unit_of_measure
into lv_unit_of_measure1
from mtl_units_of_measure_tl
where upper (uom_code) =
upper (rec_po_lines.shipment_uom_code)
and language = ‘us’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘error in getting uom code’;
end;*/
lv_shipment_type := null; –‘price break’;
–end if;
–4) ship_to_organization_code
begin
select organization_id, organization_code
into ln_ship_to_organization_id, lc_ship_to_org_code
from org_organization_definitions
where organization_code =
rec_po_lines.ship_to_organization_code;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting ship to organization ‘;
end;
if rec_po_lines.line_payment_terms is not null
then
begin
select term_id
into ln_term_id1
from ap_terms
where name = rec_po_lines.line_payment_terms
and ( end_date_active is null
or end_date_active > sysdate
)
and nvl (enabled_flag, ‘y’) = ‘y’;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘line payment terms is not available.’;
end;
end if;
if rec_po_lines.line_freight_carrier is not null
then
begin
select freight_code
into lv_freight_code1
from org_freight ofr
where freight_code = rec_po_lines.line_freight_carrier
–and (disable_date is null or disable_date > sysdate);
and nvl (ofr.disable_date, sysdate + 1) > sysdate
and ofr.organization_id = ln_org_id;
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting line freight carriers.’;
end;
end if;
if rec_po_lines.line_fob is not null
then
begin
select lookup_code
into lv_fob_code1
from po_lookup_codes
where lookup_type = ‘fob’
and lookup_code = rec_po_lines.line_fob
and sysdate < nvl (inactive_date, sysdate + 1);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message || ‘error in getting line fob.’;
end;
end if;
if rec_po_lines.line_freight_terms is not null
then
begin
select lookup_code
into lv_freight_terms1
from po_lookup_codes
where lookup_type = ‘freight terms’
and lookup_code = rec_po_lines.line_freight_terms
and sysdate < nvl (inactive_date, sysdate + 1);
exception
when others
then
lv_verify_flag := ‘n’;
lv_error_message :=
lv_error_message
|| ‘error in getting line freight terms.’;
end;
end if;
if lv_verify_flag <> ‘n’
then
dbms_output.put_line (‘inside lines insert’);
ln_interface_line_id := po_lines_interface_s.nextval;
insert into po_lines_interface
(interface_header_id, interface_line_id,
action, line_type_id,
line_num,
item,
item_revision,
category,
uom_code,
–unit_of_measure,
quantity,
unit_price,
vendor_product_num,
un_number,
hazard_class,
min_order_quantity,
max_order_quantity,
note_to_vendor, created_by,
creation_date, last_updated_by,
last_update_date, last_update_login,
–item_revision,
–list_price_per_unit,
item_description,
line_attribute1,
line_attribute2,
ship_to_organization_id,
ship_to_location_id,
effective_date,
expiration_date, fob,
freight_carrier, freight_terms,
terms_id, shipment_attribute2,
shipment_attribute3,
lead_time_unit,
lead_time
)
values (ln_interface_header_id, ln_interface_line_id,
‘add’, –action
ln_line_type_id,
rec_po_lines.quote_line_num, –line_num
rec_po_lines.quote_line_item, –item
lv_item_revision,
rec_po_lines.qt_line_item_category,
–rec_po_lines.qt_line_uom_code, –uom_code
lv_unit_of_measure, rec_po_lines.qt_line_qty,
rec_po_lines.qt_line_unit_price, –unit_price
rec_po_lines.vendor_product_num,
rec_po_lines.un_number,
rec_po_lines.hazard_class,
rec_po_lines.min_order_quantity,
rec_po_lines.max_order_quantity,
— rec_po_lines.project_number,
rec_po_lines.line_note_fm_vendor, gn_user_id,
sysdate, gn_user_id,
sysdate, gn_user_id,
–list_price_per_unit,
rec_po_lines.qt_line_item_desc,
rec_po_lines.line_attribute1,
rec_po_lines.line_attribute2,
ln_ship_to_organization_id,
ln_ship_to_location_id,
rec_po_lines.line_eff_start_dt,
rec_po_lines.line_eff_end_dt, lv_fob_code1,
lv_freight_code1, lv_freight_terms1,
ln_term_id1, rec_po_lines.shp_attribute2,
rec_po_lines.shp_attribute3,
rec_po_lines.lead_time_uom,
rec_po_lines.lead_time
);
update xx_purc_quot_stg_tbl
set record_status = ‘li’,
error_message =
‘record inserted into po lines interface table’
–lv_error_message
where quote_number = lv_legacy_quotenum
and quote_line_num = rec_po_lines.quote_line_num;
commit;
else
update xx_purc_quot_stg_tbl
set record_status = ‘ni’,
error_message = ‘error in lines-‘ || lv_error_message
–lv_error_message
where quote_number = lv_legacy_quotenum
and quote_line_num = rec_po_lines.quote_line_num;
end if;
end loop;
commit;
else
update xx_purc_quot_stg_tbl
set record_status = ‘ni’,
error_message = ‘error in header-‘ || lv_error_message
where quote_number = lv_legacy_quotenum;
commit;
end if;
end loop;
commit;
select count (*)
into ln_valid_records
from xx_purc_quot_stg_tbl
where record_status = ‘li’;
fnd_file.put_line (fnd_file.log, (‘valid records’ || ln_valid_records));
if ln_valid_records > 0
then
begin
fnd_global.apps_initialize (gn_user_id,
gn_responsibility_id,
gn_respappl_id
);
–mo_global.set_policy_context(‘s’,gn_org_id);
— fnd_request.set_org_id(gn_org_id);
fnd_file.put_line (fnd_file.log,
(‘*** submitting import price catalogs ***’)
);
ln_request_id :=
fnd_request.submit_request (application => ‘po’,
program => ‘poxpdoi’,
description => null,
start_time => null,
sub_request => null,
argument1 => null, –buyer
argument2 => ‘quotation’,
— document type
argument3 => ‘catalog’,
–document subtype
argument4 => ‘n’,
–create items
argument5 => ‘n’,
–ssourcing rules
argument6 => ‘approved’
–approved status
);
fnd_file.put_line (fnd_file.log, ‘request id- ‘ || ln_request_id);
dbms_output.put_line (‘request id- ‘ || ln_request_id);
if ln_request_id > 0
then
commit;
lb_complete :=
fnd_concurrent.wait_for_request
(request_id => ln_request_id,
interval => ln_interval,
max_wait => ln_max_wait,
phase => lc_phase,
status => lc_status,
dev_phase => lc_dev_phase,
dev_status => lc_dev_status,
message => lc_message
);
commit;
if upper (lc_dev_phase) in (‘complete’)
then
dbms_output.put_line
(‘*** complete customer items import program ***’);
fnd_file.put_line
(fnd_file.log,
(‘*** import purchase quote completed successfully ***’)
);
else
dbms_output.put_line
(‘*** not complete purchase quote import program ***’);
fnd_file.put_line
(fnd_file.log,
(‘*** import price catalog program not completed successfully ***’)
);
end if;
end if;
–l_error_msg:= sqlerrm;
end;
end if;
exception
when others
then
fnd_file.put_line (fnd_file.log,
( ‘error in load vendor quotation package-‘
|| sqlerrm
)
);
end;
END;
