[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Algorithm: Based on the in parameters select the interface record(s) into
the header record type.
==============================================================================*/
PROCEDURE fetch_headers( p_interface_header_id IN NUMBER,
x_headers OUT NOCOPY PO_AUTOCREATE_TYPES.headers_rec_type)
IS
l_api_name VARCHAR2(30) := 'fetch_headers';
SELECT interface_header_id,
draft_id,
po_header_id,
action,
document_num,
document_type_code,
document_subtype,
rate_type,
rate_type_code,
rate_date,
rate,
agent_id,
agent_name,
ship_to_location_id,
ship_to_location,
bill_to_location_id,
bill_to_location,
payment_terms,
terms_id,
vendor_name,
vendor_num,
vendor_id,
vendor_site_code,
vendor_site_id,
vendor_contact,
vendor_contact_id,
from_rfq_num,
from_header_id,
fob,
freight_carrier,
freight_terms,
pay_on_code,
shipping_control,
currency_code,
quote_warning_delay,
approval_required_flag,
reply_date,
approval_status,
approved_date,
from_type_lookup_code,
revision_num,
confirming_order_flag,
acceptance_required_flag,
min_release_amount,
closed_code,
print_count,
frozen_flag,
encumbrance_required_flag,
vendor_doc_num,
org_id,
acceptance_due_date,
amount_to_encumber,
effective_date,
expiration_date,
po_release_id,
release_num,
release_date,
revised_date,
printed_date,
closed_date,
amount_agreed,
amount_limit, --
firm_flag,
gl_encumbered_date,
gl_encumbered_period_name,
budget_account_id,
budget_account,
budget_account_segment1,
budget_account_segment2,
budget_account_segment3,
budget_account_segment4,
budget_account_segment5,
budget_account_segment6,
budget_account_segment7,
budget_account_segment8,
budget_account_segment9,
budget_account_segment10,
budget_account_segment11,
budget_account_segment12,
budget_account_segment13,
budget_account_segment14,
budget_account_segment15,
budget_account_segment16,
budget_account_segment17,
budget_account_segment18,
budget_account_segment19,
budget_account_segment20,
budget_account_segment21,
budget_account_segment22,
budget_account_segment23,
budget_account_segment24,
budget_account_segment25,
budget_account_segment26,
budget_account_segment27,
budget_account_segment28,
budget_account_segment29,
budget_account_segment30,
created_language,
style_id,
style_display_name,
global_agreement_flag,
clm_standard_form,
clm_document_format,
-- standard who columns
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
FND_API.g_FALSE, -- initial value for error_flag
-- txn table columns
NULL, -- status_lookup_code
NULL, -- cancel_flag
NULL, -- vendor_order_num
NULL, -- quote_vendor_quote_num
'AUTOCREATE', -- doc_creation_method
NULL, -- quotation_class_code
NULL, -- approved_flag
NULL, -- tax_attribute_update_code
-- blanket dist columns
NULL,
clm_award_type, -- po_dist_id
-- CLM specific attributes
clm_source_document_id,
clm_effective_date,
clm_vendor_offer_number,
clm_award_administrator,
clm_no_signed_copies_to_return,
clm_min_guarantee_award_amt,
clm_min_guar_award_amt_percent,
clm_min_order_amount,
clm_max_order_amount,
clm_amount_released,
clm_external_idv,
NULL, --clm_contract_officer bug 12987412
umbrella_program_id, --umbrella program
fon_ref_id --umbrella program
INTO x_headers.intf_header_id,
x_headers.draft_id,
x_headers.po_header_id,
x_headers.action,
x_headers.document_num,
x_headers.doc_type,
x_headers.doc_subtype,
x_headers.rate_type,
x_headers.rate_type_code,
x_headers.rate_date,
x_headers.rate,
x_headers.agent_id,
x_headers.agent_name,
x_headers.ship_to_loc_id,
x_headers.ship_to_loc,
x_headers.bill_to_loc_id,
x_headers.bill_to_loc,
x_headers.payment_terms,
x_headers.terms_id,
x_headers.vendor_name,
x_headers.vendor_num,
x_headers.vendor_id,
x_headers.vendor_site_code,
x_headers.vendor_site_id,
x_headers.vendor_contact,
x_headers.vendor_contact_id,
x_headers.from_rfq_num,
x_headers.from_header_id,
x_headers.fob,
x_headers.freight_carrier,
x_headers.freight_term,
x_headers.pay_on_code,
x_headers.shipping_control,
x_headers.currency_code,
x_headers.quote_warning_delay,
x_headers.approval_required_flag,
x_headers.reply_date,
x_headers.approval_status,
x_headers.approved_date,
x_headers.from_type_lookup_code,
x_headers.revision_num,
x_headers.confirming_order_flag,
x_headers.acceptance_required_flag,
x_headers.min_release_amount,
x_headers.closed_code,
x_headers.print_count,
x_headers.frozen_flag,
x_headers.encumbrance_required_flag,
x_headers.vendor_doc_num,
x_headers.org_id,
x_headers.acceptance_due_date,
x_headers.amount_to_encumber,
x_headers.effective_date,
x_headers.expiration_date,
x_headers.po_release_id,
x_headers.release_num,
x_headers.release_date,
x_headers.revised_date,
x_headers.printed_date,
x_headers.closed_date,
x_headers.amount_agreed,
x_headers.amount_limit,
x_headers.firm_flag,
x_headers.gl_encumbered_date,
x_headers.gl_encumbered_period,
x_headers.budget_account_id,
x_headers.budget_account,
x_headers.budget_account_segment1,
x_headers.budget_account_segment2,
x_headers.budget_account_segment3,
x_headers.budget_account_segment4,
x_headers.budget_account_segment5,
x_headers.budget_account_segment6,
x_headers.budget_account_segment7,
x_headers.budget_account_segment8,
x_headers.budget_account_segment9,
x_headers.budget_account_segment10,
x_headers.budget_account_segment11,
x_headers.budget_account_segment12,
x_headers.budget_account_segment13,
x_headers.budget_account_segment14,
x_headers.budget_account_segment15,
x_headers.budget_account_segment16,
x_headers.budget_account_segment17,
x_headers.budget_account_segment18,
x_headers.budget_account_segment19,
x_headers.budget_account_segment20,
x_headers.budget_account_segment21,
x_headers.budget_account_segment22,
x_headers.budget_account_segment23,
x_headers.budget_account_segment24,
x_headers.budget_account_segment25,
x_headers.budget_account_segment26,
x_headers.budget_account_segment27,
x_headers.budget_account_segment28,
x_headers.budget_account_segment29,
x_headers.budget_account_segment30,
x_headers.created_language,
x_headers.style_id,
x_headers.style_display_name,
x_headers.global_agreement_flag,
x_headers.clm_standard_form,
x_headers.clm_document_format,
-- standard who columns
x_headers.last_update_date,
x_headers.last_updated_by,
x_headers.last_update_login,
x_headers.creation_date,
x_headers.created_by,
x_headers.request_id,
x_headers.program_application_id,
x_headers.program_id,
x_headers.program_update_date,
x_headers.error_flag, -- set initial value on error_flag
-- tan table columns
x_headers.status_lookup_code,
x_headers.cancel_flag,
x_headers.vendor_order_num,
x_headers.quote_vendor_quote_num,
x_headers.doc_creation_method,
x_headers.quotation_class_code,
x_headers.approved_flag,
x_headers.tax_attribute_update_code,
-- blanket dist columns
x_headers.po_dist_id,
x_headers.clm_award_type,
-- CLM specific attributes
x_headers.clm_source_document_id,
x_headers.clm_effective_date,
x_headers.clm_vendor_offer_number,
x_headers.clm_award_administrator,
x_headers.clm_no_signed_copies_to_return,
x_headers.clm_min_guarantee_award_amt,
x_headers.clm_min_guar_award_amt_percent,
x_headers.clm_min_order_amount,
x_headers.clm_max_order_amount,
x_headers.clm_amount_released,
x_headers.clm_external_idv,
x_headers.clm_contract_officer,
/* bug 12987412 new award is created ,
initially default clm_contract_officer to null*/
x_headers.umbrella_program_id, --umbrella program
x_headers.fon_ref_id --umbrella program
FROM po_headers_interface
WHERE interface_header_id = p_interface_header_id
ORDER BY interface_header_id;
SELECT PHI.interface_header_id,
PH.draft_id,
PH.po_header_id,
PHI.action,
PHI.document_num,
PHI.document_type_code,
PHI.document_subtype,
PH.rate_type,
PHI.rate_type_code,
PH.rate_date,
PH.rate,
PH.agent_id,
PHI.agent_name,
PH.ship_to_location_id,
PHI.ship_to_location,
PH.bill_to_location_id,
PHI.bill_to_location,
PHI.payment_terms,
PH.terms_id,
PHI.vendor_name,
PHI.vendor_num,
PH.vendor_id,
PHI.vendor_site_code,
PH.vendor_site_id,
PHI.vendor_contact,
PH.vendor_contact_id,
PHI.from_rfq_num,
PH.from_header_id,
PHI.fob,
PHI.freight_carrier,
PHI.freight_terms,
PH.pay_on_code,
PH.shipping_control,
PH.currency_code,
PH.quote_warning_delay,
PH.approval_required_flag,
PH.reply_date,
PHI.approval_status,
PH.approved_date,
PH.from_type_lookup_code,
PHI.revision_num,
PH.confirming_order_flag,
PH.acceptance_required_flag,
PH.min_release_amount,
PH.closed_code,
PH.print_count,
PH.frozen_flag,
PH.encumbrance_required_flag,
PHI.vendor_doc_num,
PH.org_id,
PH.acceptance_due_date,
PHI.amount_to_encumber,
PHI.effective_date,
PHI.expiration_date,
PHI.po_release_id,
PHI.release_num,
PHI.release_date,
PH.revised_date,
PH.printed_date,
PH.closed_date,
PHI.amount_agreed,
PH.amount_limit, --
PHI.firm_flag,
PHI.gl_encumbered_date,
PHI.gl_encumbered_period_name,
PHI.budget_account_id,
PHI.budget_account,
PHI.budget_account_segment1,
PHI.budget_account_segment2,
PHI.budget_account_segment3,
PHI.budget_account_segment4,
PHI.budget_account_segment5,
PHI.budget_account_segment6,
PHI.budget_account_segment7,
PHI.budget_account_segment8,
PHI.budget_account_segment9,
PHI.budget_account_segment10,
PHI.budget_account_segment11,
PHI.budget_account_segment12,
PHI.budget_account_segment13,
PHI.budget_account_segment14,
PHI.budget_account_segment15,
PHI.budget_account_segment16,
PHI.budget_account_segment17,
PHI.budget_account_segment18,
PHI.budget_account_segment19,
PHI.budget_account_segment20,
PHI.budget_account_segment21,
PHI.budget_account_segment22,
PHI.budget_account_segment23,
PHI.budget_account_segment24,
PHI.budget_account_segment25,
PHI.budget_account_segment26,
PHI.budget_account_segment27,
PHI.budget_account_segment28,
PHI.budget_account_segment29,
PHI.budget_account_segment30,
PH.created_language,
PH. style_id,
PHI.style_display_name,
PH.global_agreement_flag,
PH.clm_standard_form,
PH.clm_document_format,
-- standard who columns
PH.last_update_date,
PH.last_updated_by,
PH.last_update_login,
PH.creation_date,
PH.created_by,
PH.request_id,
PH.program_application_id,
PH.program_id,
PH.program_update_date,
FND_API.g_FALSE, -- initial value for error_flag
-- txn table columns
NULL, -- status_lookup_code
NULL, -- cancel_flag
NULL, -- vendor_order_num
NULL, -- quote_vendor_quote_num
'AUTOCREATE', -- doc_creation_method
NULL, -- quotation_class_code
NULL, -- approved_flag
NULL, -- tax_attribute_update_code
-- blanket dist columns
NULL,
PH.clm_award_type, -- po_dist_id
-- CLM specific attributes
PH.clm_source_document_id,
PH.clm_effective_date,
PH.clm_vendor_offer_number,
PH.clm_award_administrator,
PH.clm_no_signed_copies_to_return,
PH.clm_min_guarantee_award_amt,
PH.clm_min_guar_award_amt_percent,
PH.clm_min_order_amount,
PH.clm_max_order_amount,
PH.clm_amount_released,
PH.clm_external_idv,
PH.clm_contract_officer,
/*bug 12987412 add to mode, default clm_contract_officer
from base document*/
PH.umbrella_program_id, --umbrella program
PH.fon_ref_id --umbrella program
INTO x_headers.intf_header_id,
x_headers.draft_id,
x_headers.po_header_id,
x_headers.action,
x_headers.document_num,
x_headers.doc_type,
x_headers.doc_subtype,
x_headers.rate_type,
x_headers.rate_type_code,
x_headers.rate_date,
x_headers.rate,
x_headers.agent_id,
x_headers.agent_name,
x_headers.ship_to_loc_id,
x_headers.ship_to_loc,
x_headers.bill_to_loc_id,
x_headers.bill_to_loc,
x_headers.payment_terms,
x_headers.terms_id,
x_headers.vendor_name,
x_headers.vendor_num,
x_headers.vendor_id,
x_headers.vendor_site_code,
x_headers.vendor_site_id,
x_headers.vendor_contact,
x_headers.vendor_contact_id,
x_headers.from_rfq_num,
x_headers.from_header_id,
x_headers.fob,
x_headers.freight_carrier,
x_headers.freight_term,
x_headers.pay_on_code,
x_headers.shipping_control,
x_headers.currency_code,
x_headers.quote_warning_delay,
x_headers.approval_required_flag,
x_headers.reply_date,
x_headers.approval_status,
x_headers.approved_date,
x_headers.from_type_lookup_code,
x_headers.revision_num,
x_headers.confirming_order_flag,
x_headers.acceptance_required_flag,
x_headers.min_release_amount,
x_headers.closed_code,
x_headers.print_count,
x_headers.frozen_flag,
x_headers.encumbrance_required_flag,
x_headers.vendor_doc_num,
x_headers.org_id,
x_headers.acceptance_due_date,
x_headers.amount_to_encumber,
x_headers.effective_date,
x_headers.expiration_date,
x_headers.po_release_id,
x_headers.release_num,
x_headers.release_date,
x_headers.revised_date,
x_headers.printed_date,
x_headers.closed_date,
x_headers.amount_agreed,
x_headers.amount_limit,
x_headers.firm_flag,
x_headers.gl_encumbered_date,
x_headers.gl_encumbered_period,
x_headers.budget_account_id,
x_headers.budget_account,
x_headers.budget_account_segment1,
x_headers.budget_account_segment2,
x_headers.budget_account_segment3,
x_headers.budget_account_segment4,
x_headers.budget_account_segment5,
x_headers.budget_account_segment6,
x_headers.budget_account_segment7,
x_headers.budget_account_segment8,
x_headers.budget_account_segment9,
x_headers.budget_account_segment10,
x_headers.budget_account_segment11,
x_headers.budget_account_segment12,
x_headers.budget_account_segment13,
x_headers.budget_account_segment14,
x_headers.budget_account_segment15,
x_headers.budget_account_segment16,
x_headers.budget_account_segment17,
x_headers.budget_account_segment18,
x_headers.budget_account_segment19,
x_headers.budget_account_segment20,
x_headers.budget_account_segment21,
x_headers.budget_account_segment22,
x_headers.budget_account_segment23,
x_headers.budget_account_segment24,
x_headers.budget_account_segment25,
x_headers.budget_account_segment26,
x_headers.budget_account_segment27,
x_headers.budget_account_segment28,
x_headers.budget_account_segment29,
x_headers.budget_account_segment30,
x_headers.created_language,
x_headers.style_id,
x_headers.style_display_name,
x_headers.global_agreement_flag,
x_headers.clm_standard_form,
x_headers.clm_document_format,
-- standard who columns
x_headers.last_update_date,
x_headers.last_updated_by,
x_headers.last_update_login,
x_headers.creation_date,
x_headers.created_by,
x_headers.request_id,
x_headers.program_application_id,
x_headers.program_id,
x_headers.program_update_date,
x_headers.error_flag, -- set initial value on error_flag
-- tan table columns
x_headers.status_lookup_code,
x_headers.cancel_flag,
x_headers.vendor_order_num,
x_headers.quote_vendor_quote_num,
x_headers.doc_creation_method,
x_headers.quotation_class_code,
x_headers.approved_flag,
x_headers.tax_attribute_update_code,
-- blanket dist columns
x_headers.po_dist_id,
x_headers.clm_award_type,
-- CLM specific attributes
x_headers.clm_source_document_id,
x_headers.clm_effective_date,
x_headers.clm_vendor_offer_number,
x_headers.clm_award_administrator,
x_headers.clm_no_signed_copies_to_return,
x_headers.clm_min_guarantee_award_amt,
x_headers.clm_min_guar_award_amt_percent,
x_headers.clm_min_order_amount,
x_headers.clm_max_order_amount,
x_headers.clm_amount_released,
x_headers.clm_external_idv,
x_headers.clm_contract_officer, --bug 12987412
x_headers.umbrella_program_id, --umbrella program
x_headers.fon_ref_id --umbrella program
FROM po_headers_all PH,
po_headers_interface PHI
WHERE PH.SEGMENT1 = REPLACE(PHI.DOCUMENT_NUM,'-')
AND PHI.interface_header_id = p_interface_header_id;
SELECT ph.po_header_id
INTO x_headers.po_header_id
FROM po_headers_all ph
WHERE ph.type_lookup_code = PO_AUTOCREATE_PARAMS.g_document_subtype
AND ph.segment1 = REPLACE(x_headers.document_num,'-')
AND NVL(org_id, -99) = NVL(PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, -99);
SELECT po_headers_s.nextval INTO x_headers.po_header_id FROM sys.dual;
SELECT req_fsp.set_of_books_id
INTO l_req_ou_sob_id
FROM financials_system_params_all req_fsp
WHERE req_fsp.org_id = PO_AUTOCREATE_PARAMS.g_hdr_requesting_ou_id;
SELECT po_fsp.set_of_books_id
INTO l_po_ou_sob_id
FROM financials_system_params_all po_fsp
WHERE po_fsp.org_id = PO_AUTOCREATE_PARAMS.g_purchasing_ou_id;
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters_all psp
WHERE psp.org_id = PO_AUTOCREATE_PARAMS.g_purchasing_ou_id;
x_headers.last_update_date := SYSDATE;
x_headers.last_updated_by := FND_GLOBAL.user_id;
x_headers.last_update_login := FND_GLOBAL.login_id;
select 'Y' into x_is_valid
from hr_locations_all
where location_id = x_valid_ship_to/ x_valid_bill_to
and NVL(ship_to_site_flag, 'N') = 'Y'
and NVL(trunc(inactive_date),trunc(SYSDATE)+1) > trunc(SYSDATE);
SELECT 'Y'
INTO x_is_valid
FROM hr_locations_all
WHERE location_id = x_valid_ship_to
AND NVL(ship_to_site_flag, 'N') = 'Y'
AND NVL(TRUNC(inactive_date),TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
SELECT 'Y'
INTO x_is_valid
FROM hr_locations_all
WHERE location_id = x_valid_bill_to
AND NVL(bill_to_site_flag, 'N') = 'Y' --bug 4229954
AND NVL(TRUNC(inactive_date),TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
SELECT ship_to_location_id
INTO x_valid_ship_to
FROM financials_system_parameters ;
SELECT bill_to_location_id
INTO x_valid_bill_to
FROM financials_system_parameters ;
DESC: Insert/update header records into po_headers_draft_all table.
ARGS: IN OUT : x_headers PO_AUTOCREATE_TYPES.headers_rec_type
NOTE: if NEW DOCUMENT
1. Create a draft record
2. Create headers_draft record
else if ADD TO DOCUMENT
if there is no draft already exists for this document
1. create a new draft record.
2. popuate the headers draft from the main table.
3. update (if required) the headers draft using headers_rec_type
else if there is a draft already exists
1. update (if required) the draft record.
2. update (if required) the headers draft record using headers_rec_type
end;
UPDATE po_drafts
SET last_update_date = x_headers.last_update_date,
last_updated_by = x_headers.last_updated_by,
created_by = x_headers.created_by,
last_update_login = x_headers.last_update_login
WHERE draft_id = x_headers.draft_id;
UPDATE po_headers_draft_all
SET last_update_date = x_headers.last_update_date,
last_updated_by = x_headers.last_updated_by,
last_update_login = x_headers.last_update_login
WHERE draft_id = x_headers.draft_id;
INSERT
INTO po_drafts
(
draft_id,
document_id,
revision_num,
owner_user_id,
owner_role,
status,
draft_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
agent_id, --Bug13547051 Mod Buyer and CLM_contract_officer are not defaulted.
clm_contract_officer --Bug13547051
)
VALUES
(
x_headers.draft_id,
x_headers.po_header_id,
NULL,
FND_GLOBAL.user_id,
'BUYER',
'DRAFT',
l_draft_type, -- bug 16425245
x_headers.last_update_date,
x_headers.last_updated_by,
x_headers.last_update_login,
x_headers.creation_date,
x_headers.created_by,
x_headers.request_id,
x_headers.agent_id, --Bug13547051
x_headers.clm_contract_officer --Bug13547051
);
INSERT
INTO po_headers_draft_all
(
draft_id,
org_id,
delete_flag,
change_accepted_flag,
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,
amount_limit,
min_release_amount,
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,
quotation_class_code,
quote_warning_delay,
quote_vendor_quote_number,
acceptance_required_flag,
acceptance_due_date,
closed_date,
user_hold_flag,
approval_required_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,
closed_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
interface_source_code,
reference_num,
pay_on_code,
quote_warning_delay_unit,
global_agreement_flag,
shipping_control,
encumbrance_required_flag,
document_creation_method,
-- new columns in R12
style_id,
created_language,
tax_attribute_update_code,
clm_standard_form,
clm_document_format,
clm_award_type,
conterms_exist_flag,
clm_document_number,
clm_source_document_id,
clm_contract_officer,
/* bug 12987412 Stamp the clm_contract_officer
into the drafts table, which will eventually
get stamped onto the PO txn tables*/
umbrella_program_id, --umbrella program
fon_ref_id, --umbrella program
supplier_notif_method, --Bug13547051 In add to mode the communication should be copied from the base award.
fax, --Bug13547051
email_address, --Bug13547051
clm_effective_date --Bug13547051 In add to mode clm_effective_date should be copied from base award.
)
SELECT x_headers.draft_id,
org_id,
NULL,
NULL,
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,
amount_limit,
min_release_amount,
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,
quotation_class_code,
quote_warning_delay,
quote_vendor_quote_number,
acceptance_required_flag,
acceptance_due_date,
closed_date,
user_hold_flag,
approval_required_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,
closed_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
interface_source_code,
reference_num,
pay_on_code,
quote_warning_delay_unit,
global_agreement_flag,
shipping_control,
encumbrance_required_flag,
document_creation_method,
style_id,
created_language,
tax_attribute_update_code,
clm_standard_form,
clm_document_format,
clm_award_type,
conterms_exist_flag,
clm_document_number,
clm_source_document_id,
clm_contract_officer,
/* bug 12987412 since we are adding the to existing award,
use value from base document*/
umbrella_program_id, --umbrella program
fon_ref_id, --umbrella program
supplier_notif_method, --Bug13547051 In add to mode the communication should be copied from the base award.
fax, --Bug13547051
email_address, --Bug13547051
clm_effective_date --Bug13547051
FROM po_headers_all
WHERE po_header_id = x_headers.po_header_id;
UPDATE po_headers_draft_all
SET last_update_date = x_headers.last_update_date,
last_updated_by = x_headers.last_updated_by,
authorization_status = NULL,
last_update_login = x_headers.last_update_login
WHERE po_header_id = x_headers.po_header_id;
INSERT
INTO po_drafts
(
draft_id,
document_id,
revision_num,
owner_user_id,
owner_role,
status,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id
)
VALUES
(
x_headers.draft_id,
x_headers.po_header_id,
0,
FND_GLOBAL.user_id,
'BUYER',
'DRAFT',
x_headers.last_update_date,
x_headers.last_updated_by,
x_headers.last_update_login,
x_headers.creation_date,
x_headers.created_by,
x_headers.request_id
);
INSERT
INTO po_headers_draft_all
(
draft_id,
org_id,
delete_flag,
change_accepted_flag,
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,
amount_limit,
min_release_amount,
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,
quotation_class_code,
quote_warning_delay,
quote_vendor_quote_number,
acceptance_required_flag,
acceptance_due_date,
closed_date,
user_hold_flag,
approval_required_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,
closed_code,
government_context,
request_id,
program_application_id,
program_id,
program_update_date,
interface_source_code,
reference_num,
pay_on_code,
quote_warning_delay_unit,
global_agreement_flag,
shipping_control,
encumbrance_required_flag,
document_creation_method,
-- new columns in R12
style_id,
created_language,
tax_attribute_update_code,
clm_standard_form,
clm_document_format,
clm_award_type,
conterms_exist_flag,
-- CLM specific attributes
clm_source_document_id,
clm_effective_date,
clm_vendor_offer_number,
clm_award_administrator,
clm_no_signed_copies_to_return,
clm_min_guarantee_award_amt,
clm_min_guar_award_amt_percent,
clm_min_order_amount,
clm_max_order_amount,
clm_amount_released,
clm_external_idv,
clm_contract_officer,
/* bug 12987412 Stamp the clm_contract_officer
into the drafts table, which will eventually
get stamped onto the PO txn tables*/
umbrella_program_id, --umbrella program
fon_ref_id --umbrella program
)
SELECT x_headers.draft_id,
PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
NULL, -- delete_flag
NULL, -- change_accepted_flag
x_headers.po_header_id,
x_headers.agent_id,
x_headers.doc_subtype,
x_headers.last_update_date,
x_headers.last_updated_by,
x_headers.document_num,
'N', -- summary flag
'Y', -- enabled_flag,
NULL, -- segment2,
NULL, -- segment3,
NULL, -- segment4,
NULL, -- segment5,
NULL, -- start_date_active,
NULL, -- end_date_active,
x_headers.last_update_login,
x_headers.creation_date,
x_headers.created_by,
x_headers.vendor_id,
x_headers.vendor_site_id,
x_headers.vendor_contact_id,
x_headers.ship_to_loc_id,
x_headers.bill_to_loc_id,
x_headers.terms_id,
x_headers.freight_carrier,
x_headers.fob,
x_headers.freight_term,
x_headers.status_lookup_code,
x_headers.currency_code,
x_headers.rate_type_code,
TRUNC(x_headers.rate_date),
x_headers.rate,
x_headers.from_header_id,
x_headers.from_type_lookup_code,
TRUNC(effective_date),
TRUNC(expiration_date),
amount_agreed,
'INCOMPLETE', -- x_headers.authorization_status,
0,
revised_date,
'N',
x_headers.approved_date,
x_headers.amount_limit, -- bug5352625
x_headers.min_release_amount,
NULL, -- note_to_authorizer,
note_to_vendor,
note_to_receiver,
x_headers.print_count,
printed_date,
x_headers.vendor_order_num,
x_headers.confirming_order_flag,
comments,
TRUNC(x_headers.reply_date),
reply_method,
TRUNC(rfq_close_date),
x_headers.doc_subtype,
x_headers.quotation_class_code,
x_headers.quote_warning_delay,
x_headers.quote_vendor_quote_num,
x_headers.acceptance_required_flag,
TRUNC(x_headers.acceptance_due_date),
NULL, -- closed date
NULL, -- user_hold_flag,
x_headers.approval_required_flag,
x_headers.cancel_flag,
NULL, -- firm_status_lookup_code,
NULL, -- firm_date,
x_headers.frozen_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
'OPEN',
NULL, -- government_context,
x_headers.request_id,
x_headers.program_application_id,
x_headers.program_id,
x_headers.program_update_date,
interface_source_code,
reference_num,
x_headers.pay_on_code,
NULL, -- quote_warning_delay_unit,
x_headers.global_agreement_flag,
x_headers.shipping_control,
x_headers.encumbrance_required_flag,
x_headers.doc_creation_method,
-- new columns added in R12
x_headers.style_id,
x_headers.created_language,
x_headers.tax_attribute_update_code,
x_headers.clm_standard_form,
x_headers.clm_document_format,
x_headers.clm_award_type,
PO_AUTOCREATE_PARAMS.x_conterms_exist_flag,
-- CLM specific attributes
x_headers.clm_source_document_id,
x_headers.clm_effective_date,
x_headers.clm_vendor_offer_number,
x_headers.clm_award_administrator,
x_headers.clm_no_signed_copies_to_return,
x_headers.clm_min_guarantee_award_amt,
x_headers.clm_min_guar_award_amt_percent,
x_headers.clm_min_order_amount,
x_headers.clm_max_order_amount,
x_headers.clm_amount_released,
x_headers.clm_external_idv,
x_headers.clm_contract_officer,
/* bug 12987412 new award is created hence use the value from
x_headers.clm_contract_officer */
x_headers.umbrella_program_id, --umbrella program
x_headers.fon_ref_id --umbrella program
FROM po_headers_interface
WHERE interface_header_id = x_headers.intf_header_id;
UPDATE po_headers_interface
SET po_header_id = x_headers.po_header_id ,
draft_id = x_headers.draft_id
WHERE interface_header_id = x_headers.intf_header_id;
DESC: Insert rows into po_ga_org_assign draft table;
INSERT
INTO po_ga_org_assign_draft
(
draft_id,
delete_flag,
change_accepted_flag,
po_header_id,
organization_id,
enabled_flag,
vendor_site_id,
purchasing_org_id,
org_assignment_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
)
SELECT x_headers.draft_id,
NULL, -- delete_flag,
NULL, -- change_accepted_flag,
x_headers.po_header_id,
PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
x_headers.global_agreement_flag,
x_headers.vendor_site_id,
PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
PO_GA_ORG_ASSIGNMENTS_S.nextval, -- org_assignment_id,
x_headers.last_update_date,
x_headers.last_updated_by,
x_headers.last_update_login,
x_headers.creation_date,
x_headers.created_by
FROM po_headers_interface
WHERE interface_header_id = x_headers.intf_header_id
AND x_headers.doc_type = 'PA'
AND NVL(x_headers.global_agreement_flag, 'N') = 'Y';
SELECT match_option
INTO x_invoice_match_option
FROM po_vendor_sites_all --
WHERE vendor_site_id = X_vendor_site_id;
SELECT match_option
INTO x_invoice_match_option
FROM po_vendors
WHERE vendor_id = X_vendor_id;
SELECT aps.match_option
INTO x_invoice_match_option
FROM ap_product_setup aps;