[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_module := 'pos.plsql.pos_wc_create_update_pvt.'||p_api_name;
SELECT 'Y' INTO l_draft_exists
FROM RCV_HEADERS_INTERFACE RHI,
RCV_TRANSACTIONS_INTERFACE RTI
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
RTI.PO_HEADER_ID = p_po_header_id AND
RHI.processing_status_code = 'DRAFT' AND
RHI.transaction_type = 'DRAFT' AND
ROWNUM = 1;
SELECT
SUM(NVL(RSL.AMOUNT, RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
INTO x_po_approved
FROM RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
PO_LINE_LOCATIONS_ALL POLL
WHERE RSL.po_header_id = p_po_header_id
AND RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSH.APPROVAL_STATUS = 'APPROVED'
AND RSL.APPROVAL_STATUS in ('APPROVED');
SELECT SUM(
NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
*POLL.PRICE_OVERRIDE,
(POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0))))
INTO l_po_ordered
FROM PO_LINE_LOCATIONS_ALL POLL
WHERE POLL.PO_HEADER_ID = p_po_header_id;
SELECT SUM(NVL(RTI.AMOUNT, Round(RTI.QUANTITY*POLL.PRICE_OVERRIDE,POS_WC_VIEW_CANCEL_PVT.get_currency_precision(poll.po_header_id))))
INTO l_intf_request
FROM RCV_TRANSACTIONS_INTERFACE RTI,
RCV_HEADERS_INTERFACE RHI,
PO_LINE_LOCATIONS_ALL POLL
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID and
RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
RHI.HEADER_INTERFACE_ID = p_wc_id;
SELECT SUM(NVL(RSL.AMOUNT_SHIPPED, Round(RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE,POS_WC_VIEW_CANCEL_PVT.get_currency_precision(poll.po_header_id))))
INTO l_ship_request
FROM RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PO_LINE_LOCATIONS_ALL POLL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
RSH.SHIPMENT_HEADER_ID = p_wc_id;
SELECT RSL.amount,
RSL.quantity_shipped,
POLL.amount,
POLL.quantity,
POLL.matching_basis,
POLL.line_location_id
INTO l_amount_shipped,
l_quantity_shipped,
l_amount_ordered,
l_quantity_ordered,
l_matching_basis,
l_line_location_id
FROM RCV_SHIPMENT_LINES RSL,
PO_LINE_LOCATIONS_ALL POLL
WHERE RSL.shipment_line_id = p_wc_pay_item_id and
POLL.line_location_id = RSL.po_line_location_id;
SELECT RHI.request_date, RHI.processing_status_code, RHI.transaction_type
INTO l_request_date, l_header_processing_status, l_header_transaction_type
FROM RCV_HEADERS_INTERFACE RHI,
RCV_TRANSACTIONS_INTERFACE RTI
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
RTI.INTERFACE_TRANSACTION_ID = p_wc_pay_item_id;
SELECT RSH.request_date INTO l_request_date
FROM RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RSL.SHIPMENT_LINE_ID = p_wc_pay_item_id;
SELECT sysdate INTO l_request_date FROM DUAL;
SELECT sum(NVL(RTI.AMOUNT,RTI.QUANTITY))
INTO l_wc_prev_submitted_intf
FROM PO_LINE_LOCATIONS_ALL POLL,
RCV_TRANSACTIONS_INTERFACE RTI,
RCV_HEADERS_INTERFACE RHI
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
RTI.PROCESSING_STATUS_CODE = 'PENDING' AND
RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND
RTI.PO_LINE_LOCATION_ID = p_po_pay_item_id AND
RHI.REQUEST_DATE < l_request_date;
SELECT sum(NVL(RSL.AMOUNT_SHIPPED, RSL.QUANTITY_SHIPPED))
INTO l_wc_prev_submitted_ship
FROM PO_LINE_LOCATIONS_ALL POLL,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
(RSL.APPROVAL_STATUS is NULL OR
RSL.APPROVAL_STATUS in ('APPROVED', 'REJECTED', 'PROCESSED')) AND
RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED' AND
RSL.PO_LINE_LOCATION_ID = p_po_pay_item_id AND
RSH.REQUEST_DATE < l_request_date;
SELECT WF_ITEM_KEY, WF_ITEM_TYPE
INTO l_wf_item_key,l_wf_item_type
FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID = p_wc_header_id;
All the updateable fields should have been updated at this point.
*/
--1)Reset the Approval Status and Comment columns of the Header and the Lines
UPDATE RCV_SHIPMENT_HEADERS
SET APPROVAL_STATUS = null, COMMENTS = null
WHERE SHIPMENT_HEADER_ID = p_wc_header_id;
UPDATE RCV_SHIPMENT_LINES
SET APPROVAL_STATUS = null, COMMENTS = null
WHERE SHIPMENT_HEADER_ID = p_wc_header_id;
select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
into l_seq_for_item_key
from sys.dual;
PROCEDURE insert_corrections_into_rti
(
p_shipment_header_id IN NUMBER,
p_line_location_id IN NUMBER,
p_group_id IN NUMBER,
p_amount_correction IN NUMBER,
p_quantity_correction IN NUMBER,
p_requested_amount_correction IN NUMBER,
p_material_stored_correction IN NUMBER,
p_comments IN varchar2)
--x_return_status OUT nocopy VARCHAR2,
--x_return_msg OUT nocopy VARCHAR2
IS
Cursor get_wcr_info(l_shipment_header_id NUMBER, l_line_location_id NUMBER) is
SELECT rsl.po_line_location_id,
pll.unit_meas_lookup_code,
rsl.unit_of_measure unit_of_measure,
rsl.unit_of_measure primary_unit_of_measure,
rsl.unit_of_measure source_doc_unit_of_measure,
NVL(pll.promised_date, pll.need_by_date) promised_date,
rsl.to_organization_id ship_to_organization_id,
null quantity_ordered,
null amount_ordered,
NVL(pll.price_override, pl.unit_price) po_unit_price,
pll.match_option,
rsl.category_id,
rsl.item_description,
pl.po_line_id,
ph.currency_code,
ph.rate_type currency_conversion_type,
ph.segment1 document_num,
null po_distribution_id, --pod.po_distribution_id,
rsl.req_distribution_id,
rsl.requisition_line_id,
rsl.deliver_to_location_id deliver_to_location_id,
rsl.deliver_to_location_id location_id,
rsl.deliver_to_person_id,
null currency_conversion_date, --pod.rate_date currency_conversion_date,
null currency_conversion_rate, --pod.rate currency_conversion_rate,
rsl.destination_type_code destination_type_code,
rsl.destination_type_code destination_context,
null charge_account_id, --pod.code_combination_id ,
null destination_organization_id, --pod.destination_organization_id,
null subinventory, --pod.destination_subinventory ,
rsl.ship_to_location_id,
rsl.comments,
rsl.attribute_category attribute_category,
rsl.attribute1 attribute1,
rsl.attribute2 attribute2,
rsl.attribute3 attribute3,
rsl.attribute4 attribute4,
rsl.attribute5 attribute5,
rsl.attribute6 attribute6,
rsl.attribute7 attribute7,
rsl.attribute8 attribute8,
rsl.attribute9 attribute9,
rsl.attribute10 attribute10,
rsl.attribute11 attribute11,
rsl.attribute12 attribute12,
rsl.attribute13 attribute13,
rsl.attribute14 attribute14,
rsl.attribute15 attribute15,
NVL(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code,
rsl.shipment_line_id,
rsl.item_id,
rsl.item_revision,
rsh.vendor_id,
rsh.shipment_num,
rsh.freight_carrier_code,
rsh.bill_of_lading,
rsh.packing_slip,
rsh.shipped_date,
rsh.expected_receipt_date,
rsh.waybill_airbill_num ,
rsh.vendor_site_id,
rsl.to_organization_id,
rsl.routing_header_id,
rsl.vendor_item_num,
rsl.vendor_lot_num,
rsl.ussgl_transaction_code,
rsl.government_context,
pll.po_header_id,
ph.revision_num po_revision_num,
pl.line_num document_line_num,
pll.shipment_num document_shipment_line_num,
null document_distribution_num , --pod.distribution_num
pll.po_release_id,
pl.job_id,
ph.org_id,
rsl.amount_shipped amount,
rsl.quantity_shipped quantity,
rsl.quantity_shipped source_doc_quantity,
rsl.quantity_shipped primary_quantity,
rsl.quantity_shipped quantity_shipped,
rsl.amount_shipped amount_shipped,
rsl.requested_amount requested_amount,
rsl.material_stored_amount material_stored_amount,
pll.matching_basis
FROM
--po_distributions_all pod,
po_line_locations_all pll,
po_lines_all pl,
po_headers_all ph,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE
rsh.shipment_header_id = l_shipment_header_id
AND rsl.po_line_location_id = l_line_location_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rsl.po_header_id = ph.po_header_id
--and pod.po_header_id = ph.po_header_id
--and pod.line_location_id = pll.line_location_id
and rsl.po_line_id = pl.po_line_id
and rsl.po_line_location_id = pll.line_location_id
and rsh.receipt_source_code = 'VENDOR'
and pll.po_line_id = pl.po_line_id
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT');
select pod.po_distribution_id,
pod.rate_date currency_conversion_date,
pod.rate currency_conversion_rate,
pod.code_combination_id charge_account_id,
pod.destination_organization_id,
pod.destination_subinventory subinventory,
pod.distribution_num document_distribution_num,
pod.quantity_ordered,
pod.amount_ordered,
pod.destination_type_code destination_type_code,
pod.destination_type_code destination_context
from po_distributions_all pod
where pod.line_location_id = l_line_location_id;
l_insert_into_rti boolean := TRUE;
l_req_amount_inserted BOOLEAN := FALSE;
l_mat_stored_inserted BOOLEAN := FALSE;
l_progress := 'POS_WC_CREATE_UPDATE_PVT.insert_corrections_into_rti:01.';
select muom.uom_code
into l_uom_code
from mtl_units_of_measure muom
WHERE muom.unit_of_measure = wcr_line_info.unit_of_measure;
SELECT Count(*)
INTO l_max_dist
FROM po_distributions_all pod
where pod.line_location_id = wcr_line_info.po_line_location_id;
-- the following two attributes take care that the requested amount and material stored values get updated only for the
-- first distribution, and for the subsequent distributions they are entered as 0.
l_req_amount_inserted := FALSE;
l_mat_stored_inserted := FALSE;
-- opening the cursor for fetching distribution level information into the wcr record to be inserted into RTI
open get_dist_info(wcr_line_info.po_line_location_id);
-- looping through the distributions cursor to insert data in RTI
loop --{
l_progress := 'POS_WC_CREATE_UPDATE_PVT.insert_corrections_into_rti:02.';
select nvl(sum(amount),0)
into l_transacted_amount
from rcv_transactions
where po_distribution_id= wcr_line_info.po_distribution_id
and destination_type_code = 'RECEIVING';
select nvl(sum(amount),0)
into l_interface_amount
from rcv_transactions_interface
where po_distribution_id= wcr_line_info.po_distribution_id
and processing_status_code='PENDING'
and transaction_status_code = 'PENDING'
and transaction_type = 'RECEIVE';
-- last distribution for pay item, insert the l_remaining_amount_correction completely
wcr_line_info.amount := l_remaining_amount_correction;
IF(l_req_amount_inserted) THEN
wcr_line_info.requested_amount := null;
IF(l_mat_stored_inserted) THEN
wcr_line_info.material_stored_amount := null;
l_req_amount_inserted := TRUE;
l_mat_stored_inserted := TRUE;
l_insert_into_rti := TRUE;
-- not the last distribution for the pay item, check if we need to insert or not
IF(l_available_correct_amount > 0) THEN
-- this distribution is not yet completely emptied,
-- so we "need to insert" the correction depending on l_remaining_amount_correction and l_available_correct_amount
IF(l_carry_over_correction_amount > 0) THEN
-- this means that the entire correction amount can be inserted for this distribution
wcr_line_info.amount := l_remaining_amount_correction;
-- this means that the entire correction can not be inserted for this distribution alone
-- need to carry over the remaining correction to the next distribution
-- for this distribution, insert only the "l_available_correct_amount" as permitted
wcr_line_info.amount := 0 - l_available_correct_amount;
-- modify the l_remaining_amount_correction value after insertion
l_remaining_amount_correction := l_remaining_amount_correction + l_available_correct_amount;
IF(l_req_amount_inserted) THEN
wcr_line_info.requested_amount := null;
IF(l_mat_stored_inserted) THEN
wcr_line_info.material_stored_amount := null;
l_req_amount_inserted := TRUE;
l_mat_stored_inserted := TRUE;
l_insert_into_rti := TRUE;
-- l_available_amount < 0, so "no need to insert"
l_insert_into_rti := FALSE;
select nvl(sum(quantity),0)
into l_transacted_quantity
from rcv_transactions
where po_distribution_id= wcr_line_info.po_distribution_id
and destination_type_code = 'RECEIVING';
select nvl(sum(quantity),0)
into l_interface_quantity
from rcv_transactions_interface
where po_distribution_id= wcr_line_info.po_distribution_id
and processing_status_code='PENDING'
and transaction_status_code = 'PENDING'
and transaction_type = 'RECEIVE';
-- last distribution for pay item, insert the l_remaining_quantity_correct completely
wcr_line_info.quantity := l_remaining_quantity_correct;
l_insert_into_rti := TRUE;
-- not the last distribution for the pay item, check if we need to insert or not
IF(l_available_correct_quantity > 0) THEN
-- this distribution is not yet completely emptied,
-- so we "need to insert" the correction depending on l_remaining_quantity_correct and l_available_correct_quantity
IF(l_carry_over_correct_quantity > 0) THEN
-- this means that the entire correction amount can be inserted for this distribution
wcr_line_info.quantity := l_remaining_quantity_correct;
-- this means that the entire correction can not be inserted for this distribution alone
-- need to carry over the remaining correction to the next distribution
-- for this distribution, insert only the l_available_correct_quantity as permitted
wcr_line_info.quantity := 0 - l_available_correct_quantity;
-- modify the l_remaining_quantity_correct value after insertion
l_remaining_quantity_correct := l_remaining_quantity_correct + l_available_correct_quantity;
l_insert_into_rti := TRUE;
-- l_available_amount < 0, so "no need to insert"
l_insert_into_rti := FALSE;
If (l_insert_into_rti) then --{
FOR i IN 1..2 LOOP
select rcv_transactions_interface_s.nextval
into l_interface_id
from dual;
select transaction_id
INTO l_parent_transaction_id
from rcv_transactions
where shipment_header_id = l_shipment_header_id
AND po_line_location_id = l_line_location_id
AND transaction_type = l_transaction_type
AND po_distribution_id = wcr_line_info.po_distribution_id;
l_progress := 'before the actual insert';
rcv_asn_interface_trx_ins_pkg.insert_row
(l_row_id,
l_interface_id,--interface_id
l_group_id, --group_id
sysdate, --last_updated_date
fnd_global.user_id, --last_updated_by,
sysdate, --creation_date,
fnd_global.login_id, --created_by,
fnd_global.login_id, -- last_update_login,
NULL, --request_id,
null, --program_application_id,
null, --program_id,
null, --program_update_date,
'CORRECT', --transaction_type,
sysdate, --transaction_date,
'PENDING', --processing_status_code,
'IMMEDIATE', --processing_mode_code,
null, --processing_request_id,
'PENDING', --.transaction_status_code,
wcr_line_info.category_id,
wcr_line_info.quantity, --quantity
wcr_line_info.unit_of_measure,
'ISP', --.interface_source_code,
NULL, --.interface_source_line_id,
NULL, --.inv_transaction_id,
wcr_line_info.item_id,
wcr_line_info.item_description,
wcr_line_info.item_revision,
l_uom_code, --uom_code,
NULL, --employee_id,
NULL, --auto_transact_code,
l_shipment_header_id, --l_shipment_header_id
wcr_line_info.shipment_line_id,
wcr_line_info.ship_to_location_id,
wcr_line_info.primary_quantity,
wcr_line_info.primary_unit_of_measure,
'VENDOR', --.receipt_source_code,
wcr_line_info.vendor_id,
wcr_line_info.vendor_site_id,
NULL, --from_organization_id,
NULL, --from_subinventory,
wcr_line_info.to_organization_id,
NULL, --.intransit_owning_org_id,
wcr_line_info.routing_header_id,
NULL, --.routing_step_id,
'PO', --source_document_code,
l_parent_transaction_id, --.parent_transaction_id (for correction purpose),
wcr_line_info.po_header_id,
wcr_line_info.po_revision_num,
wcr_line_info.po_release_id,
wcr_line_info.po_line_id,
wcr_line_info.po_line_location_id,
wcr_line_info.po_unit_price,
wcr_line_info.currency_code,
wcr_line_info.currency_conversion_type,
wcr_line_info.currency_conversion_rate,
wcr_line_info.currency_conversion_date,
wcr_line_info.po_distribution_id,
wcr_line_info.requisition_line_id,
wcr_line_info.req_distribution_id,
wcr_line_info.charge_account_id,
NULL, --.substitute_unordered_code,
NULL, --.receipt_exception_flag,
NULL, --.accrual_status_code,
'NOT INSPECTED' ,--.inspection_status_code,
NULL, --.inspection_quality_code,
wcr_line_info.destination_type_code,
wcr_line_info.deliver_to_person_id,
wcr_line_info.location_id,
wcr_line_info.deliver_to_location_id,
NULL, --.subinventory,
NULL, --.locator_id,
NULL, --.wip_entity_id,
NULL, --.wip_line_id,
NULL, --.department_code,
NULL, --.wip_repetitive_schedule_id,
NULL, --.wip_operation_seq_num,
NULL, --.wip_resource_seq_num,
NULL, --.bom_resource_id,
wcr_line_info.shipment_num,
wcr_line_info.freight_carrier_code,
wcr_line_info.bill_of_lading,
wcr_line_info.packing_slip,
wcr_line_info.shipped_date,
wcr_line_info.expected_receipt_date,
NULL, --.actual_cost,
NULL, --.transfer_cost,
NULL, --.transportation_cost,
NULL, --.transportation_account_id,
NULL, --.num_of_containers,
wcr_line_info.waybill_airbill_num,
wcr_line_info.vendor_item_num,
wcr_line_info.vendor_lot_num,
NULL,--.rma_reference,
l_comments,
wcr_line_info.attribute_category,
wcr_line_info.attribute1,
wcr_line_info.attribute2,
wcr_line_info.attribute3,
wcr_line_info.attribute4,
wcr_line_info.attribute5,
wcr_line_info.attribute6,
wcr_line_info.attribute7,
wcr_line_info.attribute8,
wcr_line_info.attribute9,
wcr_line_info.attribute10,
wcr_line_info.attribute11,
wcr_line_info.attribute12,
wcr_line_info.attribute13,
wcr_line_info.attribute14,
wcr_line_info.attribute15,
NULL, --.ship_head_attribute_category,
NULL, --.ship_head_attribute1,
NULL, --.ship_head_attribute2,
NULL, --.ship_head_attribute3,
NULL, --.ship_head_attribute4,
NULL, --.ship_head_attribute5,
NULL, --.ship_head_attribute6,
NULL, --.ship_head_attribute7,
NULL, --.ship_head_attribute8,
NULL, --.ship_head_attribute9,
NULL, --.ship_head_attribute10,
NULL, --.ship_head_attribute11,
NULL, --.ship_head_attribute12,
NULL, --.ship_head_attribute13,
NULL, --.ship_head_attribute14,
NULL, --.ship_head_attribute15,
NULL, --.ship_line_attribute_category,
NULL, --.ship_line_attribute1,
NULL, --.ship_line_attribute2,
NULL, --.ship_line_attribute3,
NULL, --.ship_line_attribute4,
NULL, --.ship_line_attribute5,
NULL, --.ship_line_attribute6,
NULL, --.ship_line_attribute7,
NULL, --.ship_line_attribute8,
NULL, --.ship_line_attribute9,
NULL, --.ship_line_attribute10,
NULL, --.ship_line_attribute11,
NULL, --.ship_line_attribute12,
NULL, --.ship_line_attribute13,
NULL, --.ship_line_attribute14,
NULL, --.ship_line_attribute15,
wcr_line_info.ussgl_transaction_code,
wcr_line_info.government_context,
NULL, --.reason_id,
wcr_line_info.destination_context,
wcr_line_info.source_doc_quantity,
wcr_line_info.source_doc_unit_of_measure,
NULL, --.movement_id,
NULL, --l_header_interface_id, --.header_interface_id,
NULL, --.vendor_cum_shipped_qty,
NULL, --.item_num,
wcr_line_info.document_num,
wcr_line_info.document_line_num,
NULL, --.truck_num,
NULL, --.ship_to_location_code,
NULL, --.container_num,
NULL, --.substitute_item_num,
NULL, --.notice_unit_price,
NULL, --.item_category,
NULL, --.location_code,
NULL, --.vendor_name,
NULL, --.vendor_num,
NULL, --.vendor_site_code,
NULL, --.from_organization_code,
NULL, --.to_organization_code,
NULL, --.intransit_owning_org_code,
NULL, --.routing_code,
NULL, --.routing_step,
NULL, --.release_num,
wcr_line_info.document_shipment_line_num,
wcr_line_info.document_distribution_num,
NULL, --.deliver_to_person_name,
NULL, --.deliver_to_location_code,
NULL, --.use_mtl_lot,
NULL, --.use_mtl_serial,
NULL, --.LOCATOR,
NULL, --.reason_name,
NULL, --.validation_flag,
NULL, --.substitute_item_id,
NULL, --.quantity_shipped,
NULL, --.quantity_invoiced,
NULL, --.tax_name,
NULL, --.tax_amount,
NULL, --.req_num,
NULL, --.req_line_num,
NULL, --.req_distribution_num,
NULL, --.wip_entity_name,
NULL, --.wip_line_code,
NULL, --.resource_code,
NULL, --.shipment_line_status_code,
NULL, --.barcode_label,
NULL, --.country_of_origin_code,
NULL, --.from_locator_id, --WMS Change
NULL, --.qa_collection_id,
NULL, --.oe_order_header_id,
NULL, --.oe_order_line_id,
NULL, --.customer_id,
NULL, --.customer_site_id,
NULL, --.customer_item_num,
NULL, --.create_debit_memo_flag,
NULL, --.put_away_rule_id,
NULL, --.put_away_strategy_id,
NULL, --.lpn_id,
NULL, --.transfer_lpn_id,
NULL, --.cost_group_id,
NULL, --.mobile_txn,
NULL, --.mmtt_temp_id,
NULL, --.transfer_cost_group_id,
NULL, --.secondary_quantity,
NULL, --.secondary_unit_of_measure,
NULL, --.secondary_uom_code,
NULL, --.qc_grade,
NULL, --.oe_order_num,
NULL, --.oe_order_line_num,
NULL, --.customer_account_number,
NULL, --.customer_party_name,
NULL, --.source_transaction_num,
NULL, --.parent_source_transaction_num,
NULL, --.parent_interface_txn_id,
NULL, --.customer_item_id,
NULL, --.interface_available_qty,
NULL, --.interface_transaction_qty,
NULL, --.from_locator,
NULL, --.lpn_group_id,
NULL, --.order_transaction_id,
NULL, --.license_plate_number,
NULL, --.transfer_license_plate_number,
wcr_line_info.amount,
wcr_line_info.job_id,
NULL, --.project_id,
NULL, --.task_id,
NULL, --.asn_attach_id,
NULL, --.timecard_id,
NULL, --.timecard_ovn,
NULL, --.interface_available_amt,
NULL, --.interface_transaction_amt
wcr_line_info.org_id, --
wcr_line_info.matching_basis,
NULL, --wcr_line_info.amount_shipped, --amount_shipped
wcr_line_info.requested_amount,
wcr_line_info.material_stored_amount);
l_progress := 'record inserted';
LOG(FND_LOG.LEVEL_UNEXPECTED,'INSERT CORRECTION DATA IN RTI',
'Unexpected error at stage: '|| l_progress);
END insert_corrections_into_rti;
l_last_updated_by NUMBER;
SELECT DISTINCT po_line_location_id
FROM rcv_transactions rt
WHERE shipment_header_id = l_shipment_header_id
AND transaction_type = 'CORRECT'
ORDER BY po_line_location_id;
SELECT distinct group_id
FROM rcv_transactions rt
WHERE shipment_header_id = l_shipment_header_id
AND transaction_type = 'CORRECT'
AND EXISTS (SELECT '1'
FROM rcv_transactions rt2
WHERE rt2.transaction_type = 'DELIVER'
AND rt2.transaction_id = rt.parent_transaction_id)
AND po_line_location_id = l_line_location_id
ORDER BY group_id ASC;
SELECT Sum(rt.quantity),
Sum(rt.amount),
Sum(rt.requested_amount) ,
Sum(rt.material_stored_amount),
po_header_id,
po_line_location_id
INTO l_old_quantity1,
l_old_total_amount1,
l_old_requestedAmount1,
l_old_matstoredamount1,
l_po_header_id,
l_po_line_location_id
FROM rcv_transactions rt
WHERE shipment_header_id = l_shipment_header_id
AND po_line_location_id = l_line_location_id
AND transaction_type = 'DELIVER'
GROUP BY po_header_id,
po_line_location_id;
SELECT Sum(Nvl(rt.quantity, 0)),
Sum(Nvl(rt.amount, 0)),
Sum(Nvl(rt.requested_amount, 0)) ,
Sum(Nvl(rt.material_stored_amount, 0)),
Min(rt.last_updated_by),
Min(Nvl(rt.employee_id, -1)),
Min(rt.creation_date),
--Min(rt.created_by),
Min(rt.transaction_type),
Min(rt.transaction_date),
rt.po_header_id,
rt.po_line_id,
Min(rt.comments),
Min(pll.quantity),
Min(pll.amount),
DECODE( pll.matching_basis, 'AMOUNT' , Min(pll.AMOUNT), Min(pll.PRICE_OVERRIDE)),
pl.line_num ,
pll.description ,
pll.matching_basis ,
pll.shipment_num
INTO l_corrected_quantity,
l_corrected_total_amount,
l_corrected_requested_amount,
l_corrected_matstoredAmount,
l_last_updated_by,
l_employee_id,
l_creation_date,
--l_created_by,
l_transaction_type,
l_transaction_date,
l_po_header_id,
l_po_line_id,
l_comments,
l_ordered_quantity,
l_ordered_amount,
l_price,
l_line_num,
l_description,
l_matching_basis,
l_shipment_num
FROM rcv_transactions rt,
po_lines_all pl,
po_line_locations_all pll
WHERE rt.shipment_header_id = l_shipment_header_id
AND rt.transaction_type = 'CORRECT'
AND EXISTS (SELECT '1'
FROM rcv_transactions rt2
WHERE rt2.transaction_type = 'DELIVER'
AND rt2.transaction_id = rt.parent_transaction_id
)
AND rt.po_line_location_id = l_line_location_id
AND rt.group_id = l_groups(j)
AND pll.line_location_id = rt.po_line_location_id
AND pl.po_line_id = rt.po_line_id
AND pl.po_line_id = pll.po_line_id
GROUP BY rt.group_id,
rt.po_header_id,
rt.po_line_id,
rt.po_line_location_id,
pl.line_num,
pll.description,
pll.matching_basis,
pll.shipment_num;
IF(l_last_updated_by >0) THEN
-- bug - 9692573 - fetching the data only for the active employee record
SELECT Nvl(full_name, ' ')
INTO l_full_name
FROM per_employees_current_x
WHERE employee_id = (SELECT employee_id FROM fnd_user WHERE user_id = l_last_updated_by);