[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(default_quote_warning_delay, 0)
INTO x_default_quote_warning_delay
FROM po_system_parameters;
select po_headers_s.nextval
into x_local_po_header_id
from dual;
insert into po_headers(
PO_HEADER_ID
,AGENT_ID
,TYPE_LOOKUP_CODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,SEGMENT1
,SUMMARY_FLAG
,ENABLED_FLAG
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,VENDOR_ID
,VENDOR_SITE_ID
,VENDOR_CONTACT_ID
,SHIP_TO_LOCATION_ID
,BILL_TO_LOCATION_ID
,TERMS_ID
,SHIP_VIA_LOOKUP_CODE
,FOB_LOOKUP_CODE
,FREIGHT_TERMS_LOOKUP_CODE
,STATUS_LOOKUP_CODE
,CURRENCY_CODE
,RATE_TYPE
,RATE_DATE
,RATE
,FROM_HEADER_ID
,FROM_TYPE_LOOKUP_CODE
,START_DATE
,END_DATE
,BLANKET_TOTAL_AMOUNT
,AUTHORIZATION_STATUS
,REVISION_NUM
,REVISED_DATE
,APPROVED_FLAG
,APPROVED_DATE
,NOTE_TO_AUTHORIZER
,NOTE_TO_VENDOR
,NOTE_TO_RECEIVER
,PRINT_COUNT
,PRINTED_DATE
,VENDOR_ORDER_NUM
,CONFIRMING_ORDER_FLAG
,COMMENTS
,REPLY_DATE
,REPLY_METHOD_LOOKUP_CODE
,RFQ_CLOSE_DATE
,QUOTE_TYPE_LOOKUP_CODE
,QUOTE_WARNING_DELAY_UNIT
,QUOTE_WARNING_DELAY
,QUOTE_VENDOR_QUOTE_NUMBER
,ACCEPTANCE_REQUIRED_FLAG
,ACCEPTANCE_DUE_DATE
,USER_HOLD_FLAG
,CANCEL_FLAG
,FIRM_STATUS_LOOKUP_CODE
,FIRM_DATE
,FROZEN_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,AMOUNT_LIMIT
,APPROVAL_REQUIRED_FLAG
,MIN_RELEASE_AMOUNT
,QUOTATION_CLASS_CODE
,CLOSED_CODE
,GOVERNMENT_CONTEXT
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,CLOSED_DATE
,ORG_ID
,DOCUMENT_CREATION_METHOD --
,STYLE_ID --
,CREATED_LANGUAGE --Bug#5401155
)
SELECT
x_local_po_header_id
,AGENT_ID
,x_new_document_type
,sysdate
,who.user_id
,x_actual_document_num
,'N'
,'Y'
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,who.login_id
,sysdate
,who.user_id
-- keep original supplier if new supplier is null
,
nvl(x_new_supplier_id, VENDOR_ID)
--keep original supplier site if new supplier site is null
,nvl(x_new_supplier_site_id, VENDOR_SITE_ID)
--only keep original supplier contact if copying to the same supplier site
,nvl(x_new_supplier_contact_id, VENDOR_CONTACT_ID)
,SHIP_TO_LOCATION_ID
,BILL_TO_LOCATION_ID
,TERMS_ID
,SHIP_VIA_LOOKUP_CODE
,FOB_LOOKUP_CODE
,FREIGHT_TERMS_LOOKUP_CODE
-- set status lookup to incomplete
,'I'
,CURRENCY_CODE
,RATE_TYPE
,RATE_DATE
,RATE
-- set from header id and type to original doc type
,po_header_id
,TYPE_LOOKUP_CODE
,START_DATE
,END_DATE
,BLANKET_TOTAL_AMOUNT
,AUTHORIZATION_STATUS
-- reset revision num, revised date, approved flag, approved date
,null
,null
,null
,null
,NOTE_TO_AUTHORIZER
,NOTE_TO_VENDOR
,NOTE_TO_RECEIVER
-- reset print count and date
,null
,null
,VENDOR_ORDER_NUM
,CONFIRMING_ORDER_FLAG
,COMMENTS
,REPLY_DATE
,REPLY_METHOD_LOOKUP_CODE
,RFQ_CLOSE_DATE
,x_new_document_subtype
,QUOTE_WARNING_DELAY_UNIT
,x_default_quote_warning_delay
,QUOTE_VENDOR_QUOTE_NUMBER
,ACCEPTANCE_REQUIRED_FLAG
,ACCEPTANCE_DUE_DATE
,USER_HOLD_FLAG
-- reset cancel flag
,NULL
,FIRM_STATUS_LOOKUP_CODE
,FIRM_DATE
-- reset frozen flag
,Null
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,AMOUNT_LIMIT
,APPROVAL_REQUIRED_FLAG
,MIN_RELEASE_AMOUNT
,QUOTATION_CLASS_CODE
-- reset closed code
,null
,GOVERNMENT_CONTEXT
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,CLOSED_DATE
,ORG_ID
-- Bug 3648268. Using lookup code instead of hardcoded value
,'COPY_DOCUMENT' --
,STYLE_ID --
,decode(x_new_document_type, 'QUOTATION', nvl(created_language, PO_ATTRIBUTE_VALUES_PVT.get_base_lang), created_language) --Bug#5401155
from po_headers
where po_header_id = x_po_header_id;
SELECT po_line_id, category_id, item_description, item_id, org_id
FROM po_lines pl
WHERE pl.po_header_id = x_get_po_header_id
ORDER BY pl.po_line_id;
select po_lines_s.nextval
into x_to_po_line_id
from dual;
SELECT poh.ship_to_location_id
INTO x_ship_to_location_id
FROM po_headers poh
WHERE poh.po_header_id = x_from_po_header_id;
SELECT hrl.inventory_organization_id
INTO x_ship_to_loc_org_id
FROM hr_locations hrl
WHERE hrl.location_id = x_ship_to_location_id;
SELECT pol.item_id
INTO x_item_id
FROM po_lines pol
WHERE pol.po_line_id = x_from_po_line_id;
insert into po_lines(
PO_LINE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,PO_HEADER_ID
,LINE_TYPE_ID
,LINE_NUM
,LAST_UPDATE_LOGIN
,creation_date
,created_by
,ITEM_ID
,ITEM_REVISION
,CATEGORY_ID
,ITEM_DESCRIPTION
,UNIT_MEAS_LOOKUP_CODE
,QUANTITY_COMMITTED
,COMMITTED_AMOUNT
,ALLOW_PRICE_OVERRIDE_FLAG
,NOT_TO_EXCEED_PRICE
,LIST_PRICE_PER_UNIT
,UNIT_PRICE
,QUANTITY
,UN_NUMBER_ID
,HAZARD_CLASS_ID
,NOTE_TO_VENDOR
,FROM_HEADER_ID
,FROM_LINE_ID
,MIN_ORDER_QUANTITY
,MAX_ORDER_QUANTITY
,QTY_RCV_TOLERANCE
,OVER_TOLERANCE_ERROR_FLAG
,MARKET_PRICE
,UNORDERED_FLAG
,CLOSED_FLAG
,USER_HOLD_FLAG
,CANCEL_FLAG
,CANCELLED_BY
,CANCEL_DATE
,CANCEL_REASON
,FIRM_STATUS_LOOKUP_CODE
,FIRM_DATE
,VENDOR_PRODUCT_NUM
,CONTRACT_NUM
,TAXABLE_FLAG
,TAX_CODE_ID
,TYPE_1099
,CAPITAL_EXPENSE_FLAG
,NEGOTIATED_BY_PREPARER_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,REFERENCE_NUM
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,MIN_RELEASE_AMOUNT
,PRICE_TYPE_LOOKUP_CODE
,CLOSED_CODE
,PRICE_BREAK_LOOKUP_CODE
,GOVERNMENT_CONTEXT
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,CLOSED_DATE
,CLOSED_REASON
,CLOSED_BY
,TRANSACTION_REASON_CODE
/*
* project_id and task_id fields are added to the the insert statement which
* will enable the Copy Document option in RFQ's form to copy both these
* fields
*/
,project_id
,task_id
,ORG_ID
--togeorge 10/05/2000
--added oke columns
,oke_contract_header_id
,oke_contract_version_id
,order_type_lookup_code
,purchase_basis
,matching_basis
,ip_category_id --Bug#5401155
)
select
x_to_po_line_id
,sysdate
,who.user_id
,x_to_po_header_id
,LINE_TYPE_ID
,LINE_NUM
,LAST_UPDATE_LOGIN
,sysdate
,who.user_id
,ITEM_ID
,ITEM_REVISION
,CATEGORY_ID
,ITEM_DESCRIPTION
,UNIT_MEAS_LOOKUP_CODE
,QUANTITY_COMMITTED
,COMMITTED_AMOUNT
,ALLOW_PRICE_OVERRIDE_FLAG
,NOT_TO_EXCEED_PRICE
,LIST_PRICE_PER_UNIT
,nvl(UNIT_PRICE,0)
,QUANTITY
,UN_NUMBER_ID
,HAZARD_CLASS_ID
,NOTE_TO_VENDOR
,x_from_po_header_id
,x_from_po_line_id
,MIN_ORDER_QUANTITY
,MAX_ORDER_QUANTITY
,QTY_RCV_TOLERANCE
,OVER_TOLERANCE_ERROR_FLAG
,MARKET_PRICE
,UNORDERED_FLAG
,'N'
,'N'
,'N'
,null
,null
,null
,FIRM_STATUS_LOOKUP_CODE
,FIRM_DATE
,VENDOR_PRODUCT_NUM
,CONTRACT_NUM
,TAXABLE_FLAG
,decode(x_tax_id,null,TAX_CODE_ID,x_tax_id) /* Bug 1484350 draising */
-- ,decode (x_new_tax_flag, 'Y', x_tax_id, TAX_CODE_ID)
,TYPE_1099
,CAPITAL_EXPENSE_FLAG
,NEGOTIATED_BY_PREPARER_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,REFERENCE_NUM
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,MIN_RELEASE_AMOUNT
,PRICE_TYPE_LOOKUP_CODE
,null
,PRICE_BREAK_LOOKUP_CODE
,GOVERNMENT_CONTEXT
,null
,null
,null
,null
,null
,null
,null
,TRANSACTION_REASON_CODE
,project_id
,task_id
,ORG_ID
--togeorge 10/05/2000
--added oke columns
,oke_contract_header_id
,oke_contract_version_id
,order_type_lookup_code
,purchase_basis
,matching_basis
,l_ip_category_id --Bug#5401155
from po_lines
where po_line_id = x_from_po_line_id;
SELECT line_location_id
FROM po_line_locations pl
WHERE pl.po_line_id = x_get_po_line_id
ORDER BY pl.line_location_id;
select po_line_locations_s.nextval, pol.po_header_id
into x_to_po_line_location_id, x_to_po_header_id
from po_lines pol
where pol.po_line_id = x_to_po_line_id;
insert into po_line_locations(
LINE_LOCATION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,PO_HEADER_ID
,PO_LINE_ID
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,QUANTITY
,QUANTITY_RECEIVED
,QUANTITY_ACCEPTED
,QUANTITY_REJECTED
,QUANTITY_BILLED
,QUANTITY_CANCELLED
,UNIT_MEAS_LOOKUP_CODE
,PO_RELEASE_ID
,SHIP_TO_LOCATION_ID
,SHIP_VIA_LOOKUP_CODE
,NEED_BY_DATE
,PROMISED_DATE
,LAST_ACCEPT_DATE
,PRICE_OVERRIDE
,ENCUMBERED_FLAG
,ENCUMBERED_DATE
,UNENCUMBERED_QUANTITY
,FOB_LOOKUP_CODE
,FREIGHT_TERMS_LOOKUP_CODE
,TAXABLE_FLAG
,TAX_CODE_ID
,ESTIMATED_TAX_AMOUNT
,FROM_HEADER_ID
,FROM_LINE_ID
,FROM_LINE_LOCATION_ID
,START_DATE
,END_DATE
,LEAD_TIME
,LEAD_TIME_UNIT
,PRICE_DISCOUNT
,TERMS_ID
,APPROVED_FLAG
,APPROVED_DATE
,CLOSED_FLAG
,CANCEL_FLAG
,CANCELLED_BY
,CANCEL_DATE
,CANCEL_REASON
,FIRM_STATUS_LOOKUP_CODE
,FIRM_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,UNIT_OF_MEASURE_CLASS
,ENCUMBER_NOW
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,INSPECTION_REQUIRED_FLAG
,RECEIPT_REQUIRED_FLAG
,QTY_RCV_TOLERANCE
,QTY_RCV_EXCEPTION_CODE
,ENFORCE_SHIP_TO_LOCATION_CODE
,ALLOW_SUBSTITUTE_RECEIPTS_FLAG
,DAYS_EARLY_RECEIPT_ALLOWED
,DAYS_LATE_RECEIPT_ALLOWED
,RECEIPT_DAYS_EXCEPTION_CODE
,INVOICE_CLOSE_TOLERANCE
,RECEIVE_CLOSE_TOLERANCE
,SHIP_TO_ORGANIZATION_ID
,SHIPMENT_NUM
,SOURCE_SHIPMENT_ID
,SHIPMENT_TYPE
,CLOSED_CODE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,GOVERNMENT_CONTEXT
,RECEIVING_ROUTING_ID
,ACCRUE_ON_RECEIPT_FLAG
,CLOSED_REASON
,CLOSED_DATE
,CLOSED_BY
,ORG_ID,
--togeorge 10/05/2000
--added note to receiver
note_to_receiver
,outsourced_assembly /* Bug 6675806. Missing in R12 SHIKYU Transition.*/
)
select
x_to_po_line_location_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,x_to_po_header_id
,x_to_po_line_id
,who.login_id
,sysdate
,who.user_id
,QUANTITY
,0
,0
,0
,0
,0
,UNIT_MEAS_LOOKUP_CODE
,null
,SHIP_TO_LOCATION_ID
,SHIP_VIA_LOOKUP_CODE
,NEED_BY_DATE
,PROMISED_DATE
,LAST_ACCEPT_DATE
,PRICE_OVERRIDE
,'N'
,null
,0
,FOB_LOOKUP_CODE
,FREIGHT_TERMS_LOOKUP_CODE
,TAXABLE_FLAG
-- ,decode(x_new_tax_flag,'Y',x_tax_id,TAX_CODE_ID) /* Bug# 1484350 */
,decode(x_tax_id,null,TAX_CODE_ID,x_tax_id)
,ESTIMATED_TAX_AMOUNT
,PO_HEADER_ID
,PO_LINE_ID
,LINE_LOCATION_ID
,START_DATE
,END_DATE
,LEAD_TIME
,LEAD_TIME_UNIT
,PRICE_DISCOUNT
,TERMS_ID
,'N'
,null
,null
,'N'
,null
,null
,null
,FIRM_STATUS_LOOKUP_CODE
,FIRM_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,UNIT_OF_MEASURE_CLASS
,ENCUMBER_NOW
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,INSPECTION_REQUIRED_FLAG
,RECEIPT_REQUIRED_FLAG
,QTY_RCV_TOLERANCE
,QTY_RCV_EXCEPTION_CODE
,ENFORCE_SHIP_TO_LOCATION_CODE
,ALLOW_SUBSTITUTE_RECEIPTS_FLAG
,DAYS_EARLY_RECEIPT_ALLOWED
,DAYS_LATE_RECEIPT_ALLOWED
,RECEIPT_DAYS_EXCEPTION_CODE
,INVOICE_CLOSE_TOLERANCE
,RECEIVE_CLOSE_TOLERANCE
,SHIP_TO_ORGANIZATION_ID
,SHIPMENT_NUM
,SOURCE_SHIPMENT_ID
,x_new_document_type
,'OPEN'
,null
,null
,null
,null
,GOVERNMENT_CONTEXT
,RECEIVING_ROUTING_ID
,ACCRUE_ON_RECEIPT_FLAG
,null
,null
,null
,ORG_ID,
--togeorge 10/05/2000
--added note to receiver
note_to_receiver
,outsourced_assembly /* Bug 6675806. Missing in R12 SHIKYU Transition.*/
from po_line_locations
where line_location_id = x_from_po_line_location_id;