[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE po_approved_supplier_list_gt GT
SET GT.process_action =
determine_action(
p_item_id => GT.item_id ,
p_category_id => GT.category_id ,
p_using_organization_id => GT.using_organization_id,
p_vendor_id => GT.vendor_id ,
p_vendor_site_id => GT.vendor_site_id
)
WHERE GT.process_action = PO_ASL_API_PUB.g_ACTION_SYNC;
UPDATE po_approved_supplier_list_gt GT
SET asl_id =
(SELECT asl_id
FROM po_approved_supplier_list PASL
WHERE (GT.item_id = PASL.item_id
OR GT.category_id = PASL.category_id)
AND GT.using_organization_id = PASL.using_organization_id
AND (GT.vendor_id = PASL.vendor_id
OR GT.manufacturer_id = PASL.manufacturer_id)
AND Nvl(GT.vendor_site_id,-1)= Nvl(PASL.vendor_site_id,-1)
AND ROWNUM < 2)
WHERE GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE;
UPDATE po_approved_supplier_list_gt GT
SET asl_id = po_approved_supplier_list_s.NEXTVAL
WHERE GT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE;
UPDATE po_asl_attributes_gt PAA
SET asl_id = (SELECT asl_id
FROM po_approved_supplier_list_gt PASL
WHERE PASL.user_key = PAA.user_key
AND ROWNUM < 2);
UPDATE po_asl_documents_gt PAD
SET asl_id = (SELECT asl_id
FROM po_approved_supplier_list_gt PASL
WHERE PASL.user_key = PAD.user_key
AND ROWNUM < 2);
UPDATE chv_authorizations_gt CHV
SET reference_id = (SELECT asl_id
FROM po_approved_supplier_list_gt PASL
WHERE PASL.user_key = CHV.user_key
AND ROWNUM < 2);
UPDATE po_supplier_item_capacity_gt PSIC
SET asl_id = (SELECT asl_id
FROM po_approved_supplier_list_gt PASL
WHERE PASL.user_key = PSIC.user_key
AND ROWNUM < 2);
UPDATE po_supplier_item_tolerance_gt PSIT
SET asl_id = (SELECT asl_id
FROM po_approved_supplier_list_gt PASL
WHERE PASL.user_key = PSIT.user_key
AND ROWNUM < 2);
/*--Populate capacity id in case of update and
--Capacity's process action is ADD
UPDATE po_supplier_item_capacity_gt PSIC
SET capacity_id = po_supplier_item_capacity_s.NEXTVAL
WHERE process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT user_key ,
'po_approved_supplier_list_gt' ,
fnd_message.get_string('PO','DUPLICATE_ASL')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_approved_supplier_list_gt GT
WHERE EXISTS
(SELECT asl_id
FROM po_approved_supplier_list PASL
WHERE (GT.item_id = PASL.item_id
OR GT.category_id = PASL.category_id)
AND GT.using_organization_id = PASL.using_organization_id
AND (GT.vendor_id = PASL.vendor_id
OR GT.manufacturer_id = PASL.manufacturer_id)
AND Nvl(GT.vendor_site_id,-1) = Nvl(PASL.vendor_site_id,-1))
AND GT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE;
SELECT user_key ,
'po_approved_supplier_list_gt' ,
fnd_message.get_string('PO','ASL_DOES_NOT_EXIST')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_approved_supplier_list_gt GT
WHERE NOT EXISTS
(SELECT asl_id
FROM po_approved_supplier_list PASL
WHERE (GT.item_id = PASL.item_id
OR GT.category_id = PASL.category_id)
AND GT.using_organization_id = PASL.using_organization_id
AND (GT.vendor_id = PASL.vendor_id
OR GT.manufacturer_id = PASL.manufacturer_id)
AND Nvl(GT.vendor_site_id,-1) = Nvl(PASL.vendor_site_id,-1))
AND GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE;
SELECT PAAGT.user_key ,
'po_asl_attributes_gt' ,
fnd_message.get_string('PO','DUPLICATE_ATTRIBUTES')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_asl_attributes_gt PAAGT,
po_approved_supplier_list_gt ASLGT
WHERE EXISTS
(SELECT 1
FROM po_asl_attributes PAA
WHERE PAAGT.asl_id = PAA.asl_id
AND PAAGT.using_organization_id = PAA.using_organization_id)
AND ASLGT.user_key = PAAGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND PAAGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT PAAGT.user_key ,
'po_asl_attributes_gt' ,
fnd_message.get_string('PO','DUPLICATE_ATTRIBUTES')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_asl_attributes_gt PAAGT,
po_approved_supplier_list_gt ASLGT
WHERE 2 <=
(SELECT Count(user_key)
FROM po_asl_attributes_gt PAA
WHERE PAAGT.asl_id = PAA.asl_id
AND PAAGT.using_organization_id = PAA.using_organization_id)
AND ASLGT.user_key = PAAGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND PAAGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT PAAGT.user_key ,
'po_asl_attributes_gt' ,
fnd_message.get_string('PO','ATTRIBUTES_NOT_EXIST')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_asl_attributes_gt PAAGT,
po_approved_supplier_list_gt ASLGT
WHERE NOT EXISTS
(SELECT 1
FROM po_asl_attributes PAA
WHERE PAAGT.asl_id = PAA.asl_id
AND PAAGT.using_organization_id = PAA.using_organization_id)
AND ASLGT.user_key = PAAGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND PAAGT.process_action IN
(PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
SELECT DOCGT.user_key ,
'po_asl_documents_gt' ,
fnd_message.get_string('PO','DUPLICATE_DOCUMENT')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_asl_documents_gt DOCGT,
po_approved_supplier_list_gt ASLGT
WHERE EXISTS
(SELECT 1
FROM po_asl_documents PAD
WHERE DOCGT.document_header_id = PAD.document_header_id
AND DOCGT.asl_id = PAD.asl_id
AND DOCGT.using_organization_id = PAD.using_organization_id)
AND ASLGT.user_key = DOCGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND DOCGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT DOCGT.user_key ,
'po_asl_documents_gt' ,
fnd_message.get_string('PO','DOCUMENT_NOT_EXIST')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_asl_documents_gt DOCGT,
po_approved_supplier_list_gt ASLGT
WHERE NOT EXISTS
(SELECT 1
FROM po_asl_documents PAD
WHERE DOCGT.document_header_id = PAD.document_header_id
AND DOCGT.asl_id = PAD.asl_id
AND DOCGT.using_organization_id = PAD.using_organization_id)
AND ASLGT.user_key = DOCGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND DOCGT.process_action IN
(PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
SELECT DOCGT.user_key ,
'po_asl_documents_gt' ,
fnd_message.get_string('PO','DUPLICATE_DOCUMENT')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_asl_documents_gt DOCGT,
po_approved_supplier_list_gt ASLGT
WHERE 2 <=
(SELECT Count(user_key)
FROM po_asl_documents_gt PAD
WHERE DOCGT.document_header_id = PAD.document_header_id
AND DOCGT.asl_id = PAD.asl_id
AND DOCGT.using_organization_id = PAD.using_organization_id)
AND ASLGT.user_key = DOCGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND DOCGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT CHVGT.user_key ,
'chv_authorizations_gt' ,
fnd_message.get_string('PO','DUPLICATE_AUTHORIZATION')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM chv_authorizations_gt CHVGT,
po_approved_supplier_list_gt ASLGT
WHERE EXISTS
(SELECT 1
FROM chv_authorizations CHV
WHERE CHVGT.reference_id = CHV.reference_id
AND CHVGT.using_organization_id = CHV.using_organization_id
AND (CHVGT.authorization_code = CHV.authorization_code
OR
CHVGT.authorization_sequence_dsp=CHV.authorization_sequence)
)
AND CHVGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND CHVGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT CHVGT.user_key ,
'chv_authorizations_gt' ,
fnd_message.get_string('PO','AUTHORIZATION_NOT_EXIST')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM chv_authorizations_gt CHVGT,
po_approved_supplier_list_gt ASLGT
WHERE NOT EXISTS
(SELECT 1
FROM chv_authorizations CHV
WHERE CHVGT.reference_id = CHV.reference_id
AND CHVGT.using_organization_id = CHV.using_organization_id
AND CHVGT.authorization_code = CHV.authorization_code
AND CHVGT.authorization_sequence_dsp=CHV.authorization_sequence
)
AND CHVGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND CHVGT.process_action IN
(PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
SELECT CHVGT.user_key ,
'chv_authorizations_gt' ,
fnd_message.get_string('PO','DUPLICATE_AUTHORIZATION')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM chv_authorizations_gt CHVGT ,
po_approved_supplier_list_gt ASLGT
WHERE 2 <=
(SELECT Count(user_key)
FROM chv_authorizations_gt CHV
WHERE CHVGT.reference_id = CHV.reference_id
AND CHVGT.using_organization_id = CHV.using_organization_id
AND (CHVGT.authorization_code = CHV.authorization_code
OR CHVGT.authorization_sequence_dsp =
CHV.authorization_sequence_dsp)
)
AND CHVGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND CHVGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT CAPGT.user_key ,
'po_supplier_item_capacity_gt' ,
fnd_message.get_string('PO','CAPACITY_NOT_EXIST')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_supplier_item_capacity_gt CAPGT ,
po_approved_supplier_list_gt ASLGT
WHERE NOT EXISTS
(SELECT 1
FROM po_supplier_item_capacity CAP
WHERE CAPGT.asl_id = CAP.asl_id
AND CAPGT.using_organization_id = CAP.using_organization_id
AND Nvl(CAPGT.to_date_dsp, SYSDATE) =
Nvl(CAP.to_date, SYSDATE)
AND CAPGT.from_date_dsp = CAP.from_date
AND CAPGT.capacity_per_day_dsp = CAP.capacity_per_day
)
AND CAPGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND CAPGT.process_action IN
(PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
SELECT TOLGT.user_key ,
'po_supplier_item_tolerance_gt' ,
fnd_message.get_string('PO','DUPLICATE_TOLERANCE')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_supplier_item_tolerance_gt TOLGT,
po_approved_supplier_list_gt ASLGT
WHERE EXISTS
(SELECT 1
FROM po_supplier_item_tolerance TOL
WHERE TOLGT.asl_id = TOL.asl_id
AND TOLGT.using_organization_id = TOL.using_organization_id
AND TOLGT.number_of_days_dsp = TOL.number_of_days
)
AND TOLGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND TOLGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT TOLGT.user_key ,
'po_supplier_item_tolerance_gt' ,
fnd_message.get_string('PO','TOLERANCE_NOT_EXIST')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_supplier_item_tolerance_gt TOLGT,
po_approved_supplier_list_gt ASLGT
WHERE NOT EXISTS
(SELECT 1
FROM po_supplier_item_tolerance TOL
WHERE TOLGT.asl_id = TOL.asl_id
AND TOLGT.using_organization_id = TOL.using_organization_id
AND TOLGT.number_of_days_dsp = TOL.number_of_days
)
AND TOLGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND TOLGT.process_action IN
(PO_ASL_API_PUB.g_ACTION_DELETE, PO_ASL_API_PUB.g_ACTION_UPDATE);
SELECT TOLGT.user_key ,
'po_supplier_item_tolerance_gt' ,
fnd_message.get_string('PO','DUPLICATE_TOLERANCE')
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_supplier_item_tolerance_gt TOLGT,
po_approved_supplier_list_gt ASLGT
WHERE 2 <=
(SELECT Count(user_key)
FROM po_supplier_item_tolerance_gt TOL
WHERE TOLGT.asl_id = TOL.asl_id
AND TOLGT.using_organization_id = TOL.using_organization_id
AND TOLGT.number_of_days_dsp = TOL.number_of_days_dsp
)
AND TOLGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND TOLGT.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_reject_reason
FROM (
--Reject records if item and category both exists for the same ASL
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','ITEM_CATEGORY_BOTH_EXIST') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE ASLGT.item_id IS NOT NULL
AND ASLGT.category_id IS NOT NULL
UNION ALL
--Reject records if item and category both empty
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','ITEM_CATEGORY_BOTH_EMPTY') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE (ASLGT.item_id IS NULL
AND ASLGT.category_id IS NULL)
OR (Trim(ASLGT.item_id) IS NULL
AND Trim(ASLGT.category_id) IS NULL)
UNION ALL
--Reject records if vendor business type is empty or null
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','INVALID_BUSINESS_TYPE') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE ASLGT.vendor_business_type IS NULL
OR Upper(ASLGT.vendor_business_type) NOT IN
(SELECT lookup_code
FROM po_lookup_codes
WHERE lookup_type = 'ASL_VENDOR_BUSINESS_TYPE')
UNION ALL
--Reject records if vendor_id is empty
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','VENDOR_EMPTY') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE ASLGT.vendor_id IS NULL
AND Upper(vendor_business_type) <> 'MANUFACTURER'
UNION ALL
--Reject records if status_id is empty
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','STATUS_EMPTY') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE ASLGT.asl_status_id IS NULL
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl1 ,
l_entity_name1 ,
l_reject_reason1
FROM (
--Reject records if manufacturer_asl_id is empty when business type is
--'DISTRIBUTOR'
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','MANUFACTURER_ASL_MANDATORY') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE ASLGT.manufacturer_asl_id IS NULL
AND Upper(ASLGT.vendor_business_type) = 'DISTRIBUTOR'
UNION ALL
--Reject records if review date entered and is past date
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','INVALID_REVIEW_DATE') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE ASLGT.review_by_date IS NOT NULL
AND ASLGT.review_by_date < SYSDATE
UNION ALL
--During update ASL, business type can't be editable if the value is DB is
--'MANUFACTURER'
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','BUSINESS_TYPE_NOT_EDITABLE') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND
EXISTS (SELECT asl_id
FROM po_approved_supplier_list ASL
WHERE ASL.asl_id = ASLGT.asl_id
AND Upper(ASL.vendor_business_type) = 'MANUFACTURER')
AND Upper(ASLGT.vendor_business_type) <> 'MANUFACTURER'
UNION ALL
--Reject records if business type is MANUFACTURER and manufacter id doesn't
--exist also reject if vendor exists for the same case
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','VENDOR_INVALID_EXP_MANUFACTUR') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE Upper(ASLGT.vendor_business_type) = 'MANUFACTURER'
AND (ASLGT.manufacturer_id IS NULL
OR ASLGT.vendor_id IS NOT NULL)
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records when business type is MANUFACTURER and if there are any
--child records exist
SELECT user_key ,
'po_approved_supplier_list_gt' AS entity ,
fnd_message.get_string('PO','MANUFAC_INVALID_AUTH_ENTRY') AS msg
FROM po_approved_supplier_list_gt ASLGT
WHERE Upper(ASLGT.vendor_business_type) = 'MANUFACTURER'
AND
(EXISTS
(SELECT 1
FROM po_asl_attributes_gt PAA
WHERE PAA.asl_id = ASLGT.asl_id
AND PAA.user_key = ASLGT.user_key)
OR
EXISTS
(SELECT 1
FROM chv_authorizations_gt CHV
WHERE CHV.reference_id = ASLGT.asl_id
AND CHV.user_key = ASLGT.user_key)
OR
EXISTS
(SELECT 1
FROM po_asl_documents_gt PAD
WHERE PAD.asl_id = ASLGT.asl_id
AND PAD.user_key = ASLGT.user_key)
OR
EXISTS
(SELECT 1
FROM po_supplier_item_capacity_gt PSIC
WHERE PSIC.asl_id = ASLGT.asl_id
AND PSIC.user_key = ASLGT.user_key)
OR
EXISTS
(SELECT 1
FROM po_supplier_item_tolerance_gt PSIT
WHERE PSIT.asl_id = ASLGT.asl_id
AND PSIT.user_key = ASLGT.user_key)));
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if item or site is null and country code is not null
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','COUNTRY_CODE_NOT_EMPTY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE (PAAGT.item_id IS NULL
OR PAAGT.vendor_site_id IS NULL)
AND PAAGT.country_of_origin_code_dsp IS NOT NULL
UNION ALL
--Reject records if Purchasing UOM empty and enable_plan_schedule_flag or
--enable_ship_schedule_flag is checked
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','PURCHASING_UOM_MANDATORY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE (PAAGT.enable_plan_schedule_flag_dsp = 'Y'
OR PAAGT.enable_ship_schedule_flag_dsp = 'Y')
AND (PAAGT.purchasing_unit_of_measure_dsp IS NULL
OR Trim(PAAGT.purchasing_unit_of_measure_dsp) = '')
UNION ALL
--Reject records if enable_autoschedule_flag is Y when
--enable_plan_schedule_flag_dsp and enable_ship_schedule_flag_dsp
--are unchecked.
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_AUTOSCHEDULE_FLAG') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE Nvl(PAAGT.enable_plan_schedule_flag_dsp,'N') <> 'Y'
AND Nvl(PAAGT.enable_ship_schedule_flag_dsp,'N') <> 'Y'
AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
UNION ALL
--Reject records if plan bucket pattern empty if enable_autoschedule_flag
--and enable_plan_schedule_flag enabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','PLAN_BUCKET_MANDATORY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.enable_plan_schedule_flag_dsp = 'Y'
AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
AND PAAGT.plan_bucket_pattern_id IS NULL
UNION ALL
--Reject records if plan schedule type empty if enable_autoschedule_flag
--and enable_plan_schedule_flag enabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','PLAN_SCHEDULE_MANDATORY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.enable_plan_schedule_flag_dsp = 'Y'
AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
AND (PAAGT.plan_schedule_type IS NULL
OR Trim(PAAGT.plan_schedule_type) = ''));
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if ship bucket pattern empty if enable_autoschedule_flag
--and enable_ship_schedule_flag enabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','SHIP_BUCKET_MANDATORY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.enable_ship_schedule_flag_dsp = 'Y'
AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
AND PAAGT.ship_bucket_pattern_id IS NULL
UNION ALL
--Reject records if ship schedule type empty if enable_autoschedule_flag
--and enable_ship_schedule_flag enabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','SHIP_SCHEDULE_MANDATORY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.enable_ship_schedule_flag_dsp = 'Y'
AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
AND (PAAGT.ship_schedule_type IS NULL
OR Trim(PAAGT.ship_schedule_type) = '')
UNION ALL
--Reject records if there is an entry in po_supplier_item_capacity_gt
-- when global_flag is N and VMI flag is not checked
SELECT PAAGT.user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_SUPP_ITEM_CAP_ENTRY') AS msg
FROM po_asl_attributes_gt PAAGT,
po_approved_supplier_list_gt ASLGT
WHERE PAAGT.user_key = ASLGT.user_key
AND ASLGT.using_organization_id <> -1
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
AND EXISTS
(SELECT 1
FROM po_supplier_item_capacity_gt PSIC
WHERE PSIC.asl_id = PAAGT.asl_id
AND PSIC.using_organization_id= PAAGT.using_organization_id
AND PSIC.user_key = PAAGT.user_key
AND PSIC.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_reject_reason
FROM (
--Reject records if plan bucket pattern is not empty if
--enable_autoschedule_flag or enable_plan_schedule_flag disabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PLAN_BUCKET') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE (Nvl(PAAGT.enable_plan_schedule_flag_dsp, 'N') <> 'Y'
OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
AND PAAGT.plan_bucket_pattern_id IS NOT NULL
UNION ALL
--Reject records if plan schedule type not empty if
--enable_autoschedule_flag or enable_plan_schedule_flag disabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PLAN_SCHEDULE') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE (Nvl(PAAGT.enable_plan_schedule_flag_dsp, 'N') <> 'Y'
OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
AND PAAGT.plan_schedule_type IS NOT NULL
UNION ALL
--Reject records if ship bucket pattern not empty if
--enable_autoschedule_flag or enable_ship_schedule_flag disabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_SHIP_BUCKET') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE (Nvl(PAAGT.enable_ship_schedule_flag_dsp, 'N') <> 'Y'
OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
AND PAAGT.ship_bucket_pattern_id IS NOT NULL
UNION ALL
--Reject records if ship schedule type not empty if
--enable_autoschedule_flag or enable_ship_schedule_flag disabled
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_SHIP_SCHEDULE') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE (Nvl(PAAGT.enable_ship_schedule_flag_dsp, 'N') <> 'Y'
OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
AND PAAGT.ship_schedule_type IS NOT NULL
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if authorization flag is not checked and
--there is an entry for chv_authorizations in case of CREATE
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_AUTHORIZATION_ENTRY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE NVL(PAAGT.enable_authorizations_flag_dsp,'N') <> 'Y'
AND EXISTS
(SELECT 1
FROM chv_authorizations_gt CHV
WHERE CHV.reference_id = PAAGT.asl_id
AND CHV.using_organization_id = PAAGT.using_organization_id
AND CHV.user_key = PAAGT.user_key
AND CHV.process_action = PO_ASL_API_PUB.g_ACTION_ADD)
AND PAAGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
UNION ALL
--Reject records if there is an entry in po_supplier_item_tolerance_gt
-- when global_flag is N and VMI flag is not checked
SELECT PAAGT.user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_SUPP_ITEM_TOL_ENTRY') AS msg
FROM po_asl_attributes_gt PAAGT,
po_approved_supplier_list_gt ASLGT
WHERE PAAGT.user_key = ASLGT.user_key
AND ASLGT.using_organization_id <> -1
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
AND EXISTS
(SELECT 1
FROM po_supplier_item_tolerance_gt PSIT
WHERE PSIT.asl_id = PAAGT.asl_id
AND PSIT.using_organization_id= PAAGT.using_organization_id
AND PSIT.user_key = PAAGT.user_key
AND PSIT.process_action = PO_ASL_API_PUB.g_ACTION_ADD)
UNION ALL
--Reject records if Price Update tolerance is -ve number
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PRICE_UPDATE_TOLERANCE') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.price_update_tolerance_dsp < 0);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if delivery calendar code is entered when global_flag is N
--or VMI flag is not checked
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','DELIVERY_CALENDAR_NOT_NULL') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.delivery_calendar_dsp IS NOT NULL
AND PAAGT.using_organization_id <> -1
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
UNION ALL
--Reject records if delivery calendar code is entered and not valid
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_DELIVERY_CALENDAR_CODE') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.delivery_calendar_dsp IS NOT NULL
AND NOT EXISTS
(SELECT 1
FROM bom_calendars BOM
WHERE Nvl(BOM.calendar_end_date, SYSDATE+1) > SYSDATE
AND BOM.calendar_code = PAAGT.delivery_calendar_dsp)
UNION ALL
--Reject records if delivery calendar code is entered when global_flag is N
--or VMI flag is not checked
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','PROCESSING_LEAD_TIME_NOT_NULL') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.processing_lead_time_dsp IS NOT NULL
AND PAAGT.using_organization_id <> -1
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
UNION ALL
--Reject records if Processing lead time is -ve number or zero
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_PROCESSING_LEAD_TIME') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.processing_lead_time_dsp <= 0);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if Min Order Qty is -ve number or zero
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_MIN_ORDER_QTY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.min_order_qty_dsp <= 0
UNION ALL
--Reject records if fixed lot multiple is -ve number or zero
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_FIXED_LOT_MULTIPLE') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.fixed_lot_multiple_dsp <= 0
UNION ALL
--Reject records if enable_vmi_flag is checked when site is null or
--ASL created for commodity or PO_THIRD_PARTY_STOCK_GRP.validate_local_asl
--retunrs false
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_VMI_FLAG') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
AND (PAAGT.vendor_site_id IS NULL
OR PAAGT.item_id IS NULL
OR validate_vmi(
p_item_id => PAAGT.item_id
,p_vendor_site_id => PAAGT.vendor_site_id
,p_using_organization_id => PAAGT.using_organization_id) = 'F'));
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if vmi_flag is not checked and automatic allowed is checked
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_AUTO_REPLENISH_FLAG') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
AND PAAGT.enable_vmi_auto_replenish_flag = 'Y'
UNION ALL
--Reject records if vmi_flag is not checked and replenishment
--method is entered
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_REPLENISH_METHOD') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
AND PAAGT.replenishment_method IS NOT NULL
UNION ALL
--Reject records if automatic allowed is not checked and replenishment
--approval is not 'SUPPLIER_OR_BUYER'
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_REPLENISH_APPROVAL') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE Nvl(PAAGT.enable_vmi_auto_replenish_flag,'N') <> 'Y'
AND PAAGT.vmi_replenishment_approval <>'SUPPLIER_OR_BUYER'
AND PAAGT.vmi_replenishment_approval IS NOT NULL
UNION ALL
--Reject records if vmi_flag, automatic allowed is checked and
--replenishment approval is empty
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','REPLENISH_APPROVAL_REQUIRED') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
AND PAAGT.enable_vmi_auto_replenish_flag = 'Y'
AND PAAGT.vmi_replenishment_approval IS NULL
UNION ALL
--Reject records if vmi_flag is checked and replenishment method is empty
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','REPLENISH_METHOD_REQUIRED') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
AND PAAGT.replenishment_method IS NULL
UNION ALL
--Reject records if forecast horizon is not +ve integer or zero
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_FORECAST_HORIZON') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.forecast_horizon_dsp <= 0
OR Round(PAAGT.forecast_horizon_dsp) <> PAAGT.forecast_horizon_dsp
OR (PAAGT.forecast_horizon_dsp IS NOT NULL
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'));
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if VMI Min Qty is -ve number or is entered when
--vmi_flag is not checked or replenishment method is 2/4
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_VIM_MIN_QTY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.vmi_min_qty_dsp < 0
OR (PAAGT.vmi_min_qty_dsp <> 0
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
OR (PAAGT.vmi_min_qty_dsp <> 0
AND PAAGT.replenishment_method IN (2,4))
UNION ALL
--Reject records if VMI Max Qty is -ve number or is entered when
--vmi flag is not checked or replenishment method is 2/3/4 or
--this qty is less than vmi min qty
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_VIM_MAX_QTY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.vmi_max_qty_dsp < 0
OR (PAAGT.vmi_max_qty_dsp < PAAGT.vmi_min_qty_dsp)
OR (PAAGT.vmi_max_qty_dsp <> 0
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
OR (PAAGT.vmi_max_qty_dsp <> 0 AND PAAGT.replenishment_method
IN (2,3,4))
UNION ALL
--Reject records if VMI Min Days is not +ve integer or is entered when
--vmi flag is not checked or replenishment method is 1/3
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_VIM_MIN_DAYS') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.vmi_min_days_dsp < 0
OR Round(PAAGT.vmi_min_days_dsp) <> PAAGT.vmi_min_days_dsp
OR (PAAGT.vmi_min_days_dsp <> 0
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
OR (PAAGT.vmi_min_days_dsp <> 0
AND PAAGT.replenishment_method IN (1,3))
UNION ALL
--Reject records if VMI Max Days is not +ve integer or is entered when
--vmi flag is not checked or replenishment method is 1/3/4
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_VIM_MAXS_DAYS') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.vmi_max_days_dsp < 0
OR Round(PAAGT.vmi_max_days_dsp) <> PAAGT.vmi_max_days_dsp
OR (PAAGT.vmi_max_days_dsp < PAAGT.vmi_min_days_dsp)
OR (PAAGT.vmi_max_days_dsp <> 0
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
OR (PAAGT.vmi_max_days_dsp <> 0
AND PAAGT.replenishment_method IN (1,3,4))
UNION ALL
--Reject records if Fixed Order Quantity is -ve number or is entered when
--vmi flag is not checked or replenishment method is 1/2
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_FIXED_ORDER_QTY') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.fixed_order_quantity_dsp < 0
OR (PAAGT.fixed_order_quantity_dsp <> 0
AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
OR (PAAGT.fixed_order_quantity_dsp <> 0
AND PAAGT.replenishment_method IN (1,2)));
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if consigned from supplier is checked when site is null or
--ASL created for commodity or PO_THIRD_PARTY_STOCK_GRP.validate_local_asl
--retunrs false
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_CONSIGNED_FLAG') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.consigned_from_supp_flag_dsp = 'Y'
AND (PAAGT.vendor_site_id IS NULL
OR PAAGT.item_id IS NULL
OR validate_vmi(
p_item_id => PAAGT.item_id
,p_vendor_site_id => PAAGT.vendor_site_id
,p_using_organization_id=>PAAGT.using_organization_id)='F')
UNION ALL
--Reject records if Consigned billing cycle is -ve number
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_CONSIGN_BILL_CYCLE') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.consigned_billing_cycle_dsp < 0
OR (PAAGT.consigned_billing_cycle_dsp IS NOT NULL
AND Nvl(PAAGT.consigned_from_supp_flag_dsp,'N') <> 'Y')
UNION ALL
--Reject records if Consume on Aging flag is checked when Consigned flag
--is not checked
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_CONSUME_AGING_FLAG') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.consume_on_aging_flag_dsp = 'Y'
AND Nvl(PAAGT.consigned_from_supp_flag_dsp,'N') <> 'Y'
UNION ALL
--Reject records if ageing period is not +ve number or zero or is entered
--when consume of ageing flag is not checked
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','INVALID_AGEING_PERIOD') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.aging_period_dsp <= 0
OR Round(PAAGT.aging_period_dsp) <> PAAGT.aging_period_dsp
OR (Nvl(PAAGT.consume_on_aging_flag_dsp,'N') <> 'Y'
AND PAAGT.aging_period_dsp IS NOT NULL)
UNION ALL
--Reject records if vendor site is null and supplier scheduling tab details
--are given
SELECT user_key ,
'po_asl_attributes_gt' AS entity ,
fnd_message.get_string('PO','SUPPLIER_SCHEDULING_DISABLED') AS msg
FROM po_asl_attributes_gt PAAGT
WHERE PAAGT.vendor_site_id IS NULL
AND PAAGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
AND (PAAGT.enable_plan_schedule_flag_dsp = 'Y'
OR PAAGT.enable_ship_schedule_flag_dsp = 'Y'
OR PAAGT.enable_autoschedule_flag_dsp = 'Y'
OR PAAGT.scheduler_id IS NOT NULL
OR PAAGT.enable_authorizations_flag_dsp = 'Y'
OR EXISTS
(SELECT 1
FROM chv_authorizations_gt CHV
WHERE CHV.reference_id = PAAGT.asl_id
AND CHV.using_organization_id = PAAGT.using_organization_id
AND CHV.user_key = PAAGT.user_key))
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if sequence number is empty or lessthan 1
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','INVALID_DOC_SEQUENCE') AS msg
FROM po_asl_documents_gt DOCGT
WHERE DOCGT.sequence_num IS NULL
OR DOCGT.sequence_num < 1
UNION ALL
--Reject records if document type is null
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','DOC_TYPE_MANDATORY') AS msg
FROM po_asl_documents_gt DOCGT
WHERE DOCGT.document_type_code IS NULL
UNION ALL
--Reject records if header id is null
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','DOC_HEADER_MANDATORY') AS msg
FROM po_asl_documents_gt DOCGT
WHERE DOCGT.document_header_id IS NULL
UNION ALL
--Reject records if document type is Not CONTRACT, 'LINE_NUM' is null
SELECT user_key ,
'po_asl_documents_gt' AS entity ,
fnd_message.get_string('PO','LINE_NUM_MANDATORY') AS msg
FROM po_asl_documents_gt DOCGT
WHERE DOCGT.document_type_code <> 'CONTRACT'
AND DOCGT.document_line_id IS NULL
AND DOCGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if sequence number is empty or not in 1,2,3,4
SELECT user_key ,
'chv_authorizations_gt' AS entity ,
fnd_message.get_string('PO','INVALID_AUTH_SEQUENCE') AS msg
FROM chv_authorizations_gt CHVGT
WHERE CHVGT.authorization_sequence_dsp IS NULL
OR CHVGT.authorization_sequence_dsp NOT IN (1,2,3,4)
UNION ALL
--Reject records if timefence days is less than 1
SELECT user_key ,
'chv_authorizations_gt' AS entity ,
fnd_message.get_string('PO','INVALID_TIMEFENCE_DAYS') AS msg
FROM chv_authorizations_gt CHVGT
WHERE CHVGT.timefence_days_dsp IS NULL
OR CHVGT.timefence_days_dsp <= 0
);
SELECT user_key ,
entity ,
msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM (
--Reject records if from_date is less than current date
SELECT user_key ,
'po_supplier_item_capacity_gt' AS entity ,
fnd_message.get_string('PO','INVALID_FROM_DATE') AS msg
FROM po_supplier_item_capacity_gt PSICGT
WHERE (PSICGT.from_date_dsp IS NULL
OR PSICGT.from_date_dsp < SYSDATE)
AND PSICGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
UNION ALL
--Reject records if to_date is less than current date or from_date
SELECT user_key ,
'po_supplier_item_capacity_gt' AS entity ,
fnd_message.get_string('PO','INVALID_TO_DATE') AS msg
FROM po_supplier_item_capacity_gt PSICGT
WHERE PSICGT.to_date_dsp < SYSDATE
OR PSICGT.to_date_dsp < PSICGT.from_date_dsp
UNION ALL
--Reject records if capacity is less than 1
SELECT user_key ,
'po_supplier_item_capacity_gt' ,
fnd_message.get_string('PO','INVALID_CAPACITY_PER_DAY') AS msg
FROM po_supplier_item_capacity_gt PSICGT
WHERE PSICGT.capacity_per_day_dsp IS NULL
OR PSICGT.capacity_per_day_dsp <= 0);
SELECT PSICGT.user_key ,
'po_supplier_item_capacity_gt' AS entity ,
fnd_message.get_string('PO','DATES_OVERLAPPED') AS msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_supplier_item_capacity_gt PSICGT ,
po_approved_supplier_list_gt ASLGT
WHERE 2 <= (SELECT Count(user_key)
FROM po_supplier_item_capacity_gt PSIC
WHERE (PSICGT.from_date_dsp
BETWEEN PSIC.from_date_dsp AND PSIC.to_date_dsp
OR PSICGT.to_date_dsp
BETWEEN PSIC.from_date_dsp AND PSIC.to_date_dsp)
AND PSICGT.user_key = PSIC.user_key
AND PSICGT.using_organization_id = PSIC.using_organization_id)
AND PSICGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
AND PSICGT.process_action = PO_ASL_API_PUB.g_ACTION_ADD;
SELECT PSICGT.user_key ,
'po_supplier_item_capacity_gt' AS entity ,
fnd_message.get_string('PO','DATES_OVERLAPPED') AS msg
BULK COLLECT INTO
l_user_key_tbl ,
l_entity_name ,
l_reject_reason
FROM po_supplier_item_capacity_gt PSICGT ,
po_approved_supplier_list_gt ASLGT
WHERE EXISTS
(SELECT 1
FROM po_supplier_item_capacity PSIC
WHERE (PSICGT.from_date_dsp
BETWEEN PSIC.from_date AND PSIC.To_Date
OR PSICGT.to_date_dsp
BETWEEN PSIC.from_date AND PSIC.To_Date)
AND PSICGT.asl_id =PSIC.asl_id
AND PSICGT.using_organization_id=PSIC.using_organization_id)
AND PSICGT.user_key = ASLGT.user_key
AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
AND PSICGT.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_reject_reason
FROM (
--Reject records if number_of_days_dsp is less than 1
SELECT user_key ,
'po_supplier_item_tolerance_gt' AS entity,
fnd_message.get_string('PO','INVALID_NUM_OF_DAYS') AS msg
FROM po_supplier_item_tolerance_gt PSITGT
WHERE PSITGT.number_of_days_dsp IS NULL
OR PSITGT.number_of_days_dsp <= 0
UNION ALL
--Reject records if tolerance_dsp is less than 1
SELECT user_key ,
'po_supplier_item_tolerance_gt' AS entity,
fnd_message.get_string('PO','INVALID_TOLERANCE') AS msg
FROM po_supplier_item_tolerance_gt PSITGT
WHERE PSITGT.tolerance_dsp IS NULL
OR PSITGT.tolerance_dsp <= 0
);
SELECT 1 INTO l_found
FROM dual
WHERE EXISTS
(SELECT asl_id
FROM po_approved_supplier_list PASL
WHERE (PASL.item_id = p_item_id
OR PASL.category_id = p_category_id)
AND PASL.using_organization_id = p_using_organization_id
AND PASL.vendor_id = p_vendor_id
AND Nvl(PASL.vendor_site_id,-1) = Nvl(p_vendor_site_id,-1));
l_process_action := PO_ASL_API_PUB.g_ACTION_UPDATE;