1: /*
2: API to assign External Bank Account to Supplier (Create Instrument) in Oracle Apps R12 - iby_disbursement_setup_pub
3: */
4: DECLARE
5: p_api_version NUMBER;
6: p_init_msg_list VARCHAR2(200);
7: p_commit VARCHAR2(200);
8: x_return_status VARCHAR2(200);
9: x_msg_count NUMBER;
10: x_msg_data VARCHAR2(200);
11: p_payee apps.iby_disbursement_setup_pub.payeecontext_rec_type;
12: p_assignment_attribs apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
13: lr_ext_bank_acct_dtl iby_ext_bank_accounts%ROWTYPE;
14: x_assign_id NUMBER;
15: x_response apps.iby_fndcpt_common_pub.result_rec_type;
16: lv_vendor_site_code VARCHAR2(100);
17: lv_vendor_name VARCHAR2(100);
18: lv_bank_acct_name VARCHAR2(100);
19: lv_bank_acct_num VARCHAR2(100);
20: lv_supp_site_id VARCHAR2(100);
21: lv_supp_party_site_id VARCHAR2(100);
22: lv_acct_owner_party_id VARCHAR2(100);
23: lv_org_id VARCHAR2(100);
24: l_msg VARCHAR2(200);
25: cursor suppliers IS
26: SELECT a.supplier_num , a.supplier_name , a.bank_account_num ,a.bank_account_name ,a.iban ,a.currency_code
27: , a.site_name
28: FROM xx_suppliers a --- your custom table to Load
29: ,ce_banks_v b
30: ,ce_bank_branches_v c
31: ,ap_suppliers s
32: ,ap_supplier_sites_all ss
33: where 1=1
34: and trim(a.supplier_name) = trim(s.vendor_name)
35: and ss.vendor_id = s.vendor_id
36: and ss.vendor_site_code = a.site_name
37: and a.bank_name = b.bank_name
38: and b.bank_party_id = c.bank_party_id
39: and c.bank_branch_name = a.bank_branch_name
40: and ss.org_id = &org_id
41: ;
42: l_count number:=0;
43: BEGIN
44: l_count := 0;
45: for rec in suppliers loop
46: -- Initialize apps session
47: fnd_global.apps_initialize(0,20639,200);
48: mo_global.init('SQLAP');
49: fnd_client_info.set_org_context(&l_org_id);
50: -- Input values
51: lv_vendor_site_code := rec.site_name;
52: lv_vendor_name := rec.supplier_name;
53: lv_bank_acct_name := rec.bank_account_name;
54: lv_bank_acct_num := rec.bank_account_num;
55: -- Assign API parameters
56: p_api_version := 1.0;
57: p_init_msg_list := fnd_api.G_FALSE;
58: p_commit := fnd_api.g_true;
59: -- get ext bank account details
60: BEGIN
61: SELECT *
62: INTO lr_ext_bank_acct_dtl
63: FROM iby_ext_bank_accounts a
64: WHERE 1=1
65: and nvl(a.bank_account_name,'0') = nvl(lv_bank_acct_name,'0')
66: and a.bank_account_num = rec.bank_account_num
67: and a.iban = rec.iban
68: and a.currency_code = rec.currency_code
69: ;
70: EXCEPTION
71: WHEN OTHERS THEN
72: DBMS_OUTPUT.put_line('Unable to derive the external bank details:' ||
73: SQLERRM);
74: END;
75: -- get supplier details
76: BEGIN
77: SELECT assa.vendor_site_id,
78: assa.party_site_id,
79: aps.party_id,
80: assa.org_id
81: INTO lv_supp_site_id,
82: lv_supp_party_site_id,
83: lv_acct_owner_party_id,
84: lv_org_id
85: FROM ap_suppliers aps, ap_supplier_sites_all assa
86: WHERE aps.vendor_id = assa.vendor_id
87: AND aps.vendor_name = lv_vendor_name
88: AND assa.vendor_site_code = lv_vendor_site_code;
89: EXCEPTION
90: WHEN OTHERS THEN
91: DBMS_OUTPUT.put_line('Error- Get supp_site_id and supp_party_site_id' ||
92: SQLCODE || SQLERRM);
93: END;
94: -- Assign payee values for supplier header level
95: p_payee.supplier_site_id := null;--lv_supp_site_id;
96: p_payee.party_id := lv_acct_owner_party_id;
97: p_payee.party_site_id := NULL; --lv_supp_party_site_id;
98: p_payee.payment_function := 'PAYABLES_DISB';
99: p_payee.org_id := NULL;--lv_org_id;
100: p_payee.org_type := NULL;--'OPERATING_UNIT';
101: /*
102: -- Assign payee values For supplier site leve
103: p_payee.supplier_site_id := lv_supp_site_id;
104: p_payee.party_id := lv_acct_owner_party_id;
105: p_payee.party_site_id := lv_supp_party_site_id;
106: p_payee.payment_function := 'PAYABLES_DISB';
107: p_payee.org_id := --lv_org_id;
108: p_payee.org_type := --'OPERATING_UNIT';
109: */
110: ----------------------------------------------------
111: -- Assignment Values
112: p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
113: p_assignment_attribs.instrument.instrument_id := lr_ext_bank_acct_dtl.ext_bank_account_id;
114: -- External Bank Account ID
115: p_assignment_attribs.priority := 1;
116: p_assignment_attribs.start_date := '01/01/2016';
117: iby_disbursement_setup_pub.set_payee_instr_assignment
118: (p_api_version => p_api_version,
119: p_init_msg_list => p_init_msg_list,
120: p_commit => p_commit,
121: x_return_status => x_return_status,
122: x_msg_count => x_msg_count,
123: x_msg_data => x_msg_data,
124: p_payee => p_payee,
125: p_assignment_attribs => p_assignment_attribs,
126: x_assign_id => x_assign_id,
127: x_response => x_response
128: );
129: DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);
130: DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);
131: DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);
132: DBMS_OUTPUT.put_line('X_ASSIGN_ID = ' || x_assign_id);
133: DBMS_OUTPUT.put_line('X_RESPONSE.Result_Code = ' ||
134: x_response.result_code);
135: DBMS_OUTPUT.put_line('X_RESPONSE.Result_Category = ' ||
136: x_response.result_category);
137: DBMS_OUTPUT.put_line('X_RESPONSE.Result_Message = ' ||
138: x_response.result_message);
139: IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
140: FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
141: l_msg := fnd_msg_pub.get(p_msg_index => i,
142: p_encoded => fnd_api.g_false);
143: DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
144: END LOOP;
145: ELSE
146: -- DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
147: l_count := l_count + 1 ;
148: END IF;
149: END LOOP;
150: dbms_output.put_line(' Created : '||l_count);
151: END;
![adop phase=fs_clone [ERROR]: At least one Oracle inventory check has failed.Provide the location of a valid inventory file.](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUxid_Sb9eyRWrrKKq6Yg7q6wZplrat_BiTw_ZH5keN-a-FH5yjkFKRmV8UQva_5I3Ui4FiK7Ty_CHoV7STcTw4BB6gSUyYfgm-utHaKJ3tGq3T2VPm6BWuGh1oBtutNA3iKG4h0w-xNm1/s72-c/oracle-dba.png)
Post a Comment