[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;