Manas Kalsa's Oracle Apps Home

Thursday, 13 December 2012

Customer Invoice conversion

AR Invoice Conversion:

In the implementation project customer invoices migration is a challenge. Without having proper understanding of AR setups, migrating AR invoice is little tough. Doing the following setups invoices with multiple lines can also be processed.

The following set ups are required to import AR Invoices and few set ups are mandatory.

1.Create a DFF in the descriptive Segments 'Line Transaction Flexfield',
set context as 'XX OPENINVOICE' ,also select INTERFACE_LINE_ATTRIBUTE1 for passing the reference value.
Pass the 'XX OPENINVOICE' value in the INTERFACE_LINE_CONTEXT and reference numbers
in the INTERFACE_LINE_ATTRIBUTE1 on RA_INTERFACE_LINES_ALL table.This is mandatory because grouping rules.

2.Create a Source(AR->SETUP>TRANSACTIONS->SOURCES), 'OPENING INVOICES' ,
type should be 'IMPORTED'and uncheck auto transaction numbering.

3.Create a Transaction Types(AR->SETUP>TRANSACTIONS->TRANSACION TYPES), 'OPEN INVOICE''
attach receivable and revenue account. Then attch this Transaction types in the Source  'OPENING INVOICES'.
If rec and rev account pass in the RA_INTERFACE_DISTRIBUTIONS_ALL all table then no need to attach the
receivable and revenue account in the transaction type. Passing the values in RA_INTERFACE_DISTRIBUTIONS_ALL account information replace the any auto account set up.

4. In the Transaction type attach grouping rule 'DEFAULT'.
In the Transaction type goto tab customer information
sold to customer- ID
BILL to customer - ID
address to customer - ID ,
goto account info tab accounting flexfiled- ID
PAYMENT TERMS -ID

5. No INTERFACE_LINE_ATTRIBUTE(i) should be attached to the grouping rule.


Table :::

CREATE TABLE XX_REC_INV_TBL
(
OPERATING_UNIT VARCHAR2(240 BYTE),
CUSTOMER_CODE VARCHAR2(2000 BYTE),
INVOICE_DATE DATE,
INVOICE_NUMBER VARCHAR2(50 BYTE),
GL_DATE DATE,
INV_CLASS VARCHAR2(20 BYTE),
CURRENCY VARCHAR2(3 BYTE),
INV_TYPE VARCHAR2(240 BYTE),
RATE NUMBER,
PAYMENT_TERMS VARCHAR2(240 BYTE),
LINE_DESCRIPTION VARCHAR2(240 BYTE),
PRICE NUMBER,
UNIT_OF_MEASURE VARCHAR2(100 BYTE),
QTY NUMBER,
REC_ACCOUNT VARCHAR2(240 BYTE),
REV_ACCOUNT VARCHAR2(240 BYTE),
PROCESS VARCHAR2(1 BYTE) DEFAULT 'N',
ERROR_DESCRIPTION VARCHAR2(4000 BYTE),
CUSTOMER_ID NUMBER,
SITE_ID NUMBER,
ORG_ID NUMBER,
REC_ID NUMBER,
REV_ID NUMBER,
TERM_ID NUMBER,
SITE_CODE VARCHAR2(100 BYTE),
UNIT_PRICE NUMBER,
VAT_CODE VARCHAR2(100 BYTE)
)

*** I populates all the ID in my validation program.

PROCEDURE interface_table (pi_operating_unit IN VARCHAR2)
IS

CURSOR c1
IS
SELECT ROWID rowxx, a.org_id, invoice_number, invoice_date,
TO_DATE (gl_date, 'DD-MON-RRRR') gl_date, currency, customer_id,
site_id, line_description, price, uom_code, qty, rec_id,
rev_id, payment_terms, term_id, inv_type, rate, vat_code
FROM xx_rec_inv_tbl a
WHERE a.process = 'V' AND operating_unit = pi_operating_unit;

p_error NUMBER;
p_interface_line_context VARCHAR2 (100) := 'XX OPENINVOICE';
p_interface_line_attribute8 VARCHAR2 (100);
p_interface_line_attribute9 VARCHAR2 (100);
p_party_id NUMBER;
p_cust_account_id NUMBER;
p_cust_account_ref VARCHAR2 (240);
p_bill_to_address VARCHAR2 (240);
p_ship_to_address VARCHAR2 (200);
p_bill_to_address_id NUMBER;
p_ship_to_address_id NUMBER;
p_item_id NUMBER;
p_uom_code VARCHAR2 (150);
p_unit_of_measure VARCHAR2 (150);
v_seq NUMBER;
p_party_name VARCHAR2 (240);
p_message1 VARCHAR2 (2000);
p_message VARCHAR2 (2000);
v_rec_ccid NUMBER;
v_rev_ccid NUMBER;
p_org_id NUMBER;
v_interface_line_id NUMBER;
--------------------------------------------------------------
l_trx_number VARCHAR2 (20) := 0;
l_customer_number VARCHAR2 (20) := 0;
l_retention_amount NUMBER;
l_advance_amount NUMBER;
l_line_no NUMBER;
v_uom_code VARCHAR2 (3);
v_line_number NUMBER;
v_trx_type_id NUMBER;
BEGIN
FOR c1_rec IN c1
LOOP
p_error := 0;
v_trx_type_id := NULL;

BEGIN
SELECT ra_customer_trx_lines_s.NEXTVAL
INTO v_interface_line_id
FROM DUAL;


SELECT cust_trx_type_id
INTO v_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = c1_rec.inv_type AND org_id = c1_rec.org_id;

INSERT INTO ra_interface_lines_all
(interface_line_id, description,
interface_line_context,
interface_line_attribute1,
trx_number, batch_source_name,
line_type, currency_code,
amount, cust_trx_type_id,
--term_name,
orig_system_bill_customer_id,
orig_system_bill_customer_ref,
orig_system_bill_address_id,
orig_system_bill_address_ref, trx_date, gl_date,
uom_code, line_number, comments, created_by,
creation_date, last_updated_by, last_update_date,
last_update_login, org_id, conversion_type,
conversion_rate, tax_code, term_id, quantity,
unit_selling_price
)
VALUES (v_interface_line_id, c1_rec.line_description,
p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
TRIM (c1_rec.invoice_number), 'OPENING INVOICES',
'LINE', c1_rec.currency,
ROUND (c1_rec.qty * c1_rec.price, 2), v_trx_type_id,
--1,
--c1_rec.payment_terms,
c1_rec.customer_id,
NULL,
c1_rec.site_id,
c1_rec.site_id, c1_rec.invoice_date, c1_rec.gl_date,
c1_rec.uom_code, 1, NULL, 0,
c1_rec.invoice_date, 0, c1_rec.invoice_date,
-1, c1_rec.org_id, 'User',
NVL (c1_rec.rate, 1), NULL, -- 'ZERO RATE',
c1_rec.term_id, c1_rec.qty,
c1_rec.price
);

INSERT INTO ra_interface_distributions_all
(interface_line_id, interface_line_context,
interface_line_attribute1,
account_class, PERCENT, org_id, comments,
code_combination_id
)
VALUES (v_interface_line_id, p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
'REC', 100, c1_rec.org_id, 'Debtors Receivables',
c1_rec.rec_id
);

INSERT INTO ra_interface_distributions_all
(interface_line_id, interface_line_context,
interface_line_attribute1,
account_class, PERCENT, org_id, comments,
code_combination_id
)
VALUES (v_interface_line_id, p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
'REV', 100, c1_rec.org_id, 'Debtors Revenue',
c1_rec.rev_id
);

COMMIT;
END;
END LOOP;
END interface_table;

6. Do no select transaction type during auto invoice import, credit memo will also be imported automatically

** For a same invoice for multiple lines payment terms can not be different.
** For credit memo , payment terms can not be passed

Run the Auto Invoice Master Program which will call the Auto Invoice Import and after both request
successful completion it will load the data. While running the Auto Invoice Master Program select above defined source.

source and transaction types.

Wednesday, 12 December 2012

Base line budget deletion API

/* Formatted on 2012/12/12 14:52 (Formatter Plus v4.8.8) */
DECLARE
l_api_version_number NUMBER := 1.0;
x_finplan_version_id NUMBER;
l_commit VARCHAR2 (1) := 'F';
l_return_status VARCHAR2 (1);
l_init_msg_list VARCHAR2 (1) := 'F';
l_msg_data VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_index_out NUMBER;
l_data VARCHAR2 (2000);
l_loop_cnt NUMBER;
l_dummy_cnt NUMBER;
BEGIN
FOR c1_rec IN (SELECT pbv.project_id, pbv.budget_status_code, pbv.budget_type_code,
pbv.fin_plan_type_id, pbv.version_type, pbv.pm_product_code,
pbv.version_number, ppa.org_id
FROM pa_budget_versions pbv, pa_projects_all ppa
WHERE pbv.project_id = 2689 AND budget_status_code = 'B'
AND pbv.project_id = ppa.project_id)
LOOP

mo_global.set_policy_context ('S', c1_rec.org_id);
--l_responsibility_id := fnd_global.resp_id;
/*
fnd_global.apps_initialize (user_id => p_user_id,
resp_id => fnd_global.resp_id,
resp_appl_id => fnd_global.resp_appl_id
);
*/
pa_interface_utils_pub.set_global_info
(p_api_version_number => l_api_version_number,
p_responsibility_id => 22593,
p_user_id => 0,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status
);

pa_budget_pub.delete_baseline_budget
(p_api_version_number => l_api_version_number,
p_commit => l_commit,
p_init_msg_list => l_init_msg_list,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_pm_product_code => c1_rec.pm_product_code,
p_pa_project_id => c1_rec.project_id,
p_pm_project_reference => NULL,
p_budget_type_code => c1_rec.budget_type_code,
p_fin_plan_type_id => c1_rec.fin_plan_type_id,
p_fin_plan_type_name => 'XX Cost and Revenue Budget',
p_version_type => c1_rec.version_type,
p_version_number => c1_rec.version_number
);

IF l_msg_count > 0
THEN
l_loop_cnt := 1;

LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_msg_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);

IF l_return_status = 'E' OR l_return_status = 'U'
THEN
l_msg_data :=
CONCAT
( 'ERROR pa_budget_pub.delete_baseline_budget>>> '
|| '>',
l_msg_data
);
DBMS_OUTPUT.put_line (l_msg_data);
--fnd_file.put_line(fnd_file.OUTPUT,x_msg_data1);
ELSE
DBMS_OUTPUT.put_line ( '*** BASE LINE BUDGET DELETE ***:'
|| l_msg_data
);
END IF;

DBMS_OUTPUT.put_line ('RETURN STATUS :' || l_return_status);
l_loop_cnt := l_loop_cnt + 1;
EXIT WHEN l_loop_cnt > l_msg_count;
END LOOP;
END IF;
END LOOP;
END;

Budget Base Line API

Once a budget is created then a budget can be base lined

PROCEDURE budget_baseline_conc (
errorbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_project_number IN VARCHAR2,
p_version_num IN VARCHAR2,
p_rev_amount IN NUMBER,
p_cost_amount IN NUMBER,
p_period_num IN VARCHAR2,
p_user_id IN NUMBER,
p_success IN OUT VARCHAR2,
p_error_message IN OUT VARCHAR2
)
l_api_version_number NUMBER := 1.0;
x_finplan_version_id NUMBER;
l_commit VARCHAR2 (1) := 'F';
l_return_status VARCHAR2 (1);
l_init_msg_list VARCHAR2 (1) := 'F';
l_msg_data VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_index_out NUMBER;
l_data VARCHAR2 (2000);
l_workflow_started VARCHAR2 (1) := 'Y';
l_pm_product_code VARCHAR2 (10) := 'XXBUDGET';
l_budget_type_code VARCHAR2 (30);
l_responsility VARCHAR2 (100 BYTE);
l_application_id NUMBER;
--l_user_id NUMBER := 10803;
--l_responsibility_id NUMBER := 51222;
l_user_id NUMBER := 0;
l_responsibility_id NUMBER := 0;
l_project_id pa_projects_all.project_id%TYPE;
l_resource_list_name pa_resource_lists.NAME%TYPE;
l_resource_list_id pa_resource_lists.resource_list_id%TYPE;
l_task_id NUMBER;
l_description pa_budget_versions.description%TYPE;
l_pm_finplan_reference pa_budget_versions.pm_budget_reference%TYPE;
l_project_reference pa_projects_all.pm_project_reference%TYPE;
l_change_reason_code pa_budget_versions.change_reason_code%TYPE;
l_budget_version_name pa_budget_versions.version_name%TYPE;
l_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
l_fin_plan_type_name pa_fin_plan_types_vl.NAME%TYPE;
l_version_type pa_budget_versions.version_type%TYPE;
l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
l_raw_cost_flag VARCHAR2 (1);
l_burdened_cost_flag VARCHAR2 (1);
l_revenue_flag VARCHAR2 (1);
l_cost_qty_flag VARCHAR2 (1);
l_revenue_qty_flag VARCHAR2 (1);
l_all_qty_flag VARCHAR2 (1);
l_create_new_working_flag VARCHAR2 (1);
l_replace_current_working_flag VARCHAR2 (1);
l_using_resource_lists_flag VARCHAR2 (1);
p_message VARCHAR2 (2000);
l_resource_list_member_id NUMBER;
l_line_return_status VARCHAR2 (1);
l_proj_count NUMBER;
l_budget_lines_in pa_budget_pub.budget_line_in_tbl_type;
l_budget_lines_in_rec pa_budget_pub.budget_line_in_rec_type;
l_budget_lines_out pa_budget_pub.budget_line_out_tbl_type;
l_budget_lines_out_rec pa_budget_pub.budget_line_out_rec_type;
l_loop_cnt NUMBER;
l_dummy_cnt NUMBER;
l_error VARCHAR2 (500);
l_cnt NUMBER;
l_project_number VARCHAR2 (30);
l_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');
l_error_message VARCHAR2 (4000);
l_start_date DATE;
l_end_date DATE;
v_budget_version_id NUMBER;
l_currency VARCHAR2 (3);
v_rev_amount NUMBER;
v_cost_amount NUMBER;
v_rate NUMBER;
v_status VARCHAR2 (1);
l_func_currency VARCHAR2 (3);
BEGIN

mo_global.set_policy_context ('S', l_org_id);
l_responsility := fnd_profile.VALUE ('XX_PROJECT_BUDGET_RESP');

SELECT responsibility_id, application_id
INTO l_responsibility_id, l_application_id
FROM fnd_responsibility_tl
WHERE responsibility_name = l_responsility;

/*
fnd_global.apps_initialize (user_id => p_user_id,
resp_id => l_responsibility_id,
resp_appl_id => l_application_id
);
*/
pa_interface_utils_pub.set_global_info
(p_api_version_number => l_api_version_number,
p_responsibility_id => l_responsibility_id,
p_user_id => p_user_id,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status
);
mo_global.set_policy_context ('S', l_org_id);

l_budget_type_code := NULL;
l_return_status := NULL;
pa_budget_pub.baseline_budget
(p_api_version_number => 1.0,
p_commit => 'T',
p_init_msg_list => l_init_msg_list,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_workflow_started => l_workflow_started,
p_pm_product_code => l_pm_product_code,
p_pa_project_id => l_project_id,
p_pm_project_reference => p_project_number,
p_budget_type_code => l_budget_type_code,
p_fin_plan_type_id => NULL,
p_fin_plan_type_name => 'XX Cost and Revenue Budget',
p_version_type => 'ALL'
);
-- IF l_return_status != 'S'
-- THEN
-- RAISE api_error;
-- END IF;
DBMS_OUTPUT.put_line (l_return_status);

--COMMIT;
IF l_msg_count > 0
THEN
l_loop_cnt := 1;

LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_msg_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);

IF l_return_status = 'E' OR l_return_status = 'U'
THEN
l_msg_data :=
CONCAT ('ERROR pa_budget_pub.baseline_budget>>> ', l_msg_data);
DBMS_OUTPUT.put_line (l_msg_data);
fnd_file.put_line (fnd_file.LOG,
'*** ERROR ENCOUNTERED ***:' || l_msg_data
);
fnd_file.put_line (fnd_file.output,
'*** ERROR ENCOUNTERED ***:' || l_msg_data
);
--fnd_file.put_line(fnd_file.OUTPUT,x_msg_data1);
v_status := 'E';
ELSE
fnd_file.put_line (fnd_file.output,
'*** BUDGET BASE LINED ***:' || l_msg_data
);
v_status := 'S';
END IF;

DBMS_OUTPUT.put_line ('RETURN STATUS :' || l_return_status);
l_error_message :=
SUBSTR (l_error_message || '~' || l_msg_data, 1, 4000);
l_loop_cnt := l_loop_cnt + 1;
EXIT WHEN l_loop_cnt > l_msg_count;
END LOOP;
END IF;

fnd_file.put_line (fnd_file.LOG,
'pa_budget_pub.baseline_budget RETURN STATUS :'
|| l_return_status
);
---------------------
fnd_file.put_line (fnd_file.LOG, 'API RETURN STATUS :' || l_return_status);
fnd_file.put_line (fnd_file.LOG, 'APT MESSAGE :' || p_error_message);
END;

Budget Creation API (Cost Budget, Revenue Budget)

Assumption: The following Budget creation program is based on Financial Plan.

/* Formatted on 2012/12/13 12:52 (Formatter Plus v4.8.8) */
PROCEDURE budget_creation_conc (
errorbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_project_number IN VARCHAR2,
p_version_num IN VARCHAR2,
p_rev_amount IN NUMBER,
p_cost_amount IN NUMBER,
p_period_num IN VARCHAR2,
p_user_id IN NUMBER,
p_success IN OUT VARCHAR2,
p_error_message IN OUT VARCHAR2
)
IS
l_api_version_number NUMBER := 1.0;
x_finplan_version_id NUMBER;
l_commit VARCHAR2 (1) := 'F';
l_return_status VARCHAR2 (1);
l_init_msg_list VARCHAR2 (1) := 'F';
l_msg_data VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_index_out NUMBER;
l_data VARCHAR2 (2000);
l_workflow_started VARCHAR2 (1) := 'Y';
l_pm_product_code VARCHAR2 (10) := 'XXBUDGET';---has to be define in the system
l_budget_type_code VARCHAR2 (30);
l_responsility VARCHAR2 (100 BYTE);
l_application_id NUMBER;
--l_user_id NUMBER := 10803;
--l_responsibility_id NUMBER := 51222;
l_user_id NUMBER := 0;
l_responsibility_id NUMBER := 0;
l_project_id pa_projects_all.project_id%TYPE;
l_resource_list_name pa_resource_lists.NAME%TYPE;
l_resource_list_id pa_resource_lists.resource_list_id%TYPE;
l_task_id NUMBER;
l_description pa_budget_versions.description%TYPE;
l_pm_finplan_reference pa_budget_versions.pm_budget_reference%TYPE;
l_project_reference pa_projects_all.pm_project_reference%TYPE;
l_change_reason_code pa_budget_versions.change_reason_code%TYPE;
l_budget_version_name pa_budget_versions.version_name%TYPE;
l_fin_plan_type_id pa_fin_plan_types_b.fin_plan_type_id%TYPE;
l_fin_plan_type_name pa_fin_plan_types_vl.NAME%TYPE;
l_version_type pa_budget_versions.version_type%TYPE;
l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
l_raw_cost_flag VARCHAR2 (1);
l_burdened_cost_flag VARCHAR2 (1);
l_revenue_flag VARCHAR2 (1);
l_cost_qty_flag VARCHAR2 (1);
l_revenue_qty_flag VARCHAR2 (1);
l_all_qty_flag VARCHAR2 (1);
l_create_new_working_flag VARCHAR2 (1);
l_replace_current_working_flag VARCHAR2 (1);
l_using_resource_lists_flag VARCHAR2 (1);
p_message VARCHAR2 (2000);
l_resource_list_member_id NUMBER;
l_line_return_status VARCHAR2 (1);
l_proj_count NUMBER;
l_budget_lines_in pa_budget_pub.budget_line_in_tbl_type;
l_budget_lines_in_rec pa_budget_pub.budget_line_in_rec_type;
l_budget_lines_out pa_budget_pub.budget_line_out_tbl_type;
l_budget_lines_out_rec pa_budget_pub.budget_line_out_rec_type;
l_loop_cnt NUMBER;
l_dummy_cnt NUMBER;
l_error VARCHAR2 (500);
l_cnt NUMBER;
l_project_number VARCHAR2 (30);
l_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');
l_error_message VARCHAR2 (4000);
l_start_date DATE;
l_end_date DATE;
v_budget_version_id NUMBER;
l_currency VARCHAR2 (3);
v_rev_amount NUMBER;
v_cost_amount NUMBER;
v_rate NUMBER;
v_status VARCHAR2 (1);
l_func_currency VARCHAR2 (3);
BEGIN
SELECT project_id, TRUNC (start_date), TRUNC (completion_date),
NVL (pm_product_code, 'XXBUDGET'), project_currency_code, org_id,
projfunc_currency_code
INTO l_project_id, l_start_date, l_end_date,
l_pm_product_code, l_currency, l_org_id,
l_func_currency
FROM apps.pa_projects_all
WHERE segment1 = p_project_number;

mo_global.set_policy_context ('S', l_org_id);
l_responsility := fnd_profile.VALUE ('XX_PROJECT_BUDGET_RESP');

SELECT responsibility_id, application_id
INTO l_responsibility_id, l_application_id
FROM fnd_responsibility_tl
WHERE responsibility_name = l_responsility;

/*
fnd_global.apps_initialize (user_id => p_user_id,
resp_id => l_responsibility_id,
resp_appl_id => l_application_id
);
*/
pa_interface_utils_pub.set_global_info
(p_api_version_number => l_api_version_number,
p_responsibility_id => l_responsibility_id,
p_user_id => p_user_id,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status
);
mo_global.set_policy_context ('S', l_org_id);

SELECT SUM (NVL (projfunc_allocated_amount, 0)),
SUM (projfunc_allocated_amount) / SUM (allocated_amount)
INTO v_rev_amount,
v_rate
FROM pa_project_fundings
WHERE project_id = l_project_id;

v_cost_amount := v_rate * p_cost_amount;

l_budget_lines_in_rec.pm_product_code := l_pm_product_code;
l_budget_lines_in_rec.pa_task_id := l_project_id;
l_budget_lines_in_rec.pm_task_reference := p_project_number;
l_budget_lines_in_rec.resource_alias := NULL;
l_budget_lines_in_rec.resource_list_member_id := 3000;
---2004; --**--
l_budget_lines_in_rec.budget_start_date := l_start_date;
l_budget_lines_in_rec.budget_end_date := l_end_date;
l_budget_lines_in_rec.period_name := p_period_num;
l_budget_lines_in_rec.description := 'Revenue And Cost Budget';
l_budget_lines_in_rec.raw_cost := v_cost_amount; -- p_cost_amount;
l_budget_lines_in_rec.revenue := v_rev_amount;

l_budget_lines_in_rec.txn_currency_code := l_func_currency;
--l_budget_lines_in_rec.burdened_cost :=0;
--l_budget_lines_in_rec.quantity := l_quantity;
l_budget_lines_in (1) := l_budget_lines_in_rec;
pa_budget_pub.create_draft_budget
(p_api_version_number => 1.0,
p_commit => 'T',
p_init_msg_list => NULL,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_pm_product_code => l_pm_product_code,
p_pm_budget_reference => NULL,
p_budget_version_name => p_version_num,
p_pa_project_id => l_project_id,
p_pm_project_reference => p_project_number,
p_description => 'Revenue And Cost Budget',
p_entry_method_code => 'PA_LT_DR_WITHOUT_RES',
p_resource_list_id => 2000, ----1003, --**--
p_budget_lines_in => l_budget_lines_in,
p_budget_lines_out => l_budget_lines_out,
p_fin_plan_type_name => 'XX Cost and Revenue Budget', ---has to be define in the system
p_version_type => 'ALL',
p_revenue_flag => 'Y',
p_create_new_curr_working_flag => 'Y',
p_using_resource_lists_flag => 'Y'
);
--COMMIT;
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_msg_count > 0
THEN
l_loop_cnt := 1;

LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_msg_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);

IF l_return_status = 'E' OR l_return_status = 'U'
THEN
l_msg_data :=
CONCAT
( 'ERROR pa_budget_pub.create_draft_budget>>> user id~'
|| p_user_id
|| 'Resp Id~'
|| l_responsibility_id
|| '>',
l_msg_data
);
DBMS_OUTPUT.put_line (l_msg_data);
fnd_file.put_line (fnd_file.LOG,
'*** ERROR ENCOUNTERED ***:' || l_msg_data
);
fnd_file.put_line (fnd_file.output,
'*** ERROR ENCOUNTERED ***:' || l_msg_data
);
--fnd_file.put_line(fnd_file.OUTPUT,x_msg_data1);
v_status := 'E';
ELSE
fnd_file.put_line (fnd_file.output,
'*** REVENUE BUDGET UPLOADED ***:'
|| l_msg_data
);
v_status := 'S';
END IF;

DBMS_OUTPUT.put_line ('RETURN STATUS :' || l_return_status);
l_error_message :=
SUBSTR (l_error_message || '~' || l_msg_data, 1, 4000);
l_loop_cnt := l_loop_cnt + 1;
EXIT WHEN l_loop_cnt > l_msg_count;
END LOOP;
ELSE
v_status := 'S';
END IF;

fnd_file.put_line (fnd_file.LOG,
'pa_budget_pub.create_draft_budget RETURN STATUS :'
|| l_return_status
);
---base line part ---
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.output,
'RETURN STATUS :' || l_return_status
);
l_error_message := SUBSTR (SQLERRM, 1, 240);
fnd_file.put_line (fnd_file.output,
'l_error_message:' || l_error_message
);
END budget_creation_conc;

Friday, 7 December 2012

Attachment migration script (PO)

--Create temporary table and store the po attachment files to be loaded
CREATE TABLE XX_UPLOAD_ATTACHMENTS_TBL
(
PO_NUMBER VARCHAR2(20 BYTE),
FILE_TYPE VARCHAR2(20 BYTE),
FILE_NAME VARCHAR2(100 BYTE),
FILE_CONTENT BLOB,
PROCESS_FLAG VARCHAR2(1 BYTE) DEFAULT 'N',
ERROR_DESCRIPTION VARCHAR2(4000 BYTE),
DOCUMENT_TYPE VARCHAR2(50 BYTE)
);

--script

CREATE OR REPLACE PROCEDURE xx_load_attachment_prc (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
)
IS
x_access_id NUMBER;
x_file_id NUMBER;
v_po_hdr_id po_headers_all.po_header_id%TYPE := NULL;

PROCEDURE upload_file (
v_filename IN VARCHAR2,
x_access_id OUT NUMBER,
x_file_id OUT NUMBER
)
AS
v_access_id NUMBER;
v_file_id NUMBER;
x_errbuf VARCHAR2 (200);
fid NUMBER := -1;
fn VARCHAR2 (256);
mt VARCHAR2 (240);
bloblength NUMBER;
ufslim NUMBER;
BEGIN
v_access_id := fnd_gfm.authorize (NULL);
x_access_id := v_access_id;
fnd_file.put_line (fnd_file.LOG, 'Access id :' || v_access_id);

-- The function fnd_gfm.confirm_upload return the file id
IF (fnd_gfm.authenticate (v_access_id))
THEN
SELECT fnd_lobs_s.NEXTVAL
INTO fid
FROM DUAL;

fn := SUBSTR (v_filename, INSTR (v_filename, '/') + 1);

SELECT DBMS_LOB.getlength (blob_content), mime_type
INTO bloblength, mt
FROM fnd_lobs_document
WHERE NAME = v_filename AND ROWNUM = 1;

IF fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT') IS NULL
THEN
ufslim := bloblength;
ELSE
/* The profile is not limited to being a numeric value. Stripping off any
reference to kilobytes. */
IF (INSTR (UPPER (fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT')),
'K'
) > 0
)
THEN
ufslim :=
SUBSTR
(fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT'),
1,
INSTR
(UPPER (fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT')
),
'K'
)
- 1
);
ELSE
ufslim := fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT');
END IF;

ufslim := ufslim * 1000;
END IF;

IF bloblength BETWEEN 1 AND ufslim
THEN
INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, file_data,
upload_date, expiration_date, program_name,
program_tag, LANGUAGE, file_format)
(SELECT upload_file.fid, fn, ld.mime_type, ld.blob_content,
SYSDATE, NULL, 'PO', 'PO', 'en',
fnd_gfm.set_file_format (mt)
FROM fnd_lobs_document ld
WHERE ld.NAME = v_filename AND ROWNUM = 1);

IF (SQL%ROWCOUNT <> 1)
THEN
RAISE NO_DATA_FOUND;
END IF;

UPDATE fnd_lob_access
SET file_id = fid
WHERE access_id = v_access_id;
-- bug 3045375, added else to return fid = -2.
ELSE
fid := -2;
END IF;
END IF;

DELETE FROM fnd_lobs_document;

DELETE FROM fnd_lobs_documentpart;

x_file_id := fid;
fnd_file.put_line (fnd_file.LOG, 'File id :' || x_file_id);
EXCEPTION
WHEN OTHERS
THEN
x_errbuf :=
'Procedure upload_file errored out with the following error : '
|| SQLERRM;
fnd_file.put_line (fnd_file.LOG, x_errbuf);
END upload_file;
BEGIN
FOR rec_1 IN (SELECT a.ROWID r_id, a.po_number, a.file_name,
fmt.mime_type
FROM xx_upload_attachments_tbl a,
fnd_mime_types_tl fmt
WHERE NVL (a.process_flag, 'N') = 'N'
AND a.file_type = fmt.file_format_code)
LOOP
x_access_id := NULL;
x_file_id := NULL;
v_po_hdr_id := NULL;

BEGIN
--inserting recorsds into fnd lob doc
INSERT INTO fnd_lobs_document
(NAME, mime_type, doc_size, content_type, blob_content)
SELECT file_name, rec_1.mime_type,
DBMS_LOB.getlength (file_content), 'BINARY', file_content
FROM xx_upload_attachments_tbl
WHERE po_number = rec_1.po_number;

fnd_file.put_line (fnd_file.LOG,
'UPLOAD_FILE_SIZE_LIMIT '
|| fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT')
);
upload_file (rec_1.file_name, x_access_id, x_file_id);
COMMIT;

SELECT po_header_id
INTO v_po_hdr_id
FROM po_headers_all
WHERE type_lookup_code NOT IN ('RFQ', 'QUOTATION')
AND segment1 = rec_1.po_number;

fnd_webattch.add_attachment
(seq_num => 10,
category_id => 1000504,
--for documents
document_description => 'PO Attachments',
datatype_id => 6,
text => NULL,
file_name => rec_1.file_name,
url => NULL,
function_name => 'PO_POXPOEPO',
entity_name => 'PO_HEADERS',
pk1_value => v_po_hdr_id,
pk2_value => 0,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => x_file_id,
user_id => 1213,---userid
usage_type => 'O'
);

UPDATE xx_upload_attachments_tbl
SET process_flag = 'Y'
WHERE ROWID = rec_1.r_id;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;
END LOOP;
END xx_load_attachment_prc;
/