PK Oracle Apps OAF Workflow

Monday, February 3, 2020

R12 Employee As Supplier/Vendor Creation API Code

--R12 Employee As Supplier/Vendor Creation API Code
CREATE OR REPLACE PACKAGE apps.xx_emp_as_spplr_crtn_pkg

API to create employee as supplier in R12

Created By Pavankumar Bondage bondagepm.erp@gmail.com

PROCEDURE main (

errbuf OUT VARCHAR2,

retcode OUT NUMBER,

p_emp_id IN NUMBER,

p_org_id IN NUMBER,

p_vendor_site_code IN VARCHAR2

);

--###

PROCEDURE LOG (p_msg VARCHAR2);

--###

PROCEDURE output (p_msg VARCHAR2);

PROCEDURE set_payee_instr_assignment_p (

p_vendor_id IN NUMBER,

p_vendor_site_id IN NUMBER,

p_org_id IN NUMBER,

p_ext_bank_account_id IN NUMBER

);

PROCEDURE create_external_payee_p (

p_vendor_id IN NUMBER,

p_org_id IN NUMBER,

p_ext_payee_id IN NUMBER -- iby_pmt_instr_uses_all.ext_pmt_party_id

);

PROCEDURE create_ce_bank_branch (

errbuf OUT VARCHAR2,

retcode OUT NUMBER,

p_bank_party_id IN NUMBER,

p_branch_name IN VARCHAR2,

p_branch_type IN VARCHAR2,

p_bank_home_country_code IN VARCHAR2

);

END xx_emp_as_spplr_crtn_pkg;

CREATE OR REPLACE PACKAGE BODY APPS.xx_emp_as_spplr_crtn_pkg

API to create employee as supplier in R12

Created By Pavankumar M. Bondage bondagepm.erp@gmail.com

g_business_group_id NUMBER := 81;

g_conc_request_id number :=fnd_global.conc_request_id;

PROCEDURE main (

errbuf OUT VARCHAR2,

retcode OUT NUMBER,

p_emp_id IN NUMBER,

p_org_id IN NUMBER,

p_vendor_site_code IN VARCHAR2

)

--CURSOR c_emp_info(p_emp_id IN VARCHAR2) IS

CURSOR c_empdet (p_emp_id IN VARCHAR2)

SELECT papf.full_name, papf.person_id, papf.employee_number

FROM per_all_people_f papf

WHERE papf.business_group_id = g_business_group_id

AND papf.person_id = p_emp_id;

CURSOR c_hou

SELECT hou.NAME

FROM hr_operating_units hou

WHERE hou.organization_id = p_org_id;

CURSOR c_vendor (v_vendor_name IN VARCHAR2)

SELECT pv.vendor_id, pv.vendor_type_lookup_code

FROM po_vendors pv

WHERE UPPER (vendor_name) = UPPER (v_vendor_name);

CURSOR c_vensite (p_vendor_id IN NUMBER, p_org_id IN NUMBER)

SELECT COUNT (1)

FROM ap_supplier_sites_all

WHERE vendor_id = p_vendor_id

AND UPPER (vendor_site_code) =

UPPER

(p_vendor_site_code)

-- IN ('HOME', 'OFFICE', 'PROVISIONAL')

AND org_id = p_org_id;

l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

ext_payee_rec iby_disbursement_setup_pub.external_payee_rec_type;

l_return_status VARCHAR2 (10);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (1000);

l_vendor_id NUMBER;

l_party_id NUMBER;

v_empname VARCHAR2 (50);

v_empid NUMBER;

p_count NUMBER;

l_vendor_site_id NUMBER;

l_party_site_id NUMBER;

l_location_id NUMBER;

l_liability_id NUMBER;

l_prepay_id NUMBER;

v_error VARCHAR2 (1000);

v_org_name VARCHAR2 (50);

l_vendor_type_code VARCHAR2 (20);

v_employee_number VARCHAR2 (20);

ln_sup_site_cnt NUMBER;

BEGIN

v_error := 'S';

xx_emp_as_spplr_crtn_pkg.output ('***************Employee Type Supplier Creation request Log Starts***********************');

OPEN c_hou;

FETCH c_hou

INTO v_org_name;

CLOSE c_hou;

xx_emp_as_spplr_crtn_pkg.output (' Organization : ' || v_org_name);

OPEN c_empdet (p_emp_id);

FETCH c_empdet

INTO v_empname, v_empid, v_employee_number;

CLOSE c_empdet;

IF v_empname IS NULL

THEN

xx_emp_as_spplr_crtn_pkg.output (' Employee does not exist for Employee Number#' || v_employee_number);

ELSE

OPEN c_vendor (v_empname);

FETCH c_vendor

INTO l_vendor_id, l_vendor_type_code;

CLOSE c_vendor;

IF l_vendor_id IS NOT NULL

THEN

v_error := 'Supp_Exists';

xx_emp_as_spplr_crtn_pkg.output ( ' Supplier Already Exists with Vendor Id#'|| l_vendor_id|| ' and Type '|| l_vendor_type_code);

OPEN c_vensite (l_vendor_id, p_org_id);

FETCH c_vensite

INTO ln_sup_site_cnt;

CLOSE c_vensite;

/* IF ln_sup_site_cnt = 3

THEN

retcode := '2';

errbuf :=

' Error - Supplier and Sites have already been created. ';

END IF;*/

END IF;

IF v_error <> 'Supp_Exists'

THEN

xx_emp_as_spplr_crtn_pkg.output

(' Creating Employee Type Supplier for :'

|| v_empname

);

l_vendor_rec.vendor_name := v_empname;

l_vendor_rec.vendor_type_lookup_code := 'EMPLOYEE';

l_vendor_rec.employee_id := v_empid;

xx_emp_as_spplr_crtn_pkg.output ('Calling Vendor Creation API...');

pos_vendor_pub_pkg.create_vendor

(p_vendor_rec => l_vendor_rec,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_vendor_id => l_vendor_id,

x_party_id => l_party_id

);

IF (l_return_status <> 'S')

THEN

xx_emp_as_spplr_crtn_pkg.output ('Error in Supplier Creation API...');

IF (l_msg_count = 1)

THEN

xx_emp_as_spplr_crtn_pkg.output ('x_msg_data '|| l_msg_data);

ELSIF (l_msg_count > 1)

THEN

LOOP

p_count := p_count + 1;

l_msg_data :=

fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF (l_msg_data IS NULL)

THEN

EXIT;

END IF;

xx_emp_as_spplr_crtn_pkg.output (' Error Message'|| p_count

|| ' ---'

|| l_msg_data);

END LOOP;

END IF;

ELSE

xx_emp_as_spplr_crtn_pkg.output(' Successfully Cretaed Supplier ');

END IF;

IF l_vendor_id IS NOT NULL AND NVL (ln_sup_site_cnt, 0) =0

THEN

BEGIN

IF v_error <> 'E'

THEN

l_vendor_site_rec.vendor_id := l_vendor_id;

l_vendor_site_rec.vendor_site_code :=

UPPER (p_vendor_site_code);

--'OFFICE';

l_vendor_site_rec.org_id := p_org_id;

l_vendor_site_rec.accts_pay_code_combination_id := 1016;

--l_liability_id;

l_vendor_site_rec.prepay_code_combination_id := 1017;

--l_prepay_id;

l_vendor_site_rec.terms_date_basis := 'Invoice';

ext_payee_rec.default_pmt_method := 'CHECK';

l_vendor_site_rec.ext_payee_rec.default_pmt_method :=

'CHECK';

xx_emp_as_spplr_crtn_pkg.output ('Calling Vendor Site Creation API...');

pos_vendor_pub_pkg.create_vendor_site

(p_vendor_site_rec => l_vendor_site_rec,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_vendor_site_id => l_vendor_site_id,

x_party_site_id => l_party_site_id,

x_location_id => l_location_id

);

IF (l_return_status <> 'S')

THEN

xx_emp_as_spplr_crtn_pkg.output( ' Error in Creating Vendor Site..');

retcode := '1';

errbuf := ' Error in Supplier Site Creation-';

IF (l_msg_count = 1)

THEN

xx_emp_as_spplr_crtn_pkg.output( ' x_msg_data '||l_msg_data);

ELSIF (l_msg_count > 1)

THEN

LOOP

p_count := p_count + 1;

l_msg_data :=

fnd_msg_pub.get (fnd_msg_pub.g_next,

fnd_api.g_false

);

IF (l_msg_data IS NULL)

THEN

EXIT;

END IF;

xx_emp_as_spplr_crtn_pkg.output(' Error Message'|| p_count|| ' ---'|| l_msg_data);

END LOOP;

END IF;

ELSE

xx_emp_as_spplr_crtn_pkg.output(' Successfully Created Supplier Site');

END IF;

EXCEPTION

WHEN OTHERS

THEN

xx_emp_as_spplr_crtn_pkg.output('SQL Error :'||dbms_utility.format_error_backtrace||SQLERRM) ;

END;

END IF;

--=== adding bank details--@@ 03-12-19/--

DECLARE

p_api_version NUMBER;

p_init_msg_list VARCHAR2 (200);

p_ext_bank_acct_rec apps.iby_ext_bankacct_pub.extbankacct_rec_type;

x_acct_id NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

x_response apps.iby_fndcpt_common_pub.result_rec_type;

lv_bank_name VARCHAR2 (100);

lv_bank_branch VARCHAR2 (100);

LV_IBAN_NUM varchar2(150);

--lv_bank_acct_num VARCHAR2 (100);

lv_bank_acct_name VARCHAR2 (100);

lv_vendor_name VARCHAR2 (100);

--ln_bank_id NUMBER;

ln_branch_id NUMBER;

lv_bank_home_country_code VARCHAR2 (100);

ln_acct_owner_party_id NUMBER;

l_msg VARCHAR2 (200);

p_person_id NUMBER := p_emp_id;--76526;

lv_bank_crt_error_msg varchar2(2000):=null;

l_lngth_of_iban number:=0;

lv_bank_party_id number :=0;

BEGIN

xx_emp_as_spplr_crtn_pkg.output('Bank Creation API code Begins...');

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Starts','Bank Creation API code Begins...',substr(SQLERRM,1,399));

-- Initialize apps session

fnd_global.apps_initialize (0, 50744, 200);

mo_global.init ('SQLAP');

fnd_client_info.set_org_context (81);

lv_bank_name := 'Doha Bank'; --'TEST SUPPLIER BANK';

lv_bank_branch := 'Main Branch'; --'TEST BRANCH';

lv_bank_acct_num := '123456789';

lv_bank_acct_name := 'TEST SUPP BANK ACCT';

lv_vendor_name := 'Ajay G Singh'; --'TEST SUPPLIER';

-- Nullify temp variables

ln_bank_id := NULL; */

ln_branch_id := NULL;

lv_bank_home_country_code := NULL;

ln_acct_owner_party_id := NULL;

xx_emp_as_spplr_crtn_pkg.output

('..rssd');

begin

SELECT DISTINCT DECODE

(hl.meaning,

'HSBC, Qatar', '4152',

'Qatar National Bank (QNB)', '25494',

'Commercial Bank of Qatar (CBQ)', '4048',

'Doha Bank', '4052',

'International Bank of Qatar (IBQ)', '4055',

'Qatar International Islamic Bank (QIIB)', '4356',

'NOT_DEFINED'

) bank_party_id, --bank_name,

-- pea.segment2 branch_name,

-- NVL (pea.segment3, 'Blank') account_type,

pea.segment4 IBAN_NO

into lv_bank_party_id, LV_IBAN_NUM

FROM per_all_people_f papf,

per_all_assignments_f paaf,

hr_lookups hl,

pay_external_accounts pea,

pay_personal_payment_methods_f pp

WHERE 1 = 1

AND pp.external_account_id = pea.external_account_id

AND papf.person_id = paaf.person_id

AND pp.assignment_id = paaf.assignment_id

AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

AND papf.effective_end_date

AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND TRUNC (SYSDATE) BETWEEN pp.effective_start_date

AND pp.effective_end_date

AND papf.current_employee_flag = 'Y'

AND paaf.primary_flag = 'Y'

AND papf.person_id = p_emp_id

AND hl.lookup_type = 'XXNEBRAS_BANK_SWIFT_CODE'

AND SUBSTR (hl.lookup_code, 1, 4) = SUBSTR (pea.segment4, 5, 4)

AND pea.segment4 IS NOT NULL;

exception when others then

lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found Employees bank account details in Employee-Assignment-PayMethod';

end;

--Get person_id

BEGIN

select aps.party_id into ln_acct_owner_party_id from ap_suppliers aps

where aps.vendor_id=l_vendor_id

and rownum <2;

exception when no_data_found then

ln_acct_owner_party_id:=l_party_id;

when OTHERS then

-- ln_acct_owner_party_id := null;

lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found The Suppliers PARTY_ID';

END;

begin

SELECT papf.full_name into lv_bank_acct_name

FROM per_all_people_f papf

WHERE papf.business_group_id = g_business_group_id

AND papf.person_id = p_emp_id

and rownum <2;

exception when no_data_found then

lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found lv_bank_acct_name';

end;

-- Get bank details

/* BEGIN

SELECT distinct bank_home_country, bank_party_id --, pk_id

INTO lv_bank_home_country_code, ln_bank_id--, ln_branch_id

FROM ce_bank_branches_v

WHERE bank_name like '%' || substr(lv_bank_name,1,10) || '%'; --lv_bank_name

-- AND bank_branch_name = lv_bank_branch;

EXCEPTION

WHEN OTHERS

THEN

lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Not found Bank and Branch details w.r.t. employees bank details in Employee-Master';

xx_emp_as_spplr_crtn_pkg.output

( 'Unable to derive Bank and Branch details'

|| SQLERRM

);

END;*/

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'lv_bank_party_id - LV_IBAN_NUM - ln_acct_owner_party_id - lv_bank_home_country_code :=>',

to_char(lv_bank_party_id)||' - '||LV_IBAN_NUM||' - '||to_char(ln_acct_owner_party_id)||' - '||to_char(lv_bank_home_country_code),substr(SQLERRM,1,399));

-- Get account owner id

/* BEGIN

SELECT party_id

INTO ln_acct_owner_party_id

FROM hz_parties

WHERE

(party_name = lv_vendor_name or party_id=76526)

;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('Unable to derive Account Owner Infor' ||

SQLERRM);

END;*/

--Bank Branch Validation

BEGIN

select length(trim(LV_IBAN_NUM)) into l_lngth_of_iban

from dual;

if l_lngth_of_iban <> 29 --QA--Qatar

then

lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- IBAN No not valid for QATAR';

end if;

if nvl(lv_bank_party_id,0)>0 then

begin

SELECT cbbv.branch_party_id--, cbbv.bank_branch_name

into ln_branch_id

FROM ce_bank_branches_v cbbv

WHERE cbbv.bank_party_id = lv_bank_party_id AND ROWNUM < 2;

exception when others then

ln_branch_id:=0;

lv_bank_crt_error_msg:=lv_bank_crt_error_msg||'- Bank Branch Not Found';

end;

end if;

END;

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'BeforeBankAPI Call lv_bank_crt_error_msg',lv_bank_crt_error_msg,substr(SQLERRM,1,399));

if lv_bank_crt_error_msg is null then

-- Assign API parameters

p_api_version := 1.0;

p_init_msg_list := fnd_api.g_true;

p_ext_bank_acct_rec.country_code := 'QA';--lv_bank_home_country_code;

p_ext_bank_acct_rec.bank_id := lv_bank_party_id;-- ln_bank_id; -- bank_id

p_ext_bank_acct_rec.branch_id := ln_branch_id; -- branch_id--@@ commented

p_ext_bank_acct_rec.acct_owner_party_id := NVL(ln_acct_owner_party_id,l_party_id);--party_id in ap_suupliers;

p_ext_bank_acct_rec.bank_account_name :=lv_bank_acct_name;--'Employees Bank';-- lv_bank_acct_name; --@@ commented

p_ext_bank_acct_rec.bank_account_num := LV_IBAN_NUM;

p_ext_bank_acct_rec.currency := 'QAR';-- bank_acct_currency

p_ext_bank_acct_rec.start_date := SYSDATE;--Employee Effective Start date

p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';

p_ext_bank_acct_rec.iban:=LV_IBAN_NUM;

-- p_ext_bank_acct_rec.PAYMENT_FACTOR_FLAG:='N';

xx_emp_as_spplr_crtn_pkg.output('calling Bank creation API');

iby_ext_bankacct_pub.create_ext_bank_acct

(p_api_version => p_api_version,

p_init_msg_list => p_init_msg_list,

p_ext_bank_acct_rec => p_ext_bank_acct_rec,

x_acct_id => x_acct_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_response => x_response

);

xx_emp_as_spplr_crtn_pkg.output ('X_ACCT_ID = ' || x_acct_id);

xx_emp_as_spplr_crtn_pkg.output ('X_RETURN_STATUS = ' || x_return_status);

xx_emp_as_spplr_crtn_pkg.output ('X_MSG_COUNT = ' || x_msg_count);

xx_emp_as_spplr_crtn_pkg.output ('X_MSG_DATA = ' || x_msg_data);

xx_emp_as_spplr_crtn_pkg.output ( 'X_RESPONSE.Result_Code = '

|| x_response.result_code

);

xx_emp_as_spplr_crtn_pkg.output ( 'X_RESPONSE.Result_Category = '

|| x_response.result_category

);

xx_emp_as_spplr_crtn_pkg.output ( 'X_RESPONSE.Result_Message = '

|| x_response.result_message

);

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'01','X_ACCT_ID - X_RETURN_STATUS - '||to_char(X_ACCT_ID)||' - '||X_RETURN_STATUS,substr(SQLERRM,1,399));

IF (x_return_status <> fnd_api.g_ret_sts_success)

THEN

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

l_msg :=

fnd_msg_pub.get (p_msg_index => i,

p_encoded => fnd_api.g_false

);

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'BankAPIError',l_msg,substr(SQLERRM,1,399));

xx_emp_as_spplr_crtn_pkg.output ('The Bank Creation API call failed with error '

|| l_msg

);

END LOOP;

ELSE

IF x_acct_id is not null then

xx_emp_as_spplr_crtn_pkg.SET_PAYEE_INSTR_ASSIGNMENT_P(l_vendor_id,l_vendor_site_id,p_org_id,x_acct_id);

end if;

xx_emp_as_spplr_crtn_pkg.output ('The Bank Creation API call ended with SUCESSS status');

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Ends','The Bank Creation API call ended with SUCESSS status',substr(SQLERRM,1,399));

END IF;

COMMIT;

end if;--lv_bank_crt_error_msg is null then Ends

EXCEPTION

WHEN OTHERS

THEN

-- ROLLBACK;

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Ends-Bank-Exception','Bank Creation API excception',substr(SQLERRM,1,399));

xx_emp_as_spplr_crtn_pkg.output ('Bank Creation API excception ' || SQLERRM);

END;

--=====end adding bank details

xx_emp_as_spplr_crtn_pkg.output ('*************** END ***********************');

EXCEPTION

WHEN OTHERS

THEN

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.MAIN',g_conc_request_id,'Ends-Main-Exception','Main excception',substr(SQLERRM,1,399));

xx_emp_as_spplr_crtn_pkg.output (' Error in Main- '||dbms_utility.format_error_backtrace||SQLERRM );

retcode := '2';

errbuf := ' Error in Main- ' || SQLERRM;

END main;

PROCEDURE LOG (p_msg VARCHAR2)

BEGIN

IF fnd_global.conc_login_id = -1

THEN

-- NULL;

dbms_output.put_line(p_msg);

ELSE

fnd_file.put_line (fnd_file.LOG, p_msg);

END IF;

END LOG;

PROCEDURE output (p_msg VARCHAR2)

BEGIN

IF fnd_global.conc_login_id = -1

THEN

dbms_output.put_line(p_msg);

-- NULL;

ELSE

fnd_file.put_line (fnd_file.output, p_msg);

END IF;

END output;

procedure SET_PAYEE_INSTR_ASSIGNMENT_P

(P_vendor_id in number,

P_vendor_site_id in number,

p_org_id in number,

P_ext_bank_account_id in NUMBER

)IS

p_api_version NUMBER;

p_init_msg_list VARCHAR2 (200);

p_commit VARCHAR2 (200);

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_payee apps.iby_disbursement_setup_pub.payeecontext_rec_type;

p_assignment_attribs apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;

lr_ext_bank_acct_dtl iby_ext_bank_accounts%ROWTYPE;

x_assign_id NUMBER;

x_response apps.iby_fndcpt_common_pub.result_rec_type;

lv_vendor_site_code VARCHAR2 (100);

lv_vendor_name VARCHAR2 (100);

lv_bank_acct_name VARCHAR2 (100);

lv_supp_site_id VARCHAR2 (100);

lv_supp_party_site_id VARCHAR2 (100);

lv_acct_owner_party_id VARCHAR2 (100);

lv_org_id VARCHAR2 (100);

l_msg VARCHAR2 (200);

lv_error_msg varchar2(2500):=NULL;

BEGIN

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.SET_PAYEE_INSTR_ASSIGNMENT_P',g_conc_request_id,'Enterred','P_vendor_id - P_vendor_site_id - p_org_id - P_ext_bank_account_id'

||P_vendor_id||' - '||P_vendor_site_id||' - '||p_org_id||' - '||P_ext_bank_account_id,substr(SQLERRM,1,399));

-- Initialize apps session

fnd_global.apps_initialize (0, 50744, 200);

mo_global.init ('SQLAP');

fnd_client_info.set_org_context (81);

-- Input values

-- lv_vendor_site_code := 'TEST SUPPLIER SITE';

-- lv_vendor_name := 'TEST SUPPLIER';

-- lv_bank_acct_name := 'TEST SUPP BANK ACCT';

-- Assign API parameters

p_api_version := 1.0;

p_init_msg_list := fnd_api.g_true;

p_commit := fnd_api.g_true;

-- get ext bank account details

BEGIN

DBMS_OUTPUT.put_line('******************Starts*******************');

SELECT *

INTO lr_ext_bank_acct_dtl

FROM iby_ext_bank_accounts

WHERE 1=1

-- AND bank_account_name = lv_bank_acct_name

and ext_bank_account_id=P_ext_bank_account_id

and rownum <2;

EXCEPTION

WHEN OTHERS

THEN

lv_error_msg:=lv_error_msg||' - Unable to derive the external bank details';

DBMS_OUTPUT.put_line

( 'Unable to derive the external bank details:'

|| SQLERRM

);

END;

-- get supplier details

BEGIN

SELECT assa.vendor_site_id, assa.party_site_id, aps.party_id,

assa.org_id

INTO lv_supp_site_id, lv_supp_party_site_id, lv_acct_owner_party_id,

lv_org_id

FROM ap_suppliers aps, ap_supplier_sites_all assa

WHERE aps.vendor_id = assa.vendor_id

-- AND aps.vendor_name = lv_vendor_name

-- AND assa.vendor_site_code = lv_vendor_site_code

and ( assa.VENDOR_SITE_ID=P_vendor_site_id OR aps.vendor_id= P_vendor_id)

and rownum <2;

EXCEPTION

WHEN OTHERS

THEN

lv_error_msg:=lv_error_msg||' - Error at Get supplier party_id';

DBMS_OUTPUT.put_line

( 'Error- Get supp_site_id and supp_party_site_id'

|| SQLCODE

|| SQLERRM

);

END;

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.SET_PAYEE_INSTR_ASSIGNMENT_P',g_conc_request_id,'lv_error_msg :=> ',substr(lv_error_msg,1,399),substr(SQLERRM,1,399));

if lv_error_msg is null then

-- Assign payee values

p_payee.supplier_site_id := null;--lv_supp_site_id;

p_payee.party_id := lv_acct_owner_party_id;

p_payee.party_site_id :=null;-- lv_supp_party_site_id;

p_payee.payment_function := 'PAYABLES_DISB';

p_payee.org_id := null;--lv_org_id;

p_payee.org_type :=null;-- 'OPERATING_UNIT';

-- Assignment Values

p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';

p_assignment_attribs.instrument.instrument_id :=

lr_ext_bank_acct_dtl.ext_bank_account_id;

-- External Bank Account ID

p_assignment_attribs.priority := 1;

p_assignment_attribs.start_date := SYSDATE;

iby_disbursement_setup_pub.set_payee_instr_assignment

(p_api_version => p_api_version,

p_init_msg_list => p_init_msg_list,

p_commit => p_commit,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_payee => p_payee,

p_assignment_attribs => p_assignment_attribs,

x_assign_id => x_assign_id,

x_response => x_response

);

DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

DBMS_OUTPUT.put_line ('X_ASSIGN_ID = ' || x_assign_id);

DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Code = ' || x_response.result_code);

DBMS_OUTPUT.put_line ( 'X_RESPONSE.Result_Category = '

|| x_response.result_category

);

DBMS_OUTPUT.put_line ( 'X_RESPONSE.Result_Message = '

|| x_response.result_message

);

IF (x_return_status <> fnd_api.g_ret_sts_success --or x_response.result_message is not null

)

THEN

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

l_msg :=

fnd_msg_pub.get (p_msg_index => i,

p_encoded => fnd_api.g_false);

DBMS_OUTPUT.put_line ('The API call failed with error ' || l_msg);

END LOOP;

ELSE

DBMS_OUTPUT.put_line ('The API call ended with SUCESSS status');

END IF;

end if;

exception when others then

xxpk_debug_prc('XX_EMP_AS_SPPLR_CRTN_PKG.SET_PAYEE_INSTR_ASSIGNMENT_P',g_conc_request_id,'Ends-Main-Exception','Main excception',substr(SQLERRM,1,399));

xx_emp_as_spplr_crtn_pkg.output (' Error in SET_PAYEE_INSTR_ASSIGNMENT_P- '||dbms_utility.format_error_backtrace||SQLERRM );

-- retcode := '2';

-- errbuf := ' Error in SET_PAYEE_INSTR_ASSIGNMENT_P- ' || SQLERRM;

END set_payee_instr_assignment_p;

procedure create_external_payee_P

(

P_VENDOR_ID in number,

p_org_id in number,

P_ext_payee_id in number -- iby_pmt_instr_uses_all.ext_pmt_party_id

)IS

BEGIN

NULL;

END create_external_payee_P;

PROCEDURE create_ce_bank_branch (

errbuf OUT VARCHAR2,

retcode OUT NUMBER,

p_bank_party_id IN NUMBER,

p_branch_name IN VARCHAR2,

p_branch_type IN VARCHAR2,

p_bank_home_country_code IN VARCHAR2

)

p_init_msg_list VARCHAR2 (200);

-- p_bank_id NUMBER;

-- p_branch_name VARCHAR2 (200);

p_branch_number VARCHAR2 (200);

-- p_branch_type VARCHAR2 (200);

p_alternate_branch_name VARCHAR2 (200);

p_description VARCHAR2 (200);

p_rfc_identifier VARCHAR2 (200);

x_branch_id NUMBER;

x_return_status VARCHAR2 (200);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (200);

p_count NUMBER;

BEGIN

p_init_msg_list := fnd_api.g_true;

-- p_branch_name := 'XXPKTest Branch';

-- p_alternate_branch_name := 'XXPKTestBranch Name';

-- p_description := 'XXPKTest Branch description';

-- branch number/Routing Transit Number is subjected to

-- the check digit validation

-- p_branch_number := 111000025;

-- Validated against AR_LOOKUPS with lookup_type = BANK_BRANCH_TYPE

-- p_branch_type := 'ABA';

-- Validated against CE_LOOKUPS with lookup_type = RFC_IDENTIFIER

p_rfc_identifier := 'AFC';

CE_BANK_PUB.CREATE_BANK_BRANCH

(p_init_msg_list => p_init_msg_list,

p_bank_id => p_bank_party_id,

p_branch_name => p_branch_name,

p_branch_number => p_branch_number,

p_branch_type => p_branch_type,

-- p_alternate_branch_name => p_alternate_branch_name,

-- p_description => p_description,

p_rfc_identifier => p_rfc_identifier,

x_branch_id => x_branch_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

DBMS_OUTPUT.put_line ('BRANCH_ID/PARTY_ID = ' || x_branch_id);

DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

IF x_msg_count = 1

THEN

DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);

ELSIF x_msg_count > 1

THEN

LOOP

p_count := p_count + 1;

x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF x_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);

END LOOP;

END IF;

commit;

exception when others then

rollback;

END create_ce_bank_branch;

END xx_emp_as_spplr_crtn_pkg;

Sunday, February 2, 2020

Kill Multiple Oracle Sessions both active and inactive by Loop

--Kill Multiple Oracle Sessions both active and inactive by Loop

BEGIN
FOR r IN (select sid,serial# from v$session where username = 'USER')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid
|| ',' || r.serial# || '''';
END LOOP;
END;

Tuesday, January 28, 2020

Assignment_Set_Creation_API

Assignment_Set_Creation_API

--Assignment_Set_Creation_API
DECLARE
l_payroll_id NUMBER := 61;
l_assignment_set_name VARCHAR2 (500) := 'XXPK_ASSIGNMENT_SET_API';
out_assignment_set_id NUMBER := NULL;
BEGIN

BEGIN
hr_jp_ast_utility_pkg.
create_asg_set (p_assignment_set_name => l_assignment_set_name,
p_business_group_id => 81,
p_payroll_id => l_payroll_id,
p_assignment_set_id => out_assignment_set_id );
COMMIT;

DBMS_OUTPUT.put_line ( out_assignment_set_id || ' has been Created Successfully !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

Sunday, January 26, 2020

Workflow Item Type And Workflow Item Key Wise Approval History Find SQL

--Workflow Item Type And Workflow Item Key Wise Approval History Find SQL

SELECT ROWNUM h_sequence, h_notification_id, h_from_role, h_from_user,
h_to_role, h_to_user, h_action, h_comment, h_action_date,
activity_status, activity_result_code,item_type,item_key
FROM (SELECT h_notification_id, h_from_role, h_from_user, h_to_role,
h_to_user, h_action, h_comment, h_action_date,
activity_status, activity_result_code,item_type,item_key
FROM (SELECT 99999999 h_sequence,
ias.notification_id h_notification_id,
ias.assigned_user h_from_role,
wf_directory.getroledisplayname2
(ias.assigned_user)
h_from_user,
'WF_SYSTEM' h_to_role,
wf_core.TRANSLATE ('WF_SYSTEM') h_to_user,
wf_core.activity_result
(a.result_type,
ias.activity_result_code
) h_action,
(SELECT text_value
FROM wf_notification_attributes
WHERE notification_id =
ias.notification_id
AND NAME = 'WF_NOTE') h_comment,
NVL (to_char(ias.end_date,'DD-MON-YYYY'), to_char(ias.begin_date,'DD-MON-YYYY')) h_action_date,
ias.activity_status, ias.activity_result_code,ias.item_type,ias.item_key
FROM wf_item_activity_statuses ias,
wf_activities a,
wf_process_activities pa,
wf_items i
WHERE ias.item_type LIKE '%'||:P_ITEM_TYPE||'%'-- 'XXSUPREQ'--'XXMPRFWF' --item_type
AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1--tem_key
--AND ias.process_activity = :process_activity
AND ias.notification_id IS NOT NULL --added
AND ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_result_code IS NOT NULL
AND ias.activity_result_code NOT IN
('#EXCEPTION',
'#FORCE',
'#MAIL',
'#NULL',
'#STUCK',
'#TIMEOUT'
)
AND i.begin_date BETWEEN a.begin_date
AND NVL (a.end_date, i.begin_date)
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.NAME
AND pa.activity_item_type = a.item_type
UNION ALL
SELECT 99999999 h_sequence,
ias.notification_id h_notification_id,
ias.assigned_user h_from_role,
wf_directory.getroledisplayname2
(ias.assigned_user)
h_from_user,
'WF_SYSTEM' h_to_role,
wf_core.TRANSLATE ('WF_SYSTEM') h_to_user,
wf_core.activity_result
(a.result_type,
ias.activity_result_code
) h_action,
(SELECT text_value
FROM wf_notification_attributes
WHERE notification_id =
ias.notification_id
AND NAME = 'WF_NOTE') h_comment,
NVL (to_char(ias.end_date,'DD-MON-YYYY'), to_char(ias.begin_date,'DD-MON-YYYY')) h_action_date,
ias.activity_status, ias.activity_result_code,ias.item_type,ias.item_key
FROM wf_item_activity_statuses_h ias,
wf_activities a,
wf_process_activities pa,
wf_items i
WHERE ias.item_type like '%'||:P_ITEM_TYPE||'%'--'XXSUPREQ'--'XXMPRFWF' --item_type
AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1 --item_key
--AND ias.process_activity = :process_activity
AND ias.notification_id IS NOT NULL --added
AND ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_result_code IS NOT NULL
AND ias.activity_result_code NOT IN
('#EXCEPTION',
'#FORCE',
'#MAIL',
'#NULL',
'#STUCK',
'#TIMEOUT'
)
AND i.begin_date BETWEEN a.begin_date
AND NVL (a.end_date, i.begin_date)
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.NAME
AND pa.activity_item_type = a.item_type
UNION ALL
SELECT c.SEQUENCE h_sequence,
c.notification_id h_notification_id,
c.from_role h_from_role, c.from_user h_from_user,
c.to_role h_to_role, c.to_user h_to_user,
wf_core.TRANSLATE (c.action) h_action,
c.user_comment h_comment,
to_char(c.comment_date,'DD-MON-YYYY') h_action_date, ias.activity_status,
ias.activity_result_code,ias.item_type,ias.item_key
FROM wf_item_activity_statuses ias, wf_comments c
WHERE ias.item_type like '%'||:P_ITEM_TYPE||'%'--'XXSUPREQ'-- 'XXMPRFWF' --item_type
AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1 --item_key
--AND ias.process_activity = :process_activity
AND ias.notification_id IS NOT NULL --added
AND ias.notification_id = c.notification_id
AND c.action NOT IN
('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
UNION ALL
SELECT c.SEQUENCE h_sequence,
c.notification_id h_notification_id,
c.from_role h_from_role, c.from_user h_from_user,
c.to_role h_to_role, c.to_user h_to_user,
wf_core.TRANSLATE (c.action) h_action,
c.user_comment h_comment,
to_char(c.comment_date,'DD-MON-YYYY') h_action_date, ias.activity_status,
ias.activity_result_code,ias.item_type,ias.item_key
FROM wf_item_activity_statuses_h ias, wf_comments c
WHERE ias.item_type like '%'||:P_ITEM_TYPE||'%'--'XXSUPREQ'--'XXMPRFWF' --item_type
AND ias.item_key LIKE '%'||:P_ITEM_KEY||'%' --:1 --item_key
--AND ias.process_activity = :process_activity
AND ias.notification_id IS NOT NULL --added
AND ias.notification_id = c.notification_id
AND c.action NOT IN
('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND'))
ORDER BY h_notification_id, h_sequence)
WHERE h_action NOT IN ('Yes', 'No', 'Cancel')
AND NVL (activity_result_code, 'X') NOT LIKE '%NULL%'

AR Open Receipt Apply to other Receipt by ar_receipt_api_pub.Apply_Open_Receipt API Example

AR Open Receipt Apply to other Receipt by ar_receipt_api_pub.Apply_Open_Receipt API Example

=======================================================================
DECLARE
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts
v_r2r_return_status VARCHAR2 (1);
v_r2r_msg_count NUMBER;
v_r2r_msg_data VARCHAR2 (240);
lv_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
lv_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
lv_applied_rec_app_id ar_receivable_applications.receivable_application_id%TYPE;
lv_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
lv_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;

cursor c_open_rcpts (cp_cus VARCHAR2) IS

SELECT DISTINCT aou.NAME operating_unit, hp.party_name customer_name,
hca.account_number customer_number, arca.receipt_number,
arca.cash_receipt_id,
(g.amount_due_remaining * NVL (g.exchange_rate, 1)
) amount_due_remaining,
arca.org_id
/*,
crh_first_posted.gl_date gl_date, crh_first_posted.gl_date trx_date, arca.status,
arca.TYPE receipt_type, arca.currency_code, arca.receipt_number,
arca.receipt_date, hp.party_name received_from,
hca.account_number customer_no, arca.amount entered_amount,
crh_current.acctd_amount functional_amount,
-1 * (crh_current.amount) total_net_amount, arm.NAME payment_method,
apbb.bank_name bank_account_name,
apbaa.bank_account_num bank_account_num, arca.comments description*/
FROM hz_parties hp,
hz_cust_accounts_all hca,
ar_cash_receipts_all arca,
ar_receipt_methods arm,
ar_receipt_classes arc,
ap_bank_accounts_all apbaa,
ap_bank_branches apbb,
ar_cash_receipt_history_all crh_first_posted,
ar_cash_receipt_history_all crh_current,
hr_all_organization_units aou,
ar_payment_schedules_all g
-- ,hz_party_sites hps,
-- hz_cust_site_uses_all hcsua,
-- hz_cust_acct_sites_all hcas
WHERE 1 = 1
AND arca.org_id = aou.organization_id(+)
-- AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
-- AND hps.party_site_id = hcas.party_site_id
-- and arca.CUSTOMER_SITE_USE_ID=hcsua.BILL_TO_SITE_USE_ID
AND arca.cash_receipt_id = 9882804 --9882805--9882804
AND NVL (arca.status, 'ZZ') <> 'APP' --Open Only
-- AND hca.ACCOUNT_NUMBER=cp_cus
AND arca.pay_from_customer = hca.cust_account_id(+)
AND hp.party_id(+) = hca.party_id
AND arca.remittance_bank_account_id = apbaa.bank_account_id(+)
AND apbb.bank_branch_id(+) = apbaa.bank_branch_id
AND arca.receipt_method_id = arm.receipt_method_id
AND arm.receipt_class_id = arc.receipt_class_id
-- AND crh_first_posted.batch_id = bat.batch_id(+)
-- AND crh_first_posted.org_id = bat.org_id(+)
-- AND bat.TYPE(+) = 'MANUAL'
AND crh_first_posted.cash_receipt_id(+) = arca.cash_receipt_id
AND crh_first_posted.org_id(+) = arca.org_id
AND crh_first_posted.first_posted_record_flag(+) = 'Y'
-- AND crh_first_posted.batch_id = bat_br.batch_id(+)
-- AND crh_first_posted.org_id = bat_br.org_id(+)
AND crh_current.cash_receipt_id = arca.cash_receipt_id
AND crh_current.org_id = arca.org_id
AND crh_current.current_record_flag =
NVL ('Y', arca.receipt_number)
-- and crh_first_posted.gl_date between to_date('01-JAN-2019') and to_date('31-DEC-2019')
--and to_char(crh_first_posted.gl_date,'YYYY')='2019'
AND arca.org_id = 103 ;--MTC
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
BEGIN

fnd_global.apps_initialize (0,
52219,--MTC Receivables Administrator
222,--Receivables
103
);
mo_global.set_policy_context ('S', 103);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Starts
--For yz in c_open_rcpts () loop
--RECEIPT-A :=>To which the open RECEIPT have to apply
ar_receipt_api_pub.apply_open_receipt
(
-- Standard API parameters.
p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => apps.fnd_api.g_true,
p_validation_level => apps.fnd_api.g_valid_level_full,--100,
x_return_status => v_r2r_return_status,
x_msg_count => v_r2r_msg_count,
x_msg_data => v_r2r_msg_data,
-- Receipt application parameters.
p_cash_receipt_id => 9882805,--To which the open RECEIPT have to apply
p_receipt_number => 'RECEIPT-A', --To which the open RECEIPT have to apply
p_applied_payment_schedule_id => NULL,
p_open_cash_receipt_id => 9882804,--Non Zero Amount Cursor Rcpt
p_open_receipt_number => 'RECEIPT-OPEN' ,--Non Zero Amount Cursor Rcpt
p_open_rec_app_id => null,--NONE,
p_amount_applied => -20000,--Non Zero Amount Cursor Rcpt Amount
p_apply_date => TRUNC (SYSDATE),
p_apply_gl_date => TRUNC (SYSDATE),
p_ussgl_transaction_code => null,--NONE,
p_attribute_rec => NULL,
-- ******* Global Flexfield parameters *******
p_global_attribute_rec => null,--NONE,
p_comments => 'Reciept to reciept Application by API',
p_org_id =>103,
x_application_ref_num =>lv_application_ref_num,
x_receivable_application_id =>lv_receivable_application_id,
x_applied_rec_app_id =>lv_applied_rec_app_id,
x_acctd_amount_applied_from =>lv_acctd_amount_applied_from,
x_acctd_amount_applied_to =>lv_acctd_amount_applied_to
);
Commit;
-- end loop;
dbms_output.put_line('v_r2r_msg_data :-'||v_r2r_msg_data);
dbms_output.put_line('x_application_ref_num :-'||lv_application_ref_num);
dbms_output.put_line('x_receivable_application_id :-'||lv_receivable_application_id);
dbms_output.put_line('x_applied_rec_app_id :-'||lv_applied_rec_app_id);
dbms_output.put_line('x_acctd_amount_applied_from :-'||lv_acctd_amount_applied_from);
dbms_output.put_line('x_acctd_amount_applied_to :-'||lv_acctd_amount_applied_to);
dbms_output.put_line('API Completed Successfully :-'||SQLERRM);
--Added By Pavankumar Bondage For Reciept to Reciept Application 26-Jan-20 Ends
-- commit;
exception when others then
dbms_output.put_line('Main Exception :-'||SQLERRM);
END;

Monday, January 20, 2020

GRN AP receipt Transaction Type wise (Deliver/Accept/Reject/RTV) Quantity Calculate Package

GRN AP receipt Transaction Type wise (Deliver/Accept/Reject/RTV) Quantity Calculate Package
==========
CREATE OR REPLACE PACKAGE APPS.XX_RCV_TRX_GENERIC_PKG AS
/* Created by Pavankumar Bondage 20-Aug-2016 bondagepm.erp@gmail.com */

FUNCTION GRN_LINE_IL_TAX_AMT(p_shipment_header_id number,
p_shipment_line_id number)
return number;

/* Following function to get the quantiy of grn line by transaction type wise like ACCEPT,REJECT,CORRECT,Deliver*/
-- syntax to use
-- select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
-- from dual
FUNCTION trx_actual_qty (
p_shipment_header_id NUMBER,
p_shipment_line_id NUMBER,
p_transaction_type VARCHAR2
) RETURN NUMBER;
END XX_RCV_TRX_GENERIC_PKG;
/

======================================
CREATE OR REPLACE PACKAGE BODY APPS.XX_RCV_TRX_GENERIC_PKG AS

/* Created by Pavankumar Bondage 20-Aug-2016 bondagepm.erp@gmail.com */

FUNCTION GRN_LINE_IL_TAX_AMT(p_shipment_header_id number,
p_shipment_line_id number)
RETURN NUMBER IS

/* corsor for recive transactions */
cursor c1 is
select rt1.TRANSACTION_ID,rt1.PARENT_TRANSACTION_ID,
NVL (rsl1.uom_conversion_rate, 1) conversion_rate,
rt1.currency_code
from rcv_transactions rt1,
rcv_shipment_headers rsh1,
rcv_shipment_lines rsl1
where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
and rt1.SHIPMENT_HEADER_ID=p_shipment_header_id
and rt1.SHIPMENT_LINE_ID=p_shipment_line_id
AND rt1.transaction_type = 'RECEIVE';

/* Cursor c1 to calculate tax amount*/
cursor c2(p_parent_tran_id number) is
select rt1.TRANSACTION_ID,
NVL (rsl1.uom_conversion_rate, 1) conversion_rate,
rt1.currency_code
from rcv_transactions rt1,
rcv_shipment_headers rsh1,
rcv_shipment_lines rsl1
where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
and rt1.PARENT_TRANSACTION_ID=p_parent_tran_id--111606
and rt1.SHIPMENT_HEADER_ID=p_shipment_header_id
and rt1.SHIPMENT_LINE_ID=p_shipment_line_id
and rt1.TRANSACTION_TYPE='CORRECT';
v_tax_amt_f number :=0;
v_tax_amt number:=0;
v_base_tax_amt number:=0;
v_tax_amt_c number:=0;

BEGIN

for i in c1 loop

SELECT jai_rcv_trx_processing_pkg.get_trxn_tax_amount
(i.TRANSACTION_ID,
p_shipment_line_id,
i.CONVERSION_RATE,
i.CURRENCY_CODE
)
into v_base_tax_amt
from dual;

for j in c2(i.TRANSACTION_ID)loop
SELECT jai_rcv_trx_processing_pkg.get_trxn_tax_amount
(j.TRANSACTION_ID,
p_shipment_line_id,
j.CONVERSION_RATE,
j.CURRENCY_CODE
)
into v_tax_amt
from dual;
v_tax_amt_c:=v_tax_amt_c+v_tax_amt;

end loop;

v_tax_amt_f:=v_base_tax_amt+v_tax_amt_c;
end loop;

/* select rt1.TRANSACTION_ID,rt1.PARENT_TRANSACTION_ID,
NVL (rsl1.uom_conversion_rate, 1) conversion_rate,rsh1.RECEIPT_NUM,
rt1.currency_code,grn.GRN_TAX_AMT(rsh1.SHIPMENT_HEADER_ID,rsl1.SHIPMENT_LINE_ID ) tax_amount,rsl1.LINE_NUM
from rcv_transactions rt1,
rcv_shipment_headers rsh1,
rcv_shipment_lines rsl1
where rt1.SHIPMENT_HEADER_ID=rsh1.SHIPMENT_HEADER_ID
and rt1.SHIPMENT_LINE_ID=rsl1.SHIPMENT_LINE_ID
-- and rt1.SHIPMENT_HEADER_ID=48874
--and rt1.SHIPMENT_LINE_ID=61233
AND rt1.transaction_type = 'RECEIVE'
order by rsh1.RECEIPT_NUM,rsl1.LINE_NUM*/

return v_tax_amt_f;
exception
when others then
return (-1);

END GRN_LINE_IL_TAX_AMT;
-- syntax to use
-- select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
-- from dual
FUNCTION trx_actual_qty (
p_shipment_header_id NUMBER,
p_shipment_line_id NUMBER,
p_transaction_type VARCHAR2)
RETURN NUMBER
IS
v_qty NUMBER:=0;
v_qty_f NUMBER;
v_transaction_type VARCHAR2 (30);
v_transaction_id NUMBER;
v_count number;
v_routing_name varchar2(30);
c_qty number:=0;
ret_rcv_qty number:=0;
v_correct_qty number:=0;
v_rtv_qty number:=0;

CURSOR c1
IS
SELECT rt.*
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rsh.shipment_header_id =
p_shipment_header_id
--rsh.RECEIPT_NUM='5102140004389'
AND rt.shipment_line_id = p_shipment_line_id --61178
AND rt.transaction_type = p_transaction_type;
BEGIN
v_qty_f := 0;

FOR r1 IN c1
LOOP

begin
SELECT count(1)
into v_count
FROM rcv_transactions rt
WHERE rt.parent_transaction_id = r1.transaction_id;
exception
when no_data_found then
v_count:=0;
end;

if v_count>0 then

FOR i IN (SELECT rt.transaction_type, rt.transaction_id,rt.QUANTITY
FROM rcv_transactions rt
WHERE rt.parent_transaction_id = r1.transaction_id)

LOOP

if i.transaction_type='RETURN TO RECEIVING'then

ret_rcv_qty:=ret_rcv_qty+(-1*i.QUANTITY);

elsif i.transaction_type='CORRECT' then

v_correct_qty:=v_correct_qty+i.QUANTITY;
elsif i.transaction_type='RETURN TO VENDOR' then
v_rtv_qty:=v_rtv_qty+(-1*i.QUANTITY);
end if;

end loop;
v_qty:=nvl(ret_rcv_qty,0)+nvl(v_correct_qty,0)+nvl(v_rtv_qty,0)+nvl(r1.QUANTITY,0);
elsif v_count=0 then

v_qty:=r1.QUANTITY;

end if;

v_qty_f := v_qty;--+r1.quantity;
END LOOP; --cursor c1 loop end

RETURN v_qty_f;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN (-1);

-- syntax to use
-- select XX_RCV_TRX_GENERIC_PKG.TRX_ACTUAL_QTY(:P_SHIPMENT_HEADER_ID,:P_SHIPMENT_LINE_ID,:P_TRX_TYPE) actual_qnty
-- from dual
END trx_actual_qty;

END XX_RCV_TRX_GENERIC_PKG;
/

Sample MDS repository Path of Seeded(Standard) OAF Pages

--Path of standard (seeded) absence path

/appltest/appl/apps/apps_st/appl/per/12.0.0/mds/absences/absencecase/webui