Manas Kalsa's Oracle Apps Home

Wednesday, 12 December 2012

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;
/

XML report template migration from one instance to another

RTF template migration from A instance to B instance.
Here XX is the custom application

---template definition download command
SELECT 'FNDLOAD apps/app5 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct '
|| template_code
|| '.ldt XDO_DS_DEFINITIONS'
FROM xdo_templates_b
WHERE application_short_name = 'XX'

---template definition upload command
SELECT 'FNDLOAD apps/apps 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct '
|| template_code
|| '.ldt'
FROM xdo_templates_b
WHERE application_short_name = 'XX'

--template file download command
SELECT 'java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME <apps> -DB_PASSWORD <apps> -JDBC_CONNECTION <A IP:PORT:SID> -LOB_TYPE '
|| lob_type
|| ' -APPS_SHORT_NAME '
|| application_short_name
|| ' -LOB_CODE '
|| lob_code
|| ' -LANGUAGE '
|| LANGUAGE
|| ' -TERRITORY '
|| territory
FROM xdo_lobs
WHERE application_short_name = 'XX' ;

--template file upload command
SELECT 'java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME <apps> -DB_PASSWORD <apps> -JDBC_CONNECTION <B IP:PORT:SID> -LOB_TYPE '
|| lob_type
|| ' -APPS_SHORT_NAME '
|| application_short_name
|| ' -LOB_CODE '
|| lob_code
|| ' -LANGUAGE '
|| LANGUAGE
|| ' -TERRITORY '
|| territory
|| ' -XDO_FILE_TYPE '
|| xdo_file_type
|| ' -FILE_CONTENT_TYPE '''
|| file_content_type
|| ''' -FILE_NAME '
|| lob_code
FROM xdo_lobs
WHERE application_short_name = 'XX'

Tuesday, 4 December 2012

R12 Query to find supplier payment method

In R12 supplier payment method information no more exists in the ap_supplier_sites_all table.
Query to find the supplier payment method

SELECT ieppm.payment_method_code
FROM ap_supplier_sites_all assa,
ap_suppliers sup,
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds ieppm
WHERE sup.vendor_id = assa.vendor_id
AND assa.pay_site_flag = 'Y'
AND assa.vendor_site_id = iepa.supplier_site_id
AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
)
AND assa.vendor_site_id = :p_vendor_site_id
AND ieppm.primary_flag = 'Y'
ORDER BY sup.vendor_name, assa.vendor_site_code

R12 Supplier Site Payment method update script

DECLARE
x_return_status VARCHAR2 (200) := NULL;
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (200) := NULL;
t_output VARCHAR2 (200) := NULL;
t_msg_dummy VARCHAR2 (200) := NULL;
l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type;
p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type;
p_ext_payee_id_tab_type iby_disbursement_setup_pub.ext_payee_id_tab_type;
i NUMBER := 0;
BEGIN
fnd_msg_pub.delete_msg (NULL);
fnd_msg_pub.initialize;

-- the cursor taking only employee type supplier whose payment method not defined
FOR c1_rec IN (SELECT ieppm.payment_method_code, iepa.payee_party_id,
assa.vendor_site_id, iepa.ext_payee_id,
assa.org_id
FROM ap_supplier_sites_all assa,
ap_suppliers sup,
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds ieppm
WHERE sup.vendor_id = assa.vendor_id
AND assa.pay_site_flag = 'Y'
AND assa.vendor_site_id = iepa.supplier_site_id
AND iepa.ext_payee_id = ieppm.ext_pmt_party_id(+)
AND sup.employee_id IS NOT NULL
AND ieppm.payment_method_code IS NULL
ORDER BY sup.vendor_name, assa.vendor_site_code)
LOOP
i := 0;
i := i + 1;
DBMS_OUTPUT.put_line ('c1_rec.payee_party_id : '
|| c1_rec.payee_party_id
);
DBMS_OUTPUT.put_line ('c1_rec.ext_payee_id: ' || c1_rec.ext_payee_id);
p_external_payee_tab_type (i).default_pmt_method := 'EFT';
p_external_payee_tab_type (i).payment_function := 'PAYABLES_DISB';
p_external_payee_tab_type (i).exclusive_pay_flag := 'N';
p_external_payee_tab_type (i).payee_party_id := c1_rec.payee_party_id;
p_ext_payee_id_tab_type (i).ext_payee_id := c1_rec.ext_payee_id;
p_external_payee_tab_type (i).payer_org_id := c1_rec.org_id;
p_external_payee_tab_type (i).payer_org_type := 'OPERATING_UNIT';
p_external_payee_tab_type (i).supplier_site_id := c1_rec.vendor_site_id;
mo_global.set_policy_context ('S', c1_rec.org_id);
iby_disbursement_setup_pub.update_external_payee
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_ext_payee_tab => p_external_payee_tab_type,
p_ext_payee_id_tab => p_ext_payee_id_tab_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_ext_payee_status_tab => l_payee_upd_status
);
DBMS_OUTPUT.put_line ('Return Status : ' || x_return_status);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);

IF x_return_status <> 'S'
THEN
IF x_msg_count > 0
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, x_msg_data, t_msg_dummy);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);
t_output := (TO_CHAR (i) || ': ' || x_msg_data);
END LOOP;
END IF;

DBMS_OUTPUT.put_line
( 'Error occurred while updating the Payment Method'
|| t_output
);
END IF;

FOR j IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST
LOOP
DBMS_OUTPUT.put_line ( 'Error Message from table type : '
|| l_payee_upd_status (j).payee_update_msg
);
END LOOP;
END LOOP;
END;