[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
D_atleast_one_doc_selected CONSTANT VARCHAR2(100) :=
PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'atleast_one_doc_selected');
D_update_doc_status CONSTANT VARCHAR2(100) :=
PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'update_doc_status');
c_atleast_one_doc_selected CONSTANT VARCHAR2(30) := 'C_ATLEAST_ONE_DOC_SELECTED';
c_atleast_one_doc_selected
, c_exemption_reason_given
, c_new_vendor_contact_provided
, c_clause_change_valid
, c_atleast_one_change_done
, c_acceptance_due_date_valid
, c_no_of_copies_ge_zero
, c_uda_addresses_valid
, c_contract_officer_provided
-- , c_header_lock_compatible removing header lock validation from address change (Bug: 13566640)
, c_address_lock_compatible
, c_validate_open_mod
);
c_atleast_one_doc_selected
, c_exemption_reason_given
, c_new_vendor_contact_provided
, c_clause_change_valid
, c_atleast_one_change_done
, c_acceptance_due_date_valid
, c_no_of_copies_ge_zero
, c_vendor_name_chg_valid
, c_header_lock_compatible
, c_validate_open_mod
);
c_atleast_one_doc_selected
, c_exemption_reason_given
, c_new_vendor_contact_provided
, c_clause_change_valid
, c_atleast_one_change_done
, c_new_vendor_site_provided
, c_new_vendor_site_CCR_valid
, c_acceptance_due_date_valid
, c_no_of_copies_ge_zero
, c_vendor_chg_valid
, c_new_vendor_on_hold
, c_header_lock_compatible
, c_validate_open_mod
);
DELETE FROM po_multi_mod_val_results
WHERE multi_mod_request_id = p_multi_mod_request_id;
SELECT multi_mod_request_type
,NVL(novation_effective_date, sysdate)
,acceptance_required_flag
,acceptance_due_date
,clm_noofcopies
,exemption_reason
,initiate_approval_flag
,clm_contract_officer
INTO l_multi_mod_request_type
,l_novation_effective_date
,l_acceptance_reqd_flag
,l_acceptance_due_date
,l_clm_noofcopies
,l_exemption_reason
,l_initiate_approval_flag
,l_clm_contract_officer
FROM po_multi_mod_requests
WHERE multi_mod_request_id = p_multi_mod_request_id;
SELECT multi_mod_doc_id
,multi_mod_request_id
,document_id
,status
,draft_id
BULK COLLECT INTO
l_multi_mod_docs.multi_mod_doc_id
,l_multi_mod_docs.multi_mod_request_id
,l_multi_mod_docs.document_id
,l_multi_mod_docs.status
,l_multi_mod_docs.draft_id
FROM po_multi_mod_docs
WHERE multi_mod_request_id = p_multi_mod_request_id;
SELECT multi_mod_change_id
,multi_mod_request_id
,change_type
,old_vendor_id
,old_vendor_name
,new_vendor_id
,new_vendor_name
,org_id
,old_vendor_site_id
,new_vendor_site_id
,new_vendor_contact_id
,old_remit_to_addr
,new_remit_to_addr
BULK COLLECT INTO
l_multi_mod_changes.multi_mod_change_id
,l_multi_mod_changes.multi_mod_request_id
,l_multi_mod_changes.change_type
,l_multi_mod_changes.old_vendor_id
,l_multi_mod_changes.old_vendor_name
,l_multi_mod_changes.new_vendor_id
,l_multi_mod_changes.new_vendor_name
,l_multi_mod_changes.org_id
,l_multi_mod_changes.old_vendor_site_id
,l_multi_mod_changes.new_vendor_site_id
,l_multi_mod_changes.new_vendor_contact_id
,l_multi_mod_changes.old_remit_to_addr
,l_multi_mod_changes.new_remit_to_addr
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_multi_mod_request_id;
WHEN c_atleast_one_doc_selected THEN
PO_MULTI_MOD_VALIDATIONS.atleast_one_doc_selected
( p_request_id => p_multi_mod_request_id
,p_multi_mod_doc_id_tbl => p_multi_mod_docs.multi_mod_doc_id
,p_validation_type => p_validation_type
,x_result_type => l_result_type
,x_results => x_results
);
PROCEDURE atleast_one_doc_selected
( p_request_id IN NUMBER
,p_multi_mod_doc_id_tbl IN PO_TBL_NUMBER
,p_validation_type IN VARCHAR2
,x_result_type OUT NOCOPY VARCHAR2
,x_results IN OUT NOCOPY PO_MULTI_MOD_VAL_RESULTS_TYPE
)
is
l_results_count NUMBER;
d_mod CONSTANT VARCHAR2(100) := D_atleast_one_doc_selected;
, p_message_name => PO_MESSAGE_S.PO_MULTIMOD_NO_DOC_SELECTED
);
END atleast_one_doc_selected;
SELECT vendor_contact_id
INTO l_vendor_contact_id
FROM po_headers_all
WHERE po_header_id = p_document_id_tbl(i);
SELECT 'Y'
INTO l_hold_exists
FROM po_vendors pov
,po_system_parameters psp
WHERE pov.vendor_id = p_new_vendor_id_tbl(i)
AND NVL(psp.enforce_vendor_hold_flag,'N') = 'Y'
AND NVL(pov.hold_flag,'N') = 'Y';
SELECT old_vendor_id, new_vendor_id
INTO l_old_vendor_id, l_new_vendor_id
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_request_id
AND change_type = 'VENDOR';
SELECT uda_template_id,
uda_key_po_header_id,
uda_key_draft_id
INTO l_uda_template_id,
l_uda_key_po_header_id,
l_uda_key_draft_id
FROM po_multi_mod_requests
WHERE multi_mod_request_id = p_request_id;
SELECT attr_group_id
INTO l_addr_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS'
AND attr_group_name = 'addresses';
SELECT NVL(SUM(NVL(n_ext_attr1,0) + NVL(n_ext_attr2,0) + NVL(n_ext_attr3,0) +
NVL(n_ext_attr4,0) + NVL(n_ext_attr5,0) + NVL(n_ext_attr6,0) +
NVL(n_ext_attr7,0) + NVL(n_ext_attr8,0) + NVL(n_ext_attr9,0) +
NVL(n_ext_attr10,0) + NVL(n_ext_attr11,0) + NVL(n_ext_attr12,0) +
NVL(n_ext_attr13,0) + NVL(n_ext_attr14,0) + NVL(n_ext_attr15,0) +
NVL(n_ext_attr16,0) + NVL(n_ext_attr17,0) + NVL(n_ext_attr18,0) +
NVL(n_ext_attr19,0) + NVL(n_ext_attr20,0)),0) attr_sum
INTO l_sum_check
FROM po_headers_all_ext_b ext,
po_multi_mod_requests multimod
WHERE ext.po_header_id = multimod.uda_key_po_header_id
AND ext.draft_id = multimod.uda_key_draft_id
AND multimod.multi_mod_request_id = p_request_id
AND ext.attr_group_id = l_addr_attr_group_id
AND ext.c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE');
SELECT Count(*)
INTO l_count
FROM po_multi_mod_clause_changes
WHERE multi_mod_request_id = p_request_id;
SELECT multi_mod_request_type
INTO l_multi_mod_request_type
FROM po_multi_mod_requests
WHERE multi_mod_request_id = p_request_id;
SELECT pmmd.document_id, pha.clm_document_number, pd.modification_number
BULK COLLECT INTO l_document_id_tbl, l_document_num_tbl, l_modification_num_tbl
FROM po_entity_locks pel,
po_multi_mod_docs pmmd,
po_headers_all pha,
po_doc_style_headers pdsh,
po_drafts pd
WHERE pmmd.multi_mod_request_id = p_request_id
AND pha.po_header_id = pmmd.document_id
AND pmmd.document_id = To_Number(pel.entity_pk1)
AND pd.draft_id = pel.lock_by_draft_id
AND pel.entity_name = 'PO_HEADER'
AND pel.lock_type IN ('F', 'P')
AND pdsh.style_id = pha.style_id
AND Nvl(pdsh.conc_mods_enabled_flag, 'N') = 'Y';
SELECT attr_group_id
INTO l_addr_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS'
AND attr_group_name = 'addresses';
SELECT
pha.clm_document_number,
(SELECT DISPLAYED_FIELD FROM po_lookup_codes
WHERE lookup_type LIKE 'PO_UDA_ADDRESS_TYPES'
AND LOOKUP_CODE = ext.c_ext_attr39) DISPLAYED_FIELD,
pd.modification_number,
NVL(SUM(NVL(n_ext_attr1,0) + NVL(n_ext_attr2,0) + NVL(n_ext_attr3,0) +
NVL(n_ext_attr4,0) + NVL(n_ext_attr5,0) + NVL(n_ext_attr6,0) +
NVL(n_ext_attr7,0) + NVL(n_ext_attr8,0) + NVL(n_ext_attr9,0) +
NVL(n_ext_attr10,0) + NVL(n_ext_attr11,0) + NVL(n_ext_attr12,0) +
NVL(n_ext_attr13,0) + NVL(n_ext_attr14,0) + NVL(n_ext_attr15,0) +
NVL(n_ext_attr16,0) + NVL(n_ext_attr17,0) + NVL(n_ext_attr18,0) +
NVL(n_ext_attr19,0) + NVL(n_ext_attr20,0)),0) attr_sum
BULK COLLECT INTO l_document_number_tbl,
l_address_code_tbl, l_modification_number_tbl,
l_sum_check_tbl
FROM po_headers_all_ext_b ext,
po_multi_mod_requests multimod,
po_entity_locks pel,
po_multi_mod_docs pmmd,
po_headers_all pha,
po_doc_style_headers pdsh,
po_drafts pd
WHERE ext.po_header_id = multimod.uda_key_po_header_id
AND ext.draft_id = multimod.uda_key_draft_id
AND ext.attr_group_id = l_addr_attr_group_id
AND multimod.multi_mod_request_id = p_request_id
AND pmmd.multi_mod_request_id = multimod.multi_mod_request_id
AND pha.po_header_id = pmmd.document_id
AND pd.draft_id = pel.lock_by_draft_id
AND ext.c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE')
AND pel.entity_name = 'PO_UDA_ADDRESS'
AND ext.c_ext_attr39 = pel.entity_pk2
AND To_Number(pel.entity_pk1) = pmmd.document_id
AND pdsh.style_id = pha.style_id
AND Nvl(pdsh.conc_mods_enabled_flag, 'N') = 'Y'
GROUP BY ext.c_ext_attr39, pha.clm_document_number, pd.modification_number;
SELECT pha.clm_document_number
BULK COLLECT INTO l_document_num_tbl
FROM po_multi_mod_docs pmmd,
po_headers_all pha,
po_doc_style_headers pdsh
WHERE pmmd.multi_mod_request_id = p_request_id
AND pmmd.document_id = pha.po_header_id
AND pha.style_id = pdsh.style_id
AND Nvl(pdsh.conc_mods_enabled_flag, 'N') = 'N'
AND EXISTS (SELECT 'Open Mod Exists'
FROM po_drafts dft
WHERE dft.document_id = pmmd.document_id
AND dft.status IN ('DRAFT', 'IN PROCESS', 'REJECTED', 'SUPPLIER SIGN', 'PRE-APPROVED'));
SELECT pmmd_a.document_id
,ph_a.clm_document_number
,ph_e.po_header_id
,ph_e.clm_document_number
BULK COLLECT INTO l_document_id_tbl
,l_document_number_tbl
,l_related_document_id_tbl
,l_related_doc_number_tbl
FROM po_multi_mod_docs pmmd_a,/*current set of documents being validated*/
po_headers_all ph_a, /*current document joined to headers, filtered by BLANKET/CONTRACT*/
po_headers_all ph_e /*list of execution documents tied to the agreement*/
WHERE pmmd_a.document_id = ph_a.po_header_id
AND pmmd_a.multi_mod_request_id = p_request_id
AND ph_a.type_lookup_code IN ('BLANKET', 'CONTRACT')
AND ph_e.clm_source_document_id = ph_a.po_header_id
AND NVL(ph_e.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND NVL(ph_e.authorization_status,'INCOMPLETE') = 'APPROVED'
AND ph_e.po_header_id NOT IN /*Subquery to retrieve all the documents in current batch*/
(SELECT pmmd_e.document_id FROM po_multi_mod_docs pmmd_e WHERE pmmd_e.multi_mod_request_id = p_request_id );
SELECT ph_e.po_header_id
,ph_e.clm_document_number
,ph_a.po_header_id
,ph_a.clm_document_number
BULK COLLECT INTO l_document_id_tbl
,l_document_number_tbl
,l_related_document_id_tbl
,l_related_doc_number_tbl
FROM po_multi_mod_docs pmmd_e,
po_headers_all ph_e,
po_headers_all ph_a
WHERE pmmd_e.multi_mod_request_id = p_request_id
AND ph_e.po_header_id = pmmd_e.document_id
AND ph_e.type_lookup_code = 'STANDARD'
AND ph_e.clm_source_document_id = ph_a.po_header_id
AND ph_e.clm_source_document_id IS NOT NULL
AND ph_e.clm_source_document_id NOT IN (SELECT pmmd_a.document_id FROM po_multi_mod_docs pmmd_a WHERE pmmd_a.multi_mod_request_id = p_request_id);
SELECT shipment_line_id
BULK COLLECT INTO l_shipment_line_id_tbl
FROM rcv_shipment_lines
WHERE po_header_id = p_document_id_tbl(i);
SELECT rcvh.shipment_header_id
,rcvh.receipt_num
,Min(rt.transaction_date)
INTO l_shipment_header_id
,l_receipt_num
,l_receipt_date
FROM rcv_shipment_lines rcvl
,rcv_shipment_headers rcvh
, rcv_transactions rt
WHERE rcvl.shipment_header_id = rcvh.shipment_header_id
AND rt.shipment_header_id = rcvh.shipment_header_id
AND rt.transaction_type IN ('RECEIVE', 'MATCH')
AND rcvl.shipment_line_id = l_shipment_line_id_tbl(i)
GROUP BY rcvh.shipment_header_id,
rcvh.receipt_num;
SELECT Nvl(Sum(Nvl(rsl.quantity_received, 0) * NVL(poll.price_override, pol.unit_price) ), 0)
INTO l_receipt_amount
FROM rcv_shipment_lines rsl,
po_line_locations_all poll,
po_lines_all pol
WHERE rsl.po_line_location_id = poll.line_location_id
AND rsl.po_line_id = pol.po_line_id
AND pol.po_line_id = poll.po_line_id
AND rsl.quantity_shipped IS NOT NULL
AND rsl.shipment_line_id = l_shipment_line_id_tbl(i);
SELECT Nvl(Sum(Nvl(rsl.amount_received, 0)), 0)
INTO l_receipt_amount
FROM rcv_shipment_lines rsl
WHERE rsl.quantity_shipped IS NULL
AND rsl.shipment_line_id = l_shipment_line_id_tbl(i);
SELECT old_vendor_id
INTO l_vendor_id
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_request_id
AND change_type = 'VENDOR';
SELECT match_option
,inspection_required_flag
,receipt_required_flag
INTO l_match_option
,l_inspection_required_flag
,l_receipt_required_flag
FROM po_line_locations_all
WHERE po_header_id = l_results.document_id(i) /*Bug 13055377 - the wrong document_id was being passes and hence the proper exceptions were not shown.passing the correct documemt_id*/
AND match_option='R' -- Bug 13690746 - get the first shipment with match_option 'R'
AND ROWNUM =1; -- Bug 13690746 - get the first shipment with match_option 'R'
SELECT apl.invoice_id, ap.invoice_num, ap.invoice_date, ap.invoice_amount
BULK COLLECT INTO l_invoice_id_tbl, l_invoice_num_tbl,l_invoice_date_tbl, l_invoice_amount_tbl
FROM ap_invoice_lines_all apl
,ap_invoices_all ap
WHERE apl.invoice_id = ap.invoice_id
AND apl.match_type = 'ITEM_TO_PO'
AND apl.po_header_id = p_document_id_tbl(i);
SELECT old_vendor_id
INTO l_vendor_id
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_request_id
AND change_type = 'VENDOR';
SELECT 'N'
INTO l_unpaid_invoices_exists
FROM ap_payment_schedules_all
WHERE invoice_id = p_invoice_id
AND amount_remaining = 0
AND payment_status_flag = 'Y';
SELECT 'Y'
INTO l_match_exists
FROM ap_invoice_lines_all inv,
rcv_transactions rcvt
WHERE inv.rcv_transaction_id = rcvt.transaction_id
AND inv.match_type = 'ITEM_TO_RECEIPT'
AND rcvt.shipment_line_id = p_shipment_line_id;
SELECT inv.invoice_id
INTO l_invoice_id
FROM ap_invoice_lines_all inv,
rcv_transactions rcvt
WHERE inv.rcv_transaction_id = rcvt.transaction_id
AND inv.match_type = 'ITEM_TO_RECEIPT'
AND rcvt.shipment_line_id = p_doc_id;
SELECT invoice_id
INTO l_invoice_id
FROM ap_invoice_lines_all
WHERE match_type = 'ITEM_TO_PO'
AND po_header_id = p_doc_id;
SELECT ap.invoice_id,ap.invoice_num, ap.invoice_date
BULK COLLECT INTO x_invoice_id_tbl, x_invoice_num_tbl, x_invoice_date_tbl
FROM ap_invoices_all ap
, ap_invoice_lines_all apl
WHERE ap.invoice_id = apl.invoice_id
AND ap.vendor_id = p_vendor_id
AND NVL(apl.match_type, 'NOT_MATCHED') = 'NOT_MATCHED';
SELECT ap.invoice_id,ap.invoice_num, ap.invoice_date
BULK COLLECT INTO x_invoice_id_tbl, x_invoice_num_tbl, x_invoice_date_tbl
FROM ap_invoice_lines_all apl
,ap_invoices_all ap
WHERE apl.prepay_invoice_id = ap.invoice_id
AND NVL(apl.match_type, 'NOT_MATCHED') = 'NOT_MATCHED'
AND ap.invoice_type_lookup_code = 'PREPAYMENT'
AND ap.vendor_id = p_vendor_id;
SELECT ap.invoice_id,ap.invoice_num, ap.invoice_date
BULK COLLECT INTO x_invoice_id_tbl, x_invoice_num_tbl, x_invoice_date_tbl
FROM ap_invoices_all ap
,ap_payment_schedules_all apps
WHERE ap.invoice_id = apps.invoice_id
AND NVL(ap.invoice_amount,0) <> NVL(ap.amount_paid,0)
AND NVL(apps.amount_remaining,0) <> 0
AND apps.payment_status_flag <> 'Y'
AND ap.vendor_id = p_vendor_id;
UPDATE po_multi_mod_requests
SET validation_status = p_validation_status
,last_update_date = sysdate
,last_update_login = fnd_global.login_id
,last_updated_by = fnd_global.user_id
WHERE multi_mod_request_id = p_multi_mod_request_id;
SELECT old_vendor_id
INTO l_vendor_id
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_request_id
AND change_type = 'VENDOR';
INSERT INTO po_multi_mod_val_results
( MULTI_MOD_VAL_RESULT_ID
,MULTI_MOD_REQUEST_ID
,MULTI_MOD_DOC_ID
,VALIDATION_TYPE
,EXCEPTION_TYPE
,RESULT_TYPE
,DOCUMENT_ID
,DOCUMENT_NUMBER
,RELATED_DOCUMENT_ID
,RELATED_DOCUMENT_NUMBER
,RELATED_DOCUMENT_DATE
,RELATED_DOCUMENT_AMOUNT
,MESSAGE_APPLICATION
,MESSAGE_NAME
,TOKEN1_NAME
,TOKEN1_VALUE
,TOKEN2_NAME
,TOKEN2_VALUE
,TOKEN3_NAME
,TOKEN3_VALUE
,TOKEN4_NAME
,TOKEN4_VALUE
,TOKEN5_NAME
,TOKEN5_VALUE
,TOKEN6_NAME
,TOKEN6_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,REQUEST_ID
)
VALUES
( l_results.multi_mod_val_result_id(i)
, l_results.multi_mod_request_id(i)
, l_results.multi_mod_doc_id(i)
, l_results.validation_type(i)
, l_results.exception_type(i)
, l_results.result_type(i)
, l_results.document_id(i)
, l_results.document_number(i)
, l_results.related_document_id(i)
, l_results.related_document_number(i)
, l_results.related_document_date(i)
, l_results.related_document_amount(i)
, l_results.message_application(i)
, l_results.message_name(i)
, l_results.token1_name(i)
, l_results.token1_value(i)
, l_results.token2_name(i)
, l_results.token2_value(i)
, l_results.token3_name(i)
, l_results.token3_value(i)
, l_results.token4_name(i)
, l_results.token4_value(i)
, l_results.token5_name(i)
, l_results.token5_value(i)
, l_results.token6_name(i)
, l_results.token6_value(i)
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, sysdate
, fnd_global.user_id
, fnd_global.conc_request_id
);
/*bug 13094620 - the selection changed flag has to be reset once the supplier change exceptions program has been regenerated.*/
UPDATE po_multi_mod_requests
SET selection_changed_flag = null
WHERE multi_mod_request_id = p_multi_mod_request_id
and selection_changed_flag is not null;