[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT type_lookup_code,revision_num,clm_document_number,org_id,vendor_id, vendor_contact_id,vendor_site_id
INTO l_po_doc_type, l_rev_number, l_doc_num,l_org_id,l_vendor_id, l_vendor_contact_id,l_vendor_site_id
FROM PO_HEADERS_ALL
WHERE po_header_id = p_doc_id;
SELECT name
INTO l_org_name
FROM hr_all_organization_units_tl
WHERE organization_id = l_org_id
AND language = UserEnv('lang');
SELECT pv.vendor_name
INTO l_vendor_name
FROM po_vendors pv
WHERE pv.vendor_id = l_vendor_id;
SELECT Nvl(LAST_NAME, '') || ', ' || Nvl(FIRST_NAME, '')
INTO l_vendor_contact_name
FROM po_vendor_contacts
WHERE vendor_contact_id = l_vendor_contact_id
AND vendor_site_id = l_vendor_site_id;
SELECT type_lookup_code,revision_num,clm_document_number
INTO l_po_doc_type, l_rev_number, l_doc_num
FROM PO_HEADERS_ALL
WHERE po_header_id = p_doc_id;
SELECT Count(1)
INTO l_count
FROM po_drafts dft
WHERE dft.document_id = p_doc_id
AND dft.draft_type= 'MOD'
AND dft.status IN ('DRAFT', 'IN PROCESS', 'REJECTED', 'SUPPLIER SIGN', 'PRE-APPROVED');
SELECT Count(1)
INTO l_count
FROM po_line_locations_all poll
WHERE poll.po_header_id = p_doc_id
AND Nvl(poll.closed_code,'OPEN') IN ('OPEN')
AND Nvl(poll.cancel_flag, 'N') = 'N';
SELECT Count(1)
INTO l_count
FROM po_line_locations_all poll
WHERE poll.po_header_id = p_doc_id
AND nvl(poll.closed_code,'OPEN') IN ('OPEN','CLOSED FOR RECEIVING',' CLOSED FOR INVOICE');
SELECT Count(1)
INTO l_count
FROM ap_invoice_distributions_all aid,
ap_invoices_all aia,
po_distributions_all pod,
po_line_locations_all poll
WHERE aid.po_distribution_id = pod.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND pod.po_header_id = poll.po_header_id
AND poll.po_header_id = p_doc_id;
SELECT Count(1)
INTO l_count
FROM ap_invoice_distributions_all aid,
ap_invoices_all aia,
po_distributions_all pod,
po_line_locations_all poll
WHERE aid.po_distribution_id = pod.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND pod.po_header_id = poll.po_header_id
AND poll.po_header_id = p_doc_id
AND nvl(poll.closed_code,'OPEN') <> 'CLOSED FOR INVOICE'
AND aia.payment_status_flag <> 'Y';
SELECT type_lookup_code,revision_num
INTO l_po_doc_type, l_rev_number
FROM PO_HEADERS_ALL
WHERE po_header_id = p_doc_id;
/*SELECT Count(1)
INTO l_count
FROM okc_rep_contract_usages orcu, okc_deliverables del
WHERE orcu.business_document_type = l_contr_type
AND orcu.business_document_id = p_doc_id -- po_header_id
--AND orcu.business_document_version = l_rev_number revision_num
AND del.business_document_type = orcu.contract_type
AND del.business_document_id = orcu.contract_id
AND del.business_document_version = -99; */
SELECT clm_document_number
INTO l_clm_doc_num
FROM po_headers_all
WHERE po_header_id = p_doc_id;
SELECT type_lookup_code
INTO l_type_lookup_code
FROM PO_HEADERS_ALL
WHERE po_header_id = p_doc_id;
SELECT document_id
FROM po_multi_mod_docs
WHERE MULTI_MOD_REQUEST_ID = p_req_id;
DELETE
FROM po_multi_mod_val_results
WHERE multi_mod_request_id = p_req_id;
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
( po_multi_mod_val_results_s.nextval
, p_req_id
, l_doc_id
, l_results.validation_type(i)
, l_results.exception_type(i)
, l_results.result_type(i)
, l_doc_id
, 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
);
UPDATE po_multi_mod_requests
SET VALIDATION_STATUS = l_validation_status
WHERE MULTI_MOD_REQUEST_ID = p_req_id;
SELECT document_id
FROM po_multi_mod_docs
WHERE MULTI_MOD_REQUEST_ID = p_req_id;
SELECT check_list_required
INTO l_checklist_req
FROM po_closeout_details
WHERE reference_doc_id = p_req_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_MASS;
SELECT type_lookup_code,revision_num,clm_document_number,org_id,vendor_id,vendor_contact_id,vendor_site_id
INTO l_po_doc_type, l_rev_number, l_doc_num,l_org_id,l_vendor_id,l_vendor_contact_id,l_vendor_site_id
FROM PO_HEADERS_ALL
WHERE po_header_id = l_doc_id;
SELECT Count(reference_doc_id)
INTO l_closeout_doc_count
FROM PO_CLOSEOUT_DETAILS
WHERE reference_doc_id = l_doc_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
INSERT
INTO PO_CLOSEOUT_DETAILS (
closeout_id,
closeout_doc_type,
reference_doc_id,
check_list_required,
ccs_required,
check_list_sign_required,
ccs_sign_required,
check_list_signer_id,
aco_signature_type,
aco_signer_id,
pco_signature_type,
pco_signer_id,
rentention_period_uom,
rentention_period,
staging_period_uom,
staging_period,
aco_remarks,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
incloseout,
closeout_date,
pco_remarks,
aco_signer_name,
pco_signer_name,
aco_signed_date,
pco_signed_date,
checklist_signed_date,
is_reopened)
(SELECT PO_CLOSEOUT_DETAILS_S.NEXTVAL,
PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL,
l_doc_id,
check_list_required,
ccs_required,
check_list_sign_required,
ccs_sign_required,
check_list_signer_id,
aco_signature_type,
aco_signer_id,
pco_signature_type,
pco_signer_id,
rentention_period_uom,
rentention_period,
staging_period_uom,
staging_period,
aco_remarks,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
'Y',
closeout_date,
pco_remarks,
aco_signer_name,
pco_signer_name,
aco_signed_date,
pco_signed_date,
checklist_signed_date,
is_reopened
FROM PO_CLOSEOUT_DETAILS
WHERE reference_doc_id = p_req_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_MASS);
SELECT name
INTO l_org_name
FROM hr_all_organization_units_tl
WHERE organization_id = l_org_id
AND language = UserEnv('lang');
SELECT pv.vendor_name
INTO l_vendor_name
FROM po_vendors pv
WHERE pv.vendor_id = l_vendor_id;
SELECT Nvl(LAST_NAME, '') || ', ' || Nvl(FIRST_NAME, '')
INTO l_vendor_contact_name
FROM po_vendor_contacts
WHERE vendor_contact_id = l_vendor_contact_id
AND vendor_site_id= l_vendor_site_id;
PO_CLOSEOUT_PVT.delete_contract(p_doc_id => p_req_id,
p_closeout_type => PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_MASS,
x_return_status => l_return_status,
x_return_msg => l_msg_data);
fnd_file.put_line(FND_FILE.LOG, 'Delete contract status ' || l_return_status);
fnd_file.put_line(FND_FILE.LOG, 'return message from Delete contract : ' || l_msg_data);
PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Delete contract status ' || l_return_status);
PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'return message from Delete contract : ' || l_msg_data);
p_selected_docs IN PO_TBL_NUMBER
, x_failed_docs OUT NOCOPY VARCHAR2
, x_result_type OUT NOCOPY VARCHAR2
)
AS
d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'mass_closeout_validate_set';
SELECT listagg(DOCUMENT_ID,',') within GROUP (ORDER BY DOCUMENT_ID)
INTO l_doc_ids
FROM po_multi_mod_docs docs,
po_multi_mod_requests req
WHERE docs.MULTI_MOD_REQUEST_ID = req.MULTI_MOD_REQUEST_ID
AND req.MULTI_MOD_REQUEST_TYPE = 'INITIATE_CLOSEOUT'
AND docs.DOCUMENT_ID IN (SELECT *
FROM TABLE (p_selected_docs));
l_query := 'SELECT listagg(clm_document_number,'','') within GROUP (ORDER BY clm_document_number)
FROM po_headers_all
WHERE po_header_id IN (' || l_doc_ids || ')';
DELETE
FROM po_multi_mod_val_results
WHERE multi_mod_request_id = p_req_id;
PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Num of Records deleted from po_multi_mod_val_results:' || SQL%ROWCOUNT);
SELECT reference_doc_id,null,null
BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl, l_mod_doc_id_tbl
FROM po_closeout_details
WHERE closeout_doc_type = 'MASS'
AND reference_doc_id = p_req_id
AND NOT EXISTS (SELECT 'Tasks not available'
FROM okc_rep_contract_usages orcu, okc_deliverables del
WHERE orcu.business_document_type = 'PO_CLOSEOUT_SET'
AND orcu.business_document_id = p_req_id
AND del.business_document_type = orcu.contract_type
AND del.business_document_id = orcu.contract_id
AND del.business_document_version = -99
);
SELECT Decode(l_module_status, 'with errors',
'with errors',
'with warnings')
INTO l_module_status
FROM dual;
SELECT clm_document_number
INTO l_clm_doc_num
FROM po_headers_all
WHERE po_header_id = x_qa_result_tbl(i).Document_id;
SELECT pha.po_header_id,pha.clm_document_number, pmmd.multi_mod_doc_id
BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
FROM po_multi_mod_docs pmmd,
po_headers_all pha
WHERE pmmd.multi_mod_request_id = p_req_id
AND pmmd.document_id = pha.po_header_id
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')
AND dft.draft_type= 'MOD');
SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
FROM po_multi_mod_docs pmmd,
po_headers_all pha
WHERE pmmd.multi_mod_request_id = p_req_id
AND pmmd.document_id = pha.po_header_id
AND pha.type_lookup_code = 'STANDARD'
AND EXISTS ( SELECT 1
FROM po_line_locations_all poll
WHERE pha.po_header_id = poll.po_header_id
AND Nvl(poll.closed_code,'OPEN') = 'OPEN'
AND Nvl(poll.cancel_flag, 'N') = 'N');
SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
FROM po_multi_mod_docs pmmd,
po_headers_all pha
WHERE pmmd.multi_mod_request_id = p_req_id
AND pmmd.document_id = pha.po_header_id
AND pha.type_lookup_code = 'STANDARD'
AND EXISTS (SELECT 1
FROM po_line_locations_all poll
WHERE pha.po_header_id = poll.po_header_id
AND nvl(poll.closed_code,'OPEN') IN ('OPEN','CLOSED FOR RECEIVING',' CLOSED FOR INVOICE')
);
SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
FROM po_headers_all pha,
po_multi_mod_docs pmmd
WHERE pmmd.multi_mod_request_id = p_req_id
AND pmmd.document_id = pha.po_header_id
AND pha.type_lookup_code = 'STANDARD'
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all aid,
ap_invoices_all aia,
po_distributions_all pod,
po_line_locations_all poll
WHERE aid.po_distribution_id = pod.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND pod.po_header_id = poll.po_header_id
AND pha.po_header_id = poll.po_header_id);
SELECT DISTINCT pha.po_header_id,pha.clm_document_number,pmmd.multi_mod_doc_id
BULK COLLECT INTO l_document_id_tbl,l_document_num_tbl,l_mod_doc_id_tbl
FROM po_headers_all pha,
po_multi_mod_docs pmmd
WHERE pmmd.multi_mod_request_id = p_req_id
AND pmmd.document_id = pha.po_header_id
AND pha.type_lookup_code = 'STANDARD'
AND EXISTS (SELECT 1
FROM ap_invoice_distributions_all aid,
ap_invoices_all aia,
po_distributions_all pod,
po_line_locations_all poll
WHERE aid.po_distribution_id = pod.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND pod.po_header_id = poll.po_header_id
AND pha.po_header_id = poll.po_header_id
AND nvl(poll.closed_code,'OPEN') <> 'CLOSED FOR INVOICE'
AND aia.payment_status_flag <> 'Y');
UPDATE po_multi_mod_requests
SET VALIDATION_STATUS = l_validation_status
WHERE MULTI_MOD_REQUEST_ID = p_req_id;
SELECT DISTINCT clm_award_administrator
FROM po_headers ph,
po_doc_style_headers pdsh
WHERE clm_award_administrator IS NOT NULL
AND authorization_status = 'APPROVED'
AND Nvl(clm_closeout_status, 'OPEN') = 'OPEN'
AND ph.style_id = pdsh.style_id
AND pdsh.clm_flag = 'Y';
SELECT clm_document_number
FROM po_headers
WHERE clm_award_administrator = p_admin; */
SELECT clm_document_number, comments,doc_type,status,supplier,totAmt,currency_code
BULK COLLECT INTO l_doc_num_tbl,l_comments_tbl,l_doc_type_tbl,
l_doc_status_tbl,l_supplier_tbl,l_tot_amt_tbl,
l_doc_curr_tbl
FROM (SELECT clm_document_number, comments , plc1.DISPLAYED_FIELD doc_type,
PLC2.DISPLAYED_FIELD status, pv.vendor_name supplier,
PO_DOCUMENT_TOTALS_PVT.getAmountOrdered('HEADER',po_header_id,'TRANSACTION',revision_num,-1) totAmt,
currency_code
FROM po_headers ph,
po_doc_style_headers pdsh,
po_lookup_codes plc1,
po_lookup_codes plc2,
po_vendors pv
WHERE clm_award_administrator = l_admin_id
AND authorization_status = 'APPROVED'
AND Nvl(clm_closeout_status, 'OPEN') = 'OPEN'
AND ph.style_id = pdsh.style_id
AND pdsh.clm_flag = 'Y'
AND plc1.lookup_code = ph.type_lookup_code
AND plc1.lookup_type = 'PO TYPE'
AND plc2.lookup_type = 'AUTHORIZATION STATUS'
AND PLC2.lookup_code = ph.authorization_status
AND pv.vendor_id = ph.vendor_id
--Award should not contain any open shipments or line associated to the shipment is cancelled
AND NOT EXISTS(SELECT 1
FROM po_line_locations_all pll,
po_lines_all pl
WHERE pll.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND (Nvl(pll.closed_code,'OPEN') IN ('OPEN')
OR pl.user_document_status IS NOT NULL)
)
--Award should not contain any unexcersized open options
AND NOT EXISTS(SELECT 1
FROM po_lines_all pl
WHERE pl.po_header_id = ph.po_header_id
AND pl.clm_option_indicator = 'O'
AND Nvl(pl.CLM_EXERCISED_FLAG,'N') = 'N'
AND (SYSDATE BETWEEN pl.start_date AND pl.expiration_date
OR user_document_status IS NULL)
)
--Award should not any draft closeout details
AND NOT EXISTS(SELECT 1
FROM po_closeout_details pcd
WHERE pcd.reference_doc_id = ph.po_header_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL
AND Nvl(pcd.incloseout,'N') = 'Y'
)
ORDER BY ph.last_update_date)
WHERE ROWNUM<11;
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
( po_multi_mod_val_results_s.nextval
, p_req_id
, p_mod_doc_id
, p_validation_type
, p_exception_type
, null
, p_doc_id
, p_doc_num
, null
, null
, null
, null
, p_message_app
, p_message_name
, p_token_name1
, p_token_value1
, p_token_name2
, p_token_value2
, p_token_name3
, p_token_value3
, p_token_name4
, p_token_value4
, p_token_name5
, p_token_value5
, null
, null
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, sysdate
, fnd_global.user_id
, fnd_global.conc_request_id
);
procedure delete_contract (
p_doc_id IN NUMBER
, p_closeout_type IN VARCHAR
, x_return_status OUT NOCOPY VARCHAR2
, x_return_msg OUT NOCOPY VARCHAR2
)
IS
l_progress NUMBER := 0;
d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'delete_contract';
SELECT type_lookup_code, clm_document_number
INTO l_po_doc_type, l_doc_num
FROM PO_HEADERS_ALL
WHERE po_header_id = p_doc_id;
okc_rep_closeout_pvt.delete_contract(p_api_version => 1.0,
p_document_rec => l_document_rec,
p_contract_type => l_contract_type,
p_contract_id => l_contract_id,
p_commit => fnd_api.g_true,
x_msg_data => x_return_msg,
x_msg_count => l_msg_count,
x_return_status => x_return_status);
END delete_contract;
UPDATE po_headers_all
SET clm_closeout_status = l_closeout_status
WHERE po_header_id = l_document_id;
UPDATE po_closeout_details
SET aco_signer_name = l_aco_singer_name,
aco_signed_date = Nvl(l_aco_signed_date, sysdate),
aco_remarks = l_aco_remarks
WHERE reference_doc_id = l_document_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
UPDATE po_closeout_details
SET closeout_date = nvl(Nvl(l_pco_signed_date,aco_signed_date), sysdate),
pco_signer_name = l_pco_singer_name,
pco_signed_date = Nvl(l_pco_signed_date, sysdate),
pco_remarks = l_pco_remarks,
incloseout = 'C' -- Completed
WHERE reference_doc_id = l_document_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
select clm_document_number,type_lookup_code, Decode(type_lookup_code, 'STANDARD', 'PO', 'PA')
into l_doc_num,l_doc_subtype,l_doc_type
from po_headers_all
where po_header_id = l_document_id;
PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'update query count ' || SQL%ROWCOUNT);
SELECT type_lookup_code,revision_num, agent_id
INTO l_po_doc_type, l_rev_number , l_agent_id
FROM PO_HEADERS_ALL
WHERE po_header_id = l_document_id;
SELECT Max(sequence_num)
INTO l_seq_num
FROM po_action_history;
INSERT INTO po_action_history(
object_id
, object_type_code
, object_sub_type_code
, sequence_num
, last_update_date
, last_updated_by
, creation_date
, created_by
, action_code
, action_date
, employee_id
, note
, object_revision_num
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, approval_path_id
, offline_code
) VALUES (
l_document_id
, 'PO'
, l_po_doc_type
, l_seq_num+1
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, l_closeout_status --ActionCode
, SYSDATE
, l_agent_id
, null
, l_rev_number
, fnd_global.LOGIN_ID
, 0
, 0
, 0
, ''
, 0
, ''
);
PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'insert query count ' || SQL%ROWCOUNT);
SELECT type_lookup_code,revision_num,clm_document_number
INTO l_po_doc_type, l_rev_number, l_doc_num
FROM PO_HEADERS_ALL
WHERE po_header_id = l_document_id;
update PO_CLOSEOUT_DETAILS
set incloseout = 'W'
where reference_doc_id = l_document_id
and closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
SELECT 'Y'
INTO l_is_physical_complete
FROM po_headers_all ph
WHERE ph.po_header_id = l_document_id
AND ph.clm_closeout_status = 'PHYSICAL_COMPLETE';
SELECT PHA.clm_document_number,
PHA.revision_num,
PHA.comments,
VO.vendor_name,
PHA.agent_id,
Nvl(PHA.clm_award_administrator, PHA.agent_id)
INTO
l_clm_document_number,
l_revision_num,
l_comments,
l_vendor_name,
l_buyer_id,
l_admin_id
FROM PO_HEADERS_ALL PHA,
PO_VENDORS VO
WHERE PHA.po_header_id = l_document_id
AND PHA.vendor_id = VO.vendor_id;
SELECT reference_doc_id, check_list_required, ccs_required, check_list_sign_required,
ccs_sign_required, aco_signature_type, pco_signature_type,
check_list_signer_id, aco_signer_id, pco_signer_id, aco_remarks, pco_remarks
INTO l_document_id, l_chklist_req, l_ccs_req, l_chklist_sign_req, l_ccs_sign_req,
l_aco_sign_type, l_pco_sign_type, l_chklist_signer, l_admin_signer,
l_cntr_signer,l_aco_remarks, l_pco_remarks
FROM PO_CLOSEOUT_DETAILS
WHERE reference_doc_id = p_doc_id
AND incloseout = 'Y'
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL
FOR UPDATE ;
SELECT PHA.clm_document_number,
Nvl(PHA.clm_award_administrator, PHA.agent_id),
PHA.clm_closeout_status
INTO
l_clm_document_number,
l_admin_id,
l_clm_closeout_status
FROM PO_HEADERS_ALL PHA
WHERE PHA.po_header_id = p_doc_id;
PO_CLOSEOUT_PVT.delete_contract(p_doc_id => p_doc_id,
p_closeout_type => PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL,
x_return_status => x_return_status,
x_return_msg => l_msg_data);
PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'Delete contract status ' || x_return_status);
PO_CLOSEOUT_PVT.Log( d_module, l_progress, 'return message from Delete contract : ' || l_msg_data);
UPDATE po_closeout_details
SET incloseout = 'W' -- Workflow
WHERE reference_doc_id = p_doc_id
AND incloseout = 'Y'
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
PO_CLOSEOUT_PVT.Log( d_module, d_position, 'Updated incloseout to W '||SQL%ROWCOUNT);
UPDATE po_closeout_details
SET attribute15 = l_user_id||':'||l_resp_id||':'||l_appl_id
WHERE reference_doc_id = p_doc_id
AND incloseout = 'Y'
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
SELECT ATTRIBUTE15
INTO l_context_ids
FROM po_closeout_details
WHERE reference_doc_id = l_document_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
SELECT regexp_substr(l_context_ids,'[^:]+', 1, LEVEL) internal_id FROM dual
CONNECT BY regexp_substr(l_context_ids, '[^:]+', 1, LEVEL) IS NOT NULL
) LOOP
l_counter := l_counter +1;
SELECT l.FILE_NAME, l.FILE_CONTENT_TYPE, l.FILE_DATA
INTO l_filename, l_filecontent_type, l_document
FROM
FND_LOBS L,
FND_DOCUMENTS D,
FND_ATTACHED_DOCUMENTS A
WHERE D.DOCUMENT_ID = A.DOCUMENT_ID
AND A.ENTITY_NAME = 'PO_CLOSEOUT_DETAILS'
AND D.MEDIA_ID = L.FILE_ID
AND D.FILE_NAME LIKE '%'||l_pdf_type||'.pdf'
AND A.PK1_VALUE =l_document_id ;
PROCEDURE Poclsout_selector (p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_x_result IN OUT nocopy VARCHAR2)
IS
-- Context setting revamp
l_session_user_id NUMBER;
d_module CONSTANT VARCHAR2(2000) := d_pkg_name || '.' || 'Poclsout_selector';
po_wf_debug_pkg.Insert_debug (itemtype => p_itemtype, itemkey => p_itemkey
,
x_progress => 'POCLSOUT_SELECTOR called with mode: '
||p_funcmode
||' itemtype: '
||p_itemtype
||' itemkey: '
||p_itemkey);
l_progress := '010 selector fn - sess_user_id:'
||l_session_user_id
||' ses_resp_id '
||l_session_resp_id;
po_wf_debug_pkg.Insert_debug(p_itemtype, p_itemkey, l_progress);
po_wf_debug_pkg.Insert_debug(itemtype => p_itemtype,
itemkey => p_itemkey,
x_progress => 'POCLSOUT_SELECTOR: inside Test Ctx ');
SELECT ATTRIBUTE15
INTO l_context_ids
FROM po_closeout_details
WHERE reference_doc_id = l_document_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;
SELECT regexp_substr(l_context_ids,'[^:]+', 1, LEVEL) internal_id FROM dual
CONNECT BY regexp_substr(l_context_ids, '[^:]+', 1, LEVEL) IS NOT NULL
) LOOP
l_counter := l_counter +1;
PO_WF_DEBUG_PKG.insert_debug(itemtype => p_itemtype,
itemkey => p_itemkey,
x_progress => 'Exception in Selector');
l_progress := '050 selector fn : set user '
||l_user_id_to_set
||' resp id '
||l_resp_id_to_set
||' appl id '
||l_appl_id_to_set;
po_wf_debug_pkg.Insert_debug(p_itemtype, p_itemkey, l_progress);
po_wf_debug_pkg.Insert_debug(itemtype => p_itemtype,
itemkey => p_itemkey,
x_progress => 'Exception in Selector');
wf_core.Context('PO_CLOSEOUT_PVT', 'POCLSOUT_SELECTOR',
p_itemtype,
p_itemkey, p_actid, p_funcmode);
END poclsout_selector;
UPDATE po_closeout_details
SET checklist_signed_date = SYSDATE
WHERE reference_doc_id = l_document_id
AND closeout_doc_type = PO_CLOSEOUT_PVT.g_CLOSEOUT_TYPE_INDIVIDUAL;