[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM PO_LINES
WHERE po_header_id = x_po_header_id
ORDER BY line_num;
SELECT *
FROM PO_LINE_LOCATIONS
WHERE po_line_id = x_po_line_id
AND SHIPMENT_TYPE NOT IN ('SCHEDULED','BLANKET') --Bug: 1773758 1992096
ORDER BY shipment_num;
SELECT *
FROM PO_DISTRIBUTIONS
WHERE line_location_id = x_line_location_id
AND distribution_type <> 'AGREEMENT' --bug 3338216: filter BPA dists
ORDER BY distribution_num;
PROCEDURE insert_header(
x_po_header_record IN PO_HEADERS%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_return_code OUT NOCOPY NUMBER,
x_copy_terms IN VARCHAR2 --
);
PROCEDURE insert_line(
x_po_line_record IN po_lines%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_return_code OUT NOCOPY NUMBER
);
PROCEDURE insert_shipment(
x_po_shipment_record IN po_line_locations%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_line_num IN po_online_report_text.line_num%TYPE,
x_accrue_on_receipt_flag IN VARCHAR2,
x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE, -- Bug 2761415
x_return_code OUT NOCOPY NUMBER,
p_is_complex_work_po IN BOOLEAN, --
p_orig_line_location_id IN NUMBER --
);
PROCEDURE insert_distribution(
x_po_distribution_record IN po_distributions%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_line_num IN po_online_report_text.line_num%TYPE,
x_shipment_num IN po_online_report_text.shipment_num%TYPE,
x_return_code OUT NOCOPY NUMBER
);
g_tax_attribute_update_code PO_HEADERS_ALL.tax_attribute_update_code%TYPE;
INSERT INTO PO_ONLINE_REPORT_TEXT (
online_report_id,
sequence,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
text_line,
message_type --
)
VALUES (
x_online_report_id,
x_sequence,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
x_text_line,
x_message_type --
);
DELETE PO_ONLINE_REPORT_TEXT
WHERE ONLINE_REPORT_ID = p_online_report_id
RETURNING MESSAGE_TYPE, TEXT_LINE
INTO x_message_type, x_message;
SELECT *
INTO x_po_header_record
FROM PO_HEADERS
WHERE po_header_id = x_from_po_header_id;
PROCEDURE insert_header(
x_po_header_record IN PO_HEADERS%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_return_code OUT NOCOPY NUMBER,
x_copy_terms IN VARCHAR2 --
) IS
l_progress VARCHAR2(4);
(p_log_head => g_module_prefix||'insert_header',
p_token => 'invoked',
p_message => 'header ID: '||x_po_header_record.po_header_id||
' online_report ID: '||x_online_report_id);
INSERT INTO PO_HEADERS (
acceptance_due_date,
acceptance_required_flag,
agent_id,
amount_limit,
approval_required_flag,
approved_date,
approved_flag,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
authorization_status,
bill_to_location_id,
blanket_total_amount,
cancel_flag,
closed_code,
closed_date,
comments,
confirming_order_flag,
created_by,
creation_date,
currency_code,
edi_processed_flag,
edi_processed_status,
enabled_flag,
end_date,
end_date_active,
firm_date,
firm_status_lookup_code,
fob_lookup_code,
freight_terms_lookup_code,
from_header_id,
from_type_lookup_code,
frozen_flag,
global_agreement_flag, -- Global Agreements (FP-I)
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute_category,
government_context,
interface_source_code,
last_updated_by,
last_update_date,
last_update_login,
min_release_amount,
mrc_rate,
mrc_rate_date,
mrc_rate_type,
note_to_authorizer,
note_to_receiver,
note_to_vendor,
org_id,
pay_on_code,
pcard_id,
po_header_id,
price_update_tolerance,
printed_date,
print_count,
-- Standard WHO column: program_application_id,
-- Standard WHO column: program_id,
-- Standard WHO column: program_update_date,
quotation_class_code,
quote_type_lookup_code,
quote_vendor_quote_number,
quote_warning_delay,
quote_warning_delay_unit,
rate,
rate_date,
rate_type,
reference_num,
reply_date,
reply_method_lookup_code,
-- Standard WHO column: request_id,
revised_date,
revision_num,
rfq_close_date,
segment1,
segment2,
segment3,
segment4,
segment5,
ship_to_location_id,
ship_via_lookup_code,
start_date,
start_date_active,
status_lookup_code,
summary_flag,
supply_agreement_flag,
terms_id,
type_lookup_code,
user_hold_flag,
vendor_contact_id,
vendor_id,
vendor_order_num,
vendor_site_id,
wf_item_key,
wf_item_type,
shipping_control, --
conterms_exist_flag, --
encumbrance_required_flag, --
document_creation_method --
,style_id --
,tax_attribute_update_code --
, created_language --
, last_updated_program --
-- Bug 5295179 START
, supplier_notif_method
, fax
, email_address
-- Bug 5295179 END
, Enable_All_Sites --
) VALUES (
x_po_header_record.acceptance_due_date,
x_po_header_record.acceptance_required_flag,
x_po_header_record.agent_id,
x_po_header_record.amount_limit,
x_po_header_record.approval_required_flag,
x_po_header_record.approved_date,
x_po_header_record.approved_flag,
x_po_header_record.attribute1,
x_po_header_record.attribute10,
x_po_header_record.attribute11,
x_po_header_record.attribute12,
x_po_header_record.attribute13,
x_po_header_record.attribute14,
x_po_header_record.attribute15,
x_po_header_record.attribute2,
x_po_header_record.attribute3,
x_po_header_record.attribute4,
x_po_header_record.attribute5,
x_po_header_record.attribute6,
x_po_header_record.attribute7,
x_po_header_record.attribute8,
x_po_header_record.attribute9,
x_po_header_record.attribute_category,
x_po_header_record.authorization_status,
x_po_header_record.bill_to_location_id,
x_po_header_record.blanket_total_amount,
x_po_header_record.cancel_flag,
x_po_header_record.closed_code,
x_po_header_record.closed_date,
x_po_header_record.comments,
x_po_header_record.confirming_order_flag,
x_po_header_record.created_by,
x_po_header_record.creation_date,
x_po_header_record.currency_code,
x_po_header_record.edi_processed_flag,
x_po_header_record.edi_processed_status,
x_po_header_record.enabled_flag,
decode(x_po_header_record.type_lookup_code, 'STANDARD', to_date(NULL),
x_po_header_record.end_date), -- Bug #2015328 BUG #5743193
x_po_header_record.end_date_active,
x_po_header_record.firm_date,
x_po_header_record.firm_status_lookup_code,
x_po_header_record.fob_lookup_code,
x_po_header_record.freight_terms_lookup_code,
x_po_header_record.from_header_id,
x_po_header_record.from_type_lookup_code,
x_po_header_record.frozen_flag,
x_po_header_record.global_agreement_flag, -- Global Agreements (FP-I)
x_po_header_record.global_attribute1,
x_po_header_record.global_attribute10,
x_po_header_record.global_attribute11,
x_po_header_record.global_attribute12,
x_po_header_record.global_attribute13,
x_po_header_record.global_attribute14,
x_po_header_record.global_attribute15,
x_po_header_record.global_attribute16,
x_po_header_record.global_attribute17,
x_po_header_record.global_attribute18,
x_po_header_record.global_attribute19,
x_po_header_record.global_attribute2,
x_po_header_record.global_attribute20,
x_po_header_record.global_attribute3,
x_po_header_record.global_attribute4,
x_po_header_record.global_attribute5,
x_po_header_record.global_attribute6,
x_po_header_record.global_attribute7,
x_po_header_record.global_attribute8,
x_po_header_record.global_attribute9,
x_po_header_record.global_attribute_category,
x_po_header_record.government_context,
x_po_header_record.interface_source_code,
x_po_header_record.last_updated_by,
x_po_header_record.last_update_date,
x_po_header_record.last_update_login,
decode(x_po_header_record.global_agreement_flag , 'Y' , null ,
x_po_header_record.min_release_amount) ,
x_po_header_record.mrc_rate,
x_po_header_record.mrc_rate_date,
x_po_header_record.mrc_rate_type,
x_po_header_record.note_to_authorizer,
x_po_header_record.note_to_receiver,
x_po_header_record.note_to_vendor,
x_po_header_record.org_id,
x_po_header_record.pay_on_code, --
x_po_header_record.pcard_id,
x_po_header_record.po_header_id,
x_po_header_record.price_update_tolerance,
x_po_header_record.printed_date,
x_po_header_record.print_count,
-- Standard WHO column: x_po_header_record.program_application_id,
-- Standard WHO column: x_po_header_record.program_id,
-- Standard WHO column: x_po_header_record.program_update_date,
x_po_header_record.quotation_class_code,
x_po_header_record.quote_type_lookup_code,
x_po_header_record.quote_vendor_quote_number,
x_po_header_record.quote_warning_delay,
x_po_header_record.quote_warning_delay_unit,
x_po_header_record.rate,
x_po_header_record.rate_date,
x_po_header_record.rate_type,
x_po_header_record.reference_num,
x_po_header_record.reply_date,
x_po_header_record.reply_method_lookup_code,
-- Standard WHO column: x_po_header_record.request_id,
x_po_header_record.revised_date,
x_po_header_record.revision_num,
x_po_header_record.rfq_close_date,
x_po_header_record.segment1,
x_po_header_record.segment2,
x_po_header_record.segment3,
x_po_header_record.segment4,
x_po_header_record.segment5,
x_po_header_record.ship_to_location_id,
x_po_header_record.ship_via_lookup_code,
decode(x_po_header_record.type_lookup_code, 'STANDARD' , to_date(NULL),
x_po_header_record.start_date), -- Bug #2015328 Bug #5743193
x_po_header_record.start_date_active,
x_po_header_record.status_lookup_code,
x_po_header_record.summary_flag,
x_po_header_record.supply_agreement_flag,
x_po_header_record.terms_id,
x_po_header_record.type_lookup_code,
x_po_header_record.user_hold_flag,
x_po_header_record.vendor_contact_id,
x_po_header_record.vendor_id,
x_po_header_record.vendor_order_num,
x_po_header_record.vendor_site_id,
x_po_header_record.wf_item_key,
x_po_header_record.wf_item_type,
x_po_header_record.shipping_control, --
decode(x_copy_terms, 'N', 'N', x_po_header_record.conterms_exist_flag),
--
x_po_header_record.encumbrance_required_flag, --
-- Bug 3648268 Use lookup code instead of hardcoded value
'COPY_DOCUMENT' --
,x_po_header_record.style_id --
,g_tax_attribute_update_code --
, x_po_header_record.created_language --
, 'COPY_DOC' --
-- Bug 5295179 START
, x_po_header_record.supplier_notif_method
, x_po_header_record.fax
, x_po_header_record.email_address
-- Bug 5295179 END
, decode(x_po_header_record.type_lookup_code,
'CONTRACT',x_po_header_record.Enable_All_Sites,
NULL) --
);
p_last_update_date => x_po_header_record.last_update_date,
p_last_updated_by => x_po_header_record.last_updated_by,
p_creation_date => x_po_header_record.creation_date,
p_created_by => x_po_header_record.created_by,
p_last_update_login => x_po_header_record.last_update_login);
l_org_assign_rec.last_update_date :=
x_po_header_record.last_update_date;
l_org_assign_rec.last_updated_by :=
x_po_header_record.last_updated_by;
l_org_assign_rec.last_update_login :=
x_po_header_record.last_update_login;
PO_GA_ORG_ASSIGN_PVT.insert_row
(p_init_msg_list => FND_API.g_false,
x_return_status => l_return_status,
p_org_assign_rec => l_org_assign_rec,
x_row_id => l_org_row_id);
copydoc_sql_error('insert_header', l_progress, sqlcode,
x_online_report_id,
x_sequence,
0, 0, 0);
(p_log_head => g_module_prefix||'insert_header',
p_progress => l_progress);
END insert_header;
PROCEDURE insert_line(
x_po_line_record IN po_lines%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_return_code OUT NOCOPY NUMBER
) IS
l_progress VARCHAR2(4);
(p_log_head => g_module_prefix||'insert_line',
p_token => 'invoked',
p_message => 'line ID: '||x_po_line_record.po_line_id||
' online_report ID: '||x_online_report_id);
INSERT INTO PO_LINES (
allow_price_override_flag,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
base_qty,
base_uom,
cancelled_by,
cancel_date,
cancel_flag,
cancel_reason,
capital_expense_flag,
category_id,
closed_by,
closed_code,
closed_date,
closed_flag,
closed_reason,
committed_amount,
-- contract_num, --
contract_id, --
created_by,
creation_date,
expiration_date,
firm_date,
firm_status_lookup_code,
from_header_id,
from_line_id,
from_line_location_id, --
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute_category,
government_context,
hazard_class_id,
item_description,
item_id,
item_revision,
last_updated_by,
last_update_date,
last_update_login,
line_num,
line_reference_num,
line_type_id,
list_price_per_unit,
market_price,
max_order_quantity,
min_order_quantity,
min_release_amount,
negotiated_by_preparer_flag,
note_to_vendor,
not_to_exceed_price,
org_id,
over_tolerance_error_flag,
po_header_id,
po_line_id,
price_break_lookup_code,
price_type_lookup_code,
project_id,
qc_grade,
qty_rcv_tolerance,
quantity,
quantity_committed,
reference_num,
secondary_qty,
secondary_uom,
task_id,
taxable_flag,
tax_code_id,
transaction_reason_code,
type_1099,
unit_meas_lookup_code,
unit_price,
base_unit_price, --
unordered_flag,
un_number_id,
user_hold_flag,
vendor_product_num,
oke_contract_header_id, --added oke columns
oke_contract_version_id, --added oke columns
secondary_unit_of_measure, -- 1548597
secondary_quantity, -- 1548597
preferred_grade, -- 1548597
job_id, -- Services FPJ
amount, -- Services FPJ
start_date, -- Services FPJ
contractor_first_name, -- Services FPJ
contractor_last_name, -- Services FPJ
order_type_lookup_code, --
purchase_basis, --
matching_basis, --
manual_price_change_flag --
,tax_attribute_update_code --
, retainage_rate --
, max_retainage_amount --
, progress_payment_rate --
, recoupment_rate --
, catalog_name --
, supplier_part_auxid --
, ip_category_id --
, last_updated_program --
) VALUES (
x_po_line_record.allow_price_override_flag,
x_po_line_record.attribute1,
x_po_line_record.attribute10,
x_po_line_record.attribute11,
x_po_line_record.attribute12,
x_po_line_record.attribute13,
x_po_line_record.attribute14,
x_po_line_record.attribute15,
x_po_line_record.attribute2,
x_po_line_record.attribute3,
x_po_line_record.attribute4,
x_po_line_record.attribute5,
x_po_line_record.attribute6,
x_po_line_record.attribute7,
x_po_line_record.attribute8,
x_po_line_record.attribute9,
x_po_line_record.attribute_category,
x_po_line_record.base_qty,
x_po_line_record.base_uom,
x_po_line_record.cancelled_by,
x_po_line_record.cancel_date,
x_po_line_record.cancel_flag,
x_po_line_record.cancel_reason,
x_po_line_record.capital_expense_flag,
x_po_line_record.category_id,
x_po_line_record.closed_by,
x_po_line_record.closed_code,
x_po_line_record.closed_date,
x_po_line_record.closed_flag,
x_po_line_record.closed_reason,
x_po_line_record.committed_amount,
-- x_po_line_record.contract_num, --
x_po_line_record.contract_id, --
x_po_line_record.created_by,
x_po_line_record.creation_date,
x_po_line_record.expiration_date,
x_po_line_record.firm_date,
x_po_line_record.firm_status_lookup_code,
x_po_line_record.from_header_id,
x_po_line_record.from_line_id,
x_po_line_record.from_line_location_id, --
x_po_line_record.global_attribute1,
x_po_line_record.global_attribute10,
x_po_line_record.global_attribute11,
x_po_line_record.global_attribute12,
x_po_line_record.global_attribute13,
x_po_line_record.global_attribute14,
x_po_line_record.global_attribute15,
x_po_line_record.global_attribute16,
x_po_line_record.global_attribute17,
x_po_line_record.global_attribute18,
x_po_line_record.global_attribute19,
x_po_line_record.global_attribute2,
x_po_line_record.global_attribute20,
x_po_line_record.global_attribute3,
x_po_line_record.global_attribute4,
x_po_line_record.global_attribute5,
x_po_line_record.global_attribute6,
x_po_line_record.global_attribute7,
x_po_line_record.global_attribute8,
x_po_line_record.global_attribute9,
x_po_line_record.global_attribute_category,
x_po_line_record.government_context,
x_po_line_record.hazard_class_id,
x_po_line_record.item_description,
x_po_line_record.item_id,
x_po_line_record.item_revision,
x_po_line_record.last_updated_by,
x_po_line_record.last_update_date,
x_po_line_record.last_update_login,
x_po_line_record.line_num,
x_po_line_record.line_reference_num,
x_po_line_record.line_type_id,
x_po_line_record.list_price_per_unit,
x_po_line_record.market_price,
x_po_line_record.max_order_quantity,
x_po_line_record.min_order_quantity,
x_po_line_record.min_release_amount,
x_po_line_record.negotiated_by_preparer_flag,
x_po_line_record.note_to_vendor,
x_po_line_record.not_to_exceed_price,
x_po_line_record.org_id,
x_po_line_record.over_tolerance_error_flag,
x_po_line_record.po_header_id,
x_po_line_record.po_line_id,
x_po_line_record.price_break_lookup_code,
x_po_line_record.price_type_lookup_code,
x_po_line_record.project_id,
x_po_line_record.qc_grade,
x_po_line_record.qty_rcv_tolerance,
x_po_line_record.quantity,
x_po_line_record.quantity_committed,
x_po_line_record.reference_num,
x_po_line_record.secondary_qty,
x_po_line_record.secondary_uom,
x_po_line_record.task_id,
x_po_line_record.taxable_flag,
x_po_line_record.tax_code_id,
x_po_line_record.transaction_reason_code,
x_po_line_record.type_1099,
x_po_line_record.unit_meas_lookup_code,
x_po_line_record.unit_price,
x_po_line_record.base_unit_price, --
x_po_line_record.unordered_flag,
x_po_line_record.un_number_id,
x_po_line_record.user_hold_flag,
x_po_line_record.vendor_product_num,
x_po_line_record.oke_contract_header_id, -- added oke columns
x_po_line_record.oke_contract_version_id, -- added oke columns
x_po_line_record.secondary_unit_of_measure, -- 1548597
x_po_line_record.secondary_quantity, -- 1548597
x_po_line_record.preferred_grade, -- 1548597
x_po_line_record.job_id, -- Services FPJ
x_po_line_record.amount, -- Services FPJ
x_po_line_record.start_date, -- Services FPJ
x_po_line_record.contractor_first_name, -- Services FPJ
x_po_line_record.contractor_last_name, -- Services FPJ
l_order_type_lookup_code, --
l_purchase_basis, --
l_matching_basis, --
x_po_line_record.manual_price_change_flag --
,g_tax_attribute_update_code --
, x_po_line_record.retainage_rate --
, x_po_line_record.max_retainage_amount --
, x_po_line_record.progress_payment_rate --
, x_po_line_record.recoupment_rate --
, x_po_line_record.catalog_name --
, x_po_line_record.supplier_part_auxid --
, x_po_line_record.ip_category_id --
, 'COPY_DOC' --
);
copydoc_sql_error('insert_line', l_progress, sqlcode,
x_online_report_id,
x_sequence,
x_po_line_record.line_num, 0, 0);
(p_log_head => g_module_prefix||'insert_line',
p_progress => l_progress);
END insert_line;
PROCEDURE insert_shipment(
x_po_shipment_record IN po_line_locations%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_line_num IN po_online_report_text.line_num%TYPE,
x_accrue_on_receipt_flag IN VARCHAR2,
x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE, -- Bug 2761415
x_return_code OUT NOCOPY NUMBER,
p_is_complex_work_po IN BOOLEAN, --
p_orig_line_location_id IN NUMBER --
) IS
l_progress VARCHAR2(4);
(p_log_head => g_module_prefix||'insert_shipment',
p_token => 'invoked',
p_message => 'ship ID: '||x_po_shipment_record.line_location_id||
' inv_org ID: '||x_inv_org_id||' accrue_on_receipt_flag: '||
x_accrue_on_receipt_flag||' online_report ID: '||
x_online_report_id);
select item_id, unit_meas_lookup_code,
order_type_lookup_code, matching_basis
into l_item_id, l_unit_meas_lookup_code,
l_line_value_basis, l_line_matching_basis
from po_lines_all
where po_line_id=x_po_shipment_record.po_line_id;
SELECT ph.quote_type_lookup_code
INTO l_quote_type_code
FROM po_headers ph
WHERE ph.po_header_id = x_po_shipment_record.po_header_id;
select inventory_organization_id
into l_to_ship_to_organization_id
from hr_locations_v
where location_id = l_to_ship_to_location_id
and ship_to_site_flag = 'Y';
select count(*) into l_count from
mtl_system_items where
inventory_item_id=l_item_id and
organization_id=l_to_ship_to_organization_id ;
select count(*) into l_count from
mtl_system_items where
inventory_item_id=l_item_id and
organization_id=x_inv_org_id;
select vendor_site_id into l_vendor_site_id from
po_headers where
po_header_id=x_po_shipment_record.po_header_id;
select ship_to_location_id into l_to_ship_to_location_id
from po_vendor_sites where
vendor_site_id=l_vendor_site_id;
select fsp.ship_to_location_id
into l_to_ship_to_location_id
from financials_system_parameters fsp;
SELECT inventory_organization_id
INTO l_to_ship_to_organization_id
FROM hr_locations_v
WHERE location_id = l_to_ship_to_location_id
AND ship_to_site_flag = 'Y';
select count(*) into l_count from
mtl_system_items where
inventory_item_id=l_item_id and
organization_id=l_to_ship_to_organization_id ;
select vendor_id, vendor_site_id
into l_vendor_id, l_vendor_site_id
from po_headers
where po_header_id = x_po_shipment_record.po_header_id;
INSERT INTO PO_LINE_LOCATIONS (
accrue_on_receipt_flag,
allow_substitute_receipts_flag,
approved_date,
approved_flag,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
calculate_tax_flag,
cancelled_by,
cancel_date,
cancel_flag,
cancel_reason,
closed_by,
closed_code,
closed_date,
closed_flag,
closed_reason,
country_of_origin_code,
created_by,
creation_date,
days_early_receipt_allowed,
days_late_receipt_allowed,
encumbered_date,
encumbered_flag,
encumber_now,
end_date,
enforce_ship_to_location_code,
estimated_tax_amount,
firm_date,
firm_status_lookup_code,
fob_lookup_code,
freight_terms_lookup_code,
from_header_id,
from_line_id,
from_line_location_id,
global_attribute1,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute2,
global_attribute20,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute_category,
government_context,
inspection_required_flag,
invoice_close_tolerance,
last_accept_date,
last_updated_by,
last_update_date,
last_update_login,
lead_time,
lead_time_unit,
line_location_id,
match_option,
need_by_date,
org_id,
po_header_id,
po_line_id,
po_release_id,
price_discount,
price_override,
-- Standard WHO column: program_application_id
-- Standard WHO column: program_id
-- Standard WHO column: program_update_date
promised_date,
qty_rcv_exception_code,
qty_rcv_tolerance,
quantity,
quantity_accepted,
quantity_billed,
quantity_cancelled,
quantity_received,
quantity_rejected,
quantity_shipped,
receipt_days_exception_code,
receipt_required_flag,
receive_close_tolerance,
receiving_routing_id,
-- Standard WHO column: request_id
shipment_num,
shipment_type,
ship_to_location_id,
ship_to_organization_id,
ship_via_lookup_code,
source_shipment_id,
start_date,
taxable_flag,
tax_code_id,
tax_name, --
tax_user_override_flag,
terms_id,
-- unencumbered_quantity, --
unit_meas_lookup_code,
unit_of_measure_class,
--togeorge 10/05/2000
--added note to receiver
note_to_receiver,
-- start of 1548597
secondary_unit_of_measure,
secondary_quantity,
preferred_grade,
secondary_quantity_received,
secondary_quantity_accepted,
secondary_quantity_rejected,
secondary_quantity_cancelled,
-- end of 1548597
consigned_flag, -- CONSIGNED FPI
amount, -- Services FPJ
amount_accepted, -- Services FPJ
amount_billed, -- Services FPJ
amount_cancelled, -- Services FPJ
amount_received, -- Services FPJ
amount_rejected, -- Services FPJ
transaction_flow_header_id, --< Shared Proc FPJ >
manual_price_change_flag --
--
, shipment_closed_date
, closed_for_receiving_date
, closed_for_invoice_date
--
,outsourced_assembly --
,tax_attribute_update_code --
, value_basis --
, matching_basis --
, payment_type --
, description --
, work_approver_id --
, original_shipment_id --
) VALUES (
l_accrue_on_receipt_flag, -- CONSIGNED FPI
-- x_accrue_on_receipt_flag, -- Bug: 1402128
-- x_po_shipment_record.accrue_on_receipt_flag,
x_po_shipment_record.allow_substitute_receipts_flag,
x_po_shipment_record.approved_date,
x_po_shipment_record.approved_flag,
x_po_shipment_record.attribute1,
x_po_shipment_record.attribute10,
x_po_shipment_record.attribute11,
x_po_shipment_record.attribute12,
x_po_shipment_record.attribute13,
x_po_shipment_record.attribute14,
x_po_shipment_record.attribute15,
x_po_shipment_record.attribute2,
x_po_shipment_record.attribute3,
x_po_shipment_record.attribute4,
x_po_shipment_record.attribute5,
x_po_shipment_record.attribute6,
x_po_shipment_record.attribute7,
x_po_shipment_record.attribute8,
x_po_shipment_record.attribute9,
x_po_shipment_record.attribute_category,
x_po_shipment_record.calculate_tax_flag,
x_po_shipment_record.cancelled_by,
x_po_shipment_record.cancel_date,
x_po_shipment_record.cancel_flag,
x_po_shipment_record.cancel_reason,
x_po_shipment_record.closed_by,
--x_po_shipment_record.closed_code,
l_closed_code, -- CONSIGNED FPI
x_po_shipment_record.closed_date,
x_po_shipment_record.closed_flag,
--x_po_shipment_record.closed_reason,
l_closed_reason, -- CONSIGNED FPI
x_po_shipment_record.country_of_origin_code,
x_po_shipment_record.created_by,
x_po_shipment_record.creation_date,
x_po_shipment_record.days_early_receipt_allowed,
x_po_shipment_record.days_late_receipt_allowed,
x_po_shipment_record.encumbered_date,
x_po_shipment_record.encumbered_flag,
x_po_shipment_record.encumber_now,
x_po_shipment_record.end_date,
x_po_shipment_record.enforce_ship_to_location_code,
x_po_shipment_record.estimated_tax_amount,
x_po_shipment_record.firm_date,
x_po_shipment_record.firm_status_lookup_code,
x_po_shipment_record.fob_lookup_code,
x_po_shipment_record.freight_terms_lookup_code,
x_po_shipment_record.from_header_id,
x_po_shipment_record.from_line_id,
x_po_shipment_record.from_line_location_id,
x_po_shipment_record.global_attribute1,
x_po_shipment_record.global_attribute10,
x_po_shipment_record.global_attribute11,
x_po_shipment_record.global_attribute12,
x_po_shipment_record.global_attribute13,
x_po_shipment_record.global_attribute14,
x_po_shipment_record.global_attribute15,
x_po_shipment_record.global_attribute16,
x_po_shipment_record.global_attribute17,
x_po_shipment_record.global_attribute18,
x_po_shipment_record.global_attribute19,
x_po_shipment_record.global_attribute2,
x_po_shipment_record.global_attribute20,
x_po_shipment_record.global_attribute3,
x_po_shipment_record.global_attribute4,
x_po_shipment_record.global_attribute5,
x_po_shipment_record.global_attribute6,
x_po_shipment_record.global_attribute7,
x_po_shipment_record.global_attribute8,
x_po_shipment_record.global_attribute9,
x_po_shipment_record.global_attribute_category,
x_po_shipment_record.government_context,
--x_po_shipment_record.inspection_required_flag,
l_inspection_required_flag, -- CONSIGNED FPI
--x_po_shipment_record.invoice_close_tolerance, -- Bug 2761415
l_invoice_close_tolerance, -- CONSIGNED FPI
x_po_shipment_record.last_accept_date,
x_po_shipment_record.last_updated_by,
x_po_shipment_record.last_update_date,
x_po_shipment_record.last_update_login,
x_po_shipment_record.lead_time,
x_po_shipment_record.lead_time_unit,
x_po_shipment_record.line_location_id,
--x_po_shipment_record.match_option,
l_match_option, -- CONSIGNED FPI
x_po_shipment_record.need_by_date,
x_po_shipment_record.org_id,
x_po_shipment_record.po_header_id,
x_po_shipment_record.po_line_id,
x_po_shipment_record.po_release_id,
x_po_shipment_record.price_discount,
x_po_shipment_record.price_override,
-- Standard WHO column: x_po_shipment_record.program_application_id
-- Standard WHO column: x_po_shipment_record.program_id
-- Standard WHO column: x_po_shipment_record.program_update_date
x_po_shipment_record.promised_date,
x_po_shipment_record.qty_rcv_exception_code,
x_po_shipment_record.qty_rcv_tolerance,
l_quantity, --bug#2717053
x_po_shipment_record.quantity_accepted,
x_po_shipment_record.quantity_billed,
x_po_shipment_record.quantity_cancelled,
x_po_shipment_record.quantity_received,
x_po_shipment_record.quantity_rejected,
x_po_shipment_record.quantity_shipped,
x_po_shipment_record.receipt_days_exception_code,
--x_po_shipment_record.receipt_required_flag,
l_receipt_required_flag, -- CONSIGNED FPI
x_po_shipment_record.receive_close_tolerance, -- Bug 2761415
x_po_shipment_record.receiving_routing_id,
-- Standard WHO column: x_po_shipment_record.request_id
x_po_shipment_record.shipment_num,
x_po_shipment_record.shipment_type,
l_to_ship_to_location_id, --bug#2717053
l_to_ship_to_organization_id, --bug#2717053
x_po_shipment_record.ship_via_lookup_code,
x_po_shipment_record.source_shipment_id,
x_po_shipment_record.start_date,
x_po_shipment_record.taxable_flag,
x_po_shipment_record.tax_code_id,
x_po_shipment_record.tax_name, --
x_po_shipment_record.tax_user_override_flag,
x_po_shipment_record.terms_id,
--
-- x_po_shipment_record.unencumbered_quantity,
x_po_shipment_record.unit_meas_lookup_code,
x_po_shipment_record.unit_of_measure_class,
--togeorge 10/05/2000
--added note to receiver
x_po_shipment_record.note_to_receiver,
x_secondary_unit_of_measure,
x_secondary_quantity,
x_po_shipment_record.preferred_grade,
decode(x_secondary_unit_of_measure,NULL,NULL,0),
decode(x_secondary_unit_of_measure,NULL,NULL,0),
decode(x_secondary_unit_of_measure,NULL,NULL,0),
decode(x_secondary_unit_of_measure,NULL,NULL,0),
--
l_consigned_flag , -- CONSIGNED FPI
x_po_shipment_record.amount, -- Services FPJ (Except for ordered amt all other amts are 0)
0, -- Services FPJ
0, -- Services FPJ
0, -- Services FPJ
0, -- Services FPJ
0, -- Services FPJ
x_po_shipment_record.transaction_flow_header_id, --< Shared Proc FPJ >
x_po_shipment_record.manual_price_change_flag --
--
, decode(l_closed_code,'CLOSED',
nvl(x_po_shipment_record.closed_date,sysdate), null) ---shipment closed date
, decode(l_closed_code,'CLOSED',nvl(x_po_shipment_record.closed_date,sysdate),
'CLOSED FOR RECEIVING',sysdate,null) --closed for receiving date
, decode(l_closed_code,'CLOSED',nvl(x_po_shipment_record.closed_date,sysdate),
'CLOSED FOR INVOICE',sysdate,null) ---closed for invoice date
--
, x_po_shipment_record.outsourced_assembly --
,g_tax_attribute_update_code --
-- bug 4958300: added nvl for basis columns
, nvl(x_po_shipment_record.value_basis, l_line_value_basis)
, nvl(x_po_shipment_record.matching_basis, l_line_matching_basis)
, x_po_shipment_record.payment_type
, x_po_shipment_record.description
, x_po_shipment_record.work_approver_id
--
, NVL2(g_tax_attribute_update_code, p_orig_line_location_id, null) --
);
copydoc_sql_error('insert_shipment', l_progress, sqlcode,
x_online_report_id,
x_sequence,
x_line_num, x_po_shipment_record.shipment_num, 0);
(p_log_head => g_module_prefix||'insert_shipment',
p_progress => l_progress);
END insert_shipment;
PROCEDURE insert_distribution(
x_po_distribution_record IN po_distributions%ROWTYPE,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_line_num IN po_online_report_text.line_num%TYPE,
x_shipment_num IN po_online_report_text.shipment_num%TYPE,
x_return_code OUT NOCOPY NUMBER
) IS
l_progress VARCHAR2(4);
(p_log_head => g_module_prefix||'insert_distribution',
p_token => 'invoked',
p_message => 'dist ID: '||
x_po_distribution_record.po_distribution_id||
' online_report ID: '||x_online_report_id);
SAVEPOINT insert_distribution_savepoint;
INSERT INTO PO_DISTRIBUTIONS (
accrual_account_id,
accrued_flag,
accrue_on_receipt_flag,
amount_billed,
attribute1,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute_category,
award_id,
-- base_amount_billed, -- June 07, 1999, bgu
bom_resource_id,
budget_account_id,
code_combination_id,
created_by,
creation_date,
deliver_to_location_id,
deliver_to_person_id,
destination_context,
destination_organization_id,
destination_subinventory,
destination_type_code,
distribution_num,
-- encumbered_amount, -- Oct. 06, 1999, bgu
encumbered_flag,
end_item_unit_number,
expenditure_item_date,
expenditure_organization_id,
expenditure_type,
failed_funds_lookup_code,
-- gl_cancelled_date,
gl_closed_date,
gl_encumbered_date,
gl_encumbered_period_name,
government_context,
kanban_card_id,
last_updated_by,
last_update_date,
last_update_login,
line_location_id,
--
-- mrc_encumbered_amount,
mrc_rate,
mrc_rate_date,
--
-- mrc_unencumbered_amount,
nonrecoverable_tax,
org_id,
po_distribution_id,
po_header_id,
po_line_id,
po_release_id,
prevent_encumbrance_flag,
-- Standard WHO column: program_application_id
-- Standard WHO column: program_id
-- Standard WHO column: program_update_date
project_accounting_context,
project_id,
quantity_billed,
quantity_cancelled,
quantity_delivered,
quantity_ordered,
rate,
rate_date,
recoverable_tax,
recovery_rate,
-- Standard WHO column: request_id
req_distribution_id,
req_header_reference_num,
req_line_reference_num,
set_of_books_id,
source_distribution_id,
task_id,
tax_recovery_override_flag,
--
-- unencumbered_amount,
-- unencumbered_quantity,
variance_account_id,
wip_entity_id,
wip_line_id,
wip_operation_seq_num,
wip_repetitive_schedule_id,
wip_resource_seq_num,
--togeorge 10/05/2000
--added oke columns
oke_contract_line_id,
oke_contract_deliverable_id,
amount_ordered, -- Services FPJ
amount_delivered, -- Services FPJ
amount_cancelled, -- Services FPJ
distribution_type, --
amount_to_encumber, -- Bug 3309589
dest_charge_account_id, --< Shared Proc FPJ >
dest_variance_account_id --< Shared Proc FPJ >
,tax_attribute_update_code --
) VALUES (
x_po_distribution_record.accrual_account_id,
-- x_po_distribution_record.accrued_flag, 2861250
null,
x_po_distribution_record.accrue_on_receipt_flag,
x_po_distribution_record.amount_billed,
x_po_distribution_record.attribute1,
x_po_distribution_record.attribute10,
x_po_distribution_record.attribute11,
x_po_distribution_record.attribute12,
x_po_distribution_record.attribute13,
x_po_distribution_record.attribute14,
x_po_distribution_record.attribute15,
x_po_distribution_record.attribute2,
x_po_distribution_record.attribute3,
x_po_distribution_record.attribute4,
x_po_distribution_record.attribute5,
x_po_distribution_record.attribute6,
x_po_distribution_record.attribute7,
x_po_distribution_record.attribute8,
x_po_distribution_record.attribute9,
x_po_distribution_record.attribute_category,
l_award_id, --
-- x_po_distribution_record.base_amount_billed,
x_po_distribution_record.bom_resource_id,
x_po_distribution_record.budget_account_id,
x_po_distribution_record.code_combination_id,
x_po_distribution_record.created_by,
x_po_distribution_record.creation_date,
x_po_distribution_record.deliver_to_location_id,
x_po_distribution_record.deliver_to_person_id,
x_po_distribution_record.destination_context,
x_po_distribution_record.destination_organization_id,
x_po_distribution_record.destination_subinventory,
x_po_distribution_record.destination_type_code,
x_po_distribution_record.distribution_num,
-- x_po_distribution_record.encumbered_amount,
-- x_po_distribution_record.encumbered_flag,
'N',
x_po_distribution_record.end_item_unit_number,
x_po_distribution_record.expenditure_item_date,
x_po_distribution_record.expenditure_organization_id,
x_po_distribution_record.expenditure_type,
x_po_distribution_record.failed_funds_lookup_code,
-- x_po_distribution_record.gl_cancelled_date,
x_po_distribution_record.gl_closed_date,
x_po_distribution_record.gl_encumbered_date,
x_po_distribution_record.gl_encumbered_period_name,
x_po_distribution_record.government_context,
x_po_distribution_record.kanban_card_id,
x_po_distribution_record.last_updated_by,
x_po_distribution_record.last_update_date,
x_po_distribution_record.last_update_login,
x_po_distribution_record.line_location_id,
--
-- x_po_distribution_record.mrc_encumbered_amount,
x_po_distribution_record.mrc_rate,
x_po_distribution_record.mrc_rate_date,
--
-- x_po_distribution_record.mrc_unencumbered_amount,
x_po_distribution_record.nonrecoverable_tax,
x_po_distribution_record.org_id,
x_po_distribution_record.po_distribution_id,
x_po_distribution_record.po_header_id,
x_po_distribution_record.po_line_id,
x_po_distribution_record.po_release_id,
x_po_distribution_record.prevent_encumbrance_flag,
-- Standard WHO column: x_po_distribution_record.program_application_id
-- Standard WHO column: x_po_distribution_record.program_id
-- Standard WHO column: x_po_distribution_record.program_update_date
x_po_distribution_record.project_accounting_context,
x_po_distribution_record.project_id,
x_po_distribution_record.quantity_billed,
x_po_distribution_record.quantity_cancelled,
x_po_distribution_record.quantity_delivered,
x_po_distribution_record.quantity_ordered,
x_po_distribution_record.rate,
x_po_distribution_record.rate_date,
x_po_distribution_record.recoverable_tax,
x_po_distribution_record.recovery_rate,
-- Standard WHO column: x_po_distribution_record.request_id
x_po_distribution_record.req_distribution_id,
x_po_distribution_record.req_header_reference_num,
x_po_distribution_record.req_line_reference_num,
x_po_distribution_record.set_of_books_id,
x_po_distribution_record.source_distribution_id,
x_po_distribution_record.task_id,
x_po_distribution_record.tax_recovery_override_flag,
--
-- x_po_distribution_record.unencumbered_amount,
-- x_po_distribution_record.unencumbered_quantity,
x_po_distribution_record.variance_account_id,
x_po_distribution_record.wip_entity_id,
x_po_distribution_record.wip_line_id,
x_po_distribution_record.wip_operation_seq_num,
x_po_distribution_record.wip_repetitive_schedule_id,
x_po_distribution_record.wip_resource_seq_num,
--togeorge 10/05/2000
--added oke columns
x_po_distribution_record.oke_contract_line_id,
x_po_distribution_record.oke_contract_deliverable_id,
x_po_distribution_record.amount_ordered, -- Services FPJ
0, -- Services FPJ
0, -- Services FPJ
x_po_distribution_record.distribution_type, --
x_po_distribution_record.amount_to_encumber, -- Bug 3309589
x_po_distribution_record.dest_charge_account_id, --< Shared Proc FPJ >
x_po_distribution_record.dest_variance_account_id --< Shared Proc FPJ >
,NVL2(g_tax_attribute_update_code,'CREATE',NULL) --
);
ROLLBACK TO SAVEPOINT insert_distribution_savepoint;
(p_log_head => g_module_prefix||'insert_distribution',
p_token => l_progress,
p_message => 'FND_API.g_exc_error exception caught.');
(p_log_head => g_module_prefix||'insert_distribution',
p_progress => l_progress);
ROLLBACK TO SAVEPOINT insert_distribution_savepoint; --
copydoc_sql_error('insert_distribution', l_progress, sqlcode,
x_online_report_id,
x_sequence,
x_line_num, x_shipment_num, x_po_distribution_record.distribution_num);
(p_log_head => g_module_prefix||'insert_distribution',
p_progress => l_progress);
END insert_distribution;
** Insert header with old and new info to the new header
** Copy attachment if necessary
***************************************************************/
PROCEDURE process_header(
x_action_code IN VARCHAR2,
x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
x_to_global_flag IN PO_HEADERS_ALL.global_agreement_flag%TYPE, -- GA
x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
x_from_po_header_id IN po_headers.po_header_id%TYPE,
x_to_segment1 IN po_headers.segment1%TYPE,
x_agent_id IN po_headers.agent_id%TYPE,
x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
x_copy_attachments IN BOOLEAN,
x_online_report_id IN po_online_report_text.online_report_id%TYPE,
x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_return_code OUT NOCOPY NUMBER,
x_copy_terms IN VARCHAR2
) IS
l_progress VARCHAR2(4);
insert_header(x_po_header_record,
x_online_report_id,
x_sequence,
x_internal_return_code,
x_copy_terms);
SELECT conterms_exist_flag
,org_id
,agent_id
,vendor_id
,vendor_site_id
,vendor_contact_id
INTO l_conterms_exist_flag
,l_internal_party_id
,l_internal_contact_id
,l_external_party_id
,l_external_party_site_id
,l_external_contact_id
FROM po_headers
WHERE po_header_id = x_from_po_header_id;
UPDATE po_headers_all
SET conterms_exist_flag = 'N'
WHERE po_header_id = x_po_header_record.po_header_id;
insert_line(x_po_line_record,
x_online_report_id,
x_sequence,
x_internal_return_code);
* if condition below , a null will be passed to the insert_shipment proc. */
l_accrue_on_receipt_flag := x_po_shipment_record.accrue_on_receipt_flag;
select receipt_required_flag
into l_poll_receipt_required_flag
from po_line_locations
where line_location_id = p_orig_line_location_id;
select expense_accrual_code into l_expense_accrual_code
from po_system_parameters;
select item_id into l_item_id from po_lines
where po_line_id = p_po_line_id;
insert_shipment(x_po_shipment_record,
p_online_report_id,
x_sequence,
p_line_num,
l_accrue_on_receipt_flag, -- Bug: 1402128
p_inv_org_id, -- Bug 2761415
l_internal_return_code,
p_is_complex_work_po, --
p_orig_line_location_id); --
insert_distribution(x_po_distribution_rec,
p_online_report_id,
x_sequence,
l_line_num,
l_shipment_num,
l_internal_return_code);
SELECT po_online_report_text_s.nextval
INTO x_online_report_id
FROM SYS.DUAL;
g_tax_attribute_update_code := 'CREATE';
g_tax_attribute_update_code := 'COPY_AND_CREATE';
SELECT * INTO x_po_distribution_record
FROM PO_DISTRIBUTIONS POD
WHERE POD.PO_HEADER_ID = x_from_po_header_id
AND POD.distribution_type = 'AGREEMENT';
SELECT quotation_class_code
INTO x_orig_quotation_class_code
FROM po_headers
WHERE po_header_id = x_from_po_header_id;
UPDATE PO_LINES
SET quantity = x_line_quantity
WHERE po_header_id = x_to_po_header_id
AND po_line_id = x_po_line_record.po_line_id;
SELECT line_location_id
FROM PO_LINE_LOCATIONS
WHERE po_header_id = p_po_header_id
AND SHIPMENT_TYPE NOT IN ('SCHEDULED','BLANKET');
SELECT count(*) INTO l_has_config_id
FROM po_lines
WHERE po_header_id = p_po_header_id AND
supplier_ref_number IS NOT NULL;
SELECT count(*) INTO l_has_config_id
FROM po_requisition_lines
WHERE requisition_header_id = p_requisition_header_id AND
supplier_ref_number IS NOT NULL;
SELECT POH.ORG_ID,POH.TYPE_LOOKUP_CODE,
POH.GLOBAL_AGREEMENT_FLAG,
FSP.INVENTORY_ORGANIZATION_ID,
FSP.SET_OF_BOOKS_ID
INTO l_doc_org_id, l_type_lookup_code,
l_global_agreement_flag,
l_inv_org_id, l_sob_id
FROM po_headers_all POH, financials_system_params_all FSP
WHERE po_header_id = p_po_header_id
AND poh.org_id = fsp.org_id;