[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE create_update_asl(
p_asl_rec IN po_approved_supplier_list_rec
, p_asl_attr_rec IN po_asl_attributes_rec
, p_asl_doc_rec IN po_asl_documents_rec
, p_chv_auth_rec IN chv_authorizations_rec
, p_capacity_rec IN po_supplier_item_capacity_rec
, p_tolerance_rec IN po_supplier_item_tolerance_rec
, p_commit IN VARCHAR2
, x_session_key OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_return_msg OUT NOCOPY VARCHAR2
)
IS
l_progress NUMBER := 0;
po_asl_api_pvt.Log('START ::: PROCEDURE create_update_asl ');
DELETE FROM po_approved_supplier_list_gt;
DELETE FROM po_asl_documents_gt ;
DELETE FROM chv_authorizations_gt;
DELETE FROM po_asl_attributes_gt;
DELETE FROM po_supplier_item_capacity_gt;
DELETE FROM po_supplier_item_tolerance_gt;
SELECT po_session_gt_s.NEXTVAL INTO x_session_key FROM dual;
SELECT Count(*)
INTO invalids
FROM po_asl_attributes_gt
WHERE user_key NOT IN
(SELECT user_key
FROM po_approved_supplier_list_gt);
SELECT Count(*)
INTO invalids
FROM po_asl_documents_gt
WHERE NOT EXISTS
(SELECT user_key
FROM po_approved_supplier_list_gt);
SELECT Count(*)
INTO invalids
FROM chv_authorizations_gt
WHERE NOT EXISTS
(SELECT user_key
FROM po_approved_supplier_list_gt);
SELECT Count(*)
INTO invalids
FROM po_supplier_item_capacity_gt
WHERE NOT EXISTS
(SELECT user_key
FROM po_approved_supplier_list_gt);
SELECT Count(*)
INTO invalids
FROM po_supplier_item_tolerance_gt
WHERE NOT EXISTS
(SELECT user_key
FROM po_approved_supplier_list_gt);
po_asl_api_pvt.log('END ::: PROCEDURE create_update_asl ');
po_asl_api_pvt.log('create_update_asl : Existing the process
due to duplicate user_keys');
po_asl_api_pvt.log('create_update_asl : Parent user_key not found');
po_asl_api_pvt.log('create_update_asl : when others exception at '
|| l_progress || ';' || SQLERRM );
END create_update_asl;
INSERT INTO po_approved_supplier_list_gt (
session_key ,
user_key ,
process_action ,
process_status ,
asl_id ,
using_organization_id ,
using_organization_dsp ,
owning_organization_id ,
owning_organization_dsp ,
vendor_business_type ,
asl_status_id ,
asl_status_dsp ,
manufacturer_id ,
manufacturer_dsp ,
vendor_id ,
vendor_dsp ,
item_id ,
item_dsp ,
category_id ,
category_dsp ,
vendor_site_id ,
vendor_site_dsp ,
primary_vendor_item ,
manufacturer_asl_id ,
manufacturer_asl_dsp ,
review_by_date ,
comments ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
disable_flag
)
VALUES (
g_session_key ,
p_asl_rec.user_key(l_index) ,
p_asl_rec.process_action(l_index) ,
PO_ASL_API_PVT.g_STATUS_PENDING ,
NULL ,
Decode(p_asl_rec.global_flag(l_index), 'Y', -1) ,
NULL ,
p_asl_rec.owning_organization_id(l_index) ,
p_asl_rec.owning_organization_dsp(l_index) ,
p_asl_rec.vendor_business_type(l_index) ,
p_asl_rec.asl_status_id(l_index) ,
p_asl_rec.asl_status_dsp(l_index) ,
p_asl_rec.manufacturer_id(l_index) ,
p_asl_rec.manufacturer_dsp(l_index) ,
p_asl_rec.vendor_id(l_index) ,
p_asl_rec.vendor_dsp(l_index) ,
p_asl_rec.item_id(l_index) ,
p_asl_rec.item_dsp(l_index) ,
p_asl_rec.category_id(l_index) ,
p_asl_rec.category_dsp(l_index) ,
p_asl_rec.vendor_site_id(l_index) ,
p_asl_rec.vendor_site_dsp(l_index) ,
p_asl_rec.primary_vendor_item(l_index) ,
p_asl_rec.manufacturer_asl_id(l_index) ,
p_asl_rec.manufacturer_asl_dsp(l_index) ,
p_asl_rec.review_by_date(l_index) ,
p_asl_rec.comments(l_index) ,
p_asl_rec.attribute_category(l_index) ,
p_asl_rec.attribute1(l_index) ,
p_asl_rec.attribute2(l_index) ,
p_asl_rec.attribute3(l_index) ,
p_asl_rec.attribute4(l_index) ,
p_asl_rec.attribute5(l_index) ,
p_asl_rec.attribute6(l_index) ,
p_asl_rec.attribute7(l_index) ,
p_asl_rec.attribute8(l_index) ,
p_asl_rec.attribute9(l_index) ,
p_asl_rec.attribute10(l_index) ,
p_asl_rec.attribute11(l_index) ,
p_asl_rec.attribute12(l_index) ,
p_asl_rec.attribute13(l_index) ,
p_asl_rec.attribute14(l_index) ,
p_asl_rec.attribute15(l_index) ,
p_asl_rec.request_id(l_index) ,
p_asl_rec.program_application_id(l_index) ,
p_asl_rec.program_id(l_index) ,
p_asl_rec.program_update_date(l_index) ,
p_asl_rec.disable_flag(l_index)
);
SELECT Count(*)
INTO counter
FROM (SELECT Count(*)
FROM po_approved_supplier_list_gt
GROUP BY user_key
HAVING Count(*) > 1);
INSERT INTO po_asl_attributes_gt (
session_key ,
user_key ,
process_action ,
asl_id ,
using_organization_id ,
using_organization_dsp ,
document_sourcing_method ,
release_generation_method ,
release_generation_method_dsp ,
purchasing_unit_of_measure_dsp ,
enable_plan_schedule_flag_dsp ,
enable_ship_schedule_flag_dsp ,
plan_schedule_type ,
plan_schedule_type_dsp ,
ship_schedule_type ,
ship_schedule_type_dsp ,
plan_bucket_pattern_id ,
plan_bucket_pattern_dsp ,
ship_bucket_pattern_id ,
ship_bucket_pattern_dsp ,
enable_autoschedule_flag_dsp ,
scheduler_id ,
scheduler_dsp ,
enable_authorizations_flag_dsp ,
vendor_id ,
vendor_dsp ,
vendor_site_id ,
vendor_site_dsp ,
item_id ,
item_dsp ,
category_id ,
category_dsp ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
price_update_tolerance_dsp ,
processing_lead_time_dsp ,
min_order_qty_dsp ,
fixed_lot_multiple_dsp ,
delivery_calendar_dsp ,
country_of_origin_code_dsp ,
enable_vmi_flag_dsp ,
vmi_min_qty_dsp ,
vmi_max_qty_dsp ,
enable_vmi_auto_replenish_flag ,
vmi_replenishment_approval ,
vmi_replenishment_approval_dsp ,
consigned_from_supp_flag_dsp ,
last_billing_date ,
consigned_billing_cycle_dsp ,
consume_on_aging_flag_dsp ,
aging_period_dsp ,
replenishment_method ,
replenishment_method_dsp ,
vmi_min_days_dsp ,
vmi_max_days_dsp ,
fixed_order_quantity_dsp ,
forecast_horizon_dsp
)
VALUES (
g_session_key ,
p_asl_attr_rec.user_key(l_index) ,
p_asl_attr_rec.process_action(l_index) ,
NULL ,
p_asl_attr_rec.using_organization_id(l_index) ,
p_asl_attr_rec.using_organization_dsp(l_index) ,
'ASL' ,
p_asl_attr_rec.release_generation_method(l_index) ,
p_asl_attr_rec.release_generation_method_dsp(l_index) ,
p_asl_attr_rec.purchasing_unit_of_measure_dsp(l_index) ,
p_asl_attr_rec.enable_plan_schedule_flag_dsp(l_index) ,
p_asl_attr_rec.enable_ship_schedule_flag_dsp(l_index) ,
p_asl_attr_rec.plan_schedule_type(l_index) ,
p_asl_attr_rec.plan_schedule_type_dsp(l_index) ,
p_asl_attr_rec.ship_schedule_type(l_index) ,
p_asl_attr_rec.ship_schedule_type_dsp(l_index) ,
p_asl_attr_rec.plan_bucket_pattern_id(l_index) ,
p_asl_attr_rec.plan_bucket_pattern_dsp(l_index) ,
p_asl_attr_rec.ship_bucket_pattern_id(l_index) ,
p_asl_attr_rec.ship_bucket_pattern_dsp(l_index) ,
p_asl_attr_rec.enable_autoschedule_flag_dsp(l_index) ,
p_asl_attr_rec.scheduler_id(l_index) ,
p_asl_attr_rec.scheduler_dsp(l_index) ,
p_asl_attr_rec.enable_authorizations_flag_dsp(l_index) ,
p_asl_attr_rec.vendor_id(l_index) ,
p_asl_attr_rec.vendor_dsp(l_index) ,
p_asl_attr_rec.vendor_site_id(l_index) ,
p_asl_attr_rec.vendor_site_dsp(l_index) ,
p_asl_attr_rec.item_id(l_index) ,
p_asl_attr_rec.item_dsp(l_index) ,
p_asl_attr_rec.category_id(l_index) ,
p_asl_attr_rec.category_dsp(l_index) ,
p_asl_attr_rec.attribute_category(l_index) ,
p_asl_attr_rec.attribute1(l_index) ,
p_asl_attr_rec.attribute2(l_index) ,
p_asl_attr_rec.attribute3(l_index) ,
p_asl_attr_rec.attribute4(l_index) ,
p_asl_attr_rec.attribute5(l_index) ,
p_asl_attr_rec.attribute6(l_index) ,
p_asl_attr_rec.attribute7(l_index) ,
p_asl_attr_rec.attribute8(l_index) ,
p_asl_attr_rec.attribute9(l_index) ,
p_asl_attr_rec.attribute10(l_index) ,
p_asl_attr_rec.attribute11(l_index) ,
p_asl_attr_rec.attribute12(l_index) ,
p_asl_attr_rec.attribute13(l_index) ,
p_asl_attr_rec.attribute14(l_index) ,
p_asl_attr_rec.attribute15(l_index) ,
p_asl_attr_rec.request_id(l_index) ,
p_asl_attr_rec.program_application_id(l_index) ,
p_asl_attr_rec.program_id(l_index) ,
p_asl_attr_rec.program_update_date(l_index) ,
p_asl_attr_rec.price_update_tolerance_dsp(l_index) ,
p_asl_attr_rec.processing_lead_time_dsp(l_index) ,
p_asl_attr_rec.min_order_qty_dsp(l_index) ,
p_asl_attr_rec.fixed_lot_multiple_dsp(l_index) ,
p_asl_attr_rec.delivery_calendar_dsp(l_index) ,
p_asl_attr_rec.country_of_origin_code_dsp(l_index) ,
p_asl_attr_rec.enable_vmi_flag_dsp(l_index) ,
p_asl_attr_rec.vmi_min_qty_dsp(l_index) ,
p_asl_attr_rec.vmi_max_qty_dsp(l_index) ,
p_asl_attr_rec.enable_vmi_auto_replenish_flag(l_index) ,
p_asl_attr_rec.vmi_replenishment_approval(l_index) ,
p_asl_attr_rec.vmi_replenishment_approval_dsp(l_index) ,
p_asl_attr_rec.consigned_from_supp_flag_dsp(l_index) ,
p_asl_attr_rec.last_billing_date(l_index) ,
p_asl_attr_rec.consigned_billing_cycle_dsp(l_index) ,
p_asl_attr_rec.consume_on_aging_flag_dsp(l_index) ,
p_asl_attr_rec.aging_period_dsp(l_index) ,
p_asl_attr_rec.replenishment_method(l_index) ,
p_asl_attr_rec.replenishment_method_dsp(l_index) ,
p_asl_attr_rec.vmi_min_days_dsp(l_index) ,
p_asl_attr_rec.vmi_max_days_dsp(l_index) ,
p_asl_attr_rec.fixed_order_quantity_dsp(l_index) ,
p_asl_attr_rec.forecast_horizon_dsp(l_index)
);
INSERT INTO po_asl_documents_gt (
session_key ,
user_key ,
process_action ,
asl_id ,
using_organization_id ,
using_organization_dsp ,
sequence_num ,
document_type_code ,
document_type_dsp ,
document_header_id ,
document_header_dsp ,
document_line_id ,
document_line_num_dsp ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
org_id
)
VALUES (
g_session_key ,
p_asl_doc_rec.user_key(l_index) ,
p_asl_doc_rec.process_action(l_index) ,
NULL ,
p_asl_doc_rec.using_organization_id(l_index) ,
p_asl_doc_rec.using_organization_dsp(l_index) ,
p_asl_doc_rec.sequence_num(l_index) ,
p_asl_doc_rec.document_type_code(l_index) ,
p_asl_doc_rec.document_type_dsp(l_index) ,
p_asl_doc_rec.document_header_id(l_index) ,
p_asl_doc_rec.document_header_dsp(l_index) ,
p_asl_doc_rec.document_line_id(l_index) ,
p_asl_doc_rec.document_line_num_dsp(l_index) ,
p_asl_doc_rec.attribute_category(l_index) ,
p_asl_doc_rec.attribute1(l_index) ,
p_asl_doc_rec.attribute2(l_index) ,
p_asl_doc_rec.attribute3(l_index) ,
p_asl_doc_rec.attribute4(l_index) ,
p_asl_doc_rec.attribute5(l_index) ,
p_asl_doc_rec.attribute6(l_index) ,
p_asl_doc_rec.attribute7(l_index) ,
p_asl_doc_rec.attribute8(l_index) ,
p_asl_doc_rec.attribute9(l_index) ,
p_asl_doc_rec.attribute10(l_index) ,
p_asl_doc_rec.attribute11(l_index) ,
p_asl_doc_rec.attribute12(l_index) ,
p_asl_doc_rec.attribute13(l_index) ,
p_asl_doc_rec.attribute14(l_index) ,
p_asl_doc_rec.attribute15(l_index) ,
p_asl_doc_rec.request_id(l_index) ,
p_asl_doc_rec.program_application_id(l_index) ,
p_asl_doc_rec.program_id(l_index) ,
p_asl_doc_rec.program_update_date(l_index) ,
p_asl_doc_rec.org_id(l_index)
);
INSERT INTO chv_authorizations_gt (
session_key ,
user_key ,
process_action ,
reference_id ,
reference_type ,
authorization_code ,
authorization_code_dsp ,
authorization_sequence_dsp ,
timefence_days_dsp ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
using_organization_id ,
using_organization_dsp
)
VALUES (
g_session_key ,
p_chv_auth_rec.user_key(l_index) ,
p_chv_auth_rec.process_action(l_index) ,
NULL ,
'ASL' ,
p_chv_auth_rec.authorization_code(l_index) ,
p_chv_auth_rec.authorization_code_dsp(l_index) ,
p_chv_auth_rec.authorization_sequence_dsp(l_index) ,
p_chv_auth_rec.timefence_days_dsp(l_index) ,
p_chv_auth_rec.attribute_category(l_index) ,
p_chv_auth_rec.attribute1(l_index) ,
p_chv_auth_rec.attribute2(l_index) ,
p_chv_auth_rec.attribute3(l_index) ,
p_chv_auth_rec.attribute4(l_index) ,
p_chv_auth_rec.attribute5(l_index) ,
p_chv_auth_rec.attribute6(l_index) ,
p_chv_auth_rec.attribute7(l_index) ,
p_chv_auth_rec.attribute8(l_index) ,
p_chv_auth_rec.attribute9(l_index) ,
p_chv_auth_rec.attribute10(l_index) ,
p_chv_auth_rec.attribute11(l_index) ,
p_chv_auth_rec.attribute12(l_index) ,
p_chv_auth_rec.attribute13(l_index) ,
p_chv_auth_rec.attribute14(l_index) ,
p_chv_auth_rec.attribute15(l_index) ,
p_chv_auth_rec.request_id(l_index) ,
p_chv_auth_rec.program_application_id(l_index) ,
p_chv_auth_rec.program_id(l_index) ,
p_chv_auth_rec.program_update_date(l_index) ,
p_chv_auth_rec.using_organization_id(l_index) ,
p_chv_auth_rec.using_organization_dsp(l_index)
);
INSERT INTO po_supplier_item_capacity_gt (
session_key ,
user_key ,
process_action ,
capacity_id ,
asl_id ,
using_organization_id ,
using_organization_dsp ,
from_date_dsp ,
to_date_dsp ,
capacity_per_day_dsp ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date
)
VALUES (
g_session_key ,
p_capacity_rec.user_key(l_index) ,
p_capacity_rec.process_action(l_index) ,
Decode(p_capacity_rec.process_action(l_index),
po_asl_api_pub.g_action_add,
po_supplier_item_capacity_s.NEXTVAL, NULL),
NULL ,
p_capacity_rec.using_organization_id(l_index) ,
p_capacity_rec.using_organization_dsp(l_index) ,
p_capacity_rec.from_date_dsp(l_index) ,
p_capacity_rec.to_date_dsp(l_index) ,
p_capacity_rec.capacity_per_day_dsp(l_index) ,
p_capacity_rec.attribute_category(l_index) ,
p_capacity_rec.attribute1(l_index) ,
p_capacity_rec.attribute2(l_index) ,
p_capacity_rec.attribute3(l_index) ,
p_capacity_rec.attribute4(l_index) ,
p_capacity_rec.attribute5(l_index) ,
p_capacity_rec.attribute6(l_index) ,
p_capacity_rec.attribute7(l_index) ,
p_capacity_rec.attribute8(l_index) ,
p_capacity_rec.attribute9(l_index) ,
p_capacity_rec.attribute10(l_index) ,
p_capacity_rec.attribute11(l_index) ,
p_capacity_rec.attribute12(l_index) ,
p_capacity_rec.attribute13(l_index) ,
p_capacity_rec.attribute14(l_index) ,
p_capacity_rec.attribute15(l_index) ,
p_capacity_rec.request_id(l_index) ,
p_capacity_rec.program_application_id(l_index) ,
p_capacity_rec.program_id(l_index) ,
p_capacity_rec.program_update_date(l_index)
);
INSERT INTO po_supplier_item_tolerance_gt (
session_key ,
user_key ,
process_action ,
asl_id ,
using_organization_id ,
using_organization_dsp ,
number_of_days_dsp ,
tolerance_dsp ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date
)
VALUES (
g_session_key ,
p_tolerance_rec.user_key(l_index) ,
p_tolerance_rec.process_action(l_index) ,
NULL ,
p_tolerance_rec.using_organization_id(l_index) ,
p_tolerance_rec.using_organization_dsp(l_index) ,
p_tolerance_rec.number_of_days_dsp(l_index) ,
p_tolerance_rec.tolerance_dsp(l_index) ,
p_tolerance_rec.attribute_category(l_index) ,
p_tolerance_rec.attribute1(l_index) ,
p_tolerance_rec.attribute2(l_index) ,
p_tolerance_rec.attribute3(l_index) ,
p_tolerance_rec.attribute4(l_index) ,
p_tolerance_rec.attribute5(l_index) ,
p_tolerance_rec.attribute6(l_index) ,
p_tolerance_rec.attribute7(l_index) ,
p_tolerance_rec.attribute8(l_index) ,
p_tolerance_rec.attribute9(l_index) ,
p_tolerance_rec.attribute10(l_index) ,
p_tolerance_rec.attribute11(l_index) ,
p_tolerance_rec.attribute12(l_index) ,
p_tolerance_rec.attribute13(l_index) ,
p_tolerance_rec.attribute14(l_index) ,
p_tolerance_rec.attribute15(l_index) ,
p_tolerance_rec.request_id(l_index) ,
p_tolerance_rec.program_application_id(l_index) ,
p_tolerance_rec.program_id(l_index) ,
p_tolerance_rec.program_update_date(l_index)
);
UPDATE po_approved_supplier_list_gt past
SET past.owning_organization_id =
(SELECT hout.organization_id
FROM hr_all_organization_units_tl hout
WHERE hout.name = past.owning_organization_dsp
AND hout.language = UserEnv('lang')
AND ROWNUM < 2)
WHERE past.owning_organization_dsp IS NOT NULL;
UPDATE po_approved_supplier_list_gt past
SET past.using_organization_id = owning_organization_id
WHERE past.using_organization_id <> -1;
UPDATE po_approved_supplier_list_gt past
SET past.asl_status_id =
(SELECT pas.status_id
FROM po_asl_statuses pas
WHERE past.asl_status_dsp = pas.status
AND ROWNUM < 2)
WHERE past.asl_status_dsp IS NOT NULL;
UPDATE po_approved_supplier_list_gt past
SET past.manufacturer_id =
(SELECT mm.manufacturer_id
FROM mtl_manufacturers mm
WHERE mm.manufacturer_name = past.manufacturer_dsp
AND ROWNUM < 2)
WHERE past.manufacturer_dsp IS NOT NULL;
UPDATE po_approved_supplier_list_gt past
SET past.vendor_id =
(SELECT pv.vendor_id
FROM po_vendors pv
WHERE pv.vendor_name = past.vendor_dsp
AND ROWNUM < 2)
WHERE past.vendor_dsp IS NOT NULL;
UPDATE po_approved_supplier_list_gt past
SET past.item_id =
(SELECT msi.inventory_item_id
FROM mtl_system_items_kfv msi
WHERE msi.concatenated_segments = past.item_dsp
AND msi.organization_id = past.owning_organization_id
AND ROWNUM < 2)
WHERE past.item_dsp IS NOT NULL;
UPDATE po_approved_supplier_list_gt past
SET past.category_id =
(SELECT mc.category_id
FROM mtl_categories_kfv mc
WHERE mc.concatenated_segments = past.category_dsp
AND ROWNUM < 2)
WHERE past.category_dsp IS NOT NULL;
UPDATE po_approved_supplier_list_gt past
SET past.vendor_site_id =
(SELECT pvs.vendor_site_id
FROM po_vendor_sites_all pvs
WHERE pvs.vendor_site_code = past.vendor_site_dsp
AND pvs.vendor_id = past.vendor_id
AND pvs.org_id = past.owning_organization_id
AND ROWNUM < 2)
WHERE past.vendor_site_dsp IS NOT NULL;
UPDATE po_approved_supplier_list_gt asl1
SET asl1.manufacturer_asl_id =
(SELECT pasl.asl_id
FROM po_approved_supplier_list pasl,
mtl_manufacturers mm,
po_asl_statuses past,
po_asl_status_rules psr
WHERE pasl.manufacturer_id = mm.manufacturer_id
AND pasl.asl_status_id = past.status_id
AND psr.status_id = past.status_id
AND psr.business_rule = '4_DISTRIBUTOR_MFR_LINK'
AND psr.allow_action_flag = 'Y'
AND (pasl.using_organization_id = -1
OR pasl.using_organization_id = asl1.using_organization_id)
AND ((pasl.item_id = asl1.item_id
AND (pasl.category_id IS NULL
AND asl1.category_id IS NULL
)
)
OR (pasl.category_id = asl1.category_id
AND (pasl.item_id IS NULL
AND asl1.item_id IS NULL
)
)
)
)
WHERE asl1.manufacturer_asl_dsp IS NOT NULL;
UPDATE po_asl_documents_gt padt
SET padt.using_organization_id =
(SELECT hout.organization_id
FROM hr_all_organization_units_tl hout
WHERE hout.name = padt.using_organization_dsp
AND hout.language = UserEnv('lang')
AND ROWNUM < 2)
WHERE padt.using_organization_dsp IS NOT NULL;
UPDATE po_asl_documents_gt padt
SET padt.document_type_code =
(SELECT polc.lookup_code
FROM po_lookup_codes polc
WHERE polc.lookup_type = 'SOURCE DOCUMENT TYPE'
AND Upper(polc.displayed_field) = Upper(padt.document_type_dsp)
AND ROWNUM < 2)
WHERE padt.document_type_dsp IS NOT NULL;
UPDATE po_asl_documents_gt padt
SET padt.document_header_id =
get_doc_header (p_user_key => padt.user_key ,
p_doc_type => padt.document_type_code ,
p_using_org_id => padt.using_organization_id ,
p_segment => padt.document_header_dsp)
WHERE padt.document_header_dsp IS NOT NULL;
UPDATE po_asl_documents_gt padt
SET padt.document_line_id =
get_doc_line_id(p_user_key => padt.user_key ,
p_header_id => padt.document_header_id ,
p_using_org_id => padt.using_organization_id ,
p_line_num => padt.document_line_num_dsp)
WHERE document_line_num_dsp IS NOT NULL;
po_asl_api_pvt.log('derive_ids_podoc UPDATE po_asl_documents_gt rowcount:'
|| SQL%ROWCOUNT);
UPDATE chv_authorizations_gt chv
SET chv.authorization_code =
(SELECT polc.lookup_code
FROM po_lookup_codes polc
WHERE polc.lookup_type = 'AUTHORIZATION_TYPE'
AND polc.displayed_field = chv.authorization_code_dsp
AND ROWNUM < 2)
WHERE chv.authorization_code_dsp IS NOT NULL;
UPDATE chv_authorizations_gt chv
SET chv.using_organization_id =
(SELECT hout.organization_id
FROM hr_all_organization_units_tl hout
WHERE hout.name = chv.using_organization_dsp
AND hout.language = UserEnv('lang')
AND ROWNUM < 2)
WHERE chv.using_organization_dsp IS NOT NULL;
po_asl_api_pvt.log('derive_ids_ch_auth update chv_authorizations_gt rowcount:'
|| SQL%ROWCOUNT);
UPDATE po_asl_attributes_gt paa
SET paa.using_organization_id =
(SELECT hout.organization_id
FROM hr_all_organization_units_tl hout
WHERE hout.name = paa.using_organization_dsp
AND hout.language = UserEnv('lang')
AND ROWNUM < 2)
WHERE paa.using_organization_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.release_generation_method =
(SELECT polc.lookup_code
FROM po_lookup_codes polc
WHERE polc.lookup_type = 'DOC GENERATION METHOD'
AND polc.displayed_field = paa.release_generation_method_dsp
AND ROWNUM < 2)
WHERE paa.release_generation_method_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.plan_schedule_type =
(SELECT polc.lookup_code
FROM po_lookup_codes polc
WHERE polc.lookup_type = 'PLAN_SCHEDULE_SUBTYPE'
AND polc.displayed_field = paa.plan_schedule_type_dsp
AND ROWNUM < 2)
WHERE paa.plan_schedule_type_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.ship_schedule_type =
(SELECT polc.lookup_code
FROM po_lookup_codes polc
WHERE polc.lookup_type = 'SHIP_SCHEDULE_SUBTYPE'
AND polc.displayed_field = paa.ship_schedule_type_dsp
AND ROWNUM < 2)
WHERE paa.ship_schedule_type_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.plan_bucket_pattern_id =
(SELECT cbp.bucket_pattern_id
FROM chv_bucket_patterns cbp
WHERE cbp.bucket_pattern_name = paa.plan_bucket_pattern_dsp
AND ROWNUM < 2)
WHERE paa.plan_bucket_pattern_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.ship_bucket_pattern_id =
(SELECT cbp.bucket_pattern_id
FROM chv_bucket_patterns cbp
WHERE cbp.bucket_pattern_name = paa.ship_bucket_pattern_dsp
AND ROWNUM < 2)
WHERE paa.ship_bucket_pattern_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.scheduler_id =
(SELECT ppf.person_id
FROM per_people_f ppf
WHERE ppf.full_name = paa.scheduler_dsp
AND ROWNUM < 2)
WHERE paa.scheduler_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.vendor_id =
(SELECT pv.vendor_id
FROM po_vendors pv
WHERE pv.vendor_name = paa.vendor_dsp
AND ROWNUM < 2)
WHERE paa.vendor_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.vendor_site_id =
(SELECT pvs.vendor_site_id
FROM po_vendor_sites_all pvs
WHERE pvs.vendor_site_code = paa.vendor_site_dsp
AND pvs.vendor_id = paa.vendor_id
AND pvs.org_id IN
(SELECT owning_organization_id
FROM po_approved_supplier_list_gt past
WHERE paa.user_key = past.user_key)
AND ROWNUM < 2)
WHERE paa.vendor_site_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.item_id =
(SELECT msi.inventory_item_id
FROM mtl_system_items_kfv msi
WHERE msi.concatenated_segments= paa.item_dsp
AND ROWNUM < 2)
WHERE paa.item_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.category_id =
(SELECT mc.category_id
FROM mtl_categories_kfv mc
WHERE mc.concatenated_segments = paa.category_dsp
AND ROWNUM < 2)
WHERE paa.category_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.vmi_replenishment_approval =
Decode(paa.vmi_replenishment_approval_dsp,
'Supplier or Buyer', 'SUPPLIER_OR_BUYER',
'None' , 'NONE',
'Buyer' , 'BUYER')
WHERE paa.vmi_replenishment_approval_dsp IS NOT NULL;
UPDATE po_asl_attributes_gt paa
SET paa.replenishment_method =
Decode(paa.replenishment_method_dsp,
'Min - Max Quantities' , 1,
'Min - Max Days' , 2,
'Min Qty and Fixed Order Qty' , 3,
'Min Days and Fixed Order Qty' , 4)
WHERE paa.replenishment_method_dsp IS NOT NULL;
po_asl_api_pvt.log('derive_ids_asl_attr UPDATE po_asl_attributes_gt rowcount:'
|| l_progress || ';' || SQL%ROWCOUNT);
UPDATE po_supplier_item_capacity_gt poic
SET poic.using_organization_id =
(SELECT hout.organization_id
FROM hr_all_organization_units_tl hout
WHERE hout.name = poic.using_organization_dsp
AND hout.language = UserEnv('lang')
AND ROWNUM < 2)
WHERE poic.using_organization_dsp IS NOT NULL;
po_asl_api_pvt.log('derive_ids_sup_cap UPDATE po_supplier_item_capacity_gt rowcount:'
|| SQL%ROWCOUNT);
UPDATE po_supplier_item_tolerance_gt poit
SET poit.using_organization_id =
(SELECT hout.organization_id
FROM hr_all_organization_units_tl hout
WHERE hout.name = poit.using_organization_dsp
AND hout.language = UserEnv('lang')
AND ROWNUM < 2)
WHERE poit.using_organization_dsp IS NOT NULL;
po_asl_api_pvt.log('derive_ids_sup_tol UPDATE po_supplier_item_tolerance_gt rowcount:'
|| SQL%ROWCOUNT);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM
(SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','OWNING_ORG_NOT_FOUND') AS msg
FROM po_approved_supplier_list_gt
WHERE owning_organization_dsp IS NOT NULL
AND owning_organization_id IS NULL
UNION ALL
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','STATUS_NOT_FOUND') AS msg
FROM po_approved_supplier_list_gt
WHERE asl_status_dsp IS NOT NULL
AND asl_status_id IS NULL
UNION ALL
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','MANUFACTURER_NOT_FOUND') AS msg
FROM po_approved_supplier_list_gt
WHERE (manufacturer_dsp IS NOT NULL
AND manufacturer_id IS NULL)
OR
(manufacturer_asl_dsp IS NOT NULL
AND manufacturer_asl_id IS NULL)
UNION ALL
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','VENDOR_NOT_FOUND') AS msg
FROM po_approved_supplier_list_gt
WHERE vendor_dsp IS NOT NULL
AND vendor_id IS NULL
UNION ALL
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','ITEM_NOT_FOUND') AS msg
FROM po_approved_supplier_list_gt
WHERE item_dsp IS NOT NULL
AND item_id IS NULL);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl1 ,
l_entity_name1 ,
l_reject_reason1
FROM
(SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','CATEGORY_NOT_FOUND') AS msg
FROM po_approved_supplier_list_gt
WHERE category_dsp IS NOT NULL
AND category_id IS NULL
UNION ALL
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','VENDOR_SITE_NOT_FOUND') AS msg
FROM po_approved_supplier_list_gt
WHERE vendor_site_dsp IS NOT NULL
AND vendor_site_id IS NULL
UNION ALL
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
FROM po_approved_supplier_list_gt
WHERE process_action NOT IN
(PO_ASL_API_PUB.g_ACTION_CREATE,
PO_ASL_API_PUB.g_ACTION_UPDATE,
PO_ASL_API_PUB.g_ACTION_SYNC)
OR process_action IS NULL
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','REL_GEN_METHOD_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE release_generation_method_dsp IS NOT NULL
AND release_generation_method IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','PLAN_SCHEDULE_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE plan_schedule_type_dsp IS NOT NULL
AND plan_schedule_type IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','SHIP_SCHEDULE_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE ship_schedule_type_dsp IS NOT NULL
AND ship_schedule_type IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','PLAN_BUCKET_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE plan_bucket_pattern_dsp IS NOT NULL
AND plan_bucket_pattern_id IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','SHIP_BUCKET_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE ship_bucket_pattern_dsp IS NOT NULL
AND ship_bucket_pattern_id IS NULL);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl1 ,
l_entity_name1 ,
l_reject_reason1
FROM (
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','SHCEDULER_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE scheduler_dsp IS NOT NULL
AND scheduler_id IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','VENDOR_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE vendor_dsp IS NOT NULL
AND vendor_id IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','ITEM_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE item_dsp IS NOT NULL
AND item_id IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','CATEGORY_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE category_dsp IS NOT NULL
AND category_id IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','VENDOR_SITE_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE vendor_site_dsp IS NOT NULL
AND vendor_site_id IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE using_organization_dsp IS NOT NULL
AND using_organization_id IS NULL);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','REPL_APPROVAL_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE vmi_replenishment_approval_dsp IS NOT NULL
AND vmi_replenishment_approval IS NULL
UNION ALL
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','REPL_METHOD_NOT_FOUND') AS msg
FROM po_asl_attributes_gt
WHERE replenishment_method_dsp IS NOT NULL
AND replenishment_method IS NULL
UNION ALL
--Reject records if process action is other than ADD,UPDATE,DELETE
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
FROM po_asl_attributes_gt
WHERE process_action NOT IN
(PO_ASL_API_PUB.g_ACTION_ADD,
PO_ASL_API_PUB.g_ACTION_UPDATE,
PO_ASL_API_PUB.g_ACTION_DELETE)
OR process_action IS NULL
UNION ALL
--Reject records if ASL Process action is create and attribute's process
--action is delete/update
SELECT PAA.user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PAA_ACTION') AS msg
FROM po_asl_attributes_gt PAA,
po_approved_supplier_list_gt ASL
WHERE ASL.user_key = PAA.user_key
AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND PAA.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_rejection_reason
FROM
(SELECT user_key ,
'chv_authorizations_gt' AS entity ,
fnd_message.get_string('PO','AUTH_CODE_NOT_FOUND') AS msg
FROM chv_authorizations_gt
WHERE authorization_code_dsp IS NOT NULL
AND authorization_code IS NULL
UNION ALL
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','DOC_TYPE_NOT_FOUND') AS msg
FROM po_asl_documents_gt
WHERE document_type_dsp IS NOT NULL
AND document_type_code IS NULL
UNION ALL
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','DOC_HEADER_NOT_FOUND') AS msg
FROM po_asl_documents_gt
WHERE document_header_dsp IS NOT NULL
AND document_header_id IS NULL
UNION ALL
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','DOC_LINE_NOT_FOUND') AS msg
FROM po_asl_documents_gt
WHERE document_line_num_dsp IS NOT NULL
AND document_line_id IS NULL
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_rejection_reason
FROM
(
--Reject records if ASL Process action is create and document's process
--action is delete/update
SELECT PAD.user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PAD_ACTION') AS msg
FROM po_asl_documents_gt PAD,
po_approved_supplier_list_gt ASL
WHERE ASL.user_key = PAD.user_key
AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND PAD.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
UNION ALL
--Reject records if ASL Process action is create and authorization's process
--action is delete/update
SELECT CHV.user_key ,
'chv_authorizations_gt' AS entity ,
fnd_message.get_string('PO','INVALID_CHV_ACTION') AS msg
FROM chv_authorizations_gt CHV,
po_approved_supplier_list_gt ASL
WHERE ASL.user_key = CHV.user_key
AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND CHV.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
UNION ALL
--Reject records if ASL Process action is create and capacity's process
--action is delete/update
SELECT CAP.user_key ,
'po_supplier_item_capacity_gt' AS entity ,
fnd_message.get_string('PO','INVALID_CAP_ACTION') AS msg
FROM po_supplier_item_capacity_gt CAP,
po_approved_supplier_list_gt ASL
WHERE ASL.user_key = CAP.user_key
AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND CAP.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
UNION ALL
--Reject records if ASL Process action is create and tolerance's process
--action is delete/update
SELECT TOL.user_key ,
'po_supplier_item_tolerance_gt' AS entity ,
fnd_message.get_string('PO','INVALID_TOL_ACTION') AS msg
FROM po_supplier_item_tolerance_gt TOL,
po_approved_supplier_list_gt ASL
WHERE ASL.user_key = TOL.user_key
AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND TOL.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_rejection_reason
FROM
(
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
FROM po_asl_documents_gt
WHERE using_organization_dsp IS NOT NULL
AND using_organization_id IS NULL
UNION ALL
SELECT user_key ,
'chv_authorizations_gt' AS entity ,
fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
FROM chv_authorizations_gt
WHERE using_organization_dsp IS NOT NULL
AND using_organization_id IS NULL
UNION ALL
SELECT user_key ,
'po_supplier_item_capacity_gt' AS entity ,
fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
FROM po_supplier_item_capacity_gt
WHERE using_organization_dsp IS NOT NULL
AND using_organization_id IS NULL
UNION ALL
SELECT user_key ,
'po_supplier_item_tolerance_gt' AS entity,
fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
FROM po_supplier_item_tolerance_gt
WHERE using_organization_dsp IS NOT NULL
AND using_organization_id IS NULL
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_rejection_reason
FROM
(
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
FROM po_asl_documents_gt
WHERE process_action NOT IN
(PO_ASL_API_PUB.g_ACTION_ADD ,
PO_ASL_API_PUB.g_ACTION_UPDATE,
PO_ASL_API_PUB.g_ACTION_DELETE)
OR process_action IS NULL
UNION
SELECT user_key ,
'chv_authorizations_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
FROM chv_authorizations_gt
WHERE process_action NOT IN
(PO_ASL_API_PUB.g_ACTION_ADD ,
PO_ASL_API_PUB.g_ACTION_UPDATE,
PO_ASL_API_PUB.g_ACTION_DELETE)
OR process_action IS NULL
UNION
SELECT user_key ,
'po_supplier_item_capacity_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
FROM po_supplier_item_capacity_gt
WHERE process_action NOT IN
(PO_ASL_API_PUB.g_ACTION_ADD ,
PO_ASL_API_PUB.g_ACTION_UPDATE,
PO_ASL_API_PUB.g_ACTION_DELETE)
OR process_action IS NULL
UNION
SELECT user_key ,
'po_supplier_item_tolerance_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
FROM po_supplier_item_tolerance_gt
WHERE process_action NOT IN
(PO_ASL_API_PUB.g_ACTION_ADD ,
PO_ASL_API_PUB.g_ACTION_UPDATE,
PO_ASL_API_PUB.g_ACTION_DELETE)
OR process_action IS NULL
);
SELECT ASL.item_id ,
ASL.category_id ,
ASL.vendor_id ,
ASL.vendor_site_id ,
ASL.owning_organization_id
INTO l_item_id ,
l_category_id ,
l_vendor_id ,
l_vendor_site_id ,
l_owning_org_id
FROM po_approved_supplier_list_gt ASL
WHERE ASL.user_key = p_user_key
AND ROWNUM < 2;
SELECT poh.po_header_id
INTO l_header_id
FROM po_lookup_codes plc,
po_headers_all poh,
hr_operating_units hrou
WHERE poh.org_id = hrou.organization_id(+)
AND poh.type_lookup_code = p_doc_type
AND ( Nvl(poh.global_agreement_flag, 'N') = 'Y'
OR poh.org_id = l_owning_org_id)
AND ( ( poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND Nvl(poh.cancel_flag, 'N') = 'N'
AND Nvl(poh.frozen_flag, 'N') = 'N'
AND Nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED' )
OR ( poh.type_lookup_code = 'CONTRACT'
AND ( ( Nvl(fnd_profile.Value(
'ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),
'N') =
'Y'
AND poh.approved_date IS NOT NULL )
OR Nvl (poh.approved_flag, 'N') = 'Y' )
AND Nvl(poh.cancel_flag, 'N') = 'N'
AND Nvl(poh.frozen_flag, 'N') = 'N'
AND Nvl(poh.closed_code, 'OPEN') = 'OPEN' )
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.status_lookup_code = 'A' )
AND ( poh.approval_required_flag = 'Y' )
AND ( EXISTS (SELECT 'x'
FROM po_quotation_approvals poqa,
po_line_locations_all poll,
po_lines_all pol
WHERE poqa.approval_type IS NOT NULL
AND poqa.line_location_id =
poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id = poh.po_header_id
AND Trunc(SYSDATE) <=
Nvl(poh.end_date, SYSDATE + 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id =
poh.po_header_id
) )
)
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.status_lookup_code = 'A' )
AND ( poh.approval_required_flag = 'N' )
AND ( EXISTS (SELECT 'x'
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id = poh.po_header_id
AND Trunc(SYSDATE) <=
Nvl(poh.end_date, SYSDATE + 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id =
poh.po_header_id
) )
) )
AND poh.vendor_id = l_vendor_id
AND ( Nvl (poh.vendor_site_id, -1) = Nvl (l_vendor_site_id,
Decode (poh.vendor_site_id, NULL, -1,
poh.vendor_site_id)
)
OR ( Nvl (poh.global_agreement_flag, 'N') = 'Y'
AND l_vendor_site_id IS NOT NULL
AND EXISTS (SELECT 'vendor site id matches'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.vendor_site_id =
Decode(Nvl(poh.enable_all_sites, 'N'), 'N',
l_vendor_site_id,
poga.vendor_site_id)
AND poga.enabled_flag = 'Y') ) )
AND ( poh.type_lookup_code = 'CONTRACT'
OR EXISTS (SELECT 'x'
FROM po_lines_all pol
WHERE pol.po_header_id = poh.po_header_id
AND pol.item_id = l_item_id
AND Nvl(pol.cancel_flag, 'N') = 'N') )
AND Trunc(SYSDATE) <= Nvl(poh.end_date, SYSDATE + 1)
AND Decode(poh.type_lookup_code, 'QUOTATION', poh.status_lookup_code,
'BLANKET', poh.authorization_status,
'CONTRACT', poh.authorization_status) =
plc.lookup_code(+)
AND Decode(poh.type_lookup_code, 'QUOTATION', 'RFQ/QUOTE STATUS',
'BLANKET', 'AUTHORIZATION STATUS',
'CONTRACT', 'AUTHORIZATION STATUS') =
plc.lookup_type(+)
AND poh.segment1 = p_segment;
SELECT poh.po_header_id
INTO l_header_id
FROM po_lookup_codes plc,
po_headers_all poh,
hr_operating_units hrou
WHERE poh.org_id = hrou.organization_id(+)
AND poh.type_lookup_code = p_doc_type
AND ( Nvl(poh.global_agreement_flag, 'N') = 'Y'
OR poh.org_id = l_owning_org_id)
AND ( ( poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND Nvl(poh.cancel_flag, 'N') = 'N'
AND Nvl(poh.frozen_flag, 'N') = 'N'
AND Nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED' )
OR ( poh.type_lookup_code = 'CONTRACT'
AND ( ( Nvl(fnd_profile.Value(
'ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),
'N') =
'Y'
AND poh.approved_date IS NOT NULL )
OR Nvl(poh.approved_flag, 'N') = 'Y' )
AND Nvl(poh.cancel_flag, 'N') = 'N'
AND Nvl(poh.frozen_flag, 'N') = 'N'
AND Nvl(poh.closed_code, 'OPEN') = 'OPEN' )
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.status_lookup_code = 'A' )
AND ( poh.approval_required_flag = 'Y' )
AND ( EXISTS (SELECT 'x'
FROM po_quotation_approvals poqa,
po_line_locations_all poll,
po_lines_all pol
WHERE poqa.approval_type IS NOT NULL
AND poqa.line_location_id =
poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.category_id = l_category_id
AND pol.po_header_id = poh.po_header_id
AND Trunc(SYSDATE) <=
Nvl(poh.end_date, SYSDATE + 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.category_id =
l_category_id
AND pol.po_header_id =
poh.po_header_id
) )
)
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.status_lookup_code = 'A' )
AND ( poh.approval_required_flag = 'N' )
AND ( EXISTS (SELECT 'x'
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.category_id = l_category_id
AND pol.po_header_id = poh.po_header_id
AND Trunc(SYSDATE) <=
Nvl(poh.end_date, SYSDATE + 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll,
po_lines_all pol
WHERE poll.po_line_id = pol.po_line_id
AND pol.category_id =
l_category_id
AND pol.po_header_id =
poh.po_header_id
) )
) )
AND poh.vendor_id = l_vendor_id
AND ( Nvl (poh.vendor_site_id, -1) = Nvl (l_vendor_site_id,
Decode (poh.vendor_site_id, NULL, -1,
poh.vendor_site_id))
OR ( Nvl (poh.global_agreement_flag, 'N') = 'Y'
AND l_vendor_site_id IS NOT NULL
AND EXISTS (SELECT 'vendor site id matches'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.vendor_site_id =
Decode(Nvl(poh.enable_all_sites, 'N'), 'Y',
poga.vendor_site_id,
l_vendor_site_id)
AND poga.enabled_flag = 'Y') ) )
AND ( poh.type_lookup_code = 'CONTRACT'
OR EXISTS (SELECT 'x'
FROM po_lines_all pol
WHERE pol.po_header_id = poh.po_header_id
AND pol.category_id = l_category_id
AND Nvl(pol.cancel_flag, 'N') = 'N') )
AND Trunc(SYSDATE) <= Nvl(poh.end_date, SYSDATE + 1)
AND Decode(poh.type_lookup_code, 'QUOTATION', poh.status_lookup_code,
'BLANKET', poh.authorization_status,
'CONTRACT', poh.authorization_status) =
plc.lookup_code(+)
AND Decode(poh.type_lookup_code, 'QUOTATION', 'RFQ/QUOTE STATUS',
'BLANKET', 'AUTHORIZATION STATUS',
'CONTRACT', 'AUTHORIZATION STATUS') =
plc.lookup_type(+)
AND poh.segment1 = p_segment;
SELECT ASL.item_id ,
ASL.category_id
INTO l_item_id ,
l_category_id
FROM po_approved_supplier_list_gt ASL
WHERE ASL.user_key = p_user_key
AND ROWNUM < 2;
SELECT pol.po_line_id
INTO l_line_id
FROM po_lines_all pol,
fnd_currencies_vl fnc,
po_headers_all poh
WHERE pol.po_header_id = p_header_id
AND pol.item_id = l_item_id
AND pol.po_header_id = poh.po_header_id
AND Nvl(pol.cancel_flag, 'N') = 'N'
AND ( ( poh.type_lookup_code = 'BLANKET' )
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.approval_required_flag = 'Y' )
AND ( EXISTS (SELECT 1
FROM po_quotation_approvals poqa,
po_line_locations_all poll
WHERE poqa.approval_type IS NOT NULL
AND poqa.line_location_id =
poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id = poh.po_header_id
AND SYSDATE <
Nvl(poh.end_date, SYSDATE + 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id =
poh.po_header_id
) )
)
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.approval_required_flag = 'N' )
AND ( EXISTS (SELECT 1
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id = poh.po_header_id
AND SYSDATE <
Nvl(poh.end_date, SYSDATE + 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND pol.item_id = l_item_id
AND pol.po_header_id =
poh.po_header_id
) )
) )
AND poh.currency_code = fnc.currency_code
AND pol.line_num = p_line_num;
SELECT pol.po_line_id
INTO l_line_id
FROM po_lines_all pol,
fnd_currencies_vl fnc,
po_headers_all poh
WHERE pol.po_header_id = p_header_id
AND pol.category_id = l_category_id
AND pol.po_header_id = poh.po_header_id
AND Nvl(pol.cancel_flag, 'N') = 'N'
AND ( ( poh.type_lookup_code = 'BLANKET' )
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.approval_required_flag = 'Y' )
AND ( EXISTS (SELECT *
FROM po_quotation_approvals poqa,
po_line_locations_all poll
WHERE poqa.approval_type IS NOT NULL
AND poqa.line_location_id =
poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.category_id = l_category_id
AND pol.po_header_id = poh.po_header_id
AND SYSDATE < Nvl(poh.end_date, SYSDATE
+ 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND pol.category_id =
l_category_id
AND pol.po_header_id =
poh.po_header_id
) )
)
OR ( poh.type_lookup_code = 'QUOTATION'
AND ( poh.approval_required_flag = 'N' )
AND ( EXISTS (SELECT *
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND pol.category_id = l_category_id
AND pol.po_header_id = poh.po_header_id
AND SYSDATE < Nvl(poh.end_date, SYSDATE
+ 1))
OR NOT EXISTS (SELECT 'no shipments exists'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND pol.category_id =
l_category_id
AND pol.po_header_id =
poh.po_header_id
) )
) )
AND poh.currency_code = fnc.currency_code
AND pol.line_num = p_line_num;