[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: Select_Orders
DESCRIPTION: This server procedure is defined as a concurrent
PL/SQL executable program and is scheduled to run
from the Concurrent Manager at a regular intervals
(e.g. every day).
This procedure does the following:
- Open a cursor on RCV_CONFIRM_RECEIPT_V table to
select open PO shipments. The records are grouped
by PO number, Requester ID and Due date.
- For each unique PO number, Requester ID and Due date
it calls the Start_Rcpt_Process to initiate the
Confirm Receipt workflow process.
CHANGE HISTORY: WLAU 1/15/1997 Created
WLAU 2/25/1997 Added WF_PURGE.total to delete
the completed WF activities
===========================================================================*/
TYPE rcpt_record IS RECORD (
line_number NUMBER,
expected_qty NUMBER,
quantity_received NUMBER,
ordered_qty NUMBER,
unit_of_measure VARCHAR2(25),
item_description VARCHAR2(240),
currency_code VARCHAR2(15),
unit_price NUMBER,
po_distribution_id NUMBER);
SELECT int_err.error_message_name
INTO x_message_token
FROM po_interface_errors int_err
WHERE int_err.batch_id = x_group_id
AND int_err.interface_transaction_id =
(SELECT MIN(int_err2.interface_transaction_id)
FROM po_interface_errors int_err2
WHERE int_err2.batch_id = x_group_id);
x_inserted_txn BOOLEAN;
x_insert_txns_count NUMBER := 0;
x_insert_txns_status NUMBER;
type select_shipments_Cursor is ref cursor ;
Porcpt_Shipment select_shipments_Cursor;
SELECT rcv_interface_groups_s.nextval
INTO x_group_id
FROM sys.dual;
SELECT rcv.po_header_id,
po_line_location_id,
expected_receipt_qty,
primary_uom,
-- Bug 4672728
primary_uom_non_tl,
item_id,
primary_uom_class,
to_organization_id,
po_distribution_id,
null,
null,
null
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ((expected_receipt_date is not NULL
AND trunc(rcv.expected_receipt_date + 1) <=
trunc(SYSDATE))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty))
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND po_header_ID = x_po_header_ID
AND requestor_ID = x_requester_ID;
SELECT po_header_id,
po_line_location_id,
expected_receipt_qty,
primary_uom,
-- Bug 4672728
primary_uom_non_tl,
item_id,
primary_uom_class,
to_organization_id,
po_distribution_id,
null,
null,
null
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ((expected_receipt_date is not NULL
AND trunc(rcv.expected_receipt_date + 1) <=
trunc(SYSDATE))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty))
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND po_header_ID = x_po_header_ID
AND requestor_ID = x_requester_ID;
x_insert_txns_status := POR_RCV_ORD_SV.groupPoTransaction( t_po_header_id,
t_line_location_id,
t_expected_receipt_qty,
-- Bug 4672728 - We need to pass the
-- non translated UOM for standard UOM conversion
--t_ordered_uom,
t_ordered_uom_non_tl,
SYSDATE,
t_item_id,
t_primary_uom_class,
t_org_id,
t_po_distribution_id,
x_group_id,
'AUTO_RECEIVE',
t_Comments,
t_PackingSlip,
t_WayBillNum);
IF x_insert_txns_status = 0 THEN
x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'AUTO_RECEIVE');
PROCEDURE NAME: Select_Orders
DESCRIPTION: This server procedure is defined as a concurrent
PL/SQL executable program and is scheduled to run
from the Concurrent Manager at a regular intervals
(e.g. every day).
This procedure does the following:
- Open a cursor on RCV_CONFIRM_RECEIPT_V table to
select open PO shipments. The records are grouped
by PO number, Requester ID and Due date.
- For each unique PO number, Requester ID and Due date
it calls the Start_Rcpt_Process to initiate the
Confirm Receipt workflow process.
CHANGE HISTORY: WLAU 1/15/1997 Created
WLAU 2/25/1997 Added WF_PURGE.total to delete
the completed WF activities
===========================================================================*/
PROCEDURE Select_Orders IS
-- Define cursor for selecting records to start the Purchasing
-- Confirm Receipt workflow process. Records are retrieved from
-- the RCV_CONFIRM_RECEIPT_V view which is shared by the
-- Receive Orders Web Page.
type select_orders_Cursor is ref cursor ;
Porcpt_c select_orders_Cursor;
SELECT rcv.po_header_ID, rcv.requestor_ID,
rcv.expected_receipt_date, rcv.revision_num,
rcv.po_distribution_id, po_num_rel_num
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ( (rcv.expected_receipt_date is not NULL
AND trunc(rcv.expected_receipt_date + 1) <=
trunc(SYSDATE)
)
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty
)
)
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
GROUP BY rcv.po_header_ID, rcv.requestor_ID,
rcv.expected_receipt_date, rcv.revision_num,
rcv.po_distribution_id, po_num_rel_num;
SELECT rcv.po_header_ID, rcv.requestor_ID,
rcv.expected_receipt_date, rcv.revision_num,
rcv.po_distribution_id, po_num_rel_num
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ( (rcv.expected_receipt_date is not NULL
AND trunc(rcv.expected_receipt_date + 1) <=
trunc(SYSDATE)
)
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty
)
)
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
GROUP BY rcv.po_header_ID, rcv.requestor_ID,
rcv.expected_receipt_date, rcv.revision_num,
rcv.po_distribution_id, po_num_rel_num;
select PORL.auto_receive_flag
into x_auto_receive_flag
from PO_REQUISITION_LINES PORL,
PO_REQ_DISTRIBUTIONS PORD,
PO_DISTRIBUTIONS pod
where PORD.DISTRIBUTION_ID = POD.REQ_DISTRIBUTION_ID AND
PORD.REQUISITION_LINE_ID = PORL.requisition_line_id AND
POD.PO_DISTRIBUTION_ID = x_po_distribution_id;
select sysdate into x_sys_date from dual;
SELECT count(1) into x_item_key_count
FROM wf_items
WHERE item_type = 'PORCPT'
AND item_key like x_po_header_id||';'|| x_requester_id||';%'
update po_distributions
set wf_item_key = ''
where po_header_id = x_po_header_ID
and wf_item_key like x_po_header_ID||';'||x_requester_id||';%';
Select_Internal_Orders;
wf_core.context ('PORCPTWF','Select_Orders','No data found');
wf_core.context ('PORCPTWF','Select_Orders','SQL error ' || sqlcode);
END Select_Orders;
x_inserted_txn_status NUMBER := 0;
type select_shipments_Cursor is ref cursor ;
Porcpt_Shipment select_shipments_Cursor;
SELECT rcv_interface_groups_s.nextval
INTO x_group_id
FROM sys.dual;
SELECT REQUISITION_LINE_ID,
EXPECTED_RECEIPT_QTY,
PRIMARY_UOM,
ITEM_ID,
PRIMARY_UOM_CLASS,
TO_ORGANIZATION_ID,
COMMENTS,
PACKING_SLIP,
WAYBILL_AIRBILL_NUM
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_header_ID
AND requestor_ID = x_requester_ID;
SELECT REQUISITION_LINE_ID,
EXPECTED_RECEIPT_QTY,
PRIMARY_UOM,
ITEM_ID,
PRIMARY_UOM_CLASS,
TO_ORGANIZATION_ID,
COMMENTS,
PACKING_SLIP,
WAYBILL_AIRBILL_NUM
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_header_ID
AND requestor_ID = x_requester_ID;
x_inserted_txn_status := POR_RCV_ORD_SV.groupInternalTransaction (t_req_line_id,
t_expected_receipt_qty,
t_ordered_uom,
t_item_id,
t_primary_uom_class,
t_org_id,
t_comments,
t_packingSlip,
t_waybillNum,
x_group_id,
x_exp_receipt_date,
'WP4_CONFIRM');
IF x_inserted_txn_status = 0 THEN
x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'AUTO_RECEIVE');
PROCEDURE Select_Internal_Orders IS
-- Define cursor for selecting records to start the Purchasing
-- Confirm Receipt workflow process. Records are retrieved from
-- the RCV_CONFIRM_RECEIPT_V view which is shared by the
-- Receive Orders Web Page.
type select_orders_Cursor is ref cursor ;
Porcpt_c select_orders_Cursor;
SELECT so_header_ID,
requestor_ID,
expected_receipt_date,
requisition_line_ID,
requisition_header_ID
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND trunc(expected_receipt_date + 1) <= trunc(SYSDATE)
AND destination_type_code = 'EXPENSE'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
GROUP BY so_header_ID, requestor_ID, expected_receipt_date, requisition_line_ID, requisition_header_ID;
SELECT so_header_ID,
requestor_ID,
expected_receipt_date,
requisition_line_ID,
requisition_header_ID
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND trunc(expected_receipt_date + 1) <= trunc(SYSDATE)
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
GROUP BY so_header_ID, requestor_ID, expected_receipt_date, requisition_line_ID, requisition_header_ID;
select PORL.auto_receive_flag
into x_auto_receive_flag
from PO_REQUISITION_LINES PORL
where PORL.requisition_line_id = x_requisition_line_ID;
SELECT count(1) into x_item_key_count
FROM wf_items
WHERE item_type = 'PORCPT'
AND item_key like x_header_id||';'|| x_requester_id||';%'
wf_core.context ('PORCPTWF','Select_Internal_Orders','No data found');
wf_core.context ('PORCPTWF','Select_Internal_Orders','SQL error ' || sqlcode);
END Select_Internal_Orders;
Select 'X'
Into dummy
From per_workforce_current_x
Where person_id = x_requester_id;
select agent_id
into x_buyer_id
from po_headers
where po_header_id=x_header_id;
SELECT poh.segment1,
pov.vendor_name,
poh.agent_id,
poh.note_to_receiver
INTO x_po_number,
x_supplier_name,
x_buyer_id,
x_note_to_receiver
FROM PO_HEADERS poh,
PO_VENDORS pov
WHERE po_header_id = x_po_header_id
AND poh.vendor_id = pov.vendor_id (+);
select ORDER_NUMBER
into x_so_number
from oe_order_headers_all osh
where osh.HEADER_ID = x_so_header_id;
SELECT FND.user_id
INTO x_user_id
FROM FND_USER FND
WHERE FND.USER_NAME = x_requester_username
AND FND.START_DATE < sysdate
AND nvl(FND.END_DATE, sysdate + 1) >= sysdate
AND ROWNUM = 1;
select MIN(fr.responsibility_id)
into x_resp_id
from fnd_user_resp_groups fur,
fnd_responsibility_vl fr,
financials_system_parameters fsp
where fur.user_id = x_user_id
and fur.responsibility_application_id in (x_ip_resp_appl_id, x_po_resp_appl_id)
and fur.responsibility_id = fr.responsibility_id
and fr.start_date < sysdate
and nvl(fr.end_date, sysdate +1) >= sysdate
and fur.start_date < sysdate
and nvl(fur.end_date, sysdate +1) >= Sysdate
AND nvl(fnd_profile.value_specific('ORG_ID', NULL,
fr.responsibility_id, fur.responsibility_application_id),-1) = nvl(x_org_id,-1)
and nvl(fsp.org_id,-1) = nvl(x_org_id,-1)
and nvl(fsp.business_group_id,-1) =
nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL,
fr.responsibility_id, fur.responsibility_application_id),-1);
/* Bug 6277620- FP of 6054138 - Select the ip/po responsibility first and if not found then look for custom responsibilities*/
if (x_resp_id = -1) THEN
BEGIN
select MIN(fr.responsibility_id)
into x_resp_id
from fnd_user_resp_groups fur,
fnd_responsibility_vl fr,
financials_system_parameters fsp
where fur.user_id = x_user_id
and fur.responsibility_id = fr.responsibility_id
and fr.start_date < sysdate
and nvl(fr.end_date, sysdate +1) >= sysdate
and fur.start_date < sysdate
and nvl(fur.end_date, sysdate +1) >= Sysdate
AND nvl(fnd_profile.value_specific('ORG_ID', NULL,
fr.responsibility_id, fur.responsibility_application_id),-1) = nvl(x_org_id,-1)
and nvl(fsp.org_id,-1) = nvl(x_org_id,-1)
and nvl(fsp.business_group_id,-1) =
nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL,
fr.responsibility_id, fur.responsibility_application_id),-1);
SELECT MIN(responsibility_application_id)
INTO x_resp_appl_id
FROM fnd_user_resp_groups fur,
fnd_responsibility_vl fr,
financials_system_parameters fsp
WHERE
fur.responsibility_id = fr.responsibility_id and
fr.responsibility_id = x_resp_id and
fur.user_id = x_user_id and
fr.start_date < sysdate and
nvl(fr.end_date, sysdate +1) >= sysdate and
fur.start_date < sysdate and
nvl(fur.end_date, sysdate +1) >= Sysdate AND
nvl(fnd_profile.value_specific('ORG_ID', NULL, fr.responsibility_id,
fur.responsibility_application_id),-1) = nvl(x_org_id,-1) and
nvl(fsp.org_id,-1) = nvl(x_org_id,-1) and
nvl(fsp.business_group_id,-1) =
nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL,
fr.responsibility_id, fur.responsibility_application_id),-1);
procedure to insert the receipt records into the
receiving transaction interface table.
The Receiving Transaction Manager is then called in
'ON-LINE' mode to process the receipt records immediately.
If there are errors returned from the Receiving
Transaction Manager, the error status is set the
workflow item attribute for notifying the buyer and
requester of the error.
CHANGE HISTORY: WLAU 1/15/1997 Created
===========================================================================*/
PROCEDURE Process_Rcv_Trans ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funmode in varchar2,
result out NOCOPY varchar2 ) IS
TYPE shipment_orders_cursor IS ref CURSOR;
x_inserted_txn BOOLEAN;
x_insert_txns_count NUMBER := 0;
x_insert_txns_status NUMBER;
SELECT rcv_interface_groups_s.nextval
INTO x_group_id
FROM sys.dual;
SELECT po_header_id,
po_line_location_id,
expected_receipt_qty,
primary_uom,
item_id,
primary_uom_class,
to_organization_id,
po_distribution_id,
null,
null,
null
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ((expected_receipt_date is not NULL
AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty ))
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND po_header_ID = x_po_header_ID
AND requestor_ID = x_requester_ID;
SELECT po_header_id,
po_line_location_id,
expected_receipt_qty,
primary_uom,
item_id,
primary_uom_class,
to_organization_id,
po_distribution_id,
null,
null,
null
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ((expected_receipt_date is not NULL
AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty ))
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND po_header_ID = x_po_header_ID
AND requestor_ID = x_requester_ID;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
x_insert_txns_status := POR_RCV_ORD_SV.groupPoTransaction( t_po_header_id,
t_line_location_id,
t_expected_receipt_qty,
t_ordered_uom,
SYSDATE,
t_item_id,
t_primary_uom_class,
t_org_id,
t_po_distribution_id,
x_group_id,
'WP4_CONFIRM',
t_Comments,
t_PackingSlip,
t_WayBillNum);
IF x_insert_txns_status = 0 THEN
x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'WF');
update po_distributions
set wf_item_key = ''
where po_header_id = x_po_header_ID
and wf_item_key like x_po_header_ID||';'||x_requester_id||';%';
SELECT count(*)
INTO x_tmp_count
FROM POR_RCV_ALL_ITEMS_V1
WHERE expected_receipt_date is not NULL
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND po_header_ID = x_po_header_ID;
SELECT count(*)
INTO x_tmp_count1
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ((expected_receipt_date is not NULL
AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty ))
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND expected_receipt_qty = 0
AND po_header_ID = x_po_header_ID
AND requestor_ID = x_requester_ID;
SELECT count(*)
INTO x_tmp_count
FROM POR_RCV_ALL_ITEMS_V1
WHERE expected_receipt_date is not NULL
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_id is not NULL
AND po_header_ID = x_po_header_ID;
SELECT count(*)
INTO x_tmp_count1
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE ((expected_receipt_date is not NULL
AND trunc(to_date(to_char(expected_receipt_date+1), 'DD/MM/YYYY'))<=
trunc(to_date(x_ntf_trigerred_date, 'DD/MM/YYYY')))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty ))
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_id is not NULL
AND expected_receipt_qty = 0
AND po_header_ID = x_po_header_ID
AND requestor_ID = x_requester_ID;
x_rcv_trans_status := 'RCV_RCPT_INSERT_FAILED';
select multi_org_flag
from fnd_product_groups
where rownum < 2;
select org_id
from po_headers_all
where po_header_id = x_header_id;
select org_id
from oe_order_headers_all
where header_id = x_header_id;
SELECT po_header_ID, requestor_ID,
expected_receipt_date,
revision_num
FROM POR_RCV_ALL_ITEMS_V1
WHERE expected_receipt_date is not NULL
AND expected_receipt_date + 1 <= SYSDATE
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
GROUP BY po_header_ID, requestor_ID, expected_receipt_date, revision_num;
SELECT po_header_ID, requestor_ID, expected_receipt_date, revision_num
FROM POR_RCV_ALL_ITEMS_V1
WHERE expected_receipt_date is not NULL
AND expected_receipt_date + 1 <= SYSDATE
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
GROUP BY po_header_ID, requestor_ID, expected_receipt_date, revision_num;
po_wf_debug_pkg.insert_debug(x_wf_itemtype,x_wf_itemkey,'purge_orders SQL error ' || sqlcode || ' error message: ' || substr(sqlerrm,1,512));
SELECT COUNT(*) INTO
X_COUNT
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_so_header_id
AND requestor_ID = x_requester_id;
SELECT COUNT(*)
INTO X_COUNT
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_so_header_id
AND requestor_ID = x_requester_id;
ELSE /* the reminder should have an updated line count */
-- ash_debug.debug('does_item_exist x_count 1' , x_count);
select prh.segment1
INTO x_req_number
from po_requisition_headers prh,
po_requisition_lines prl,po_distributions pod,
po_req_distributions pord
where pod.po_distribution_id = x_po_distribution_id and
pord.distribution_id = pod.req_distribution_id and
pord.requisition_line_id = prl.requisition_line_id and
prl.requisition_header_id = prh.requisition_header_id;
Select 'X'
Into dummy
From per_workforce_current_x
Where person_id = x_requester_id;
Select cwk.supervisor_id
into x_manager_id
From per_workforce_current_x cwk
Where cwk.person_id = x_requester_id;
x_inserted_txn_status NUMBER;
SELECT rcv_interface_groups_s.nextval
INTO x_group_id
FROM sys.dual;
SELECT REQUISITION_LINE_ID,
EXPECTED_RECEIPT_QTY,
ORDERED_UOM,
ITEM_ID,
PRIMARY_UOM_CLASS,
TO_ORGANIZATION_ID,
COMMENTS,
PACKING_SLIP,
WAYBILL_AIRBILL_NUM
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_header_ID
AND requestor_ID = x_requester_ID;
SELECT REQUISITION_LINE_ID,
EXPECTED_RECEIPT_QTY,
ORDERED_UOM,
ITEM_ID,
PRIMARY_UOM_CLASS,
TO_ORGANIZATION_ID,
COMMENTS,
PACKING_SLIP,
WAYBILL_AIRBILL_NUM
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND requestor_id is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_header_ID
AND requestor_ID = x_requester_ID;
po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
x_inserted_txn_status := POR_RCV_ORD_SV.groupInternalTransaction (t_req_line_id,
t_expected_receipt_qty,
t_ordered_uom,
t_item_id,
t_primary_uom_class,
t_org_id,
t_comments,
t_packingSlip,
t_waybillNum,
x_group_id,
SYSDATE,
'WP4_CONFIRM');
IF x_inserted_txn_status = 0 THEN
x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'WF');
SELECT count(*)
INTO x_tmp_count
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND so_header_ID = x_header_ID;
SELECT count(*)
INTO x_tmp_count1
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_id is not NULL
AND expected_receipt_qty = 0
AND so_header_ID = x_header_ID
AND requestor_ID = x_requester_id;
SELECT count(*)
INTO x_tmp_count
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_id is not NULL
AND so_header_ID = x_header_ID;
SELECT count(*)
INTO x_tmp_count1
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND expected_receipt_date = x_exp_receipt_date
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_id is not NULL
AND expected_receipt_qty = 0
AND so_header_ID = x_header_ID
AND requestor_ID = X_REQUESTER_ID;
x_rcv_trans_status := 'RCV_RCPT_INSERT_FAILED';
type select_line_info_Cursor is ref cursor ;
Porcpt_LineInfo select_line_info_Cursor;
SELECT so_line_number,
expected_receipt_qty,
quantity_delivered,
ordered_qty,
primary_uom,
item_description,
currency_code,
unit_price,
req_number
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_header_id
AND requestor_ID = x_requester_id
ORDER BY so_line_number;
SELECT so_line_number,
expected_receipt_qty,
quantity_delivered,
ordered_qty,
primary_uom,
item_description,
currency_code,
unit_price,
req_number
FROM POR_CONFIRM_INTERNAL_RECEIPT_V
WHERE expected_receipt_date is not NULL
AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
AND so_header_ID = x_header_id
AND requestor_ID = x_requester_id
ORDER BY so_line_number;
SELECT po_line_number,
expected_receipt_qty,
quantity_delivered,
ordered_qty,
primary_uom,
item_description,
currency_code,
unit_price,
po_distribution_id
FROM POR_RCV_ALL_ITEMS_V1
WHERE expected_receipt_date is not NULL
AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
AND NVL(receipt_required_flag,'N') = 'Y'
AND destination_type_code = 'EXPENSE'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
AND po_header_ID = x_header_id
AND requestor_ID = x_requester_id
ORDER BY po_line_number;
SELECT po_line_number,
expected_receipt_qty,
quantity_delivered,
ordered_qty,
primary_uom,
item_description,
currency_code,
unit_price,
po_distribution_id
FROM POR_RCV_ALL_ITEMS_V1
WHERE expected_receipt_date is not NULL
AND trunc(expected_receipt_date) = trunc(x_exp_receipt_date)
AND NVL(receipt_required_flag,'N') = 'Y'
AND requestor_ID is not NULL
AND expected_receipt_qty > 0
AND po_header_ID = x_header_id
AND requestor_ID = x_requester_id
ORDER BY po_line_number;
type select_line_info_Cursor is ref cursor ;
Porcpt_LineInfo select_line_info_Cursor;
type select_line_info_Cursor is ref cursor ;
Porcpt_LineInfo select_line_info_Cursor;
SELECT rcv.po_distribution_id,
rcv.quantity_invoiced,
rcv.quantity_delivered
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE rcv.po_header_ID = x_po_header_ID
AND rcv.requestor_ID = x_requester_ID
AND ((rcv.expected_receipt_date is not null
AND trunc(rcv.expected_receipt_date + 1) <= trunc(SYSDATE))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty))
AND NVL(rcv.receipt_required_flag,'N') = 'Y'
AND rcv.destination_type_code = 'EXPENSE'
AND rcv.expected_receipt_qty > 0
ORDER BY po_distribution_id;
SELECT rcv.po_distribution_id,
rcv.quantity_invoiced,
rcv.quantity_delivered
FROM POR_RCV_ALL_ITEMS_V1 rcv
WHERE rcv.po_header_ID = x_po_header_ID
AND rcv.requestor_ID = x_requester_ID
AND ((rcv.expected_receipt_date is not null
AND trunc(rcv.expected_receipt_date + 1) <= trunc(SYSDATE))
OR EXISTS (SELECT 1 FROM ap_holds aph
WHERE aph.line_location_id = rcv.po_line_location_id
AND aph.hold_lookup_code in ('QTY REC', 'AMT REC')
AND aph.release_lookup_code IS NULL
AND rcv.quantity_invoiced > quantity_delivered
AND rcv.quantity_invoiced <= ordered_qty))
AND NVL(rcv.receipt_required_flag,'N') = 'Y'
AND rcv.expected_receipt_qty > 0
ORDER BY po_distribution_id;
UPDATE PO_DISTRIBUTIONS_ALL
SET WF_ITEM_KEY = conf_item_key,
invoiced_val_in_ntfn = x_qty_inv
WHERE po_distribution_id = x_dist_id;
procedure to insert the receipt records into the
receiving transaction interface table.
The Receiving Transaction Manager is then called in
'ON-LINE' mode to process the receipt records immediately.
If there are errors returned from the Receiving
Transaction Manager, the error status is set the
workflow item attribute for notifying the buyer and
requester of the error.
CHANGE HISTORY: SVASAMSE 13/05/2003 Created
===========================================================================*/
PROCEDURE Process_rcv_amt_billed(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funmode in varchar2,
result out NOCOPY varchar2)
IS
TYPE shipment_orders_cursor IS ref CURSOR;
x_insert_txns_status NUMBER;
SELECT rcv_interface_groups_s.nextval
INTO x_group_id
FROM sys.dual;
SELECT po_header_id,
po_line_location_id,
decode(SIGN(invoiced_val_in_ntfn-quantity_delivered),1,
(invoiced_val_in_ntfn-quantity_delivered),0)
expected_receipt_qty,
primary_uom,
item_id,
primary_uom_class,
to_organization_id,
po_distribution_id,
null,
null,
null
FROM POR_RCV_ALL_ITEMS_V1
WHERE po_header_ID = x_po_header_ID
AND wf_item_key = itemKey;
po_wf_debug_pkg.insert_debug(itemtype, itemkey, x_progress);
x_insert_txns_status := POR_RCV_ORD_SV.groupPoTransaction(
t_po_header_id,
t_line_location_id,
t_expected_receipt_qty,
t_ordered_uom,
SYSDATE,
t_item_id,
t_primary_uom_class,
t_org_id,
t_po_distribution_id,
x_group_id,
'WP4_CONFIRM',
t_Comments,
t_PackingSlip,
t_WayBillNum);
IF x_insert_txns_status = 0 THEN
x_RCV_txns_rc := por_rcv_ord_sv.process_transactions(X_group_id, 'WF');
update po_distributions
set wf_item_key = ''
where wf_item_key = itemKey;
x_rcv_trans_status := 'RCV_RCPT_INSERT_FAILED';
select sysdate
into x_sys_date
from dual;