[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE
FROM OE_APPROVER_TRANSACTIONS
WHERE TRANSACTION_ID = l_transaction_id;
/* OE_ORDER_WF_UTIL.Update_Flow_Status_Code
(p_item_type => itemtype,
p_header_id => l_transaction_id,
p_flow_status_code => 'INTERNAL_APPROVED',
p_sales_document_type_code => l_sales_document_type_code,
x_return_status => l_return_status );
oe_debug_pub.add('Initiate_approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
select sold_to_org_id, expiration_date, salesrep_id
into l_sold_to_org_id, l_expiration_date, l_salesrep_id
from oe_order_headers_all
where header_id = to_number(itemkey);
select obha.sold_to_org_id, obhe.end_date_active, obha.salesrep_id
into l_sold_to_org_id, l_expiration_date, l_salesrep_id
from oe_blanket_headers_all obha, oe_blanket_headers_ext obhe
where obha.header_id = to_number(itemkey)
and obha.order_number = obhe.order_number;
OE_ORDER_WF_UTIL.Update_Flow_Status_Code
(p_item_type => itemtype,
p_header_id => l_transaction_id,
p_flow_status_code => 'PENDING_INTERNAL_APPROVAL',
p_sales_document_type_code => l_sales_document_type_code,
x_return_status => l_return_status );
oe_debug_pub.add('Initiate_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
the definition/setup in the OM Approver List form, insert/update
the proper record in the OM Approval transaction table
OE_APPROVAL_TRANSACTIONS. Checks the max(approver_sequence) from
OE_APPROVAL_TRANSACTIONS given a transaction_id, and then fetches
the role from the OE_APPROVER_LISTS with approver_sequence = max+1
and insert the record in OE_APPROVER_TRANSACTIONS.
Returns Y if it finds the next approver and returns N if there is no
approver left. In the case there is no more approvers, it will update
the status to APPROVED
*/
Procedure Get_Next_Approver
(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY /* file.sql.39 change */ varchar2)
IS
l_transaction_id NUMBER;
UPDATE OE_APPROVER_TRANSACTIONS
SET APPROVAL_STATUS = 'APPROVED'
WHERE TRANSACTION_ID = l_transaction_id
--- ?? phase code = not really needed
AND APPROVER_SEQUENCE = (select max(APPROVER_SEQUENCE)
from OE_APPROVER_TRANSACTIONS
WHERE TRANSACTION_ID = l_transaction_id);
OE_ORDER_WF_UTIL.Update_Flow_Status_Code
(p_item_type => itemtype,
p_header_id => l_transaction_id,
p_flow_status_code => 'APPROVED',
p_sales_document_type_code => l_sales_document_type_code,
x_return_status => l_return_status );
oe_debug_pub.add('Get_next_approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
select sold_to_org_id, expiration_date, salesrep_id
into l_sold_to_org_id, l_expiration_date, l_salesrep_id
from oe_order_headers_all
where header_id = l_transaction_id;
select obha.sold_to_org_id, obhe.end_date_active, obha.salesrep_id
into l_sold_to_org_id, l_expiration_date, l_salesrep_id
from oe_blanket_headers_all obha,
oe_blanket_headers_ext obhe
where obha.header_id = l_transaction_id
and obha.order_number = obhe.order_number;
select ROLE, APPROVER_SEQUENCE
from OE_APPROVER_LIST_MEMBERS
where list_id = l_list_id
and APPROVER_SEQUENCE > l_curr_approver_sequence
and ACTIVE_FLAG = 'Y'
order by APPROVER_SEQUENCE;
select ORDER_TYPE_ID, nvl(TRANSACTION_PHASE_CODE, 'F')
into l_transaction_type_id, l_transaction_phase_code
from oe_blanket_headers_all
where header_id = p_transaction_id;
select ORDER_TYPE_ID, nvl(TRANSACTION_PHASE_CODE, 'F')
into l_transaction_type_id, l_transaction_phase_code
from oe_order_headers_all
where header_id = p_transaction_id;
select list_id
into l_list_id
from OE_APPROVER_LISTS
where TRANSACTION_TYPE_ID = l_transaction_type_id
and TRANSACTION_PHASE_CODE is not NULL
and TRANSACTION_PHASE_CODE = l_transaction_phase_code
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
AND NVL(END_DATE_ACTIVE, SYSDATE );
select list_id
into l_list_id
from OE_APPROVER_LISTS
where TRANSACTION_TYPE_ID = l_transaction_type_id
and TRANSACTION_PHASE_CODE is NULL
and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
AND NVL(END_DATE_ACTIVE, SYSDATE );
select max(APPROVER_SEQUENCE)
into l_curr_approver_sequence
from OE_APPROVER_TRANSACTIONS
where TRANSACTION_ID = p_transaction_id
and TRANSACTION_TYPE_ID = l_transaction_type_id
and TRANSACTION_PHASE_CODE = l_transaction_phase_code;
oe_debug_pub.add('Inserting into OE_APPROVER_TRANSACTIONS ', 1) ;
INSERT INTO OE_APPROVER_TRANSACTIONS
(
TRANSACTION_ID
,TRANSACTION_TYPE_ID --?? Do we need this. evalute
,TRANSACTION_PHASE_CODE
,ROLE
,APPROVER_SEQUENCE
,APPROVAL_STATUS
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_transaction_id
,l_transaction_type_id
,l_transaction_phase_code
,l_role
,l_approver_sequence
,NULL --APPROVAL_STATUS
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,l_user_id
);
This procedure will update the OM Approval transaction table
OE_APPROVER_TRANSACTIONS with proper results. Perform a status
update to INTERNAL_APPROVED.
*/
Procedure Approve_Approval
(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY /* file.sql.39 change */ varchar2)
IS
l_transaction_id NUMBER;
OE_ORDER_WF_UTIL.Update_Flow_Status_Code
(p_item_type => itemtype,
p_header_id => l_transaction_id,
p_flow_status_code => 'INTERNAL_APPROVED',
p_sales_document_type_code => l_sales_document_type_code,
x_return_status => l_return_status );
oe_debug_pub.add('Approve_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
This procedure will update the OM Approval transaction table
OE_APPROVER_TRANSACTIONS with proper results. Perform a status
update to DRAFT_INTERNAL_REJECTED. And update the column
DRAFT_SUBMITTED_FLAG to 'N' to the base table.
*/
Procedure Reject_Approval
(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY /* file.sql.39 change */ varchar2)
IS
l_transaction_id NUMBER;
UPDATE OE_APPROVER_TRANSACTIONS
SET APPROVAL_STATUS = 'REJECTED'
WHERE TRANSACTION_ID = to_number(itemkey)
AND APPROVER_SEQUENCE = ( select max(APPROVER_SEQUENCE)
from OE_APPROVER_TRANSACTIONS
where TRANSACTION_ID = to_number(itemkey));
OE_ORDER_WF_UTIL.Update_Quote_Blanket(
p_item_type => OE_GLOBALS.G_WFI_NGO,
p_item_key => to_number(itemkey),
p_flow_status_code => 'DRAFT_INTERNAL_REJECTED',
p_draft_submitted_flag => 'N',
x_return_status => l_return_status);
OE_ORDER_WF_UTIL.Update_flow_status_code(
p_item_type => itemtype,
p_header_id => to_number(itemkey),
p_flow_status_code => 'INTERNAL_REJECTED',
x_return_status => l_return_status);
oe_debug_pub.add('Reject_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
select to_number(ITEM_KEY)
into l_transaction_id
from WF_ITEM_ACTIVITY_STATUSES
where NOTIFICATION_ID = to_number(document_id);
select role
into l_role
from OE_APPROVER_TRANSACTIONS
where TRANSACTION_ID = p_transaction_id
and APPROVER_SEQUENCE = ( select max(APPROVER_SEQUENCE)
from OE_APPROVER_TRANSACTIONS
where TRANSACTION_ID = p_transaction_id);
select to_number(ITEM_KEY)
into l_transaction_id
from WF_ITEM_ACTIVITY_STATUSES
where NOTIFICATION_ID = to_number(document_id);
select meaning
into l_sales_document_type_desc
from oe_lookups
where LOOKUP_TYPE = 'SALES_DOCUMENT_TYPE'
and LOOKUP_CODE = l_sales_document_type_code;
SELECT parent_item_key
INTO l_parent_item_key
FROM wf_items wi
WHERE wi.item_type = itemtype AND wi.item_key = itemkey;
DELETE FROM oe_approver_transactions
WHERE transaction_id = l_transaction_id;
SELECT quote_number
INTO l_transaction_number
FROM oe_order_headers_all -- 16084377 AME BSA
WHERE header_id = TO_NUMBER (itemkey);
SELECT ORDER_number -- it is order number. Not quote number.
INTO l_transaction_number
FROM oe_blanket_headers_all
WHERE header_id = TO_NUMBER (itemkey);
SELECT TO_CHAR (oe_ame_parallel_s.NEXTVAL)
INTO l_item_key
FROM SYS.DUAL;
SELECT sold_to_org_id, expiration_date,
salesrep_id, transaction_phase_code,
order_type_id
INTO l_sold_to_org_id, l_expiration_date,
l_salesrep_id, l_transaction_phase_code,
l_transaction_type_id
FROM oe_order_headers_all
WHERE header_id = TO_NUMBER (itemkey);
SELECT obha.sold_to_org_id, obhe.end_date_active,
obha.salesrep_id,
obha.transaction_phase_code,
obha.order_type_id
INTO l_sold_to_org_id, l_expiration_date,
l_salesrep_id,
l_transaction_phase_code,
l_transaction_type_id
FROM oe_blanket_headers_all obha,
oe_blanket_headers_ext obhe
WHERE obha.header_id = TO_NUMBER (itemkey)
AND obha.order_number = obhe.order_number;
oe_order_wf_util.update_flow_status_code
(p_item_type => itemtype,
p_header_id => l_transaction_id,
p_flow_status_code => 'PENDING_INTERNAL_APPROVAL',
p_sales_document_type_code => l_sales_document_type_code,
x_return_status => l_return_status
);
( 'Initiate_Approval STATUS FROM Update_Flow_Status_Code: '
|| l_return_status
);
SELECT COUNT (1)
INTO l_open_children
FROM wf_items wi
WHERE wi.item_type = 'OEAME'
AND wi.parent_item_type = itemtype
AND wi.parent_item_key = itemkey
AND wi.item_key <> NVL (g_current_item_key, 'XXXX')
AND wi.end_date IS NULL;
SELECT item_type, item_key
FROM wf_items
WHERE parent_item_type = itemtype AND parent_item_key = itemkey;
SELECT parent_item_key, parent_item_type
INTO l_parent_itemkey, l_parent_itemtype
FROM wf_items
WHERE item_type = itemtype AND item_key = itemkey;
l_step := 'updateApprovalStatus2';
ame_api2.updateapprovalstatus2
(applicationidin => 660,
transactiontypein => l_ame_transaction_type,
--itemtype,
transactionidin => TO_NUMBER
(l_parent_itemkey),
approvalstatusin => ame_util.approvedstatus,
approvernamein => l_name,
itemclassin => 'header',
itemidin => TO_NUMBER
(l_parent_itemkey)
);
l_step := 'updateApprovalStatus2';
ame_api2.updateapprovalstatus2
(applicationidin => 660,
transactiontypein => l_ame_transaction_type,
--itemtype,
transactionidin => TO_NUMBER
(l_parent_itemkey),
approvalstatusin => ame_util.approvedstatus,
approvernamein => l_name,
itemclassin => 'header',
itemidin => TO_NUMBER
(l_parent_itemkey)
);
l_step := 'updateApprovalStatus2';
ame_api2.updateapprovalstatus2
(applicationidin => 660,
transactiontypein => l_ame_transaction_type,
--itemtype,
transactionidin => TO_NUMBER
(l_parent_itemkey),
approvalstatusin => ame_util.approvedstatus,
approvernamein => l_name,
itemclassin => 'header',
itemidin => TO_NUMBER
(l_parent_itemkey)
);
oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
|| itemtype
|| '-'
|| itemkey,
NULL || '-' || l_application_id
);
oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
|| itemtype
|| '-'
|| itemkey
|| '-'
|| l_application_id
|| SQLERRM
);
SELECT parent_item_key, parent_item_type
INTO l_parent_itemkey, l_parent_item_type
FROM wf_items
WHERE item_type = itemtype AND item_key = itemkey;
l_step := 'updateApprovalStatus2';
ame_api2.updateapprovalstatus2
(applicationidin => 660,
transactiontypein => l_ame_transaction_type,
--itemtype,
transactionidin => TO_NUMBER
(l_parent_itemkey),
approvalstatusin => ame_util.rejectstatus,
approvernamein => l_name,
itemclassin => 'header',
itemidin => TO_NUMBER
(l_parent_itemkey)
);
l_step := 'updateApprovalStatus2';
ame_api2.updateapprovalstatus2
(applicationidin => 660,
transactiontypein => l_ame_transaction_type,
--itemtype,
transactionidin => TO_NUMBER
(l_parent_itemkey),
approvalstatusin => ame_util.rejectstatus,
approvernamein => l_name,
itemclassin => 'header',
itemidin => TO_NUMBER
(l_parent_itemkey)
);
l_step := 'updateApprovalStatus2';
ame_api2.updateapprovalstatus2
(applicationidin => 660,
transactiontypein => l_ame_transaction_type,
--itemtype,
transactionidin => TO_NUMBER
(l_parent_itemkey),
approvalstatusin => ame_util.rejectstatus,
approvernamein => l_name,
itemclassin => 'header',
itemidin => TO_NUMBER
(l_parent_itemkey)
);
oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
|| itemtype
|| '-'
|| itemkey,
NULL || '-' || l_application_id
);
oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
|| itemtype
|| '-'
|| itemkey
|| '-'
|| l_application_id
|| SQLERRM
);
SELECT wfi.item_type, wfi.item_key
FROM wf_items wfi, wf_item_activity_statuses wfias
WHERE wfi.parent_item_key = p_itemkey
AND wfi.item_type = 'OEAME'
AND wfi.parent_item_type = p_itemtype
AND wfias.item_type = wfi.item_type
AND wfias.item_key = wfi.item_key
AND wfias.activity_status = 'NOTIFIED'
AND wfias.notification_id IS NOT NULL
AND wfi.item_key <> itemkey;
SELECT parent_item_type, parent_item_key
INTO l_parent_item_type, l_parent_item_key
FROM wf_items
WHERE item_type = itemtype AND item_key = itemkey;
oe_debug_pub.ADD ( 'api_insertion'
|| ' '
|| approverlist (i).api_insertion
);
AND approverlist (i).api_insertion = ame_util.oamgenerated
AND approverlist (i).group_or_chain_id =
l_current_appr_group_id
)
THEN
l_orig_system := approverlist (i).orig_system;
SELECT person_id
INTO l_person_id
FROM (SELECT person.person_id
FROM per_all_people_f person,
per_all_assignments_f asg
WHERE asg.position_id = l_orig_system_id
AND TRUNC (SYSDATE)
BETWEEN person.effective_start_date
AND NVL
(person.effective_end_date,
TRUNC (SYSDATE)
)
AND person.person_id = asg.person_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ('E', 'C')
AND ( person.current_employee_flag = 'Y'
OR person.current_npw_flag = 'Y'
)
AND asg.assignment_status_type_id NOT IN (
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status =
'TERM_ASSIGN')
AND TRUNC (SYSDATE)
BETWEEN asg.effective_start_date
AND asg.effective_end_date
ORDER BY person.last_name)
WHERE ROWNUM = 1;
SELECT employee_id
INTO l_person_id
FROM fnd_user
WHERE user_id = l_orig_system_id
AND TRUNC (SYSDATE) BETWEEN start_date
AND NVL (end_date, SYSDATE + 1);
SELECT 1
INTO l_abort
FROM wf_item_attribute_values
WHERE item_type = l_child_wf_cur.item_type
AND NAME = 'ORIG_SYSTEM_ID'
AND number_value = l_orig_system_id
AND item_key = l_child_wf_cur.item_key;
SELECT use_ame_approval
INTO l_ame_used
FROM oe_transaction_types_all
WHERE transaction_type_id = (SELECT order_type_id
FROM oe_blanket_headers_all -- 16084377 AME BSA
WHERE header_id = TO_NUMBER (itemkey));
SELECT use_ame_approval
INTO l_ame_used
FROM oe_transaction_types_all
WHERE transaction_type_id = (SELECT order_type_id
FROM oe_order_headers_all
WHERE header_id = TO_NUMBER (itemkey));
SELECT 'Y'
INTO l_rejected_once
FROM wf_item_activity_statuses_h wiash,
wf_process_activities wpa
WHERE wiash.process_activity = wpa.instance_id
AND wiash.item_type = itemtype
AND wiash.item_key = itemkey
AND wpa.activity_name = 'INTERNAL_APPROVAL_PROCESS_AME'
AND wiash.activity_status = 'COMPLETE'
AND wiash.activity_result_code = 'REJECTED'
AND ROWNUM = 1;
SELECT parent_item_key, parent_item_type
INTO l_parent_item_key, l_parent_item_type
FROM wf_items
WHERE item_type = itemtype AND item_key = itemkey;
SELECT category_code
INTO l_cust_category
FROM hz_parties hp, fnd_lookup_values fl, fnd_languages fndl -- 16084377 AME BSA
WHERE fl.lookup_type = 'CUSTOMER_CATEGORY'
AND fl.LANGUAGE = fndl.language_code
AND fndl.installed_flag = 'B'
AND fl.lookup_code = hp.category_code
AND party_id = (SELECT sold_to_org_id
FROM oe_blanket_headers_all
WHERE header_id = p_header_id);
SELECT category_code
INTO l_cust_category
FROM hz_parties hp, fnd_lookup_values fl, fnd_languages fndl
WHERE fl.lookup_type = 'CUSTOMER_CATEGORY'
AND fl.LANGUAGE = fndl.language_code
AND fndl.installed_flag = 'B'
AND fl.lookup_code = hp.category_code
AND party_id = (SELECT sold_to_org_id
FROM oe_order_headers_all
WHERE header_id = p_header_id);