[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_requisition_internal(p_header_id IN NUMBER, p_working_copy IN BOOLEAN, p_is_purge_req_process IN BOOLEAN default false) IS
l_line_ids dbms_sql.NUMBER_TABLE;
SELECT type_lookup_code
INTO l_DocumentTypeCode
FROM po_requisition_headers
WHERE requisition_header_id = p_header_id;
DELETE FROM po_requisition_headers_all
WHERE requisition_header_id = p_header_id;
DELETE FROM po_requisition_lines_all
WHERE requisition_header_id = p_header_id
RETURNING requisition_line_id
BULK COLLECT INTO l_line_ids;
DELETE FROM po_req_distributions_all
WHERE requisition_line_id = l_line_ids(idx)
RETURNING award_id
BULK COLLECT INTO l_award_ids;
gms_por_api.delete_adl(l_award_ids(idx), l_status, l_err_msg);
fnd_attached_documents2_pkg.delete_attachments('REQ_HEADERS',
p_header_id,
null,
null,
null,
null,
'Y');
fnd_attached_documents2_pkg.delete_attachments('REQ_LINES',
l_line_ids(idx),
null,
null,
null,
null,
'Y');
DELETE FROM por_template_info
WHERE requisition_line_id = l_line_ids(idx);
DELETE FROM por_item_attribute_values
WHERE requisition_line_id = l_line_ids(idx);
DELETE FROM po_requisition_suppliers
WHERE requisition_line_id = l_line_ids(idx);
DELETE FROM po_price_differentials
WHERE entity_id = l_line_ids(idx)
AND entity_type = 'REQ LINE';
DELETE FROM po_approval_list_lines
WHERE approval_list_header_id IN
(SELECT approval_list_header_id
FROM po_approval_list_headers
WHERE document_id = p_header_id
AND document_type = 'REQUISITION');
DELETE FROM po_approval_list_headers
WHERE document_id = p_header_id
AND document_type = 'REQUISITION';
delete from ZX_LINES_DET_FACTORS
where trx_id = p_header_id
and ENTITY_CODE = 'REQUISITION'
and event_class_code = 'REQUISITION'
and application_id =201;
'Exception at POR_UTL_PKG.delete_requisition_internal(p_header_id:'
|| p_header_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
END delete_requisition_internal;
CURSOR C IS SELECT enable_all_sites
FROM po_headers_all WHERE po_header_id = p_header_id;
PROCEDURE delete_requisition(p_header_id IN NUMBER) IS
BEGIN
delete_requisition_internal(p_header_id, false);
END delete_requisition;
delete_requisition_internal(p_header_id, false, true);
PROCEDURE delete_working_copy_req(p_req_number IN VARCHAR2) IS
l_header_id NUMBER;
SELECT requisition_header_id
INTO l_header_id
FROM po_requisition_headers_all
WHERE segment1 = p_req_number;
delete_requisition_internal(l_header_id, TRUE);
END delete_working_copy_req;
select sequence_num,
action_code,
employee_id
from po_action_history
where object_id = p_req_header_id
and object_type_code = 'REQUISITION'
order by sequence_num desc;
select fs.segment_num, gls.chart_of_accounts_id
into l_segment_num, l_account_id
from FND_ID_FLEX_SEGMENTS fs,
fnd_segment_attribute_values fsav,
financials_system_parameters fsp,
gl_sets_of_books gls
where fsp.set_of_books_id = gls.set_of_books_id and
fsav.id_flex_num = gls.chart_of_accounts_id and
fsav.id_flex_code = 'GL#' and
fsav.application_id = 101 and
fsav.segment_attribute_type = 'FA_COST_CTR' and
fsav.id_flex_num = fs.id_flex_num and
fsav.id_flex_code = fs.id_flex_code and
fsav.application_id = fs.application_id and
fsav.application_column_name = fs.application_column_name and
fsav.attribute_value='Y';
SELECT 1 into l_count
FROM po_requisition_headers
WHERE segment1 = to_char(req_num);
SELECT 1 into l_count
FROM po_requisition_headers_all
WHERE requisition_header_id = p_req_header_id;
SELECT 1 into l_count
FROM
fnd_user fnd,
per_employees_current_x hr
WHERE fnd.user_id = p_user_id
AND fnd.employee_id = hr.employee_id
AND rownum = 1;
SELECT 1 into l_count
FROM
fnd_user fnd,
per_workforce_current_x hr
WHERE fnd.user_id = p_user_id
AND fnd.employee_id = hr.person_id
AND rownum = 1;
sqlString := 'select POR_REQ_NUMBER_S.nextval from sys.dual';
SELECT (current_max_unique_identifier + 1) INTO l_po_num
FROM po_unique_identifier_control
WHERE table_name = table_name_p
FOR UPDATE OF current_max_unique_identifier NOWAIT;
UPDATE po_unique_identifier_control
SET current_max_unique_identifier =
current_max_unique_identifier + 1
WHERE table_name= table_name_p;
SELECT (current_max_unique_identifier + 1) INTO l_po_num
FROM po_unique_identifier_cont_all
WHERE table_name = table_name_p
AND org_id = org_id_p
FOR UPDATE OF current_max_unique_identifier NOWAIT;
UPDATE po_unique_identifier_cont_all
SET current_max_unique_identifier =
current_max_unique_identifier + 1
WHERE table_name= table_name_p
AND org_id = org_id_p;
PROCEDURE update_gms_distributions(p_origHeaderId IN NUMBER) IS
l_forGMSReqDistributionId po_req_distributions_all.distribution_id%type;
select distribution_id,
project_id,
task_id,
req_award_id,
expenditure_organization_id,
expenditure_type,
expenditure_item_date
from po_req_distributions_all prd,
po_requisition_lines_all prl
where prl.requisition_header_id = p_origHeaderId
and prl.requisition_line_id = prd.requisition_line_id;
GMS_POR_API.when_update_line( X_distribution_id => l_forGMSReqDistributionId,
X_project_id => l_forGMSProjectId,
X_task_id => l_forGMSTaskId,
X_award_id => l_forGMSAwardId,
X_expenditure_type => l_forGMSExpenditureType,
X_expenditure_item_date => l_forGMSExpenditureDate,
X_status => l_GMSAPIStatus
);
'Exception at POR_UTL_PKG.update_gms_distributions.afterGMSAPIcall[APIstatus:'||l_GMSAPIStatus||
'] (p_origDistId:' || l_forGMSReqDistributionId
|| ') ' || l_progress || ' SQLERRM:' || SQLERRM);
END update_gms_distributions;
PROCEDURE update_notif_header_attr(p_header_id IN NUMBER) IS
l_wf_itemtype PO_REQUISITION_HEADERS_ALL.wf_item_type%TYPE;
l_procedure_name CONSTANT VARCHAR2(30) := 'update_notif_header_attr';
select item_type, item_key
from wf_items
where parent_item_type = l_wf_itemtype
and parent_item_key = l_wf_itemkey;
select notification_id
from wf_item_activity_statuses
where item_type = l_wf_itemtype
and item_key = l_wf_itemkey
and notification_id is not null;
SELECT wf_item_type, wf_item_key
INTO l_wf_itemtype, l_wf_itemkey
FROM PO_REQUISITION_HEADERS_ALL
WHERE REQUISITION_HEADER_ID = p_header_id;
l_log_msg := 'Error in update_notif_header_attr... : SQLERRM= ' ||
SQLERRM || ' : Progress= ' || l_progress;
END update_notif_header_attr;
SELECT segment1, authorization_status, contractor_requisition_flag
INTO l_origReqNumber, l_status, l_contractor_requisition_flag
FROM po_requisition_headers_all
WHERE requisition_header_id = p_origHeaderId;
UPDATE po_requisition_lines_all
SET labor_req_line_id = (SELECT labor_req_line_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_origLineIds(idx))
WHERE requisition_line_id = p_tempLineIds(idx)
AND labor_req_line_id is not null;
delete_requisition_internal(p_origHeaderId, TRUE);
UPDATE po_requisition_headers_all
SET requisition_header_id = p_origHeaderId,
-- segment1 = l_origReqNumber,
segment1=LTrim(segment1,'##'),
authorization_status = l_status
WHERE requisition_header_id = p_tempHeaderId;
UPDATE po_requisition_lines_all
SET requisition_header_id = p_origHeaderId
WHERE requisition_header_id = p_tempHeaderId;
UPDATE po_requisition_lines_all
SET requisition_line_id = p_origLineIds(idx)
WHERE requisition_line_id = p_tempLineIds(idx);
UPDATE po_requisition_lines_all
SET Group_line_id = p_origLineIds(idx)
WHERE Group_line_id = p_tempLineIds(idx);
UPDATE po_requisition_lines_all
SET Clm_Base_line_num = p_origLineIds(idx)
WHERE Clm_Base_line_num = p_tempLineIds(idx);
UPDATE po_requisition_lines_all
SET labor_req_line_id = p_origLineIds(idx)
WHERE labor_req_line_id = p_tempLineIds(idx)
AND contractor_requisition_flag = 'Y';
UPDATE po_req_distributions_all
SET requisition_line_id = p_origLineIds(idx)
WHERE requisition_line_id = p_tempLineIds(idx);
UPDATE po_req_distributions_all
SET distribution_id = p_origDistIds(idx),
encumbered_flag='N', encumbered_amount=0
WHERE distribution_id = p_tempDistIds(idx);
UPDATE fnd_attached_documents
SET pk1_value = to_char(p_origLineIds(idx))
WHERE pk1_value = to_char(p_tempLineIds(idx))
AND entity_name = 'REQ_LINES';
UPDATE fnd_attached_documents
SET pk1_value = to_char(p_origHeaderId)
WHERE pk1_value = to_char(p_tempHeaderId)
AND entity_name = 'REQ_HEADERS';
UPDATE por_template_info
SET requisition_line_id = p_origLineIds(idx)
WHERE requisition_line_id = p_tempLineIds(idx);
UPDATE por_item_attribute_values
SET requisition_line_id = p_origLineIds(idx),
requisition_header_id = p_origHeaderId
WHERE requisition_line_id = p_tempLineIds(idx);
UPDATE po_requisition_suppliers
SET requisition_line_id = p_origLineIds(idx)
WHERE requisition_line_id = p_tempLineIds(idx);
UPDATE po_requisition_suppliers
SET requisition_supplier_id = p_origReqSupplierIds(idx)
WHERE requisition_supplier_id = p_tempReqSupplierIds(idx);
UPDATE po_price_differentials
SET entity_id = p_origLineIds(idx)
WHERE entity_id = p_tempLineIds(idx)
AND entity_type = 'REQ LINE';
UPDATE po_price_differentials
SET price_differential_id = p_origPriceDiffIds(idx)
WHERE price_differential_id = p_tempPriceDiffIds(idx);
UPDATE po_approval_list_headers
SET document_id = p_origHeaderId
WHERE document_id = p_tempHeaderId
AND document_type = 'REQUISITION';
update_notif_header_attr(p_origHeaderId);
update_gms_distributions(p_origHeaderId);
UPDATE ZX_LINES_DET_FACTORS
SET trx_id = p_origHeaderId
WHERE trx_id = p_tempHeaderId
and ENTITY_CODE = 'REQUISITION'
and event_class_code = 'REQUISITION'
and application_id =201;
UPDATE ZX_LINES_DET_FACTORS
SET trx_line_id = p_origLineIds(idx)
WHERE trx_line_id = p_tempLineIds(idx)
and ENTITY_CODE = 'REQUISITION'
and event_class_code = 'REQUISITION'
and application_id =201;
delete from PO_REQ_HEADERS_EXT_B where REQUISITION_HEADER_ID =p_origHeaderId;
update PO_REQ_HEADERS_EXT_B set REQUISITION_HEADER_ID= p_origHeaderId
WHERE requisition_header_id = p_tempHeaderId;
UPDATE PO_REQ_HEADERS_EXT_B ext set ext.C_EXT_ATTR40 = LTrim(ext.C_EXT_ATTR40,'##')
WHERE ext.requisition_header_id = p_origHeaderId AND
EXISTS (SELECT 1 FROM
PO_UDA_AG_TEMPLATE_USAGES usages, ego_attr_groups_v ags
WHERE usages.template_id =ext.uda_template_id
AND ext.attr_group_id= usages.attribute_group_id
AND usages.ATTRIBUTE1='Base Document'
AND usages.ATTRIBUTE2='NA'
AND usages.ATTRIBUTE_CATEGORY='DOCUMENT_NUMBERING'
AND usages.attribute_group_id = ags.attr_group_id) ;
delete from PO_REQ_LINES_EXT_B where requisition_line_id = p_origLineIds(idx);
UPDATE PO_REQ_LINES_EXT_B
SET requisition_line_id = p_origLineIds(idx)
WHERE requisition_line_id = p_tempLineIds(idx);
delete from PO_REQ_DISTRIBUTIONS_EXT_B where distribution_id = p_origDistIds(idx);
UPDATE PO_REQ_DISTRIBUTIONS_EXT_B
SET distribution_id = p_origDistIds(idx)
WHERE distribution_id = p_tempDistIds(idx);
UPDATE PO_REQUISITION_HEADERS_ALL
SET CONTRACTOR_STATUS = 'ASSIGNED'
WHERE REQUISITION_HEADER_ID = req_Header_Id
AND CONTRACTOR_STATUS = 'PENDING';
PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
'ERROR while running wf_engine.CompleteActivity:' || SQLERRM);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
'AFTER wf_engine.CompleteActivity');
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
'X_AUTHORIZATION_STATUS = IN PROCESS');
update po_requisition_headers_all
set WF_ITEM_TYPE = NULL, WF_ITEM_KEY = NULL
where REQUISITION_HEADER_ID = req_Header_Id;
select segment1, preparer_id
into p_req_num, p_preparer_id
from po_requisition_headers_all
where requisition_header_id =req_Header_Id;
select EMPLOYEE_ID into p_approver_id
from (
select SEQUENCE_NUM,EMPLOYEE_ID from po_action_history
where object_id = req_Header_Id
and OBJECT_TYPE_CODE='REQUISITION'
and OBJECT_SUB_TYPE_CODE='PURCHASE'
and ACTION_CODE='NO ACTION'
order by SEQUENCE_NUM desc ) last_approver
where rownum =1;
SELECT authorization_status, supplier_notified_flag,
contractor_requisition_flag, wf_item_key, wf_item_type,
type_lookup_code
INTO X_AUTHORIZATION_STATUS, X_SUPPL_NOTIFIED_FLAG,
X_CONTRACTOR_REQ_FLAG, X_WF_ITEM_KEY, X_WF_ITEM_TYPE,
p_document_subtype
FROM PO_REQUISITION_HEADERS_ALL
WHERE REQUISITION_HEADER_ID = req_Header_Id;
PO_WF_DEBUG_PKG.insert_debug(X_WF_ITEM_TYPE, X_WF_ITEM_KEY,
'X_AUTHORIZATION_STATUS, X_SUPPL_NOTIFIED_FLAG,
X_CONTRACTOR_REQ_FLAG, X_WF_ITEM_KEY, X_WF_ITEM_TYPE:' ||
X_AUTHORIZATION_STATUS || X_SUPPL_NOTIFIED_FLAG ||
X_CONTRACTOR_REQ_FLAG || X_WF_ITEM_KEY || X_WF_ITEM_TYPE);
SELECT
to_char(req_Header_Id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
INTO p_item_key
FROM sys.dual;
SELECT
wf_approval_itemtype,
wf_approval_process
INTO
p_item_type,
p_workflow_process
FROM po_document_types
WHERE document_type_code = p_document_type
AND document_subtype = p_document_subtype ;
SELECT NVL(src.process_enabled_flag,'N'), NVL(dest.process_enabled_flag,'N')
INTO l_src_process_enabled_flag, l_dest_process_enabled_flag
FROM mtl_parameters src, mtl_parameters dest
WHERE src.organization_id = x_source_organization_id
AND dest.organization_id = x_dest_organization_id;
SELECT to_number(src.org_information3) src_ou, to_number(dest.org_information3) dest_ou
INTO l_from_ou, l_to_ou
FROM hr_organization_information src, hr_organization_information dest
WHERE src.organization_id = x_source_organization_id
AND src.org_information_context = 'Accounting Information'
AND dest.organization_id = x_dest_organization_id
AND dest.org_information_context = 'Accounting Information';
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = x_unit_of_measure ;
select gsob.currency_code
,ood.set_of_books_id,
psp.DEFAULT_RATE_TYPE
into l_dest_currency
,l_set_of_books_id,
l_def_rate_type
from gl_sets_of_books gsob,
org_organization_definitions ood,
po_system_parameters psp
where ood.set_of_books_id = gsob.set_of_books_id
and ood.organization_id = x_dest_organization_id;
SELECT action_code, employee_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = p_headerId
ORDER BY sequence_num desc;
select item_key
from wf_items item
where item.item_type = itemtype
AND item.parent_item_key = itemkey;
SELECT wf_item_type, wf_item_key, type_lookup_code
INTO l_item_type, l_item_key, l_doc_sub_type
FROM po_requisition_headers_all
WHERE requisition_header_id= p_headerId;
UPDATE po_requisition_headers_all
SET wf_item_type = NULL,
wf_item_key = NULL,
authorization_status = 'INCOMPLETE'
WHERE requisition_header_id = p_headerId;
UPDATE po_requisition_lines_all
set reqs_in_pool_flag= NULL
where requisition_header_id = p_headerId;
SELECT NVL(activity_status, 'N')
INTO l_activity_status
FROM wf_item_activity_statuses wfs,
wf_items wfi,
wf_process_activities wfa
WHERE wfi.item_type = l_item_type
and wfi.item_key = l_item_key
and wfa.activity_name = wfi.root_activity
and wfs.process_activity = wfa.instance_id
and wfi.item_type = wfs.item_type
and wfi.item_key = wfs.item_key;
SELECT NVL(activity_status, 'N')
INTO l_activity_status
FROM wf_item_activity_statuses wfs,
wf_items wfi,
wf_process_activities wfa
WHERE wfi.item_type = l_item_type
and wfi.item_key = c1_rec.item_key
and wfa.activity_name = wfi.root_activity
and wfs.process_activity = wfa.instance_id
and wfi.item_type = wfs.item_type
and wfi.item_key = wfs.item_key;
po_forward_sv1.update_action_history (p_headerId,
'REQUISITION',
l_pending_emp_id,
'NO ACTION',
NULL,
fnd_global.user_id,
fnd_global.login_id);
po_forward_sv1.insert_action_history (p_headerId,
'REQUISITION',
l_doc_sub_type,
NULL,
'WITHDRAW',
sysdate,
fnd_global.employee_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
fnd_global.user_id,
fnd_global.login_id);
delete from PO_CHANGE_REQUESTS
where document_header_id = p_headerId
and request_status = 'SYSTEMSAVE'
and initiator = 'REQUESTER';
l_update_header_required BOOLEAN;
SELECT requisition_header_id, authorization_status, description
FROM po_requisition_headers_all
WHERE last_updated_by = p_user_id
AND active_shopping_cart_flag = 'Y';
SELECT item_description
FROM po_requisition_lines_all
WHERE requisition_header_id = l_active_req_header_id
ORDER BY line_num;
l_update_header_required := FALSE;
l_update_header_required := TRUE;
l_update_header_required := TRUE;
IF (l_update_header_required ) THEN
l_progress := '080';
UPDATE po_requisition_headers_all
SET description = l_req_description,
authorization_status = l_authorization_status
WHERE requisition_header_id = l_active_req_header_id;
update po_requisition_headers_all
set active_shopping_cart_flag = null
where last_updated_by = p_user_id
and active_shopping_cart_flag = 'Y';
DELETE from po_encumbrance_gt;
INSERT INTO po_encumbrance_gt(
adjustment_status,
distribution_type,
header_id,
line_id,
line_location_id,
distribution_id,
segment1,
line_num,
distribution_num,
reference_num,
item_description,
budget_account_id,
gl_encumbered_date,
value_basis,
encumbered_amount,
amount_ordered,
quantity_ordered,
quantity_delivered,
quantity_on_line,
unit_meas_lookup_code,
item_id,
price,
nonrecoverable_tax,
transferred_to_oe_flag,
source_type_code,
cancel_flag,
closed_code,
encumbered_flag,
prevent_encumbrance_flag,
project_id,
task_id,
award_num,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
vendor_id,
row_index,
clm_doc_flag,
/* Bug#12961490: <> */
funded_value,
quantity_funded,
amount_funded,
change_in_funded_value
/* Bug#12961490: <> */
)
VALUES (
PO_DOCUMENT_FUNDS_GRP.g_adjustment_status_NEW,
PO_DOCUMENT_FUNDS_GRP.g_dist_type_REQUISITION,
p_dist_data.requisition_header_id(i),
p_dist_data.requisition_line_id(i),
p_dist_data.line_location_id(i),
p_dist_data.distribution_id(i),
p_dist_data.segment1(i),
p_dist_data.line_num(i),
p_dist_data.distribution_num(i),
p_dist_data.reference_num(i),
p_dist_data.item_description(i),
p_dist_data.budget_account_id(i),
p_dist_data.gl_encumbered_date(i),
p_dist_data.order_type_lookup_code(i),
p_dist_data.encumbered_amount(i),
p_dist_data.req_line_amount(i),
p_dist_data.req_line_quantity(i),
p_dist_data.quantity_delivered(i),
p_dist_data.quantity(i),
p_dist_data.unit_meas_lookup_code(i),
p_dist_data.item_id(i),
p_dist_data.unit_price(i),
p_dist_data.nonrecoverable_tax(i),
p_dist_data.transferred_to_oe_flag(i),
p_dist_data.source_type_code(i),
p_dist_data.cancel_flag(i),
p_dist_data.closed_code(i),
p_dist_data.encumbered_flag(i),
p_dist_data.prevent_encumbrance_flag(i),
p_dist_data.project_id(i),
p_dist_data.task_id(i),
p_dist_data.award_num(i),
p_dist_data.expenditure_type(i),
p_dist_data.expenditure_organization_id(i),
p_dist_data.expenditure_item_date(i),
p_dist_data.vendor_id(i),
p_dist_data.row_index(i),
l_clm_doc_flag,
/* Bug#12961490: <> */
p_dist_data.funded_value(i),
p_dist_data.quantity_funded(i),
p_dist_data.amount_funded(i),
p_dist_data.change_in_funded_value(i)
/* Bug#12961490: <> */
);
SELECT requisition_header_id INTO l_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_doc_level_id_tbl(1);
UPDATE po_requisition_headers_all
SET authorization_status = 'APPROVED'
WHERE requisition_header_id = l_header_id
AND authorization_status = 'PRE-APPROVED';
SELECT requisition_header_id INTO l_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_doc_level_id_tbl(1);
UPDATE po_requisition_headers_all
SET authorization_status = 'PRE-APPROVED'
WHERE requisition_header_id = l_header_id
AND authorization_status = 'APPROVED';
select stat.notification_id
from wf_item_activity_statuses stat,
wf_items item
where stat.item_type = itemtype
AND item.item_type = itemtype
AND item.parent_item_key = itemkey
AND stat.item_key = item.item_key
AND stat.activity_status = 'NOTIFIED'
UNION
select notification_id
from wf_item_activity_statuses
where item_type = itemtype
AND item_key = itemkey
AND activity_status = 'NOTIFIED'
AND notification_id is NOT NULL;
select wf_item_type, wf_item_key
into itype, ikey
from po_requisition_headers
where requisition_header_id = p_headerId;
l_last_updated_by po_tbl_number;
l_last_update_date po_tbl_date;
l_last_update_login po_tbl_number;
SELECT * FROM POR_TEMPLATE_INFO WHERE REQUISITION_LINE_ID = p_old_reqline_id;
l_last_updated_by (counter),
l_last_update_date (counter),
l_last_update_login (counter),
l_attribute1 (counter),
l_attribute2 (counter),
l_attribute3 (counter),
l_attribute4 (counter),
l_attribute5 (counter),
l_attribute6 (counter),
l_attribute7 (counter),
l_attribute8 (counter),
l_attribute9 (counter),
l_attribute10(counter),
l_attribute11(counter),
l_attribute12(counter),
l_attribute13(counter),
l_attribute14(counter),
l_attribute15(counter);
l_last_updated_by ,
l_last_update_date,
l_last_update_login,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15;
SELECT COUNT(*) INTO l_info_templ_count
FROM POR_TEMPLATES_V PTV,
POR_TEMPLATE_ATTRIBUTES_B PTAB
WHERE PTV.TEMPLATE_CODE = PTAB.TEMPLATE_CODE
AND PTAB.ATTRIBUTE_CODE = l_attribute_code (i)
AND PTAB.NODE_DISPLAY_FLAG = 'Y'
AND PTAB.TEMPLATE_CODE = PTV.TEMPLATE_CODE
AND PTV.TEMPLATE_CODE IN
(
SELECT ASSOC.REGION_CODE
FROM POR_TEMPLATE_ASSOC ASSOC
WHERE
--check item association
(ASSOC.ITEM_OR_CATEGORY_FLAG = 'I'
AND ASSOC.ITEM_OR_CATEGORY_ID=p_item_id)
--check category associtation
OR (ASSOC.ITEM_OR_CATEGORY_FLAG = 'C'
AND ASSOC.ITEM_OR_CATEGORY_ID = p_category_id )
OR (ASSOC.ITEM_OR_CATEGORY_FLAG = 'N'
AND ASSOC.ITEM_OR_CATEGORY_ID = p_category_id)) ;
INSERT INTO POR_TEMPLATE_INFO
(
REQUISITION_LINE_ID,
ATTRIBUTE_CODE,
ATTRIBUTE_LABEL_LONG,
ATTRIBUTE_VALUE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15) VALUES
(
p_new_reqline_id,
l_attribute_code (i),
l_attribute_label_long (i),
l_attribute_value (i),
l_created_by (i),
SYSDATE,
l_last_updated_by (i),
SYSDATE,
l_last_update_login (i),
l_attribute1 (i),
l_attribute2 (i),
l_attribute3 (i),
l_attribute4 (i),
l_attribute5 (i),
l_attribute6 (i),
l_attribute7 (i),
l_attribute8 (i),
l_attribute9 (i),
l_attribute10(i),
l_attribute11(i),
l_attribute12(i),
l_attribute13(i),
l_attribute14(i),
l_attribute15(i) );
PROCEDURE update_attachment_to_standard(p_req_header_id in NUMBER) IS
l_progress VARCHAR2(4) := '000';
l_api_name CONSTANT VARCHAR2(100) := 'update_attachment_to_standard';
l_procedure_name CONSTANT VARCHAR2(30) := 'update_attachment_to_standard';
SELECT document_id
FROM fnd_attached_documents
WHERE entity_name = 'REQ_HEADERS' and pk1_value = to_char(p_req_header_id)
UNION
SELECT document_id
FROM fnd_attached_documents
WHERE entity_name = 'REQ_LINES' AND pk1_value IN (SELECT to_char(requisition_line_id)
FROM po_requisition_lines_all prl, po_requisition_headers_all prh
WHERE prl.requisition_header_id = prh.requisition_header_id AND prh.requisition_header_id=to_number(p_req_header_id));
UPDATE fnd_documents SET usage_type = 'S' WHERE document_id=doc_id_v(indx);
'Exception at POR_UTL_PKG.update_attachment_to_standard(p_req_header_id:'
|| p_req_header_id || ') ' || l_progress || ' SQLERRM:' || SQLERRM);
END update_attachment_to_standard;
select count(*) into l_enc_dist_count
from po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
where prh.requisition_header_id = p_req_header_id
and prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and nvl(prd.encumbered_flag,'N') = 'Y';
SELECT round(p_amount,nvl( fc.precision,0))
INTO l_rounded_amount
FROM fnd_currencies fc
WHERE fc.currency_code = p_currency_code;
SELECT 'Y' INTO l_are_udas_already_present FROM dual WHERE EXISTS
(SELECT extension_id FROM po_req_lines_ext_b
WHERE REQUISITION_LINE_ID = p_req_line_id);
SELECT UDA_TEMPLATE_ID, BLANKET_PO_HEADER_ID, BLANKET_PO_LINE_NUM
INTO l_from_template_id, l_blanket_po_header_id, l_blanket_po_line_num
FROM PO_REQUISITION_LINES_ALL
WHERE REQUISITION_LINE_ID = p_req_line_id;
SELECT uda_template_id INTO l_to_template_id
FROM PO_REQUISITION_LINES_ALL
WHERE REQUISITION_LINE_ID = p_req_line_id;
SELECT PO_LINE_ID, UDA_TEMPLATE_ID
INTO l_po_line_id, l_from_template_id
FROM PO_LINES_ALL
WHERE PO_HEADER_ID = l_blanket_po_header_id
AND LINE_NUM = l_blanket_po_line_num
AND Nvl(DRAFT_ID, -1) = -1;
SELECT REQUISITION_HEADER_ID, SUGGESTED_BUYER_ID, AUCTION_HEADER_ID, LINE_LOCATION_ID
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id;
SELECT AUTHORIZATION_STATUS INTO l_req_status
FROM po_requisition_headers_all
WHERE requisition_header_id = l_req_header_id;
SELECT 'AWARD_CREATED' INTO l_line_status FROM PO_CLMREQ_LINE_AWD_REFS_V prh
WHERE prh.requisition_line_id= p_req_line_id and Nvl(prh.authorization_status,'INCOMPLETE') <> 'APPROVED' and ROWNUM=1;
SELECT approved_flag INTO l_approved_flag
FROM po_headers_all
WHERE po_header_id = (SELECT po_header_id FROM po_line_locations_all WHERE line_location_id = l_line_loc_id);
SELECT AUCTION_STATUS INTO l_auction_status
FROM pon_auction_headers_all
WHERE auction_header_id = l_auction_header_id;
SELECT meaning INTO l_line_status_display
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type = 'PO_REQ_LINE_STATUSES'
AND lookup_code = l_line_status;
SELECT REQUISITION_HEADER_ID, SUGGESTED_BUYER_ID, AUCTION_HEADER_ID, LINE_LOCATION_ID, REQUISITION_LINE_ID
FROM po_requisition_lines_all
WHERE requisition_header_id = p_req_header_id;
SELECT AUTHORIZATION_STATUS INTO l_req_status
FROM po_requisition_headers_all
WHERE requisition_header_id = c_get_req_details_rec.REQUISITION_HEADER_ID;
SELECT 'AWARD_CREATED' INTO l_line_status FROM PO_CLMREQ_LINE_AWD_REFS_V prh
WHERE prh.requisition_line_id= c_get_req_details_rec.REQUISITION_LINE_ID and Nvl(prh.authorization_status,'INCOMPLETE') <> 'APPROVED' and ROWNUM=1;
SELECT approved_flag INTO l_approved_flag
FROM po_headers_all
WHERE po_header_id = (SELECT po_header_id FROM po_line_locations_all WHERE line_location_id = c_get_req_details_rec.LINE_LOCATION_ID);
SELECT AUCTION_STATUS INTO l_auction_status
FROM pon_auction_headers_all
WHERE auction_header_id = c_get_req_details_rec.auction_header_id;
SELECT po_line_id
INTO l_po_line_id
FROM po_distributions_all
WHERE po_distribution_id = p_distribution_id ;
SELECT Nvl(partial_funded_flag,'N')
INTO l_partial_funded_flag
FROM po_distributions_all
WHERE po_distribution_id = p_distribution_id ;
SELECT UNIT_MEAS_LOOKUP_CODE, MATCHING_BASIS
INTO p_uom, l_matching_basis
FROM po_lines_all
WHERE po_line_id = l_po_line_id ;
SELECT Round(Nvl(quantity_funded,0),15), Round(Nvl(quantity_funded,0) - Nvl(quantity_delivered,0),15)
INTO p_funded, p_can_be_received
FROM po_distributions_all
WHERE po_distribution_id = p_distribution_id ;
SELECT Round(Nvl(amount_funded,0),15), Round(Nvl(amount_funded,0) - Nvl(amount_delivered,0),15)
INTO p_funded, p_can_be_received
FROM po_distributions_all
WHERE po_distribution_id = p_distribution_id ;
SELECT Round(Nvl(funded_value,0),15)
INTO p_funded_value
FROM po_req_distributions_all
WHERE distribution_id = p_distribution_id ;
SELECT Nvl(CAN_PREPARER_APPROVE_FLAG,'N')
INTO p_owner_can_approve
FROM po_document_types
WHERE DOCUMENT_TYPE_CODE = 'REQUISITION'
AND DOCUMENT_SUBTYPE = p_document_type ;
xUpdate_sourcing_rules_flag OUT NOCOPY VARCHAR2,
xAuto_sourcing_flag OUT NOCOPY VARCHAR2)
IS
BEGIN
SELECT Nvl(update_sourcing_rules_flag,'N'), Nvl(auto_sourcing_flag,'N')
INTO xUpdate_sourcing_rules_flag,xAuto_sourcing_flag
FROM po_headers_all
WHERE po_header_id = pHeaderId;
xUpdate_sourcing_rules_flag := 'N';
SELECT count(*) INTO l_count FROM po_requisition_lines_all
WHERE line_location_id IS NOT null
AND requisition_header_id = p_req_header_id;
SELECT count(*) INTO l_count FROM po_requisition_lines_all
WHERE REQS_IN_POOL_FLAG= 'N'
AND requisition_header_id = p_req_header_id;
PROCEDURE delete_supply (p_req_header_id in NUMBER,p_req_line_id IN NUMBER) IS
l_progress VARCHAR2(4) := '000';
l_api_name CONSTANT VARCHAR2(100) := 'delete_supply';
delete FROM mtl_supply where REQ_HEADER_ID = p_req_header_id AND REQ_LINE_ID=p_req_line_id;
l_log_msg := 'Error in delete_supply : Progress= ' || l_progress;
END delete_supply;
SELECT destination_organization_id,wip_entity_id,wip_operation_seq_num,wip_resource_seq_num,wip_line_id,wip_repetitive_schedule_id,po_line_id FROM po_distributions_all
WHERE po_distribution_id = x_po_distribution_id;
select award_id
from po_req_distributions_all
where distribution_id = X_distribution_id ;
UPDATE PO_REQ_DISTRIBUTIONS_ALL SET award_id = NULL
where distribution_id = X_distribution_id ;
SELECT DATE_RELEASED INTO X_JOB_RELEASED_DATE
FROM WIP_DISCRETE_JOBS wdj, po_distributions_all pod
WHERE wdj.ORGANIZATION_ID = pod.DESTINATION_ORGANIZATION_ID
AND pod.wip_entity_id = wdj.wip_entity_id
AND pod.po_distribution_id = x_dist_id;
SELECT DATE_RELEASED INTO X_JOB_RELEASED_DATE
FROM WIP_DISCRETE_JOBS wdj, po_distributions_all pod
WHERE wdj.ORGANIZATION_ID = pod.DESTINATION_ORGANIZATION_ID
AND pod.wip_entity_id = wdj.wip_entity_id
AND pod.po_distribution_id = x_dist_id;
SELECT PROJECT_ID INTO x_pjt_id FROM po_distributions_all WHERE PO_DISTRIBUTION_ID = x_dist_id;
SELECT DISTINCT project_id INTO x_pjt_id_dummy from(
SELECT project_id
FROM pa_projects_expend_v
WHERE project_id = x_pjt_id
UNION ALL
SELECT project_id
FROM pjm_seiban_numbers
WHERE project_id = x_pjt_id);
SELECT PROJECT_ID INTO x_pjt_id FROM po_distributions_all WHERE PO_DISTRIBUTION_ID = p_po_dist_id;
SELECT NAME INTO x_pjt_name FROM PA_PROJECTS_ALL WHERE project_id = x_pjt_id;
SELECT DISTINCT project_id
FROM po_req_distributions_all
WHERE REQUISITION_LINE_ID = x_line_id;
SELECT Count( DISTINCT project_id) INTO x_valid_pjt_cnt from(
SELECT project_id
FROM pa_projects_expend_v
WHERE project_id = x_pjt_id_dummy
UNION ALL
SELECT project_id
FROM pjm_seiban_numbers
WHERE project_id =x_pjt_id_dummy);
SELECT DISTINCT project_id
FROM po_req_distributions_all
WHERE REQUISITION_LINE_ID = p_req_line_id;
SELECT Count( DISTINCT project_id) INTO x_valid_pjt_cnt from(
SELECT project_id
FROM pa_projects_expend_v
WHERE project_id = x_cur_pjt_id
UNION ALL
SELECT project_id
FROM pjm_seiban_numbers
WHERE project_id =x_cur_pjt_id);
SELECT NAME INTO x_cur_pjt_name FROM PA_PROJECTS_ALL WHERE project_id = x_cur_pjt_id;
and updates other accounts if already exists in req interface
Parameters :
p_request_id IN NUMBER : concurrent req no
p_coa_id IN NUMBER : chart of accounts id
p_user_id IN NUMBER : user id
p_login_id IN NUMBER : login id
p_prog_application_id IN NUMBER : program application id
p_program_id IN NUMBER : program id
*/
PROCEDURE req_imp_act_up_frm_wf(
p_request_id IN NUMBER,
p_coa_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_prog_application_id IN NUMBER,
p_program_id IN NUMBER )
IS
l_category_id PO_REQUISITIONS_INTERFACE_ALL.CATEGORY_ID%TYPE;
' SELECT ROWID,
CATEGORY_ID, DESTINATION_TYPE_CODE, DELIVER_TO_LOCATION_ID, DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY,
EXPENDITURE_TYPE, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_ITEM_DATE, ITEM_ID, LINE_TYPE_ID,
PREPARER_ID, PROJECT_ID, DOCUMENT_TYPE_CODE, SOURCE_TYPE_CODE, SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY, TASK_ID, AWARD_ID, DELIVER_TO_REQUESTOR_ID, SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_SITE_ID, WIP_ENTITY_ID, WIP_LINE_ID, WIP_REPETITIVE_SCHEDULE_ID, WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM, PREVENT_ENCUMBRANCE_FLAG, GL_DATE,
HEADER_ATTRIBUTE1, HEADER_ATTRIBUTE2, HEADER_ATTRIBUTE3, HEADER_ATTRIBUTE4, HEADER_ATTRIBUTE5,
HEADER_ATTRIBUTE6, HEADER_ATTRIBUTE7, HEADER_ATTRIBUTE8, HEADER_ATTRIBUTE9, HEADER_ATTRIBUTE10,
HEADER_ATTRIBUTE11, HEADER_ATTRIBUTE12, HEADER_ATTRIBUTE13, HEADER_ATTRIBUTE14, HEADER_ATTRIBUTE15,
LINE_ATTRIBUTE1, LINE_ATTRIBUTE2, LINE_ATTRIBUTE3, LINE_ATTRIBUTE4, LINE_ATTRIBUTE5,
LINE_ATTRIBUTE6, LINE_ATTRIBUTE7, LINE_ATTRIBUTE8, LINE_ATTRIBUTE9, LINE_ATTRIBUTE10,
LINE_ATTRIBUTE11, LINE_ATTRIBUTE12, LINE_ATTRIBUTE13, LINE_ATTRIBUTE14, LINE_ATTRIBUTE15,
DISTRIBUTION_ATTRIBUTE1, DISTRIBUTION_ATTRIBUTE2, DISTRIBUTION_ATTRIBUTE3, DISTRIBUTION_ATTRIBUTE4, DISTRIBUTION_ATTRIBUTE5,
DISTRIBUTION_ATTRIBUTE6, DISTRIBUTION_ATTRIBUTE7, DISTRIBUTION_ATTRIBUTE8, DISTRIBUTION_ATTRIBUTE9, DISTRIBUTION_ATTRIBUTE10,
DISTRIBUTION_ATTRIBUTE11, DISTRIBUTION_ATTRIBUTE12, DISTRIBUTION_ATTRIBUTE13, DISTRIBUTION_ATTRIBUTE14, DISTRIBUTION_ATTRIBUTE15,
UNIT_PRICE, BATCH_ID, TRANSACTION_ID, BUDGET_ACCOUNT_ID, ACCRUAL_ACCOUNT_ID, VARIANCE_ACCOUNT_ID
FROM PO_REQUISITIONS_INTERFACE
WHERE REQUEST_ID =:c_request_id AND CHARGE_ACCOUNT_ID IS NULL'
;
SELECT APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS
WHERE ID_FLEX_NUM=v_id_flex_num
AND ID_FLEX_CODE ='GL#';
UPDATE PO_REQUISITIONS_INTERFACE
SET CHARGE_ACCOUNT_ID=l_o_code_combation_id
WHERE ROWID =l_rowid;
UPDATE PO_REQUISITIONS_INTERFACE
SET BUDGET_ACCOUNT_ID=l_o_budget_account_id
WHERE ROWID =l_rowid;
UPDATE PO_REQUISITIONS_INTERFACE
SET ACCRUAL_ACCOUNT_ID=l_o_accrual_account_id
WHERE ROWID =l_rowid;
UPDATE PO_REQUISITIONS_INTERFACE
SET VARIANCE_ACCOUNT_ID=l_o_variance_account_id
WHERE ROWID =l_rowid;
INSERT
INTO PO_INTERFACE_ERRORS
(
interface_type,
interface_transaction_id,
column_name,
error_message,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
table_name,
batch_id
)
VALUES
(
'REQIMPORT',
l_transaction_id,
'GENERATE_ACCOUNTS_USING_WORKFLOW',
l_o_FB_ERROR_MSG,
SYSDATE ,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id ,
p_prog_application_id,
p_program_id,
SYSDATE,
'PO_REQUISITIONS_INTERFACE',
l_batch_id
);
INSERT INTO PO_INTERFACE_ERRORS
(
interface_type,
interface_transaction_id,
column_name,
error_message,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
table_name,
batch_id
)
VALUES
(
'REQIMPORT',
l_transaction_id,
'GENERATE_ACCOUNTS_USING_WORKFLOW',
'SQL Error In Account Generator wf',
SYSDATE ,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id ,
p_prog_application_id,
p_program_id,
SYSDATE,
'PO_REQUISITIONS_INTERFACE',
l_batch_id
);
Function : updates the accounts in req distribution interface table by calling
workflow api if charge account, charge account segments are empty.
and updates other accounts if already exists in req dist interface
Parameters :
p_request_id IN NUMBER : concurrent req no
p_coa_id IN NUMBER : chart of accounts id
p_user_id IN NUMBER : user id
p_login_id IN NUMBER : login id
p_prog_application_id IN NUMBER : program application id
p_program_id IN NUMBER : program id
*/
PROCEDURE req_imp_mul_dst_act_up_frm_wf
(
p_request_id IN NUMBER,
p_coa_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_prog_application_id IN NUMBER,
p_program_id IN NUMBER
)
IS
l_category_id PO_REQUISITIONS_INTERFACE_ALL.CATEGORY_ID%TYPE;
' SELECT PRDI.ROWID,
PRI.CATEGORY_ID, PRDI.DESTINATION_TYPE_CODE, PRI.DELIVER_TO_LOCATION_ID, PRDI.DESTINATION_ORGANIZATION_ID, PRDI.DESTINATION_SUBINVENTORY,
PRDI.EXPENDITURE_TYPE, PRDI.EXPENDITURE_ORGANIZATION_ID, PRDI.EXPENDITURE_ITEM_DATE, PRDI.ITEM_ID, PRI.LINE_TYPE_ID,
PRI.PREPARER_ID, PRDI.PROJECT_ID, PRI.DOCUMENT_TYPE_CODE, PRI.SOURCE_TYPE_CODE, PRI.SOURCE_ORGANIZATION_ID,
PRI.SOURCE_SUBINVENTORY, PRDI.TASK_ID, PRI.AWARD_ID, PRI.DELIVER_TO_REQUESTOR_ID, PRI.SUGGESTED_VENDOR_ID,
PRI.SUGGESTED_VENDOR_SITE_ID, PRI.WIP_ENTITY_ID, PRI.WIP_LINE_ID, PRI.WIP_REPETITIVE_SCHEDULE_ID, PRI.WIP_OPERATION_SEQ_NUM,
PRI.WIP_RESOURCE_SEQ_NUM, PRDI.PREVENT_ENCUMBRANCE_FLAG, PRDI.GL_DATE,
PRI.HEADER_ATTRIBUTE1, PRI.HEADER_ATTRIBUTE2, PRI.HEADER_ATTRIBUTE3, PRI.HEADER_ATTRIBUTE4, PRI.HEADER_ATTRIBUTE5,
PRI.HEADER_ATTRIBUTE6, PRI.HEADER_ATTRIBUTE7, PRI.HEADER_ATTRIBUTE8, PRI.HEADER_ATTRIBUTE9, PRI.HEADER_ATTRIBUTE10,
PRI.HEADER_ATTRIBUTE11, PRI.HEADER_ATTRIBUTE12, PRI.HEADER_ATTRIBUTE13, PRI.HEADER_ATTRIBUTE14, PRI.HEADER_ATTRIBUTE15,
PRI.LINE_ATTRIBUTE1, PRI.LINE_ATTRIBUTE2, PRI.LINE_ATTRIBUTE3, PRI.LINE_ATTRIBUTE4, PRI.LINE_ATTRIBUTE5,
PRI.LINE_ATTRIBUTE6, PRI.LINE_ATTRIBUTE7, PRI.LINE_ATTRIBUTE8, PRI.LINE_ATTRIBUTE9, PRI.LINE_ATTRIBUTE10,
PRI.LINE_ATTRIBUTE11, PRI.LINE_ATTRIBUTE12, PRI.LINE_ATTRIBUTE13, PRI.LINE_ATTRIBUTE14, PRI.LINE_ATTRIBUTE15,
PRDI.DISTRIBUTION_ATTRIBUTE1, PRDI.DISTRIBUTION_ATTRIBUTE2, PRDI.DISTRIBUTION_ATTRIBUTE3, PRDI.DISTRIBUTION_ATTRIBUTE4, PRDI.DISTRIBUTION_ATTRIBUTE5,
PRDI.DISTRIBUTION_ATTRIBUTE6, PRDI.DISTRIBUTION_ATTRIBUTE7, PRDI.DISTRIBUTION_ATTRIBUTE8, PRDI.DISTRIBUTION_ATTRIBUTE9, PRDI.DISTRIBUTION_ATTRIBUTE10,
PRDI.DISTRIBUTION_ATTRIBUTE11, PRDI.DISTRIBUTION_ATTRIBUTE12, PRDI.DISTRIBUTION_ATTRIBUTE13, PRDI.DISTRIBUTION_ATTRIBUTE14, PRDI.DISTRIBUTION_ATTRIBUTE15,
PRI.UNIT_PRICE, PRDI.BATCH_ID, PRDI.TRANSACTION_ID, PRDI.BUDGET_ACCOUNT_ID, PRDI.ACCRUAL_ACCOUNT_ID, PRDI.VARIANCE_ACCOUNT_ID
FROM PO_REQUISITIONS_INTERFACE PRI, PO_REQ_DIST_INTERFACE PRDI
WHERE PRI.REQ_DIST_SEQUENCE_ID = PRDI.DIST_SEQUENCE_ID
AND PRI.REQUEST_ID =:c_request_id AND PRDI.CHARGE_ACCOUNT_ID IS NULL '
;
SELECT APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS
WHERE ID_FLEX_NUM=v_id_flex_num
AND ID_FLEX_CODE ='GL#';
UPDATE PO_REQ_DIST_INTERFACE
SET CHARGE_ACCOUNT_ID=l_o_code_combation_id
WHERE ROWID =l_rowid;
UPDATE PO_REQ_DIST_INTERFACE
SET BUDGET_ACCOUNT_ID=l_o_budget_account_id
WHERE ROWID =l_rowid;
UPDATE PO_REQ_DIST_INTERFACE
SET ACCRUAL_ACCOUNT_ID=l_o_accrual_account_id
WHERE ROWID =l_rowid;
UPDATE PO_REQ_DIST_INTERFACE
SET VARIANCE_ACCOUNT_ID=l_o_variance_account_id
WHERE ROWID =l_rowid;
INSERT
INTO PO_INTERFACE_ERRORS
(
interface_type,
interface_transaction_id,
column_name,
error_message,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
table_name,
batch_id
)
VALUES
(
'REQIMPORT',
l_transaction_id,
'GENERATE_ACCOUNTS_USING_WORKFLOW',
l_o_FB_ERROR_MSG,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
SYSDATE,
'PO_REQUISITIONS_INTERFACE',
l_batch_id
);
INSERT INTO PO_INTERFACE_ERRORS
(
interface_type,
interface_transaction_id,
column_name,
error_message,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
table_name,
batch_id
)
VALUES
(
'REQIMPORT',
l_transaction_id,
'GENERATE_ACCOUNTS_USING_WORKFLOW',
'SQL Error In Account Generator wf',
SYSDATE ,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id ,
p_prog_application_id,
p_program_id,
SYSDATE,
'PO_REQUISITIONS_INTERFACE',
l_batch_id
);
CURSOR c1 is SELECT Acrn, error_message
FROM (SELECT acrn,
MAX(ltrim
(sys_connect_by_path(gen_vals,' and '),' and ')) error_message
FROM (SELECT acrn acrn, c_l ||
MAX(ltrim(sys_connect_by_path(gen_value,', '),', ')) gen_vals
FROM (SELECT char5 acrn,(
CASE
WHEN char2 IS NOT NULL
THEN l_loas
ELSE l_charge_acc
END ) c_l,
CHAR3 Gen_value
FROM po_session_gt
where KEY = l_key)
CONNECT BY NOCYCLE prior acrn=acrn
AND prior Gen_value <> Gen_value
AND prior c_l = c_l
GROUP BY acrn, c_l
HAVING acrn IS NOT NULL)
CONNECT BY NOCYCLE prior acrn=acrn
AND prior gen_vals <> gen_vals
GROUP BY acrn)
WHERE( instr(error_message,',') <> 0 OR
( instr(error_message,',') = 0 and instr(error_message,'and') <> 0)) AND ROWNUM < 2;
CURSOR c2 is SELECT c_l || ' ' || gen_val, acrns
FROM (SELECT gen_val,
c_l,
MAX(ltrim(sys_connect_by_path(acrn,', '),', ')) acrns
FROM (SELECT char3 gen_val,
(
CASE -- case statement to identify whether or not it is a loa
WHEN char2 IS NOT NULL
THEN l_loas
ELSE l_charge_acc
END) c_l,
char5 acrn
FROM po_session_gt
WHERE char5 IS NOT NULL
and KEY = l_key
)-- This part of the sql selects the gen value and identifies it as loa/ca
CONNECT BY NOCYCLE prior gen_val=gen_val
AND prior acrn <> acrn
AND prior c_l = c_l
GROUP BY gen_val,
c_l
)-- This part of the sql joins the result with its previous rows and forms
-- comma seperated values.
WHERE instr(acrns,',') <> 0 AND ROWNUM < 2;
INSERT INTO PO_SESSION_GT
(KEY,NUM1,CHAR1,CHAR2,CHAR3,CHAR5)
SELECT L_KEY,
DISTRIBUTION_ID,
CHARGE_ACC,
CLM_MISC_LOA,
NVL(CLM_MISC_LOA,CHARGE_ACC) GEN_VAL,
ACRN
FROM
(
SELECT PRD.DISTRIBUTION_ID,
GLCC.CONCATENATED_SEGMENTS CHARGE_ACC,
PRD.CLM_MISC_LOA ,
PRD.ACRN
FROM PO_REQ_DISTRIBUTIONS_ALL PRD,
GL_CODE_COMBINATIONS_KFV GLCC
WHERE PRD.DISTRIBUTION_ID IN
(
SELECT DISTRIBUTION_ID
FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE REQUISITION_LINE_ID IN
(
SELECT REQUISITION_LINE_ID
FROM PO_REQUISITION_LINES_ALL
WHERE REQUISITION_HEADER_ID = x_document_id
))
AND PRD.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
);
and will insert them into po_online_report_text_gt to display error message.
Logic of the query is as shown below
Data:
CHARGE_ACC | LOA | ACRN
A B AA
A AA
A D AK
B AB
C D AB
E AA
F A AA
Stage 1 of the query gets data in this format
ACRN | CHARGE/LOA | VALUES
AA CHARGE_ACC A,E
AA LOA B,A
Stage 2 of the query gets the data in this format
ACRN | ERROR_MESSAGE
AA CHARGE_ACC A,E and LOA B,A
These two values will be pased to form the message PO_DUPLICATE_ACRN which
will be inserted into the po_online_report_text_gt
*/
l_loas := PO_CORE_S.get_translated_text('PO_LOA_DISPLAY');
will be inserted into the po_online_report_text_gt
*/
OPEN c2;
select count(1) into l_pdf_exists
from fnd_lobs fl, fnd_documents fd, fnd_attached_documents fad
where fad.document_id = fd.document_id and fd.media_id = fl.file_id
and fad.entity_name = 'REQ_HEADS' and fad.pk1_value = p_document_id;
select max(revision_num) into l_revision_num
from po_requisition_headers_all
where requisition_header_id = p_document_id
and authorization_status = 'APPROVED';
select to_char (PO_WF_ITEMKEY_S.NEXTVAL) into l_seq_for_item_key from sys.dual;
SELECT segment1 into l_segment1
from po_requisition_headers_all
where requisition_header_id = p_document_id;
PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
select count(1) into l_user_exists
from wf_users where ORIG_SYSTEM_ID = p_to_user_id;
SELECT wfl.nls_language,
wfl.nls_territory
INTO l_adhocuser_lang, l_adhocuser_territory
FROM wf_languages wfl,
fnd_languages_vl flv
WHERE wfl.code = flv.language_code
AND flv.installed_flag = 'B';
SELECT email_address INTO l_email_address
FROM fnd_user WHERE employee_id = p_to_user_id;
select count(1) into l_performer_exists
from wf_users where name = Upper(l_po_email_performer);
SELECT name into l_po_email_performer
from wf_users where ORIG_SYSTEM_ID = p_to_user_id AND ROWNUM = 1;
PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(l_itemtype, l_itemkey, l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
SELECT fl.file_data, fl.file_name into l_document, l_filename
FROM fnd_lobs fl, fnd_documents fd, fnd_attached_documents fad
WHERE fad.document_id = fd.document_id AND fd.media_id = fl.file_id
AND fad.entity_name = 'REQ_HEADS' AND fad.pk1_value = l_document_id;
PROCEDURE insert_action_in_mipr_history(p_document_id in number,
p_action_code in varchar2,
p_remarks IN VARCHAR2,
p_office_details IN varchar2,
p_contact_id IN number) IS
l_progress VARCHAR2(300);
l_api_name CONSTANT VARCHAR2(50) := 'insert_action_in_mipr_history';
l_progress := 'POR_UTIL_PKG.insert_action_in_mipr_history: Start';
SELECT Count(*) INTO l_rec_found
FROM PO_CLM_MIPR_ACTION_HISTORY
WHERE requisition_header_id = p_document_id
AND action_code = p_action_code;
INSERT INTO PO_CLM_MIPR_ACTION_HISTORY(
requisition_header_id,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
last_update_login,
remarks,
office,
contact,
notes ) VALUES (
p_document_id,
NVL((SELECT Max(sequence_num) + 1
FROM PO_CLM_MIPR_ACTION_HISTORY
WHERE requisition_header_id = p_document_id),1),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
p_action_code,
SYSDATE,
fnd_global.login_id,
p_remarks,
p_office_details,
p_contact_id,
'');
l_progress := 'POR_UTIL_PKG.insert_action_in_mipr_history: In Exception handler';
END insert_action_in_mipr_history;