[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_create_or_update_item_flag IN VARCHAR2,
X_header_processable_flag IN OUT NOCOPY VARCHAR2,
X_global_agreement_flag IN VARCHAR2, -- FPI GA
X_type_lookup_code IN VARCHAR2) -- Bug 3362369
IS
X_progress varchar2(3) := NULL;
X_allow_item_desc_update_flag
mtl_system_items.allow_item_desc_update_flag%TYPE;
select bom_item_type
into l_bom_item_type
from mtl_system_items
where inventory_item_id = X_item_id
and organization_id = X_def_inv_org_id;
what is setup for the item. Would not allow item_description update
if item attribute allow_item_desc_update_flag is N
****/
X_progress := '090';
SELECT msi.allow_item_desc_update_flag,
mtl.description
INTO X_allow_item_desc_update_flag,
X_msi_item_description
FROM mtl_system_items msi, mtl_system_items_tl mtl
WHERE mtl.inventory_item_id = msi.inventory_item_id
and mtl.organization_id = msi.organization_id
and mtl.language = USERENV('LANG')
and mtl.inventory_item_id = X_item_id
and msi.organization_id = X_def_inv_org_id;
IF (X_allow_item_desc_update_flag = 'N') AND
(X_item_description <> X_msi_item_description) AND
(X_create_or_update_item_flag = 'N')
THEN
/*** error because descriptions do not match and item attribute
does not allow item description update and update item runtime
parameter is not set.
***/
X_progress := '110';
X_create_or_update_item_flag IN VARCHAR2,
X_header_processable_flag IN OUT NOCOPY VARCHAR2,
X_global_agreement_flag IN VARCHAR2, -- FPI GA
X_type_lookup_code IN VARCHAR2) -- Bug 3362369
IS
X_progress varchar2(3) := null;
X_create_or_update_item_flag,
X_header_processable_flag,
X_global_agreement_flag, -- FPI GA
X_type_lookup_code); -- Bug 3362369
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_po_header_id IN NUMBER,
x_line_type_id IN NUMBER,
x_line_num IN NUMBER,
x_last_update_login IN NUMBER,
x_creation_date IN DATE,
x_created_by IN NUMBER,
x_item_id IN NUMBER,
x_item_revision IN VARCHAR2,
x_category_id IN NUMBER,
x_item_description IN VARCHAR2,
x_unit_meas_lookup_code IN VARCHAR2,
x_quantity_committed IN NUMBER,
x_committed_amount IN NUMBER,
x_allow_price_override_flag IN VARCHAR2,
x_not_to_exceed_price IN NUMBER,
x_list_price_per_unit IN NUMBER,
X_base_unit_price IN NUMBER, --
x_unit_price IN NUMBER,
x_quantity IN NUMBER,
x_un_number_id IN NUMBER,
x_hazard_class_id IN NUMBER,
x_note_to_vendor IN VARCHAR2,
x_from_header_id IN NUMBER,
x_from_line_id IN NUMBER,
x_min_order_quantity IN NUMBER,
x_max_order_quantity IN NUMBER,
x_qty_rcv_tolerance IN NUMBER,
x_over_tolerance_error_flag IN VARCHAR2,
x_market_price IN NUMBER,
x_unordered_flag IN VARCHAR2,
x_closed_flag IN VARCHAR2,
x_cancel_flag IN VARCHAR2,
x_cancelled_by IN NUMBER,
x_cancel_date IN DATE,
x_cancel_reason IN VARCHAR2,
x_vendor_product_num IN VARCHAR2,
x_contract_num IN VARCHAR2,
x_taxable_flag IN VARCHAR2,
x_tax_name IN VARCHAR2,
x_tax_code_id IN NUMBER,
x_type_1099 IN VARCHAR2,
x_capital_expense_flag IN VARCHAR2,
x_negotiated_by_preparer_flag IN VARCHAR2,
x_attribute_category IN VARCHAR2,
x_attribute1 IN VARCHAR2,
x_attribute2 IN VARCHAR2,
x_attribute3 IN VARCHAR2,
x_attribute4 IN VARCHAR2,
x_attribute5 IN VARCHAR2,
x_attribute6 IN VARCHAR2,
x_attribute7 IN VARCHAR2,
x_attribute8 IN VARCHAR2,
x_attribute9 IN VARCHAR2,
x_attribute10 IN VARCHAR2,
x_attribute11 IN VARCHAR2,
x_attribute12 IN VARCHAR2,
x_attribute13 IN VARCHAR2,
x_attribute14 IN VARCHAR2,
x_attribute15 IN VARCHAR2,
x_min_release_amount IN NUMBER,
x_price_type_lookup_code IN VARCHAR2,
x_closed_code IN VARCHAR2,
x_price_break_lookup_code IN VARCHAR2,
x_ussgl_transaction_code IN VARCHAR2,
x_government_context IN VARCHAR2,
x_request_id IN NUMBER,
x_program_application_id IN NUMBER,
x_program_id IN NUMBER,
x_program_update_date IN DATE,
x_closed_date IN DATE,
x_closed_reason IN VARCHAR2,
x_closed_by IN NUMBER,
x_transaction_reason_code IN VARCHAR2,
x_org_id IN NUMBER,
x_line_reference_num IN VARCHAR2,
x_terms_id IN NUMBER,
x_qty_rcv_exception_code IN VARCHAR2,
x_lead_time_unit IN VARCHAR2,
x_freight_carrier IN VARCHAR2,
x_fob IN VARCHAR2,
x_freight_terms IN VARCHAR2,
x_release_num IN NUMBER,
x_po_release_id IN NUMBER,
x_source_shipment_id IN NUMBER,
x_inspection_required_flag IN VARCHAR2,
x_receipt_required_flag IN VARCHAR2,
x_receipt_days_exception_code IN VARCHAR2,
x_need_by_date IN DATE,
x_promised_date IN DATE,
x_lead_time IN NUMBER,
x_invoice_close_tolerance IN NUMBER,
x_receive_close_tolerance IN NUMBER,
x_firm_flag IN VARCHAR2,
x_days_early_receipt_allowed IN NUMBER,
x_days_late_receipt_allowed IN NUMBER,
x_enforce_ship_to_loc_code IN VARCHAR2,
x_allow_sub_receipts_flag IN VARCHAR2,
x_receiving_routing IN VARCHAR2,
x_receiving_routing_id IN NUMBER,
x_header_processable_flag IN OUT NOCOPY VARCHAR2,
x_def_inv_org_id IN NUMBER,
x_uom_code IN VARCHAR2,
x_hd_type_lookup_code IN VARCHAR2,
x_create_or_update_item_flag IN VARCHAR2,
X_global_agreement_flag IN VARCHAR2, -- FPI GA
p_shipment_num IN NUMBER, /* */
p_contract_id IN NUMBER, --
--
p_job_id IN NUMBER,
p_effective_date IN DATE,
p_expiration_date IN DATE,
p_amount IN NUMBER,
p_order_type_lookup_code IN VARCHAR2,
p_purchase_basis IN VARCHAR2,
p_service_uom_class IN VARCHAR2
--
--
, p_contractor_first_name IN VARCHAR2
, p_contractor_last_name IN VARCHAR2
--
, p_job_business_group_id IN NUMBER --
)
IS
x_progress VARCHAR2(3) := null;
(x_create_or_update_item_flag <> 'Y')
/*** allow market price to have NOT NULL values because
Item Open Interface is to be called ***/
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'MARKET_PRICE',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'MARKET_PRICE',
x_market_price,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'QTY_RCV_TOLERANCE',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'QTY_RCV_TOLERANCE',
x_qty_rcv_tolerance,
null,null,null,null,
x_header_processable_flag);
(X_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'QTY_RCV_EXCEPTION_CODE',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'QTY_RCV_EXCEPTION_CODE',
x_qty_rcv_exception_code,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y' )
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'RECEIPT_REQUIRED_FLAG',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'RECEIPT_REQUIRED_FLAG',
x_receipt_required_flag,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'INSPECTION_REQUIRED_FLAG',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'INSPECTION_REQUIRED_FLAG',
x_inspection_required_flag,
null,null,null,null,
x_header_processable_flag);
select poh.effective_date, poh.expiration_date, pol.expiration_date
into l_header_start_date, l_header_end_date, l_exp_date
from po_headers_interface poh,
po_lines_interface pol
where poh.interface_header_id = x_interface_header_id
and pol.interface_line_id = x_interface_line_id
and poh.interface_header_id = pol.interface_header_id;
SELECT COUNT(FSP.business_group_id)
INTO x_count
FROM FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE FSP.business_group_id = p_job_business_group_id;
SELECT COUNT(PBG.business_group_id)
INTO x_count
FROM PER_BUSINESS_GROUPS_PERF PBG
WHERE PBG.business_group_id = p_job_business_group_id
AND TRUNC(sysdate) BETWEEN NVL(TRUNC(PBG.date_from), TRUNC(sysdate))
AND NVL(TRUNC(PBG.date_to), TRUNC(sysdate));
SELECT COUNT(PJ.job_id)
INTO x_count
FROM PER_JOBS_VL PJ,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PJ.job_id = p_job_id
AND PJ.business_group_id = FSP.business_group_id
AND FSP.business_group_id = NVL(p_job_business_group_id,
FSP.business_group_id)
AND TRUNC(sysdate) BETWEEN NVL(TRUNC(PJ.date_from), TRUNC(sysdate))
AND NVL(TRUNC(PJ.date_to), TRUNC(sysdate));
SELECT COUNT(PJ.job_id)
INTO x_count
FROM PER_JOBS_VL PJ,
PER_BUSINESS_GROUPS_PERF PBG
WHERE PJ.job_id = p_job_id
AND PJ.business_group_id = p_job_business_group_id
AND PJ.business_group_id = PBG.business_group_id
AND TRUNC(sysdate) BETWEEN NVL(TRUNC(PJ.date_from), TRUNC(sysdate))
AND NVL(TRUNC(PJ.date_to), TRUNC(sysdate))
AND TRUNC(sysdate) BETWEEN NVL(TRUNC(PBG.date_from), TRUNC(sysdate))
AND NVL(TRUNC(PBG.date_to), TRUNC(sysdate));
SELECT COUNT(*)
INTO x_count
FROM PO_JOB_ASSOCIATIONS_B PJA,
PER_JOBS_VL PJ
WHERE PJA.job_id = p_job_id
AND PJA.category_id = x_category_id
AND PJA.job_id = PJ.job_id
AND NVL(TRUNC(PJA.inactive_date), TRUNC(sysdate)) >= TRUNC(sysdate)
AND NVL(TRUNC(PJ.date_from), TRUNC(sysdate)) <= TRUNC(sysdate)
AND NVL(TRUNC(PJ.date_to), TRUNC(sysdate)) >= TRUNC(sysdate);
select rate_type
into l_rate_type
from po_headers_all
where po_header_id = X_po_header_id;
SELECT count(*)
INTO x_count
FROM MTL_UNITS_OF_MEASURE_VL
WHERE uom_class = p_service_uom_class
AND unit_of_measure = x_unit_meas_lookup_code
AND sysdate < NVL(disable_date, sysdate + 1);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'RECEIPT_DAYS_EXCEPTION_CODE',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'RECEIPT_DAYS_EXCEPTION_CODE',
x_receipt_days_exception_code,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'INVOICE_CLOSE_TOLERANCE',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'INVOICE_CLOSE_TOLERANCE',
x_invoice_close_tolerance,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'RECEIVE_CLOSE_TOLERANCE',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'RECEIVE_CLOSE_TOLERANCE',
x_receive_close_tolerance,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'DAYS_EARLY_RECEIPT_ALLOWED',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'DAYS_EARLY_RECEIPT_ALLOWED',
x_days_early_receipt_allowed,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'DAYS_LATE_RECEIPT_ALLOWED',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'DAYS_LATE_RECEIPT_ALLOWED',
x_days_late_receipt_allowed,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'ENFORCE_SHIP_TO_LOCATION_CODE',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'ENFORCE_SHIP_TO_LOCATION_CODE',
x_enforce_ship_to_loc_code,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'ALLOW_SUBSTITUTE_RECEIPTS_FLAG',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'ALLOW_SUBSTITUTE_RECEIPTS_FLAG',
x_allow_sub_receipts_flag,
null,null,null,null,
x_header_processable_flag);
(x_create_or_update_item_flag <> 'Y')
THEN
po_interface_errors_sv1.handle_interface_errors(
'PO_DOCS_OPEN_INTERFACE',
'FATAL',
null,
x_interface_header_id,
x_interface_line_id,
'PO_PDOI_COLUMN_NULL',
'PO_LINES_INTERFACE',
'RECEIVING_ROUTING',
'COLUMN_NAME',
'VALUE',
null,null,null,null,
'RECEIVING_ROUTING',
x_receiving_routing,
null,null,null,null,
x_header_processable_flag);
X_create_or_update_item_flag,
X_header_processable_flag,
X_global_agreement_flag, -- FPI GA
x_hd_type_lookup_code); -- Bug 3362369
select count(*) into x_count
from ap_tax_codes
where tax_id = x_tax_code_id
and enabled_flag = 'Y';