[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PHA.segment1,
PHA.revision_num,
PHA.comments,
VO.vendor_name,
PHA.type_lookup_code,
PHA.po_header_id,
HRL_B.location_code bill_to_location,
HRL_S.location_code ship_to_location,
DECODE(PHA.vendor_contact_id, NULL, NULL,
VC.last_name||', '||VC.first_name) vendor_contact,
PHA.blanket_total_amount
FROM PO_HEADERS_ALL PHA,
PO_VENDORS VO,
PO_VENDOR_CONTACTS VC,
HR_LOCATIONS_ALL_TL HRL_S,
HR_LOCATIONS_ALL_TL HRL_B
WHERE PHA.po_header_id = p_po_header_id
AND PHA.vendor_id = VO.vendor_id
AND VC.vendor_contact_id (+) = PHA.vendor_contact_id
AND HRL_S.location_id (+) = PHA.ship_to_location_id
AND HRL_S.language(+) = USERENV('LANG')
AND HRL_B.location_id (+) = PHA.bill_to_location_id
AND HRL_B.language(+) = USERENV('LANG');
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT type_name
INTO l_doc_display_name
FROM PO_DOCUMENT_TYPES
WHERE document_type_code = l_document_type
AND document_subtype = l_document_subtype;
SELECT NVL(poh.conterms_exist_flag,'N')
INTO l_conterms_exist
FROM po_headers_all poh
WHERE poh.po_header_id = l_document_id;
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
/* SELECT HRL.name
INTO l_buyer_org
FROM HR_OPERATING_UNITS HRO,
HR_LEGAL_ENTITIES HRL
WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
AND HRO.organization_id = l_orgid; */
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT displayed_field
INTO l_response_code
FROM Po_Lookup_Codes
WHERE Lookup_Type = 'ERECORD_RESPONSE'
AND Lookup_Code = l_response;
SELECT displayed_field
INTO l_reason_code
FROM Po_Lookup_Codes
WHERE Lookup_Type = 'ERECORD_REASON'
AND Lookup_Code = 'ERES_REASON';
SELECT displayed_field
INTO l_signer_type
FROM Po_Lookup_Codes
WHERE Lookup_Type = 'ERECORD_SIGNER_TYPE'
AND Lookup_Code = Decode(l_signer,'SUPPLIER','SUPPLIER','BUYER','CUSTOMER');
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
'End erecords_exception:PO_SIGNATURE_PVT.CREATE_ERECORD ');
PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
'ERROR RETURNED '||l_msg_data);
l_last_update_date PO_ACCEPTANCES.last_update_date%TYPE;
l_last_updated_by PO_ACCEPTANCES.last_updated_by%TYPE;
l_last_update_login PO_ACCEPTANCES.last_update_login%TYPE;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT HP.person_title
INTO l_role
FROM FND_USER FU,
HZ_PARTIES HP
WHERE HP.party_id = FU.customer_id
AND FU.user_id = fnd_global.user_id;
SELECT wf_item_type,
wf_item_key
INTO l_po_itemtype,
l_po_itemkey
FROM PO_HEADERS_ALL
WHERE po_header_id = l_document_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT displayed_field
INTO l_response_code
FROM Po_Lookup_Codes
WHERE Lookup_Type = 'ERECORD_RESPONSE'
AND Lookup_Code = l_response;
PO_ACCEPTANCES_INS_PVT.insert_row(
x_rowid => l_rowid,
x_acceptance_id => l_acceptance_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_po_header_id => l_document_id,
p_po_release_id => Null,
p_action => l_response_code,
p_action_date => l_acceptance_date,-- CONTERMS FPJ
p_employee_id => l_employee_id,
p_revision_num => l_revision_num,
p_accepted_flag => l_accepted_flag,
p_acceptance_lookup_code => Null,
p_note => l_acceptance_note,
p_accepting_party => l_accepting_party,
p_signature_flag => 'Y',
p_erecord_id => l_erecord_id,
p_role => l_role,
x_last_update_date => l_last_update_date,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login);
Update_Po_Details(p_po_header_id => l_document_id,
p_status => 'REJECTED',
p_action_code => l_action_code,
p_object_type_code => l_document_type_code,
p_object_subtype_code => l_document_sub_type_code,
p_employee_id => l_employee_id,
p_revision_num => l_revision_num);
Update_Po_Details(p_po_header_id => l_document_id,
p_status => 'APPROVED',
p_action_code => 'SIGNED',
p_object_type_code => l_document_type_code,
p_object_subtype_code => l_document_sub_type_code,
p_employee_id => l_employee_id,
p_revision_num => l_revision_num);
PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
p_po_header_id => l_document_id,
p_signed_date => l_acceptance_date,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 03'||'Updated PO tables';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'End contracts_call_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'ERROR RETURNED '||l_msg_data);
PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
'End binding_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');
PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
'ERROR RETURNED '||l_msg_data);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
/* SELECT HRL.name
INTO l_buyer_org
FROM HR_OPERATING_UNITS HRO,
HR_LEGAL_ENTITIES HRL
WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
AND HRO.organization_id = l_orgid; */
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
/* SELECT HRL.name
INTO l_buyer_org
FROM HR_OPERATING_UNITS HRO,
HR_LEGAL_ENTITIES HRL
WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
AND HRO.organization_id = l_orgid;*/
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress);
l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document Type = '||l_document_type);
l_progress:='020'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document ID = '||l_document_id);
SELECT acceptance_required_flag
INTO l_acceptance_flag
FROM po_headers_all
WHERE po_header_id = l_document_id;
l_progress:='030'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Acceptance Required Flag = '||l_acceptance_flag);
l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = TRUE');
l_progress:='050'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = FALSE');
PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||SQLERRM);
SELECT 'Y'
INTO l_signatures
FROM dual
WHERE EXISTS (SELECT 1
FROM PO_ACTION_HISTORY
WHERE object_id = p_document_id
AND object_type_code IN ('PO','PA')
AND action_code = 'SIGNED');
PROCEDURE Update_Po_Details(p_po_header_id IN NUMBER,
p_status IN VARCHAR2,
p_action_code IN VARCHAR2,
p_object_type_code IN VARCHAR2,
p_object_subtype_code IN VARCHAR2,
p_employee_id IN NUMBER,
p_revision_num IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PO_DETAILS'; -- Bug 3602512
/*Added NVL condition for approved_flag to update the value after buyer signed the
document.*/
UPDATE PO_LINE_LOCATIONS_ALL
SET approved_flag = 'Y',
approved_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
WHERE po_header_id = p_po_header_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
-- : Include PREPAYMENT shipment_type
AND shipment_type IN ('STANDARD','BLANKET','SCHEDULED','PREPAYMENT')
AND NVL(approved_flag,'N') <> 'Y';
UPDATE PO_HEADERS_ALL
SET authorization_status = p_status,
approved_flag = l_approved_flag,
pending_signature_flag = 'N',
acceptance_required_flag = 'N',
acceptance_due_date = Null,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
last_update_date = sysdate
WHERE po_header_id = p_po_header_id;
PO_ACTION_HISTORY_SV.insert_action_history(
p_doc_id_tbl => po_tbl_number(p_po_header_id)
, p_doc_type_tbl => po_tbl_varchar30(p_object_type_code)
, p_doc_subtype_tbl => po_tbl_varchar30(p_object_subtype_code)
, p_doc_revision_num_tbl => po_tbl_number(p_revision_num)
, p_action_code_tbl => po_tbl_varchar30(p_action_code)
, p_employee_id => p_employee_id -- bug3738420
);
PO_DELREC_PVT.create_update_delrec (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action => 'APPROVE',
p_doc_type => p_object_type_code,
p_doc_subtype => p_object_subtype_code,
p_doc_id => p_po_header_id,
p_line_id => NULL,
p_line_location_id => NULL
);
END UPDATE_PO_DETAILS;
SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
INTO l_seq_for_item_key
FROM sys.dual;
SELECT item_key
INTO l_itemkey
FROM WF_ITEMS
WHERE item_type = 'POAPPRV'
AND item_key LIKE l_itemkey_like
AND end_date IS NULL;
SELECT item_key
INTO l_itemkey
FROM WF_ITEMS
WHERE item_type = 'POAPPRV'
AND item_key = p_itemkey
AND end_date IS NULL;
SELECT WPA.activity_name
INTO l_activity_name
FROM WF_PROCESS_ACTIVITIES WPA,
WF_ITEM_ACTIVITY_STATUSES WIA
WHERE WIA.item_type = 'POAPPRV'
AND WIA.item_key = p_itemkey
AND WIA.process_activity = WPA.INSTANCE_ID
AND WPA.activity_name IN ('BLOCK_PREAPP','BLOCK_CHGAPP')
AND WIA.activity_status = 'NOTIFIED';
SELECT object_revision_num
FROM PO_ACTION_HISTORY PAH
WHERE PAH.object_id = p_po_header_id
AND PAH.object_type_code IN ('PO','PA')
AND (
(PAH.action_code = 'SIGNED')
OR
(PAH.action_code = 'APPROVE'
and
not exists (
SELECT 1
FROM PO_ACTION_HISTORY PAH1
WHERE PAH1.object_id = PAH.object_id
AND PAH1.object_type_code = pah.object_type_code
AND PAH1.action_code IN ('BUYER REJECTED','SUPPLIER REJECTED')
AND PAH1.object_revision_num = PAH.object_revision_num
)
)
)
AND PAH.object_revision_num < p_revision_num
ORDER BY object_revision_num DESC;
SELECT NVL(pending_signature_flag,'N')
INTO x_pending_signature
FROM PO_HEADERS_ALL
WHERE po_header_id = p_po_header_id
AND nvl(user_hold_flag, 'N') <> 'Y';
SELECT 'Y'
INTO x_erecord_exist
FROM dual
WHERE EXISTS (SELECT 1
FROM PO_ACCEPTANCES
WHERE po_header_id = p_po_header_id
AND revision_num = p_revision_num
AND signature_flag = 'Y'
AND erecord_id IS NOT NULL);
SELECT SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',1,0))) Buyer_Accepted,
SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',0,1))) Buyer_Rejected,
SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',1,0))) Supplier_Accepted,
SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',0,1))) Supplier_Rejected
INTO l_buyer_accepted_count,
l_buyer_rejected_count,
l_supplier_accepted_count,
l_supplier_rejected_count
FROM PO_ACCEPTANCES
WHERE Po_Header_Id = p_po_header_id
AND Revision_Num = p_revision_num
AND Signature_Flag = 'Y';
SELECT
Type_Lookup_Code
, DECODE( type_lookup_code
, PO_CONSTANTS_SV.BLANKET, PO_CONSTANTS_SV.PA
, PO_CONSTANTS_SV.CONTRACT, PO_CONSTANTS_SV.PA
, PO_CONSTANTS_SV.PO
)
, Agent_Id,
wf_item_type,
wf_item_key
INTO l_type_lookup_code,
l_object_code,
l_agent_id,
l_po_itemtype,
l_po_itemkey
FROM PO_HEADERS_ALL
WHERE Po_Header_Id = p_po_header_id;
update_po_details(
p_po_header_id => p_po_header_id,
p_status => 'REJECTED',
p_action_code => 'BUYER REJECTED',
p_object_type_code => l_object_code,
p_object_subtype_code => l_type_lookup_code,
p_employee_id => l_employee_id, -- bug3738420
p_revision_num => p_revision_num);
update_po_details(
p_po_header_id => p_po_header_id,
p_status => 'APPROVED',
p_action_code => 'SIGNED',
p_object_type_code => l_object_code,
p_object_subtype_code => l_type_lookup_code,
p_employee_id => l_employee_id, -- bug3738420
p_revision_num => p_revision_num);
SELECT max(action_date)
INTO l_acceptance_date
FROM PO_ACCEPTANCES
WHERE Po_Header_Id = p_po_header_id
AND Revision_Num = p_revision_num
AND Signature_Flag = 'Y'
AND ACCEPTING_PARTY IN ('B','S')
AND ACCEPTED_FLAG= 'Y';
PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
p_po_header_id => p_po_header_id,
p_signed_date => l_acceptance_date,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
SELECT Count(Signature_Flag)
INTO l_no_signatures
FROM PO_ACCEPTANCES
WHERE po_header_id = p_po_header_id
AND revision_num = p_revision_num
AND signature_flag = 'Y'
AND accepting_party = 'B' --bug 3420562
AND erecord_id IS NULL;
SELECT Count(Signature_Flag)
INTO l_no_signatures
FROM PO_ACCEPTANCES
WHERE po_header_id = p_po_header_id
AND revision_num = p_revision_num
AND signature_flag = 'Y'
AND erecord_id IS NOT NULL;
SELECT DECODE(accepted_flag, 'N', 'Y', 'N')
INTO x_supplier_rejected
FROM po_acceptances
WHERE po_header_id = p_po_header_id
AND revision_num = p_revision_num
AND accepting_party = 'S'
AND signature_flag = 'Y';
SELECT 'Y'
INTO x_buyer_rejected
FROM po_acceptances
WHERE po_header_id = p_po_header_id
AND revision_num = p_revision_num
AND accepting_party = 'B'
AND accepted_flag = 'N'
AND signature_flag= 'Y';
x_if_acc_flag_updated OUT NOCOPY VARCHAR2)
IS
l_was_sign_reqd boolean := FALSE;
x_if_acc_flag_updated := 'N';
UPDATE PO_HEADERS_ALL POH
SET POH.acceptance_required_flag = 'S'
WHERE POH.po_header_id = p_document_id;
x_if_acc_flag_updated := 'Y';
PO_LOG.proc_end(d_module, 'x_if_acc_flag_updated', x_if_acc_flag_updated);
x_if_acc_flag_updated OUT NOCOPY VARCHAR2)
IS
d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.if_rev_and_signed_set_acc_flag';
SELECT revision_num
INTO l_revision_num
FROM po_headers_all
WHERE po_header_id = p_document_id;
x_if_acc_flag_updated => x_if_acc_flag_updated);
x_if_acc_flag_updated := 'N';