[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT phi.interface_header_id interface_header_id,
phi.interface_source_code interface_source_code,
phi.document_type_code,
phi.batch_id batch_id,
phi.action action,
phi.document_subtype document_subtype,
phi.document_num document_num,
phi.po_header_id po_header_id,
phi.agent_id agent_id,
phi.vendor_id vendor_id,
phi.vendor_site_id vendor_site_id,
phi.vendor_contact_id vendor_contact_id,
phi.ship_to_location_id ship_to_location_id,
phi.bill_to_location_id bill_to_location_id,
phi.terms_id terms_id,
phi.freight_carrier ship_via_lookup_code,
phi.fob fob_lookup_code,
phi.pay_on_code pay_on_code,
phi.freight_terms freight_terms_lookup_code,
phi.creation_date creation_date,
phi.created_by created_by,
phi.last_update_date last_update_date,
phi.last_updated_by last_updated_by,
phi.last_update_login last_update_login,
phi.revision_num revision_num,
phi.print_count print_count,
phi.closed_code h_closed_code,
phi.frozen_flag frozen_flag,
phi.firm_flag h_firm_status_lookup_code,
phi.confirming_order_flag confirming_order_flag,
phi.acceptance_required_flag acceptance_required_flag,
phi.currency_code h_currency_code,
phi.rate_type_code h_rate_type,
phi.rate_date h_rate_date,
phi.rate h_rate,
phi.amount_agreed,
phi.effective_date,
phi.expiration_date,
phi.amount_limit,
phi.global_agreement_flag,
phi.shipping_control,
phi.org_id,
phi.clm_award_type,
phi.clm_standard_form,
phi.clm_document_format,
phi.umbrella_program_id --umbrella program
FROM po_headers_interface phi
WHERE phi.interface_header_id = p_interface_header_id;
PROCEDURE INSERT_CPA (
p_auction_header_id IN PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE,
p_bid_number IN PON_BID_HEADERS.bid_number%TYPE,
p_sourcing_k_doc_type IN VARCHAR2,
p_conterms_exist_flag IN PO_HEADERS_ALL.conterms_exist_flag%TYPE,
p_document_creation_method IN VARCHAR2,
x_document_id OUT NOCOPY PO_HEADERS_ALL.po_header_id%TYPE,
x_document_number OUT NOCOPY PO_HEADERS_ALL.segment1%TYPE,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_rowid VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CPA';
p_message => 'Before calling PO_HEADERS_PKG_S0.Insert_Row');
PO_HEADERS_PKG_S0.Insert_Row(
X_Rowid => l_rowid,
X_Po_Header_Id => l_po_header_id,
X_Agent_Id => g_cpa_csr.agent_id,
X_Type_Lookup_Code => g_cpa_csr.document_subtype,
X_Last_Update_Date => g_cpa_csr.last_update_date,
X_Last_Updated_By => g_cpa_csr.last_updated_by,
X_Segment1 => x_document_num,
X_Summary_Flag => 'N',
X_Enabled_Flag => 'Y',
X_Segment2 => NULL,
X_Segment3 => NULL,
X_Segment4 => NULL,
X_Segment5 => NULL,
X_Start_Date_Active => NULL,
X_End_Date_Active => NULL,
X_Last_Update_Login => nvl(g_cpa_csr.last_update_login,fnd_global.login_id),
X_Creation_Date => g_cpa_csr.creation_date,
X_Created_By => g_cpa_csr.created_by,
X_Vendor_Id => g_cpa_csr.vendor_id,
X_Vendor_Site_Id => g_cpa_csr.vendor_site_id,
X_Vendor_Contact_Id => g_cpa_csr.vendor_contact_id,
X_Ship_To_Location_Id => g_cpa_csr.ship_to_location_id,
X_Bill_To_Location_Id => g_cpa_csr.bill_to_location_id,
X_Terms_Id => g_cpa_csr.terms_id,
X_Ship_Via_Lookup_Code => g_cpa_csr.ship_via_lookup_code,
X_Fob_Lookup_Code => g_cpa_csr.fob_lookup_code,
X_Pay_On_Code => g_cpa_csr.pay_on_code,
X_Freight_Terms_Lookup_Code => g_cpa_csr.freight_terms_lookup_code,
X_Status_Lookup_Code => NULL,
X_Currency_Code => g_cpa_csr.h_currency_code,
X_Rate_Type => g_cpa_csr.h_rate_type,
X_Rate_Date => nvl(g_cpa_csr.h_rate_date,trunc(sysdate)),
X_Rate => g_cpa_csr.h_rate,
X_From_Header_Id => NULL,
X_From_Type_Lookup_Code => NULL,
X_Start_Date => g_cpa_csr.effective_date,
X_End_Date => g_cpa_csr.expiration_date,
X_Blanket_Total_Amount => g_cpa_csr.amount_agreed,
X_Authorization_Status => NULL,
X_Revision_Num => g_cpa_csr.revision_num,
X_Revised_Date => NULL,
X_Approved_Flag => NULL,
X_Approved_Date => NULL,
X_Amount_Limit => nvl(g_cpa_csr.amount_limit, g_cpa_csr.amount_agreed),
X_Min_Release_Amount => NULL,
X_Note_To_Authorizer => NULL,
X_Note_To_Vendor => NULL,
X_Note_To_Receiver => NULL,
X_Print_Count => g_cpa_csr.print_count,
X_Printed_Date => NULL,
X_Vendor_Order_Num => NULL,
X_Confirming_Order_Flag => g_cpa_csr.confirming_order_flag,
X_Comments => NULL,
X_Reply_Date => NULL,
X_Reply_Method_Lookup_Code => NULL,
X_Rfq_Close_Date => NULL,
X_Quote_Type_Lookup_Code => NULL,
X_Quotation_Class_Code => NULL,
X_Quote_Warning_Delay_Unit => NULL,
X_Quote_Warning_Delay => NULL,
X_Quote_Vendor_Quote_Number => NULL,
X_Acceptance_Required_Flag => g_cpa_csr.acceptance_required_flag,
X_Acceptance_Due_Date => NULL,
X_Closed_Date => NULL,
X_User_Hold_Flag => NULL,
X_Approval_Required_Flag => NULL,
X_Cancel_Flag => 'N',
X_Firm_Status_Lookup_Code => nvl(g_cpa_csr.h_firm_status_lookup_code,'N'),
X_Firm_Date => NULL,
X_Frozen_Flag => g_cpa_csr.frozen_flag,
X_Global_Agreement_Flag => g_cpa_csr.global_agreement_flag,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Closed_Code => g_cpa_csr.h_closed_code,
X_Ussgl_Transaction_Code => NULL,
X_Government_Context => NULL,
X_Supply_Agreement_flag => 'N',
X_Manual => l_manual,
X_Price_Update_Tolerance => NULL,
X_Global_Attribute_Category => NULL,
X_Global_Attribute1 => NULL,
X_Global_Attribute2 => NULL,
X_Global_Attribute3 => NULL,
X_Global_Attribute4 => NULL,
X_Global_Attribute5 => NULL,
X_Global_Attribute6 => NULL,
X_Global_Attribute7 => NULL,
X_Global_Attribute8 => NULL,
X_Global_Attribute9 => NULL,
X_Global_Attribute10 => NULL,
X_Global_Attribute11 => NULL,
X_Global_Attribute12 => NULL,
X_Global_Attribute13 => NULL,
X_Global_Attribute14 => NULL,
X_Global_Attribute15 => NULL,
X_Global_Attribute16 => NULL,
X_Global_Attribute17 => NULL,
X_Global_Attribute18 => NULL,
X_Global_Attribute19 => NULL,
X_Global_Attribute20 => NULL,
p_shipping_control => g_cpa_csr.shipping_control,
p_encumbrance_required_flag => NULL,
p_org_id => l_current_org, --
p_style_id => g_style_id,-- bug 10017321: Adding g_style_id while call
p_umbrella_program_id => g_cpa_csr.umbrella_program_id --umbrella program
);
p_message => 'After calling PO_HEADERS_PKG_S0.Insert_Row. po_header_id = '||l_po_header_id||': Segment1 = '||l_document_num);
l_org_assign_rec.last_update_date := g_cpa_csr.last_update_date;
l_org_assign_rec.last_updated_by := g_cpa_csr.last_updated_by;
l_org_assign_rec.last_update_login := g_cpa_csr.last_update_login;
p_message => 'Before calling PO_GA_ORG_ASSIGN_PVT.Insert_Row');
PO_GA_ORG_ASSIGN_PVT.insert_row(
p_init_msg_list => FND_API.g_true,
x_return_status => l_return_status,
p_org_assign_rec => l_org_assign_rec,
x_row_id => l_org_row_id);
p_message => 'After calling PO_GA_ORG_ASSIGN_PVT.Insert_Row');
SELECT standard_form,
document_format
INTO l_standard_form,
l_document_format
FROM po_print_form_formats
WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
AND standard_form = g_cpa_csr.clm_standard_form
AND document_format = g_cpa_csr.clm_document_format;
SELECT standard_form,
document_format
INTO l_standard_form,
l_document_format
FROM po_print_form_formats
WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
AND default_flag = 'Y'
AND style_id = g_style_id
AND document_type = 'PO_IDV_STD_FORM';
UPDATE PO_HEADERS_ALL
SET uda_template_id = x_template_id,
uda_template_date = SYSDATE,
style_id = g_style_id,
clm_standard_form = l_standard_form,
clm_document_format = l_document_format,
clm_award_type = g_cpa_csr.clm_award_type
WHERE po_header_id = l_po_header_id;
X_last_update_login => g_cpa_csr.last_update_login,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_column1 => 'NEG');
X_last_update_login => g_cpa_csr.last_update_login,
X_program_application_id => '',
X_program_id => '',
X_request_id => NULL,
X_column1 => 'NEG');
x_last_update_login => g_cpa_csr.last_update_login,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL);
x_last_update_login => g_cpa_csr.last_update_login,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL);
x_last_update_login => g_cpa_csr.last_update_login,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL);
SELECT phda.clm_source_document_id
INTO x_clm_source_document_id
FROM po_headers_all phda
WHERE phda.po_header_id = l_po_header_id;
p_message => 'Before Selecting document number in CLM flow');
p_message => 'After Selecting document number in CLM flow');
p_message => 'Before Selecting document number from po_unique_identifier_cont_all');
p_message => 'After Selecting document number from po_unique_identifier_cont_all');
UPDATE PO_HEADERS_ALL
SET conterms_exist_flag = decode(p_conterms_exist_flag,'Y','Y','N'),
document_creation_method = p_document_creation_method,
segment1 = x_document_num,
clm_document_number = Nvl(x_doc_number, x_document_num)
WHERE po_header_id = l_po_header_id;
SELECT po_ame_approvals_s.nextval
INTO l_ame_approval_id
FROM dual;
SELECT pdsh.ame_transaction_type
INTO l_ame_transaction_type
FROM po_doc_style_headers pdsh, po_headers_all pha
WHERE pha.po_header_id = l_po_header_id
AND pha.style_id = pdsh.style_id;
UPDATE po_headers_all
SET ame_approval_id = l_ame_approval_id,
ame_transaction_type = l_ame_transaction_type
WHERE po_header_id = l_po_header_id;
SELECT 'Y',
podsh.ame_transaction_type
INTO l_new_ame_appr_id_req,
l_ame_transaction_type
FROM po_headers_all poh,
po_doc_style_headers podsh
WHERE poh.style_id = podsh.style_id
AND podsh.ame_transaction_type IS NOT NULL
AND poh.po_header_id = l_po_header_id;
UPDATE po_headers_all
SET ame_approval_id = DECODE(l_new_ame_appr_id_req,
'Y', po_ame_approvals_s.NEXTVAL,
ame_approval_id),
ame_transaction_type = DECODE(l_new_ame_appr_id_req,
'Y', l_ame_transaction_type,
ame_transaction_type)
WHERE po_header_id = l_po_header_id;
p_message => 'Insert_CPA: Inside l_contracts_call_exception');
, VALUE => 'PO_INTERFACE_S.INSERT_CPA');
p_message => 'PO_SOURCING_PVT.INSERT_CPA: Inside l_uda_template_exception'
);
END INSERT_CPA;
SELECT phi.style_id
INTO g_style_id
FROM po_headers_interface phi
WHERE phi.interface_header_id=p_interface_header_id;
SELECT nvl(pdsh.clm_flag,'N'),
Nvl(pdsh.change_process_type,'CHANGE_ORDER')
INTO l_clm_enabled_flag,
l_change_process_type
FROM po_doc_style_headers pdsh
WHERE pdsh.style_id=g_style_id;
-- Insert record in the po_headers table and add attachments and contract terms
g_progress := '505';
p_message => 'Before calling po_sourcing_pvt.insert_cpa');
INSERT_CPA (
p_auction_header_id => p_auction_header_id,
p_bid_number => p_bid_number,
p_sourcing_k_doc_type => p_sourcing_k_doc_type,
p_conterms_exist_flag => p_conterms_exist_flag,
p_document_creation_method => p_document_creation_method,
x_document_id => x_document_id,
x_document_number => x_document_number,
x_return_status => l_return_status
);
p_message => 'After calling po_sourcing_pvt.insert_cpa');
PROCEDURE DELETE_INTERFACE_HEADER (
p_interface_header_id IN PO_HEADERS_INTERFACE.INTERFACE_HEADER_ID%TYPE,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INTERFACE_HEADER';
DELETE po_headers_interface
WHERE interface_header_id = p_interface_header_id;
p_message => 'No of Records deleted from PO_HEADERS_INTERFACE'||SQL%rowcount);
END DELETE_INTERFACE_HEADER;