Oracle Apps KT

Sunday, March 10, 2024

Query GL Apps R12 Difference in two Years group by Months

select * from (

select code_combination_id, PERIOD_NUM, nvl(a3.ENTERED_YEAR_TO_DATE,0) - nvl(a4.ENTERED_YEAR_TO_DATE,0) amount from (

SELECT bal.code_combination_id,

( bal.begin_balance_dr_beq

- bal.begin_balance_cr_beq

+ bal.period_net_dr_beq

- bal.period_net_cr_beq)

ENTERED_YEAR_TO_DATE

,PERIOD_NUM

,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

)code_comb_desc,

(SELECT kfv.concatenated_segments

FROM gl_code_combinations_kfv kfv

WHERE code_combination_id = bal.code_combination_id)segments

FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl

WHERE (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)

AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)

AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)

AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)

AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)

AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL)

AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)

AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)

AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')

AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID

AND bal.LEDGER_ID = gl.LEDGER_ID

AND cc.CHART_OF_ACCOUNTS_ID = 50320

AND bal.LEDGER_ID = 2091

AND cc.TEMPLATE_ID IS NULL

AND :P_YTD_PTD = 'YTD'

AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD)

GROUP BY

PERIOD_NUM,

( bal.begin_balance_dr_beq

- bal.begin_balance_cr_beq

+ bal.period_net_dr_beq

- bal.period_net_cr_beq)

,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

),bal.code_combination_id

) A3

full join (

SELECT bal.code_combination_id,

( bal.begin_balance_dr_beq

- bal.begin_balance_cr_beq

+ bal.period_net_dr_beq

- bal.period_net_cr_beq)

ENTERED_YEAR_TO_DATE

,PERIOD_NUM

,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

)code_comb_desc,

(SELECT kfv.concatenated_segments

FROM gl_code_combinations_kfv kfv

WHERE code_combination_id = bal.code_combination_id)segments

FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl

WHERE (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)

AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)

AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)

AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)

AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)

AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL)

AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)

AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)

AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')

AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID

AND bal.LEDGER_ID = gl.LEDGER_ID

AND cc.CHART_OF_ACCOUNTS_ID = 50320

AND bal.LEDGER_ID = 2091

AND cc.TEMPLATE_ID IS NULL

AND :P_YTD_PTD = 'YTD'

AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD_2 AND :P_TO_PERIOD_2)

GROUP BY

PERIOD_NUM,

( bal.begin_balance_dr_beq

- bal.begin_balance_cr_beq

+ bal.period_net_dr_beq

- bal.period_net_cr_beq)

,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

),bal.code_combination_id

) A4

using(code_combination_id, PERIOD_NUM)

)

pivot (

SUM(AMOUNT) as YTD_DIFF

for PERIOD_NUM in (

'1' as Jan, '2' as Feb, '3' as Mar,

'4' as Apr, '5' as May, '6' as Jun,

'7' as Jul, '8' as Aug, '9' as Sep,

'10' as Oct, '11' as Nov, '12' as Dec, '13' as Adj

)

);

Wednesday, February 28, 2024

Apps R12 API to Create Supplier, Sites with interface Table

CREATE TABLE APPS.XX_VENDOR_API_GL

(

VENDOR_NO VARCHAR2(1000 BYTE),

VENDOR_NAME VARCHAR2(1000 BYTE),

VENDOR_SITE_CODE VARCHAR2(1000 BYTE),

ADDRESS VARCHAR2(1000 BYTE),

CITY VARCHAR2(1000 BYTE),

COUNTRY VARCHAR2(1000 BYTE),

VENDOR_TYPE VARCHAR2(1000 BYTE),

LIABILITY_ACCOUNT VARCHAR2(1000 BYTE),

PREPAID_ACCOUNT VARCHAR2(1000 BYTE),

CONTACT_PERSON VARCHAR2(1000 BYTE),

CONTACT_NUMBER VARCHAR2(1000 BYTE),

PAY_GROUP_CODE VARCHAR2(1000 BYTE),

PAY_DATE_BASIS_CODE VARCHAR2(1000 BYTE),

PAYMENT_CURRENCY_CODE VARCHAR2(1000 BYTE),

PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(1000 BYTE),

PAY_TERMS VARCHAR2(1000 BYTE),

TAX_CODE VARCHAR2(1000 BYTE),

STATUS CHAR(1 BYTE),

NUM NUMBER,

INVOICE_CURRENCY_CODE VARCHAR2(30 BYTE),

VENDOR_ID NUMBER,

VENDOR_SITE_ID NUMBER,

TERM_ID NUMBER

);

SELECT * FROM

XX_VENDOR_API_GL;

SELECT * FROM FND_LOOKUP_VALUES

WHERE --LOOKUP_CODE LIKE 'AU%'AND

LOOKUP_TYPE = 'VENDOR TYPE'

AND LANGUAGE = 'US'

;

SELECT * FROM GL_CODE_COMBINATIONS_V

where LAST_UPDATE_DATE like sysdate

ORDER BY LAST_UPDATE_DATE DESC

;

SELECT AP.*

FROM AP_SUPPLIERS AP, AP_SUPPLIER_SITES_ALL APSA

WHERE AP.VENDOR_ID = APSA.VENDOR_ID

AND ORG_ID = 2400

AND APSA.CREATION_DATE LIKE SYSDATE;

UPDATE XX_VENDOR_API_GL GL

SET GL.VENDOR_ID =

(SELECT AP.VENDOR_ID FROM AP_SUPPLIERS AP

WHERE AP.VENDOR_NAME = GL.VENDOR_NAME);

--------------------------------------------------------------------------------------------------------

declare

l_org_id number := 2400;

l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

l_return_status VARCHAR2(20000);

l_msg_count NUMBER;

l_msg_data VARCHAR2(1000);

l_vendor_id NUMBER;

l_party_id NUMBER;

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_vendor_site_id NUMBER;

l_party_site_id NUMBER;

l_location_id NUMBER;

V_count number:=0;

l_Vendor_No number;

l_liability number;

l_prepaid number;

l_term number;

v_country varchar2(100);

l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;

l_vendor_contact_id NUMBER;

l_per_party_id NUMBER;

l_rel_party_id NUMBER;

l_rel_id NUMBER;

l_org_contact_id NUMBER;

l_msg varchar2(200);

begin

for i in (

--run multiple times, if error appears or records dont move check us and other suppliers

select *

from

XX_VENDOR_API_GL where

-- VENDOR_NAME = '???? ????? ??????' AND

-- NUM NOT IN (1,2,5)

VENDOR_ID IS NULL AND

VENDOR_SITE_ID IS NULL

) loop

dbms_output.put_line(1);

begin

select code_combination_id

into l_liability

from GL_CODE_COMBINATIONS

where SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7=-- '01.000.00.2210000.0001.0000.0000';

i.LIABILITY_ACCOUNT;

exception

when no_data_found then null;

end;

dbms_output.put_line(2);

l_vendor_rec.VENDOR_TYPE_LOOKUP_CODE:='EMPLOYEES';--i.Vendor_Type;

--l_vendor_rec.segment1 := i.Vendor_No;

l_vendor_rec.vendor_name := i.Vendor_Name;

l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE:= 'DUE';

--l_vendor_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_CODE;

l_vendor_rec.PAYMENT_CURRENCY_CODE := 'AED';

l_vendor_rec.invoice_CURRENCY_CODE := 'AED';

--l_vendor_rec.TERMS_NAME:=i.Pay_Terms;

l_vendor_rec.TERMS_ID:=10000;

--l_vendor_rec.ACCTS_PAY_CODE_COMBINATION_ID:=l_liability;

--l_vendor_rec.PREPAY_CODE_COMBINATION_ID:=l_prepaid;

dbms_output.put_line('1--create vendor');

BEGIN

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

);

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;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

dbms_output.put_line('2--create vendor site');

select TERRITORY_CODE--, vl.*

into v_country

from FND_TERRITORIES_VL vl

where TERRITORY_SHORT_NAME = 'United Arab Emirates'; --'China'

l_vendor_site_rec.vendor_id :=l_vendor_id;

l_vendor_site_rec.vendor_site_code :=i.Vendor_Site_Code;

l_vendor_site_rec.address_line1 := 'Fujairah';

l_vendor_site_rec.country := 'AE';

l_vendor_site_rec.city := i.City;

l_vendor_site_rec.org_id := l_org_id;--'121';

l_vendor_site_rec.purchasing_site_flag:='Y';

l_vendor_site_rec.pay_site_flag :='Y';

l_vendor_site_rec.rfq_only_site_flag :='N';

IF i.Country = 'United States' then

l_vendor_site_rec.state := '-' ;

l_vendor_site_rec.county := '-';

end if;

--l_vendor_site_rec.vat_code:=i.tax_Code;

l_vendor_site_rec.TERMS_ID:=10000;

l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE:='DUE';

--l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_CODE;

l_vendor_site_rec.INVOICE_CURRENCY_CODE:= 'AED';

l_vendor_site_rec.PAYMENT_CURRENCY_CODE:= 'AED';

l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := l_liability;

--l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID:=l_prepaid; --AFZAL

--l_vendor_site_rec.PHONE:=i.CONTACT_NUMBER;

--l_vendor_site_rec.AUTO_TAX_CALC_FLAG:='Y';

BEGIN

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

);

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;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);

dbms_output.put_line('return_status: '||l_return_status);

dbms_output.put_line('msg_data: '||l_msg_data);

dbms_output.put_line(7);

if l_return_status = 'S' then

dbms_output.put_line(8);

UPDATE XX_VENDOR_API_GL SET status='Y',VENDOR_ID = l_vendor_id, VENDOR_SITE_ID = l_vendor_site_id

where vendor_name||'.'=i.vendor_name||'.'

and VENDOR_SITE_CODE= i.Vendor_Site_Code;

if i.tax_Code like '%RCM%' then

update ZX_PARTY_TAX_PROFILE set PROCESS_FOR_APPLICABILITY_FLAG='N' ,ALLOW_OFFSET_TAX_FLAG='Y' where party_id=l_party_id;

--dbms_output.put_line(9);

dbms_output.put_line(i.tax_Code);

else

update ZX_PARTY_TAX_PROFILE set PROCESS_FOR_APPLICABILITY_FLAG='Y' ,ALLOW_OFFSET_TAX_FLAG='N' where party_id=l_party_id;

--dbms_output.put_line(10);

dbms_output.put_line(i.tax_Code);

end if;

else

dbms_output.put_line( 'x_return_status: ' || l_return_status);

dbms_output.put_line( 'x_msg_data: ' || l_msg_data);

end if;

COMMIT;

End loop;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('ERROR :'||SQLERRM);

End;

Apps R12 to create Code Combination from Back End.

create or replace function create_ccid
( p_concat_segs in varchar2
) return varchar2
is
-- pragma autonomous_transaction; -- if you need autonomy!
l_keyval_status BOOLEAN;
l_coa_id NUMBER;
l_err_msg varchar2(2000);
l_error varchar2(255);
begin

--SELECT * FROM gl_sets_of_books;

begin
select chart_of_accounts_id
into l_coa_id
from gl_sets_of_books
where set_of_books_id = 2159;--fnd_profile.value('GL_SET_OF_BKS_ID');
exception
when no_data_found then
dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID');
dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize');
raise;
end;
-- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION
--create will only work if dynamic inserts on and cross validation rules not broken
l_keyval_status := fnd_flex_keyval.validate_segs(
'CREATE_COMBINATION',
'SQLGL',
'GL#',
l_coa_id,
p_concat_segs,
'V',
sysdate,
'ALL', NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);

if l_keyval_status then
return 'S';
else
--return l_error;
l_err_msg:=substr(fnd_flex_keyval.error_message, 1, 240); --fnd_message.get;

l_error := substr(fnd_flex_keyval.error_message, 1, 240);
dbms_output.put_line(l_error);
dbms_output.put_line('ERROR SEGMENT :');
l_error := to_char(fnd_flex_keyval.error_segment);
dbms_output.put_line(l_error);
dbms_output.put_line('ERROR ENCODED :');
l_error := substr(fnd_flex_keyval.encoded_error_message, 1, 240);
dbms_output.put_line(l_error);
dbms_output.put_line('FALSE');

dbms_output.put_line(l_err_msg||substr(sqlerrm,150,3));
return l_error;
end if;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end create_ccid;

DECLARE
L_RETURN VARCHAR2(2000);
BEGIN
FOR I IN (SELECT LIABILITY_ACCOUNT FROM XX_VENDOR_API_GL) LOOP
SELECT create_ccid(i.LIABILITY_ACCOUNT)
INTO L_RETURN
FROM DUAL;
COMMIT;
END LOOP;
END;

Tuesday, December 19, 2023

Sales Order API Apps R12 using interface Tables

--Custom table and updates.

CREATE TABLE APPS.XX_SO_INT_GLO

(

SNO NUMBER,

OLD_REF_NUM NUMBER,

ORDER_TYPE VARCHAR2(240 BYTE),

DATE_ORDERED VARCHAR2(240 BYTE),

CUSTOMER_NUMBER VARCHAR2(240 BYTE),

CUSTOMER_NAME VARCHAR2(240 BYTE),

PRICE_LIST VARCHAR2(240 BYTE),

CURRENCY VARCHAR2(240 BYTE),

SOLD_FROM_ORG VARCHAR2(240 BYTE),

SALES_REP VARCHAR2(240 BYTE),

LINE_NUM NUMBER,

INVENTORY_ITEM VARCHAR2(240 BYTE),

ITEM_ID NUMBER,

ORG_ID NUMBER,

ORDERED_QTY NUMBER,

UOM VARCHAR2(240 BYTE),

SELLING_PRICE NUMBER,

EXTRA1 VARCHAR2(240 BYTE),

EXTRA2 VARCHAR2(240 BYTE),

EXTRA3 VARCHAR2(240 BYTE),

STATUS VARCHAR2(240 BYTE),

MESSAGE VARCHAR2(240 BYTE),

SHIP_TO NUMBER,

BILL_TO NUMBER,

CUSTOMER_ID NUMBER,

SALESREP_ID NUMBER,

ORDER_TYPE_ID NUMBER,

PRICE_LIST_ID NUMBER,

WAREHOUSE_ID NUMBER

);

--1 ITEM_ID

UPDATE XX_SO_INT_GLO

SET ITEM_ID = (SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = INVENTORY_ITEM AND ORGANIZATION_ID = 122)

WHERE ITEM_ID IS NULL

;

COMMIT;

--2 CUSTOMER_ID

UPDATE XX_SO_INT_GLO XX1

SET CUSTOMER_ID =

(SELECT hca.CUST_ACCOUNT_ID

--,HCSA.ORG_ID

FROM HZ_CUST_ACCOUNTS_ALL HCA, hz_cust_acct_sites_all HCSA,HZ_PARTIES HZP

WHERE HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID

AND HCSA.ORG_ID = 122

AND HCA.PARTY_ID = HZP.PARTY_ID

AND HCA.ACCOUNT_NUMBER = XX1.CUSTOMER_NUMBER

--AND UPPER(TRIM(CUSTOMER_NAME)) = UPPER(TRIM(PARTY_NAME))

AND BILL_TO_FLAG = 'P'

AND HCSA.STATUS = 'A'

)

WHERE CUSTOMER_ID IS NULL;

--3 SHIP_TO, BILL_TO

UPDATE XX_SO_INT_GLO XX1

SET BILL_TO =

(

SELECT SITE_USE_ID FROM

AR_CUSTOMERS AR, hz_cust_acct_sites_all HZC, hz_cust_site_uses_all HZA

WHERE CUSTOMER_ID = CUST_ACCOUNT_ID

AND HZA.CUST_ACCT_SITE_ID = HZC.CUST_ACCT_SITE_ID

--AND CUSTOMER_ID =8318

--AND CUSTOMER_NUMBER = XX1.CUSTOMER_NUMBER

AND HZC.CUST_ACCOUNT_ID = XX1.CUSTOMER_ID

AND SITE_USE_CODE = 'BILL_TO'

AND BILL_TO_FLAG = 'P'

AND HZC.ORG_ID = 122

AND HZC.STATUS = 'A'

AND HZA.STATUS = 'A'

--AND ROWNUM =1 --NO SITE PROVIDED

)

WHERE BILL_TO IS NULL

--WHERE CUSTOMER_ID = 43050

;

SELECT * FROM XX_SO_INT_GLO WHERE BILL_TO IS NULL AND CUSTOMER_ID IS NOT NULL;

--SHIP TO

UPDATE XX_SO_INT_GLO XX1

SET SHIP_TO =

(

SELECT SITE_USE_ID FROM

AR_CUSTOMERS AR, hz_cust_acct_sites_all HZC, hz_cust_site_uses_all HZA

WHERE CUSTOMER_ID = CUST_ACCOUNT_ID

AND HZA.CUST_ACCT_SITE_ID = HZC.CUST_ACCT_SITE_ID

--AND CUSTOMER_ID =8318

--AND CUSTOMER_NUMBER = XX1.CUSTOMER_NUMBER

AND HZC.CUST_ACCOUNT_ID = XX1.CUSTOMER_ID

AND SITE_USE_CODE = 'SHIP_TO'

AND BILL_TO_FLAG = 'P'

AND HZC.ORG_ID = 122

AND HZC.STATUS = 'A'

AND HZA.STATUS = 'A'

--AND ROWNUM =1 --NO SITE PROVIDED

)

--WHERE CUSTOMER_ID = 43050

WHERE SHIP_TO IS NULL

;

COMMIT;

SELECT DISTINCT CUSTOMER_NAME FROM XX_SO_INT_GLO WHERE (SHIP_TO IS NULL OR BILL_TO IS NULL) AND CUSTOMER_ID IS NOT NULL;

SELECT * FROM XX_SO_INT_GLO;

--4 order_type_id

SELECT NAME, OTT.TRANSACTION_TYPE_ID FROM

oe_transaction_types_all ott

,oe_transaction_types_tl ottl

WHERE OTT.TRANSACTION_TYPE_ID = OTTL.TRANSACTION_TYPE_ID

AND OTT.TRANSACTION_TYPE_ID = 1021;

UPDATE XX_SO_INT_GLO

SET order_type_id =

(SELECT OTT.TRANSACTION_TYPE_ID FROM

oe_transaction_types_all ott

,oe_transaction_types_tl ottl

WHERE OTT.TRANSACTION_TYPE_ID = OTTL.TRANSACTION_TYPE_ID

AND UPPER(NAME) = UPPER(ORDER_TYPE));

COMMIT;

SELECT * FROM XX_SO_INT_GLO

WHERE order_type_id IS NULL;

WHERE ORDER_TYPE LIKE '%Return%';

WHERE ORDER_TYPE_ID IS NULL;

--5 PRICE_LIST_ID

SELECT LIST_HEADER_ID, NAME FROM qp_secu_list_headers_v ;

UPDATE XX_SO_INT_GLO

SET PRICE_LIST_ID =

(SELECT LIST_HEADER_ID FROM qp_secu_list_headers_v

WHERE UPPER(NAME) = UPPER(price_list)

)

WHERE PRICE_LIST_ID IS NULL;

COMMIT;

SELECT DISTINCT PRICE_LIST FROM XX_SO_INT_GLO WHERE PRICE_LIST_ID IS NULL;

SELECT * FROM XX_SO_INT_GLO WHERE PRICE_LIST_ID IS NULL;

--6 salesrep_id

--dont use below query, search for the salesperson_id and update manual

UPDATE XX_SO_INT_GLO XX1

SET SALESREP_ID =

(

SELECT HZA.PRIMARY_SALESREP_ID FROM

AR_CUSTOMERS AR, hz_cust_acct_sites_all HZC, hz_cust_site_uses_all HZA

WHERE CUSTOMER_ID = CUST_ACCOUNT_ID

AND HZA.CUST_ACCT_SITE_ID = HZC.CUST_ACCT_SITE_ID

--AND CUSTOMER_ID =8318

AND CUSTOMER_NUMBER = XX1.CUSTOMER_NUMBER

AND SITE_USE_CODE = 'BILL_TO'

AND BILL_TO_FLAG = 'P'

AND HZC.ORG_ID = 121

AND HZC.STATUS = 'A'

AND HZA.STATUS = 'A'

--AND ROWNUM = 1 --NO SITE PROVIDED

)

WHERE SALESREP_ID IS NULL

;

SELECT JTF.SALESREP_ID,NAME,

SALESREP_NUMBER,

SOURCE_EMAIL,

SOURCE_JOB_TITLE,

SOURCE_ADDRESS1,

SOURCE_MGR_NAME,

SOURCE_MOBILE_PHONE,

JTF.SALESREP_ID

FROM jtf_rs_salesreps JTF, JTF_RS_RESOURCE_EXTNS_VL JTFRES

WHERE JTF.RESOURCE_ID = JTFRES.RESOURCE_ID

AND UPPER(NAME) like UPPER('%SS%');

--AND JTF.SALESREP_ID =HZCU.PRIMARY_SALESREP_ID

SELECT DISTINCT SALES_REP FROM XX_SO_INT_GLO XX1

WHERE SALESREP_ID IS NULL;

UPDATE XX_SO_INT_GLO XX1

SET SALESREP_ID = 100000067

WHERE SALES_REP = 'Mr.Afzal';

COMMIT;

SELECT * FROM XX_SO_INT_GLO WHERE SALESREP_ID IS NULL AND CUSTOMER_ID IS NOT NULL;

--7 item exists in price_list NOT NEEDED

SELECT * FROM qp_list_lines_v;

SELECT OLD_REF_NUM,SELLING_PRICE,INVENTORY_ITEM, PRICE_LIST_ID, (SELECT OPERAND FROM qp_list_lines_v WHERE PRODUCT_ATTR_VALUE = ITEM_ID AND LIST_HEADER_ID = PRICE_LIST_ID)SELLING_PRICE_LSIT

FROM XX_SO_INT_GLO

WHERE OLD_REF_NUM NOT IN

(SELECT ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL)

AND ITEM_ID IS NOT NULL

-- AND OLD_REF_NUM IN (4800, 4830) --5022 --5090

-- AND CURRENCY = 'AED'

-- ORDER BY 2

;

--8 check the price list price and given price --not required, we are passing manual amt

SELECT OLD_REF_NUM,SELLING_PRICE,INVENTORY_ITEM, PRICE_LIST_ID, OPERAND

--(SELECT OPERAND FROM qp_list_lines_v WHERE PRODUCT_ATTR_VALUE = ITEM_ID AND LIST_HEADER_ID = PRICE_LIST_ID)SELLING_PRICE_LSIT

FROM XX_SO_INT_GLO XX1, qp_list_lines_v QPV

WHERE OLD_REF_NUM NOT IN

(SELECT ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL)

AND XX1.ITEM_ID = QPV.PRODUCT_ATTR_VALUE

AND LIST_HEADER_ID = PRICE_LIST_ID

AND SELLING_PRICE <> OPERAND

-- AND OLD_REF_NUM IN (4800, 4830) --5022 --5090

-- AND CURRENCY = 'AED'

ORDER BY 2

;

--8 UPDATE PAYMENT_TERMS

UPDATE XX_SO_INT_GLO

SET TERMS_ID = (SELECT TERM_ID FROM RA_TERMS WHERE UPPER(TRIM(PAY_TERMS)) = UPPER(TRIM(NAME)))

WHERE TERMS_ID IS NULL

;

commit;

SELECT DISTINCT PAY_TERMS, TERMS_ID from XX_SO_INT_GLO where terms_id is null;

UPDATE

XX_SO_INT_GLO

SET TERMS_ID = 1009

WHERE TERMS_ID IS NULL

AND PAY_TERMS LIKE '10%AB90D-PDCOBD';

;

SELECT DISTINCT PAY_TERMS FROM XX_SO_INT_GLO

WHERE TERMS_ID IS NULL

;

UPDATE XX_SO_INT_GLO

SET TERMS_ID =5

WHERE TERMS_ID IS NULL;

SELECT * FROM XX_SO_INT_GLO

WHERE TERMS_ID IS NULL

;

SELECT TERM_ID, NAME FROM RA_TERMS-- WHERE TERMS_ID IS NULL

WHERE NAME like '10%B%';

--2 Interface Tables of SO inserting data.

delete from OE_HEADERS_IFACE_ALL;

delete from OE_LINES_IFACE_ALL;

delete FROM OE_ACTIONS_IFACE_ALL;

delete FROM OE_PROCESSING_MSGS_TL;

delete FROM OE_PROCESSING_MSGS;

select * from OE_PROCESSING_MSGS_TL;

select * from OE_PROCESSING_MSGS;

select *

--delete

from OE_HEADERS_IFACE_ALL;

edit

OE_HEADERS_IFACE_ALL;

--to process errored lines

UPDATE OE_HEADERS_IFACE_ALL

SET REQUEST_ID = NULL, ERROR_FLAG = NULL, XML_MESSAGE_ID = NULL

--to check error lines with item_id

select *--distinct ORIG_SYS_DOCUMENT_REF

--delete

from OE_LINES_IFACE_ALL

WHERE ORIG_SYS_LINE_REF IN (SELECT ORIGINAL_SYS_DOCUMENT_LINE_REF

FROM

OE_PROCESSING_MSGS

where type = 'ERROR' )

;

edit

--select distinct ORIG_SYS_DOCUMENT_REF, line_number

OE_LINES_IFACE_ALL

order by ORIG_SYS_DOCUMENT_REF, LINE_NUMBER

;

SELECT *

--delete

FROM OE_ACTIONS_IFACE_ALL;

SELECT *

--delete

FROM

OE_PROCESSING_MSGS

where type = 'ERROR'

--ORDER BY CREATION_DATE DESC

;

SELECT *

--delete

FROM

OE_PROCESSING_MSGS_TL

WHERE TRANSACTION_ID IN ( SELECT TRANSACTION_ID FROM

OE_PROCESSING_MSGS

where type = 'ERROR'

--AND TRANSACTION_ID IN (22363,22376,22375)

)

ORDER BY CREATION_DATE DESC;

SELECT * FROM OE_ORDER_HEADERS_ALL

WHERE LAST_UPDATE_DATE LIKE SYSDATE;

--workflow status ENTERED, BOOKED ..etc

SELECT *

FROM fnd_lookup_values

WHERE lookup_type = 'LINE_FLOW_STATUS'

AND language = 'US';

SELECT *

FROM fnd_lookup_values

WHERE lookup_type = 'TRIP_STATUS'

AND language = 'US';

--CREATE TABLE XX_SO_INT_GLO_BKUP2

--AS SELECT * FROM XX_SO_INT_GLO;

--DELETE FROM XX_SO_INT_GLO;

--for return type Order_type use return_reason = 'NO REASON' ex: Return Material UV - Direct CM

DECLARE

l_org_id hr_operating_units.organization_id%TYPE := 122;

PRICE_LIST_ID qp_list_headers_tl.list_header_id%TYPE;

l_verify_flag CHAR (1);

l_error_message VARCHAR2 (3000);

termS_id ra_terms_tl.term_id%TYPE;

ORDER_TYPE_ID oe_transaction_types_tl.transaction_type_id%TYPE;

l_order_source_id oe_order_sources.order_source_id%TYPE := 0; --Online default

l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;

l_organization_id org_organization_definitions.organization_id%TYPE;

l_uom_code mtl_units_of_measure_vl.uom_code%TYPE;

l_line_type_id oe_transaction_types_tl.transaction_type_id%TYPE;

l_currency_code FND_CURRENCIES_VL.currency_code%TYPE;

l_customer_id ra_customers.customer_id%TYPE;

l_invoice_to_orgid oe_invoice_to_orgs_v.organization_id%TYPE;

l_shipto_org_id oe_ship_to_orgs_v.organization_id%TYPE;

l_user_id fnd_user.user_id%TYPE;

l_ship_to_org_id NUMBER (10);

l_bill_to_org_id NUMBER (10);

L_OLD_REF_NUM NUMBER := 0;

UPDATE OE_ORDER_LINES OEL

SET SHIP_FROM_ORG_ID = 121--'SGL'

WHERE OEL.HEADER_ID IN (SELECT OEH.HEADER_ID FROM OE_ORDER_HEADERS OEH

WHERE order_type_id = 1321 and oeh.org_id = 121)

AND SHIP_FROM_ORG_ID IS NULL

AND LAST_UPDATE_DATE LIKE SYSDATE --= 1170

;

--global

--total 151

--exists 52

--new 99

--llc

--total 54

--exists 21

--new 33

CURSOR c_header IS

--if order_type like '%Return%' then add ship_from_org_id = org_id (121/122/123) header and lines use above script later if forgot

SELECT *--distinct old_ref_num

--SELECT DISTINCT OLD_REF_NUM--, LINE_NUM

--select distinct order_type

FROM XX_SO_INT_GLO

-- WHERE ORDER_TYPE LIKE 'Return Material%'

WHERE --OLD_REF_NUM IN ( '5639') AND

OLD_REF_NUM not IN (SELECT ORDER_NUMBER

FROM OE_ORDER_HEADERS_ALL --where creation_date like sysdate

WHERE ORG_ID = 122)

-- AND QUOTE_DATE IS NULL

AND DATE_ORDERED IS NOT NULL

-- AND ITEM_ID IS NULL

-- and header_STATUS IN ('BOOKED','CLOSED','ENTERED')

-- AND LINE_STATUS <> 'CANCELLED'

-- and order_type like '%Return%'

-- AND OLD_REF_NUM = '5186'

-- AND NVL (verify_flag, 'N') = 'N'

-- AND CURRENCY = 'AED'

-- AND HEADER_STATUS = 'BOOKED'

-- AND LINE_STATUS <> 'CANCELLED'

-- ORDER BY 2,3;

ORDER BY OLD_REF_NUM, LINE_NUM;

-- EDIT XX_SO_INT_GLO WHERE OLD_REF_NUM IN ( '831' ,'5857') AND header_STATUS IN ('BOOKED','CLOSED') AND LINE_STATUS <> 'CANCELLED' ORDER BY LINE_NUM

/* CURSOR c_lines (P_OLD_REF_NUM VARCHAR2)

update XX_SO_INT_GLO

set line_num = sno

where old_ref_num = 5251

SELECT *

FROM XX_SO_INT_GLO

where old_ref_num = 5251;

BEGIN

FOR h1 IN c_header

LOOP

l_error_message := NULL;

l_verify_flag := 'Y';

IF L_OLD_REF_NUM <> h1.OLD_REF_NUM

THEN

BEGIN

L_OLD_REF_NUM := h1.OLD_REF_NUM;

--savepoint a;

INSERT INTO oe_headers_iface_all (order_source_id,

orig_sys_document_ref,

order_number,

--quote_number,

org_id,

sold_from_org_id --,ship_from_org_id

ordered_date,

--quote_date,

order_type_id,

sold_to_org_id,

payment_term_id,

operation_code,

created_by,

creation_date,

last_updated_by,

last_update_date,

customer_po_number,

price_list_id --,context

--,attribute19

ship_to_org_id,

invoice_to_org_id,

salesrep_id-- ,booked_flag

,ATTRIBUTE1

,ATTRIBUTE4

,TRANSACTIONAL_CURR_CODE

,FREIGHT_TERMS

--,RETURN_REASON_CODE

)

VALUES (l_order_source_id --order_source_id

oe_order_headers_s.NEXTVAL --orig_sys_document_ref

h1.OLD_REF_NUM, --order_number / QuoteNumber

--h1.OLD_REF_NUM, --quote_number

l_org_id --org_id

l_org_id --sold_from_org_id

--,l_organization_id --ship_from_org_id

,NVL (TO_DATE (h1.DATE_ORDERED, 'DD-Mon-YYYY'),TO_DATE (h1.CREATION_DATE, 'DD-Mon-YYYY')) --ordered_date

--,TO_DATE (h1.QUOTE_DATE, 'DD-MON-YYYY')

h1.order_type_id --order_type_id

h1.customer_id --sold_to_org_id

h1.terms_id --payment_term_id

oe_globals.g_opr_create -- 'CREATE' --operation_code

-1 --created_by

SYSDATE --creation_date

-1 --last_updated_by

SYSDATE --last_update_date

NULL --customer_po_number

h1.price_list_id --price_list_id

--,'xxx Legacy SO Number'

--,trim(h1.legacy_so_num)

h1.SHIP_TO,

h1.BILL_TO,

NVL (h1.salesrep_id, -3)-- ,'Y'

,H1.ENQ_NO_DFF --||' '||H1.NEW_DESCRIPTION_DFF

,H1.CUSTOMER_NAME_DFF

,h1.currency

,'EXWORK'

--,'NO REASON'

);

EXCEPTION

WHEN OTHERS

THEN

l_error_message := SQLERRM;

l_verify_flag := 'N';

UPDATE XX_SO_INT_GLO

SET verify_flag = 'N',

error_message = 'Header error' || l_error_message

WHERE customer_id = h1.customer_id

AND OLD_REF_NUM = h1.OLD_REF_NUM;

--goto next_so;

END;

------------------Line Details ---------------------

/*INSERT INTO OE_ACTIONS_IFACE_ALL (

order_source_id

,orig_sys_document_ref

,operation_code

)

VALUES (

l_order_source_id --order_source_id

, oe_order_headers_s.currVAL--orig_sys_document_ref

,'ENTERED'--'DRAFT'--'BOOK_ORDER' --operation_code

);*/

-- commit;

END IF;

BEGIN

INSERT INTO oe_lines_iface_all (order_source_id,

orig_sys_document_ref,

orig_sys_line_ref,

line_number,

inventory_item_id,

ordered_quantity --,ship_from_org_id

org_id,

pricing_quantity,

unit_selling_price,

unit_list_price,

price_list_id,

payment_term_id --,schedule_ship_date

created_by,

creation_date,

last_updated_by,

last_update_date --,line_type_id

calculate_price_flag

-- ,REQUEST_DATE ----,request_date

---- ,OPERATION_CODE

-- --,SCHEDULE_SHIP_DATE

-- ,SCHEDULE_DATE

-- ,SCHEDULE_ARRIVAL_DATE

-- , PROMISE_DATE

)

VALUES (l_order_source_id --order_source_id

oe_order_headers_s.CURRVAL --orig_sys_document_ref

oe_order_lines_s.NEXTVAL --orig_sys_line_ref

TRIM (h1.line_num) --line_number

h1.item_id --inventory_item_id

TRIM (h1.ordered_qty) --ordered_quantity

--,l_organization_id --ship_from_org_id

l_org_id --ship_from_org_id

TRIM (h1.ordered_qty) --pricing_quantity

TRIM (h1.selling_price) --unit_selling_price

TRIM (h1.selling_price) --unit_list_price

h1.price_list_id --price_list_id

h1.terms_id --payment_term_id

--,to_date(h1.scheduled_ship_date,'DD-MON-RRRR') --schedule_ship_date

--,to_date(h1.request_date,'DD-MON-RRRR') --request_date

-1 --created_by

SYSDATE --creation_date

-1 --last_updated_by

SYSDATE --last_update_date

--,l_line_type_id --line_type_id

'N'

-- TO_DATE (h1.QUOTE_DATE, 'DD-MON-YYYY') --REQUEST_DATE

---- ,H1.LINE_STATUS --OPEATION_CODE

-- ,NVL(TO_DATE(H1.SCHEDULE_SHIP_DATE, 'DD-MON-YYYY'),TO_DATE (h1.DATE_ORDERED, 'DD-MON-YYYY'))

);

UPDATE XX_SO_INT_GLO

SET verify_flag = 'Y'

WHERE customer_id = h1.customer_id

AND OLD_REF_NUM = h1.OLD_REF_NUM

AND item_id = h1.item_Id;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK TO a;

l_error_message := SQLERRM;

UPDATE XX_SO_INT_GLO

SET verify_flag = 'N',

error_message = 'Line error' || l_error_message

WHERE customer_id = h1.customer_id

AND OLD_REF_NUM = h1.OLD_REF_NUM

AND item_id = h1.item_Id;

END;

COMMIT;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

commit;

--3 running the concurrent program.

Order Import --> concurrent Program

Parameters:

Operaring Unit: <select_OU>

Validate Only? No

Instances: 4

Trim Trailing Blanks: No

Validate Descriptive Flexfield: No

--Other parameters leave blank

--4 api to process ENTERED to BOOKED

DECLARE

v_api_version_number NUMBER := 1;

v_return_status VARCHAR2 (2000);

v_msg_count NUMBER;

v_msg_data VARCHAR2 (2000);

-- IN Variables --

v_header_rec oe_order_pub.header_rec_type;

v_line_tbl oe_order_pub.line_tbl_type;

v_action_request_tbl oe_order_pub.request_tbl_type;

v_line_adj_tbl oe_order_pub.line_adj_tbl_type;

-- OUT Variables --

v_header_rec_out oe_order_pub.header_rec_type;

v_header_val_rec_out oe_order_pub.header_val_rec_type;

v_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;

v_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;

v_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;

v_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;

v_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;

v_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;

v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;

v_line_tbl_out oe_order_pub.line_tbl_type;

v_line_val_tbl_out oe_order_pub.line_val_tbl_type;

v_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;

v_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;

v_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;

v_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;

v_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;

v_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;

v_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;

v_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;

v_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;

v_action_request_tbl_out oe_order_pub.request_tbl_type;

CURSOR C1 IS

--UPDATE OE_ORDER_LINES_ALL

--SET PRICE_LIST_ID = 11019

--WHERE HEADER_ID IN (SELECT HEADER_ID FROM

SELECT * FROM

OE_ORDER_HEADERS_ALL

WHERE

FLOW_STATUS_CODE = 'ENTERED' And

ORG_ID = 122

AND ORDER_NUMBER <> '6952'

AND LAST_UPDATE_DATE LIKE SYSDATE

AND ORDER_NUMBER IN (SELECT OLD_REF_NUM FROM XX_SO_INT_GLO

--WHERE header_STATUS IN ('BOOKED','CLOSED')

--AND ORDER_TYPE LIKE 'Return Material%'

)

--AND CREATED_BY = 1170

;

--wait 1 min after script is run, to check.

BEGIN

DBMS_OUTPUT.PUT_LINE('Starting of script');

-- Setting the Enviroment --

mo_global.init('ONT');

fnd_global.apps_initialize ( user_id => 1170

,resp_id => 50958 --LLOC --GLOBAL 50957

,resp_appl_id => 660);

mo_global.set_policy_context('S',122);

FOR I IN C1 LOOP

v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;

v_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;

v_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;

v_action_request_tbl(1).entity_id := I.HEADER_ID; --pass header_id here

----DBMS_OUTPUT.PUT_LINE('Starting of API');

-- Calling the API to to Book an Existing Order --

BEGIN

OE_ORDER_PUB.PROCESS_ORDER (

p_api_version_number => v_api_version_number

, p_header_rec => v_header_rec

, p_line_tbl => v_line_tbl

, p_action_request_tbl => v_action_request_tbl

, p_line_adj_tbl => v_line_adj_tbl

-- OUT variables

, x_header_rec => v_header_rec_out

, x_header_val_rec => v_header_val_rec_out

, x_header_adj_tbl => v_header_adj_tbl_out

, x_header_adj_val_tbl => v_header_adj_val_tbl_out

, x_header_price_att_tbl => v_header_price_att_tbl_out

, x_header_adj_att_tbl => v_header_adj_att_tbl_out

, x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out

, x_header_scredit_tbl => v_header_scredit_tbl_out

, x_header_scredit_val_tbl => v_header_scredit_val_tbl_out

, x_line_tbl => v_line_tbl_out

, x_line_val_tbl => v_line_val_tbl_out

, x_line_adj_tbl => v_line_adj_tbl_out

, x_line_adj_val_tbl => v_line_adj_val_tbl_out

, x_line_price_att_tbl => v_line_price_att_tbl_out

, x_line_adj_att_tbl => v_line_adj_att_tbl_out

, x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out

, x_line_scredit_tbl => v_line_scredit_tbl_out

, x_line_scredit_val_tbl => v_line_scredit_val_tbl_out

, x_lot_serial_tbl => v_lot_serial_tbl_out

, x_lot_serial_val_tbl => v_lot_serial_val_tbl_out

, x_action_request_tbl => v_action_request_tbl_out

, x_return_status => v_return_status

, x_msg_count => v_msg_count

, x_msg_data => v_msg_data

);

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

DBMS_OUTPUT.PUT_LINE('Completion of API');

IF v_return_status = fnd_api.g_ret_sts_success THEN

COMMIT;

DBMS_OUTPUT.put_line ('Booking of an Existing Order is Success ');

ELSE

DBMS_OUTPUT.put_line ('Booking of an Existing Order failed:'||v_msg_data);

ROLLBACK;

FOR i IN 1 .. v_msg_count

LOOP

v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

dbms_output.put_line( i|| ') '|| v_msg_data);

END LOOP;

END IF;

END LOOP;

END;

COMMIT;

--5 Query:

SELECT

ORDER_NUMBER,

(SELECT NAME--, OTT.TRANSACTION_TYPE_ID

FROM

oe_transaction_types_all ott

,oe_transaction_types_tl ottl

WHERE OTT.TRANSACTION_TYPE_ID = OTTL.TRANSACTION_TYPE_ID

AND OTT.TRANSACTION_TYPE_ID = OEH.ORDER_TYPE_ID

AND ORDER_TYPE_ID = OEH.ORDER_TYPE_ID

)ORDER_TYPE,

ORDERED_DATE,

(SELECT CUSTOMER_NUMBER FROM AR_CUSTOMERS WHERE CUSTOMER_ID =OEH.SOLD_TO_ORG_ID) CUSTOMER_NUMBER,

--OEH.SOLD_TO_ORG_ID,

TRANSACTIONAL_CURR_CODE,

NVL((SELECT NAME FROM jtf_rs_salesreps JTF, JTF_RS_RESOURCE_EXTNS_VL JTFRES

WHERE JTF.RESOURCE_ID = JTFRES.RESOURCE_ID

AND OEH.SALESREP_ID = JTF.SALESREP_ID

AND ORG_ID = OEH.ORG_ID),OEH.SALESREP_ID)SALESREP_NAME,

(SELECT NAME FROM qp_secu_list_headers_v

WHERE LIST_HEADER_ID = OEH.PRICE_LIST_ID)PRICE_LIST_NAME,

LINE_NUMBER,

(SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = OEL.INVENTORY_ITEM_ID AND ORGANIZATION_ID = OEH.ORG_ID)ITEM_CODE

,ORDERED_QUANTITY,

ORDER_QUANTITY_UOM,unit_selling_price

,OEH.flow_status_code HEADER_STATUS

,OEH.ORG_ID

FROM OE_ORDER_HEADERS_ALL OEH, OE_ORDER_LINES_ALL OEL

WHERE --OEH.CREATION_DATE LIKE SYSDATE

OEH.HEADER_ID = OEL.HEADER_ID

ORDER BY ORDER_NUMBER, LINE_NUMBER

;

Sunday, December 10, 2023

Apps R12 AP Payments for Pre-payments

step1: validate all invoices using conc program 'Invoice Validation'

step2: initialize

begin

fnd_global.apps_initialize(1170,50977,200);

mo_global.init ('S');

end;

step3:

DECLARE

-- Declarations

l_P_API_VERSION NUMBER;

l_P_INIT_MSG_LIST VARCHAR2 (32767);

l_P_INVOICE_ID NUMBER;

l_P_PAYMENT_TYPE_FLAG VARCHAR2 (32767);

l_P_INTERNAL_BANK_ACCT_ID NUMBER;

l_P_PAYMENT_METHOD_CODE VARCHAR2 (32767);

l_P_PAYMENT_PROFILE_ID NUMBER;

l_P_PAYMENT_DOCUMENT_ID NUMBER;

l_P_CHECK_DATE DATE;

l_P_DOC_CATEGORY_CODE VARCHAR2 (32767);

l_P_EXCHANGE_RATE_TYPE VARCHAR2 (32767);

l_P_EXCHANGE_RATE NUMBER;

l_P_EXCHANGE_DATE DATE;

l_RETURN_STATUS VARCHAR2 (32767);

l_MSG_COUNT NUMBER;

l_MSG_DATA VARCHAR2 (32767);

lv_sqlerrm VARCHAR2 (32767);

l_X_ERRORIDS APPS.IBY_DISBURSE_SINGLE_PMT_PKG.TRXNERRORIDSTAB;

L_GL_DATE DATE := TO_DATE('30-SEP-2023');

LN_MSG_INDEX_OUT VARCHAR2(240);

CURSOR C1 IS

SELECT INVOICE_DATE,INVOICE_NUM,INVOICE_ID ,PAYMENT_STATUS_FLAG, INVOICE_CURRENCY_CODE, PAYMENT_METHOD_CODE,VENDOR_NUMBER--, INVOICE_NUM

FROM AP_INVOICES_V WHERE

--INVOICE_NUM IN( '563693','566156','563027')AND

INVOICE_TYPE= 'Prepayment'AND

ORG_ID = 121

--AND INVOICE_ID = 47133

AND PAYMENT_STATUS_FLAG = 'N'

AND GL_DATE = TO_DATE('30-SEP-2023')

AND CREATION_DATE LIKE SYSDATE

--AND INVOICE_ID = 42461

--AND INVOICE_CURRENCY_CODE <> 'AED'

----AND INVOICE_ID IN (14641)--551661

------,14350

--)

--AND APPROVAL_STATUS = 'Available'

;

BEGIN

DBMS_OUTPUT.ENABLE(1000000); -- Clear DBMS_OUTPUT buffer.

FOR I IN C1 LOOP

-- Initialization

l_P_API_VERSION := NULL;

l_P_INIT_MSG_LIST := NULL;

l_P_INVOICE_ID := NULL;

l_P_PAYMENT_TYPE_FLAG := 'Q';

l_P_INTERNAL_BANK_ACCT_ID := NULL;

l_P_PAYMENT_METHOD_CODE := I.PAYMENT_METHOD_CODE;

l_P_PAYMENT_PROFILE_ID := 144;

l_P_PAYMENT_DOCUMENT_ID := NULL;

l_P_CHECK_DATE := L_GL_DATE;

l_P_DOC_CATEGORY_CODE := NULL;

l_P_EXCHANGE_RATE_TYPE := NULL;

l_P_EXCHANGE_RATE := NULL;

l_P_EXCHANGE_DATE := L_GL_DATE;

BEGIN

APPS.XXAP_PAY_SINGLE_INVOICE_PKG.AP_PAY_INVOICE_IN_FULL (

p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

p_invoice_id => I.INVOICE_ID,

p_payment_type_flag => 'Q', --'A'

p_internal_bank_acct_id => 16000,--10080,--l_bank_account_id, SELECT bank_account_id,BAnk_ACCOUNT_NAME FROM ce_bank_accounts WHERE bank_account_name like '%Temperory Account';

p_payment_method_code => I.PAYMENT_METHOD_CODE,--l_payment_method_code,

p_payment_profile_id => 144,--l_payment_profile_id,

p_payment_document_id => null,

p_take_discount => 'N',

p_check_date => L_GL_DATE,

p_doc_category_code => null,

p_exchange_rate_type => 'Corporate',

p_exchange_rate => null,

p_exchange_date => L_GL_DATE,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_errorids => l_X_ERRORIDS

);

IF l_return_status = fnd_api.g_ret_sts_error OR

l_return_status = fnd_api.g_ret_sts_unexp_error THEN

dbms_output.put_line('FND_MSG_PUB.Count_Msg ');

dbms_output.put_line(l_return_status||':'||sqlerrm);

FOR l_msg_count IN 1..FND_MSG_PUB.Count_Msg

LOOP

FND_MSG_PUB.Get(p_msg_index => l_msg_count

,p_encoded => 'F'

,p_data => l_msg_data

,p_msg_index_OUT => ln_msg_index_out

);

lv_sqlerrm := SUBSTR(lv_sqlerrm||REPLACE(l_msg_data,CHR(10),''),1,3998);

-- W_Status := 'E';

-- W_Message := lv_sqlerrm;

dbms_output.put_line(l_return_status||':'||lv_sqlerrm);

END LOOP;

FND_MSG_PUB.Delete_Msg;

END IF;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

-- -- Call

-- APPS.XXAP_PAY_SINGLE_INVOICE_PKG.AP_PAY_INVOICE_IN_FULL (

-- P_API_VERSION => l_P_API_VERSION,

-- P_INIT_MSG_LIST => l_P_INIT_MSG_LIST,

-- P_INVOICE_ID => l_P_INVOICE_ID,

-- P_PAYMENT_TYPE_FLAG => l_P_PAYMENT_TYPE_FLAG,

-- P_INTERNAL_BANK_ACCT_ID => l_P_INTERNAL_BANK_ACCT_ID,

-- P_PAYMENT_METHOD_CODE => l_P_PAYMENT_METHOD_CODE,

-- P_PAYMENT_PROFILE_ID => l_P_PAYMENT_PROFILE_ID,

-- P_PAYMENT_DOCUMENT_ID => l_P_PAYMENT_DOCUMENT_ID,

-- P_CHECK_DATE => l_P_CHECK_DATE,

-- P_DOC_CATEGORY_CODE => l_P_DOC_CATEGORY_CODE,

-- P_EXCHANGE_RATE_TYPE => l_P_EXCHANGE_RATE_TYPE,

-- P_EXCHANGE_RATE => l_P_EXCHANGE_RATE,

-- P_EXCHANGE_DATE => l_P_EXCHANGE_DATE,

-- X_RETURN_STATUS => l_X_RETURN_STATUS,

-- X_MSG_COUNT => l_X_MSG_COUNT,

-- X_MSG_DATA => l_X_MSG_DATA,

-- X_ERRORIDS => l_X_ERRORIDS);

-- Transaction Control

COMMIT;

END LOOP;

-- Output values, do not modify

DBMS_OUTPUT.PUT_LINE( l_RETURN_STATUS);

-- DBMS_OUTPUT.PUT_LINE( l_X_MSG_COUNT);

-- DBMS_OUTPUT.PUT_LINE(l_X_MSG_DATA);

END;

COMMIT;

--SELECT bank_account_id,BAnk_ACCOUNT_NAME FROM ce_bank_accounts WHERE bank_account_name like '%SFFECO GLOBAL FZE'; --001- OD- SFFECO GLOBAL FZE

--select * from XX_AP_INVOICES_GL;

------------------------------------------------------Package------------------------------------

CREATE OR REPLACE PACKAGE APPS.XXAP_PAY_SINGLE_INVOICE_PKG AUTHID CURRENT_USER AS

/* $Header: appayins.pls 120.6 2006/02/23 02:33:14 bghose noship $ */

PROCEDURE AP_Pay_Invoice_In_Full

(p_api_version IN NUMBER,

p_init_msg_list IN VARCHAR2,

p_invoice_id IN NUMBER,

p_payment_type_flag IN VARCHAR2,

p_internal_bank_acct_id IN NUMBER,

p_payment_method_code IN VARCHAR2,

p_payment_profile_id IN NUMBER,

p_payment_document_id IN NUMBER,

p_take_discount IN VARCHAR2 DEFAULT 'Y',

p_check_date IN DATE,

p_doc_category_code IN VARCHAR2,

p_exchange_rate_type IN VARCHAR2,

p_exchange_rate IN NUMBER,

p_exchange_date IN DATE,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

x_errorIds OUT NOCOPY

IBY_DISBURSE_SINGLE_PMT_PKG.trxnErrorIdsTab

);

END XXAP_PAY_SINGLE_INVOICE_PKG;

---------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.XXAP_PAY_SINGLE_INVOICE_PKG AS

/* $Header: appayinb.pls 120.15.12020000.5 2015/10/06 22:32:04 anacastr ship $ */

-- Declare global variables

G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_PAY_SINGLE_INVOICE_PKG';

G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;

G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;

G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;

G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;

G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;

G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;

G_LINES_PER_FETCH CONSTANT NUMBER := 1000;

G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;

G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;

G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;

G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;

G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;

G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;

G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;

G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_PAY_SINGLE_INVOICE_PKG';

PROCEDURE print_debuginfo

(p_module IN VARCHAR2,

p_debug_text IN VARCHAR2,

p_debug_level IN NUMBER)

BEGIN

-- Writing debug text to the pl/sql debug file.

-- DBMS_OUTPUT.put_line(p_module || p_debug_text);

IF (p_debug_level >= G_CURRENT_RUNTIME_LEVEL) THEN

fnd_log.string(p_debug_level,

p_module,

substr(RPAD(p_module,55)||' : '||

p_debug_text,0,150));

END IF;

-- if in a concurrent request, also add to the CP log

IF fnd_global.CONC_REQUEST_ID <> -1 THEN

FND_FILE.put_line(FND_FILE.LOG,

substr(RPAD(p_module,55)||' : '||

p_debug_text,0,150));

END IF;

EXCEPTION

WHEN OTHERS THEN

NULL;

END print_debuginfo;

-- Bug 2842080

PROCEDURE AP_Pay_Invoice_In_Full

(p_api_version IN NUMBER,

p_init_msg_list IN VARCHAR2,

p_invoice_id IN NUMBER,

p_payment_type_flag IN VARCHAR2,

p_internal_bank_acct_id IN NUMBER,

p_payment_method_code IN VARCHAR2,

p_payment_profile_id IN NUMBER,

p_payment_document_id IN NUMBER,

p_take_discount IN VARCHAR2 DEFAULT 'Y',

p_check_date IN DATE,

p_doc_category_code IN VARCHAR2,

p_exchange_rate_type IN VARCHAR2,

p_exchange_rate IN NUMBER,

p_exchange_date IN DATE,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

x_errorIds OUT NOCOPY

IBY_DISBURSE_SINGLE_PMT_PKG.trxnErrorIdsTab)

l_amount NUMBER;

l_base_amount NUMBER;

l_num_invs_sel_for_pmt NUMBER;

l_return_code NUMBER;

l_application_id NUMBER;

l_name VARCHAR2(80);

l_dummy_rowid VARCHAR2(18);

l_seq_num_profile VARCHAR2(80);

l_hold_all_payments_flag ap_suppliers.hold_all_payments_flag%TYPE;

l_active_pay_sites ap_supplier_sites_all.pay_site_flag%TYPE;

l_accounting_event_id NUMBER(38);

l_quick_check_id AP_CHECKS.checkrun_name%type;

l_next_check_number AP_CHECKS.check_number%type;

l_check_number AP_CHECKS.check_number%type;

l_period_name GL_PERIOD_STATUSES.period_name%TYPE;

l_debug_info VARCHAR2(100);

l_curr_calling_sequence VARCHAR2(2000);

l_doc_category_code varchar2(30);

l_seqval number;

l_dbseqid number;

l_docseq_id number;

l_docseq_type varchar(1);

l_docseq_name varchar2(30);

l_db_seq_name varchar2(30);

l_seq_ass_id number;

l_prd_tab_name varchar2(30);

l_aud_tab_name varchar2(30);

l_msg_flag varchar(1);

l_valid_sequence_exists varchar2(30);

l_payment_type_flag ap_checks.payment_type_flag%TYPE; -- Bug3343314

l_creation_date ap_checks.creation_date%TYPE; -- Bug3343314

l_created_by ap_checks.created_by%TYPE; -- Bug3343314

l_last_update_date ap_checks.last_update_date%TYPE; -- Bug3343314

l_last_updated_by ap_checks.last_updated_by%TYPE; -- Bug3343314

l_last_update_login ap_checks.last_update_login%TYPE; -- Bug3343314

l_org_id NUMBER;

l_bank_account_id ce_bank_acct_uses_all.bank_acct_use_id%TYPE;

l_bank_account_name ce_bank_accounts.bank_account_name%TYPE;

l_processing_type iby_payment_profiles.processing_type%TYPE;

l_print_instr_immed_flag iby_payment_profiles.print_instruction_immed_flag%TYPE;

l_default_printer iby_payment_profiles.default_printer%TYPE;

l_payment_document_id ce_payment_documents.payment_document_id %TYPE;

l_bills_payable iby_payment_methods_vl.support_bills_payable_flag%TYPE;

l_maturity_date_offset_days iby_payment_methods_vl.maturity_date_offset_days%TYPE;

l_maturity_date ap_checks.future_pay_due_date%TYPE;

l_vendor_type_lookup_code ap_suppliers.vendor_type_lookup_code%TYPE;

l_return_status VARCHAR2(100);

l_return_message VARCHAR2(3000);

l_msg_count NUMBER;

l_msg_data VARCHAR2(3000);

l_temp_status VARCHAR2(1);

l_is_duplicate BOOLEAN := TRUE;

l_module_name VARCHAR2(200) := G_PKG_NAME ||

'.ap_pay_invoice_in_full';

l_api_version CONSTANT NUMBER := 1.0;

l_api_name CONSTANT VARCHAR2(30) := 'ap_pay_invoice_in_full';

l_errbuf VARCHAR2(5000);

l_retcode VARCHAR2(2000);

l_transaction_type AP_PAYMENT_HISTORY_ALL.TRANSACTION_TYPE%TYPE;

TYPE r_ap_system_param_info IS RECORD

(

Auto_Calc_Int_Flag AP_SYSTEM_PARAMETERS_ALL.Auto_Calculate_Interest_Flag%TYPE

,Base_Currency_Code AP_SYSTEM_PARAMETERS_ALL.Base_Currency_Code%TYPE

,Pay_Doc_Override AP_SYSTEM_PARAMETERS_ALL.Pay_Doc_Category_Override%TYPE

,Make_Rate_Mandatory_Flag AP_SYSTEM_PARAMETERS_ALL.Make_Rate_Mandatory_Flag%TYPE

,Set_Of_Books_id AP_SYSTEM_PARAMETERS_ALL.Set_Of_Books_id%TYPE

,Default_Exchange_Rate_Type AP_SYSTEM_PARAMETERS_ALL.Default_Exchange_Rate_Type%TYPE

,Multi_Currency_Flag AP_SYSTEM_PARAMETERS_ALL.Multi_Currency_Flag%TYPE

,Xrate_type AP_SYSTEM_PARAMETERS_ALL.default_exchange_rate_type%TYPE

);

TYPE r_check_info IS RECORD

(

Xrate AP_CHECKS_ALL.exchange_rate%TYPE

,Xrate_date AP_CHECKS_ALL.exchange_date%TYPE

,Xrate_type AP_CHECKS_ALL.exchange_rate_type%TYPE

,Check_id AP_CHECKS_ALL.check_id%TYPE

,Checkrun_Name AP_CHECKS_ALL.checkrun_name%TYPE

,Internal_Bank_Acct_Id CE_BANK_ACCT_USES_ALL.bank_acct_use_id%TYPE

,Bank_Account_Name CE_BANK_ACCOUNTS.bank_account_name%TYPE

,Bank_Account_Num CE_BANK_ACCOUNTS.bank_account_num%TYPE

,Bank_Account_Type CE_BANK_ACCOUNTS.account_classification%TYPE

,Bank_Num CE_BANK_BRANCHES_V.branch_number%TYPE

,Legal_Entity_Id CE_BANK_ACCOUNTS.account_owner_org_id%TYPE

,Status_Lookup_Code AP_CHECKS_ALL.status_lookup_code%TYPE

);

TYPE r_ap_ven_sites_info IS RECORD

(

Address_Line1 HZ_LOCATIONS.Address1%TYPE

,Address_Line2 HZ_LOCATIONS.Address2%TYPE

,Address_Line3 HZ_LOCATIONS.Address3%TYPE

,City HZ_LOCATIONS.City%TYPE

,country HZ_LOCATIONS.Country%TYPE

,Vendor_site_code AP_SUPPLIER_SITES_ALL.Vendor_Site_Code%TYPE

,Zip HZ_LOCATIONS.Postal_Code%TYPE

,Province HZ_LOCATIONS.Province%TYPE

,State HZ_LOCATIONS.State%TYPE

,Address_Line4 HZ_LOCATIONS.Address4%TYPE

,County HZ_LOCATIONS.Country%TYPE

,Address_Style HZ_LOCATIONS.Address_Style%TYPE

,Vendor_Id AP_SUPPLIERS.Vendor_ID%TYPE

,Vendor_Site_Id AP_SUPPLIER_SITES_ALL.Vendor_Site_ID%TYPE

,Pay_Site_Flag AP_SUPPLIER_SITES_ALL.Pay_Site_Flag%TYPE

,Primary_Pay_Site AP_SUPPLIER_SITES_ALL.Primary_Pay_Site_Flag%TYPE

);

TYPE r_vendor_info IS RECORD

(

Auto_calc_int_flag AP_SUPPLIERS.Auto_calculate_interest_flag%TYPE

,Vendor_name AP_SUPPLIERS.Vendor_name%TYPE

);

TYPE r_apinv_info IS RECORD

(

Vendor_id AP_INVOICES_ALL.vendor_id%TYPE

,Vendor_Site_id AP_INVOICES_ALL.vendor_site_id%TYPE

,Party_Id AP_INVOICES_ALL.party_id%TYPE

,Party_Site_Id AP_INVOICES_ALL.party_site_id%TYPE

,External_Bank_Account_Id AP_PAYMENT_SCHEDULES_ALL.external_bank_account_id%TYPE

,Pmt_Currency_code AP_INVOICES_ALL.payment_currency_code%TYPE

,Payment_Method AP_INVOICES_ALL.Payment_Method_Code%TYPE

,Inv_Currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE

,Org_id AP_INVOICES_ALL.org_id%TYPE

,Payment_Function AP_INVOICES_ALL.payment_function%TYPE

,Pay_Proc_Trxn_Type_Code AP_INVOICES_ALL.pay_proc_trxn_type_code%TYPE

);

l_vendor_rec r_vendor_info;

l_inv_rec r_apinv_info;

l_apvs_rec r_ap_ven_sites_info;

l_check_rec r_check_info;

l_asp_rec r_ap_system_param_info;

l_manual_payment_flag VARCHAR2(1);

l_num_printed_docs NUMBER;

l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;

l_pmt_ref_num IBY_PAYMENTS_ALL.payment_reference_number%TYPE;

l_errorIds IBY_DISBURSE_SINGLE_PMT_PKG.trxnErrorIdsTab;

l_msg_index_out NUMBER;

l_payment_id NUMBER;

l_error_msg VARCHAR2(2000);

l_bank_acct_use_id NUMBER; -- Bug 21841763

BEGIN

print_debuginfo(l_module_name, 'ENTER',

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_invoice_id : ' || p_invoice_id,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_payment_type_flag: '||p_payment_type_flag,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_internal_bank_acct_id: '||p_internal_bank_acct_id,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_payment_method: '||p_payment_method_code,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_payment_profile_id: '||p_payment_profile_id,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_payment_document_id: '||p_payment_document_id,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_take_discount: '||p_take_discount,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_doc_category_code: '||p_doc_category_code,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_exchange_rate_type: '||p_exchange_rate_type,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_exchange_rate: '||to_char(p_exchange_rate),

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'p_exchange_date: '||p_exchange_date,

G_LEVEL_PROCEDURE);

/* standard call to check for api compatibility */

IF NOT FND_API.Compatible_API_Call(

l_api_version,

p_api_version,

l_api_name,

G_PKG_NAME) THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

END IF;

/* initialize message list if p_init_msg_list is set to TRUE. */

IF FND_API.to_Boolean(p_init_msg_list) THEN

FND_MSG_PUB.initialize;

END IF;

/* initialize API return status to success */

x_return_status := FND_API.G_RET_STS_SUCCESS;

l_temp_status := 'S';

l_debug_info := 'Get invoice_info';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

BEGIN

-- All we want to make sure here is that can we pay the invoice

-- that has been passed in (invoice_id)

l_debug_info := 'Select the Invoice if it can be paid ';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

SELECT apiv.vendor_id,

apiv.vendor_site_id,

apiv.party_id,

apiv.party_site_id,

apiv.external_bank_account_id,

ai.payment_currency_code,

ai.invoice_currency_code,

ai.payment_method_code,

ai.org_id,

NVL(ai.payment_function, 'PAYABLES_DISB'),

NVL(ai.pay_proc_trxn_type_code, 'PAYABLES_DOC')

INTO l_inv_rec.vendor_id,

l_inv_rec.vendor_site_id,

l_inv_rec.party_id,

l_inv_rec.party_site_id,

l_inv_rec.external_bank_account_id,

l_inv_rec.pmt_currency_code,

l_inv_rec.inv_currency_code,

l_inv_rec.payment_method,

l_inv_rec.org_id,

l_inv_rec.payment_function,

l_inv_rec.pay_proc_trxn_type_code

FROM ap_invoices_ready_to_pay_v apiv,

ap_invoices_all ai

WHERE apiv.invoice_id = ai.invoice_id

AND apiv.invoice_id = p_invoice_id

AND apiv.payment_method_code = NVL(p_payment_method_code,

apiv.payment_method_code)

GROUP BY apiv.vendor_id,

apiv.vendor_site_id,

apiv.party_id,

apiv.party_site_id,

apiv.external_bank_account_id,

ai.payment_currency_code,

ai.invoice_currency_code,

ai.payment_method_code,

ai.org_id,

ai.payment_function,

ai.pay_proc_trxn_type_code;

l_debug_info := 'Get system parameter info ';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

l_debug_info := 'Select System Option based on Invoice Org Id ';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

SELECT nvl(asp.auto_calculate_interest_flag,'N'),

asp.base_currency_code,

nvl(asp.pay_doc_category_override, 'N'),

nvl(make_rate_mandatory_flag,'N'),

set_of_books_id,

nvl(default_exchange_rate_type, 'User'),

nvl(multi_currency_flag,'N')

INTO l_asp_rec.auto_calc_int_flag,

l_asp_rec.base_currency_code,

l_asp_rec.pay_doc_override,

l_asp_rec.make_rate_mandatory_flag,

l_asp_rec.set_of_books_id,

l_asp_rec.xrate_type,

l_asp_rec.multi_currency_flag

FROM ap_system_parameters_all asp

WHERE org_id = l_inv_rec.org_id;

-- In the Payment work bench user has the option to to change the

-- payment date, so here we check to see if what they have passed

-- or the current date is within an open period.

l_debug_info := 'Get open period name ';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

l_period_name := ap_utilities_pkg.get_current_gl_date(p_check_date,

l_inv_rec.org_id);

IF l_period_name IS NULL THEN

-- We call the ap debug pkg to get the message onto the stack. We pass

-- in a TRUE value for the P_called_Online parameter (the last param)

-- because we don't want the ap debug pkg to get it off the stack at

-- the same time.

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_PERIOD_NOT_OPEN');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END IF;

l_debug_info := 'Checking For Valid Internal Bank Account';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

BEGIN

SELECT BA.account_owner_org_id legal_entity_id,

BA.bank_account_name,

BA.bank_account_num,

BA.account_classification,

CBB.branch_number,

Cbau.bank_acct_use_id -- Bug 21841763

INTO l_check_rec.legal_entity_id,

l_check_rec.bank_account_name,

l_check_rec.bank_account_num,

l_check_rec.bank_account_type,

l_check_rec.bank_num,

l_bank_acct_use_id -- Bug 21841763

FROM CE_BANK_ACCOUNTS BA,

CE_BANK_BRANCHES_V CBB,

CE_BANK_ACCT_USES_OU_V CBAU

WHERE CBAU.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID

AND CBB.branch_party_id = BA.bank_branch_id

AND SYSDATE < NVL(BA.END_DATE,SYSDATE+1)

AND BA.ACCOUNT_CLASSIFICATION = 'INTERNAL'

AND CBAU.ap_use_enable_flag = 'Y'

AND CBAU.org_id = l_inv_rec.org_id

AND CBAU.bank_account_id = p_internal_bank_acct_id; /* Added for bug#12971160 */

/*AND CBAU.bank_acct_use_id = p_internal_bank_acct_id; Commented for bug#12971160 */

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_CE_INVALID_BANK_ACCT');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END;

l_debug_info := 'Checking Exchange Rate related info';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

IF (l_inv_rec.pmt_currency_code <> l_asp_rec.base_currency_code) THEN

l_check_rec.xrate_type := nvl(P_Exchange_Rate_Type,

l_asp_rec.xrate_type);

IF (l_check_rec.xrate_type = 'User' ) THEN

IF (P_exchange_rate is NULL) THEN

-- State that we need to to have an exchange rate if the

-- type is USER this is irrespective of the value of the

-- make_rate_mandatory_flag on AP_SYSTEM_PARAMETERS

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_USER_XRATE');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

ELSE

l_check_rec.xrate_date := nvl(trunc(P_exchange_date),trunc(sysdate)); -- bug 21531246

l_check_rec.xrate := P_Exchange_Rate;

END IF;

ELSE -- exchange rate is other than USER

-- --------------------------------------------------------

-- To calculate the exchange_rate we need to see what the

-- the value of the system parameter make_rate_mandatory_flag

-- --------------------------------------------------------

l_check_rec.xrate_date := nvl(trunc(P_exchange_date),trunc(sysdate));

l_check_rec.xrate := ap_utilities_pkg.get_exchange_rate(

l_inv_rec.pmt_currency_code,

l_asp_rec.base_currency_code,

l_check_rec.xrate_type,

l_check_rec.xrate_date,

'APAYFULB');

IF (l_check_rec.xrate is NULL

AND l_asp_rec.make_rate_mandatory_flag = 'Y') THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_XRATE');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END IF; -- mandatory_flag = Y

END IF; -- Exchange Rate = USER

END IF; -- currency_code <> base_currency_code

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_INVOICE_CANNOT_BE_PAID');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

WHEN TOO_MANY_ROWS THEN

-- This means that the payment schedules for the one invoice may

-- have different payment methods. If that is the case, we cannot

-- handle in one payment.

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_MISMATCHED_PMT_SCHEDS');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END;

l_debug_info := 'Check if the Invoice is being paid by a payment batch';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

SELECT count(*)

INTO l_num_invs_sel_for_pmt

FROM ap_selected_invoices_all

WHERE invoice_id = P_invoice_id;

IF (l_num_invs_sel_for_pmt > 0) THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_INV_PAY_FAIL_SEL_BY_BATCH');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END IF;

|| ==============================================================

|| Verify if we should pay invoices for this supplier

|| -------------------------------------------------------------

|| We want to verify that if Oracle Payables should place all

|| payments for this supplier on HOLD also, verify that the

|| vendor site that we have selected in step 1 is Pay Site

|| which is also active

|| ==============================================================

l_debug_info := 'Verrify Supplier Site Address related info';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

BEGIN

SELECT nvl(asup.hold_all_payments_flag,'N'),

nvl(assp.pay_site_flag, 'N'),

asup.vendor_type_lookup_code

INTO l_hold_all_payments_flag,

l_active_pay_sites,

l_vendor_type_lookup_code

FROM ap_suppliers asup,

ap_supplier_sites_all assp

WHERE asup.vendor_id = l_inv_rec.vendor_id

AND asup.vendor_id = assp.vendor_id

AND assp.vendor_site_id = l_inv_rec.vendor_site_id;

IF (l_hold_all_payments_flag = 'Y' or l_active_pay_sites = 'N') THEN

RAISE NO_DATA_FOUND;

ELSE

IF l_vendor_type_lookup_code <> 'EMPLOYEE' THEN

SELECT hzl.Address1,

hzl.Address2,

hzl.Address3,

hzl.City,

hzl.Country,

hzl.Postal_Code,

hzl.Province,

hzl.State,

hzl.Address4,

hzl.County,

hzl.Address_Style,

asus.Vendor_Id,

asus.Vendor_Site_Id,

asus.Vendor_site_code,

nvl(asus.Pay_Site_Flag,'N'),

nvl(asus.Primary_Pay_Site_Flag,'N')

INTO l_apvs_rec.Address_Line1,

l_apvs_rec.Address_Line2,

l_apvs_rec.Address_Line3,

l_apvs_rec.City,

l_apvs_rec.Country,

l_apvs_rec.Zip,

l_apvs_rec.Province,

l_apvs_rec.State,

l_apvs_rec.Address_Line4,

l_apvs_rec.County,

l_apvs_rec.Address_Style,

l_apvs_rec.Vendor_Id,

l_apvs_rec.Vendor_Site_Id,

l_apvs_rec.Vendor_site_code,

l_apvs_rec.Pay_Site_Flag,

l_apvs_rec.Primary_Pay_Site

FROM ap_supplier_sites_all asus,

hz_locations hzl

WHERE asus.vendor_site_id = l_inv_rec.vendor_site_id

AND asus.location_id = hzl.location_id

AND nvl(trunc(asus.inactive_date),sysdate+1) > trunc(sysdate);

IF l_apvs_rec.Pay_Site_Flag = 'N' THEN

BEGIN

-- We want to search if there is a Primary Pay Site for

-- for this Vendor

SELECT hzl.Address1,

hzl.Address2,

hzl.Address3,

hzl.City,

hzl.Country,

hzl.Postal_Code,

hzl.Province,

hzl.State,

hzl.Address4,

hzl.County,

hzl.Address_Style,

asus.Vendor_Id,

asus.Vendor_Site_Id,

asus.Vendor_Site_Code,

nvl(asus.Pay_Site_Flag,'N'),

nvl(asus.Primary_Pay_Site_Flag,'N')

INTO l_apvs_rec.Address_Line1,

l_apvs_rec.Address_Line2,

l_apvs_rec.Address_Line3,

l_apvs_rec.City,

l_apvs_rec.Country,

l_apvs_rec.Zip,

l_apvs_rec.Province,

l_apvs_rec.State,

l_apvs_rec.Address_Line4,

l_apvs_rec.County,

l_apvs_rec.Address_Style,

l_apvs_rec.Vendor_Id,

l_apvs_rec.Vendor_Site_Id,

l_apvs_rec.Vendor_site_code,

l_apvs_rec.Pay_Site_Flag,

l_apvs_rec.Primary_Pay_Site

FROM ap_supplier_sites_all asus,

hz_locations hzl

WHERE asus.vendor_site_id = l_inv_rec.vendor_site_id

AND asus.location_id = hzl.location_id

AND nvl(trunc(asus.inactive_date),sysdate+1)

> trunc(sysdate)

AND nvl(asus.primary_pay_site_flag,'N') = 'Y';

EXCEPTION

WHEN NO_DATA_FOUND THEN

BEGIN

SELECT hzl.Address1,

hzl.Address2,

hzl.Address3,

hzl.City,

hzl.Country,

hzl.Postal_Code,

hzl.Province,

hzl.State,

hzl.Address4,

hzl.County,

hzl.Address_Style,

asus.Vendor_Id,

asus.Vendor_Site_Id,

asus.Vendor_site_code,

nvl(asus.Pay_Site_Flag,'N'),

nvl(asus.Primary_Pay_Site_Flag,'N')

INTO l_apvs_rec.Address_Line1,

l_apvs_rec.Address_Line2,

l_apvs_rec.Address_Line3,

l_apvs_rec.City,

l_apvs_rec.Country,

l_apvs_rec.Zip,

l_apvs_rec.Province,

l_apvs_rec.State,

l_apvs_rec.Address_Line4,

l_apvs_rec.County,

l_apvs_rec.Address_Style,

l_apvs_rec.Vendor_Id,

l_apvs_rec.Vendor_Site_Id,

l_apvs_rec.Vendor_site_code,

l_apvs_rec.Pay_Site_Flag,

l_apvs_rec.Primary_Pay_Site

FROM ap_supplier_sites_all asus,

hz_locations hzl

WHERE asus.vendor_site_id = l_inv_rec.vendor_site_id

AND asus.location_id = hzl.location_id

AND nvl(trunc(asus.inactive_date),sysdate+1)

> trunc(sysdate)

AND nvl(asus.pay_site_flag,'N') = 'Y'

AND rownum = 1;

-- We want to find ANY Pay Site for this Vendor

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL; -- Can't have no data found here as there

-- has to be another pay site (verify)

END;

END IF; -- l_apvs_rec.Pay_Site_Flag = 'N'

END IF; -- l_vendor_type_lookup_code

END IF;

EXCEPTION

WHEN NO_DATA_FOUND Then

-- The Vendor Site that was on the invoice does not exist or

-- we cannot pay invoices for this vendor or the vendor has no

-- active pay sites

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_VENDOR_SITE');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END;

-- At this stage the we are assuming that the Vendor Site is a

-- pay site and is active

-- Verify if Processing Type of Payment Process Profile provided

-- is 'PRINTED', then Payment Document Id needs to be provided

l_debug_info := 'Deriving Proceeing Type for Payment Process Profile';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

BEGIN

SELECT processing_type,

print_instruction_immed_flag,

default_printer

INTO l_processing_type,

l_print_instr_immed_flag,

l_default_printer

FROM IBY_PAYMENT_PROFILES

WHERE payment_profile_id = p_payment_profile_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_IBY_INVALID_PAYMENT_PROF');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END;

-- Verify payment document is needed or not

IF l_processing_type = 'PRINTED' THEN

IF p_payment_document_id IS NULL THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_CE_PAYMENT_DOC_NEEDED');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END IF;

-- Verify whether payment_document_id provided is locked by

-- batch and also valid for the internal bank account that

-- is provided

l_debug_info := 'Check If Payment Document Id is Valid and Get the next check';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

IF p_payment_document_id IS NOT NULL THEN

BEGIN

SELECT payment_document_id

INTO l_payment_document_id

FROM CE_PAYMENT_DOCUMENTS

WHERE payment_document_id = p_payment_document_id

AND internal_bank_account_id = p_internal_bank_acct_id

AND payment_instruction_id IS NULL;

IBY_DISBURSE_UI_API_PUB_PKG.Validate_Paper_Doc_Number

(p_api_version => 1.0,

p_init_msg_list => p_init_msg_list,

p_payment_doc_id => p_payment_document_id,

x_paper_doc_num => l_next_check_number,

x_return_status => l_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

show_warn_msgs_flag => 'T');

IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_PAY_DOCUMENT_ALREADY_IN_USE');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END;

END IF;

|| =======================================================

|| Need to get the party name from hz_parties as it

|| it is needed for the insert into ap_checks

|| =======================================================

l_debug_info := 'Get Supplier Party Name';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

BEGIN

SELECT asup.auto_calculate_interest_flag,

hp.party_name

INTO l_vendor_rec.Auto_calc_int_flag,

l_vendor_rec.Vendor_name

FROM ap_suppliers asup,

hz_parties hp

WHERE asup.vendor_id = l_inv_rec.vendor_id

AND asup.party_id = hp.party_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_PAY_NO_VENDOR');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END;

|| ==============================================================

|| - Lock Invoices

|| --------------------------------------------------------------

|| Payment workbench actually calls the following code to

|| AP_PAY_IN_FULL_PKG.AP_LOCK_INVOICES

|| However all this code does is that it locks all the invoices

|| that are passed in via the p_invoice_id_list parameter.

|| It also will return the payment_method_code for each

|| payment_num on the list p_payment_num_list.

|| It will also return the following bits of information needed

|| to insert a row in the table AP_CHECKS_ALL

|| Currency_code,

|| Payment_method,

|| Vendor_id, <-- Already have this information

|| Vendor_site_id <-- Alreadt have this information

|| For this requirement, we will always have a single invoice

|| and a single payment so all we really need to do is to lock

|| the invoice row

|| Any additional bits of information we need, we can select

|| outside in this procedure.

|| ==============================================================

l_debug_info := 'Lock The Invoice';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

AP_INVOICES_PKG.LOCK_ROW(p_invoice_id,

l_curr_calling_sequence);

|| ==============================================================

|| -- - Get the the follwoing for the document :-

|| o Status_Lookup_Code

|| o Payment_Type_Flag

|| --------------------------------------------------------------

-- Derive check_status based on payment method

l_debug_info := 'Is Payment Method Bills Payable enabled ';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

--Afzal

l_maturity_date := null;

l_check_rec.status_lookup_code := 'NEGOTIABLE';

BEGIN

SELECT support_bills_payable_flag,

maturity_date_offset_days

INTO l_bills_payable,

l_maturity_date_offset_days

FROM IBY_PAYMENT_METHODS_VL

WHERE payment_method_code = l_inv_rec.payment_method;

--l_maturity_date Afzal

IF l_bills_payable = 'Y' THEN

l_check_rec.status_lookup_code := 'ISSUED';

l_maturity_date := NVL(trunc(p_check_date),trunc(sysdate))

+ NVL(l_maturity_date_offset_days, 0);

ELSE

l_check_rec.status_lookup_code := 'NEGOTIABLE';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_IBY_INVALID_PAYMENT_METHOD');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(

p_count => x_msg_count,

p_data => x_msg_data

);

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

l_temp_status := 'E';

END;

--Afzal

-- ---------------------------------------------------------------

-- Note: We will only populate the Checkrun_ID for Automatic

-- Payments i.e. Payments in a Batch for Quick and Manual

-- Payments this value will be Null.

-- ---------------------------------------------------------------

-- Step 1 : Sequential Numbering Logic

-- ---------------------------------------------------------------

FND_PROFILE.GET('UNIQUE:SEQ_NUMBERS',l_seq_num_profile);

--5007576, re-wrote the doc sequencing section below

l_doc_category_code := p_doc_category_code;

if l_seq_num_profile in ('P','A') then

if l_doc_category_code is not null then

begin

SELECT 'row exists'

INTO l_valid_sequence_exists

FROM fnd_doc_sequence_categories

WHERE code = l_doc_category_code

AND table_name IN ('AP_CHECKS','AP_CHECKS_ALL');

exception

when no_data_found then

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_NO_DOC_CAT');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,

p_data => x_msg_data);

print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);

l_temp_status := 'E';

return;

end;

else

CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat(p_payment_document_id,

p_payment_method_code,

p_internal_bank_acct_id,

l_doc_category_code);

end if;

if l_doc_category_code = '-1' and l_seq_num_profile = 'A' then

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_DOC_CAT_NO_FOUND');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,

p_data => x_msg_data);

print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);

l_temp_status := 'E';

return;

end if;

if l_doc_category_code <> '-1' and

l_asp_rec.pay_doc_override <> 'Y' and

l_doc_category_code <> p_doc_category_code then

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_DOC_NO_REQ');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,

p_data => x_msg_data);

print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);

l_temp_status := 'E';

return;

end if;

--at this point if we haven't rejected we need to verify the sequence assignment is valid

if l_doc_category_code <> '-1' then

l_return_code := fnd_seqnum.get_seq_info(

app_id => 200,

cat_code => l_doc_category_code,

sob_id => l_asp_rec.set_of_books_id,

met_code => 'A',

trx_date => nvl(trunc(p_check_date), trunc(sysdate)),

docseq_id => l_docseq_id,

docseq_type => l_docseq_type,

docseq_name => l_docseq_name,

db_seq_name => l_db_seq_name,

seq_ass_id => l_seq_ass_id,

prd_tab_name => l_prd_tab_name,

aud_tab_name => l_aud_tab_name,

msg_flag => l_msg_flag);

--reject if invalid and seq numbering is always used

if (l_return_code <> 0 or l_docseq_type = 'M') and l_seq_num_profile = 'A' then

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_CREATE_ERROR');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,

p_data => x_msg_data);

print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);

l_temp_status := 'E';

return;

elsif l_return_code = 0 and l_docseq_type = 'A' then

--get seq value

l_return_code := FND_SEQNUM.GET_SEQ_VAL(

200,

l_doc_category_code,

l_asp_rec.set_of_books_id,

'A',

nvl(trunc(p_check_date), trunc(sysdate)),

l_seqval,

l_dbseqid,

'N',

'N');

if l_return_code <> 0 and l_seq_num_profile = 'A' then

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_CREATE_ERROR');

FND_MSG_PUB.ADD;

FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,

p_data => x_msg_data);

print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);

l_temp_status := 'E';

return;

end if;

end if; --l_doc_category_code is not null then

end if; --l_seq_num_profile in ('P','A')

-- We call Get_Single_Payment_Amount to get the amount for the payment

-- net of the available discount. We pass in NULL for the payment num

-- parameter because we want the entire invoice (all pmt scheds)

-- to be paid

l_debug_info := 'Getting the Check Amount';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

l_amount := AP_PAY_IN_FULL_PKG.AP_Get_Check_Amount(

to_char(p_invoice_id),

NULL,

p_payment_type_flag,

sysdate,

l_inv_rec.Pmt_Currency_code,

p_take_discount,

l_asp_rec.Auto_Calc_Int_Flag,

l_vendor_rec.auto_calc_int_flag,

'APAYFULB');

IF ( l_inv_rec.Pmt_Currency_code <> l_asp_rec.base_currency_code ) THEN

IF ( l_check_rec.xrate_type = 'User' ) THEN

l_base_amount := ap_utilities_pkg.ap_round_currency(

(l_amount*l_check_rec.xrate),

l_asp_rec.base_currency_code);

ELSE -- exchange rate is other than USER

l_base_amount := gl_currency_api.convert_amount(

l_inv_rec.pmt_currency_code,

l_asp_rec.base_currency_code,

l_check_rec.xrate_date,

l_check_rec.xrate_type,

l_amount);

END IF;

-- Only when we are sure we want to insert should we select the

-- check_id

|| ==============================================================

|| Step ?: Get Check_ID for creaation of record in the table

|| AP_CHECKS

|| --------------------------------------------------------------

-- First select the value for the CHECK_ID before insert

SELECT ap_checks_s.nextval

INTO l_check_rec.check_id

FROM sys.dual;

l_debug_info := 'Get Quick Check Id';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

--start of the custom code An?l Altunkan

if l_next_check_number is null and l_seq_num_profile in ('P') and p_doc_category_code is null then

l_next_check_number := -1;

end if;

--end of the custom code An?l Altunkan

IF (P_payment_type_flag = 'Q') THEN

SELECT alc1.displayed_field

INTO l_quick_check_id

FROM ap_lookup_codes alc1

WHERE alc1.lookup_type = 'NLS TRANSLATION'

AND alc1.lookup_code = 'QUICKCHECK ID';

l_check_rec.checkrun_name :=

substrb(l_quick_check_id, 1,

30 - nvl(lengthb(to_char(l_check_rec.check_id)), 0))

||to_char(l_check_rec.check_id);

END IF;

|| ==============================================================

|| Step ? - Create Check (record in the table AP_CHECKS

|| --------------------------------------------------------------

l_debug_info := 'Calling Ap_Check_Pkg.Insert_Row';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

IF (l_Doc_Category_Code = '-1') THEN

l_Doc_Category_Code := NULL;

END IF;

AP_CHECKS_PKG.Insert_Row(

X_Rowid => l_dummy_rowid,

X_Amount => l_amount,

X_Ce_Bank_Acct_Use_Id => l_bank_acct_use_id , --p_internal_bank_acct_id, -- Bug 21841763

X_Bank_Account_Name => l_check_rec.bank_account_name,

X_Check_Date => nvl(trunc(p_check_date),

trunc(sysdate)),

X_Check_Id => l_check_rec.check_id,

X_Check_Number => l_next_check_number,

X_Currency_Code => l_inv_rec.Pmt_Currency_code,

X_Last_Updated_By => FND_GLOBAL.USER_ID,

X_Last_Update_Date => SYSDATE,

X_Payment_Type_Flag => p_payment_type_flag,

X_Address_Line1 => l_apvs_rec.Address_Line1,

X_Address_Line2 => l_apvs_rec.Address_Line2,

X_Address_Line3 => l_apvs_rec.Address_Line3,

X_Checkrun_Name => l_check_rec.Checkrun_Name,

X_Check_Format_Id => NULL,

X_Check_Stock_Id => NULL,

X_City => l_apvs_rec.City,

X_Country => l_apvs_rec.Country,

X_Created_By => FND_GLOBAL.USER_ID,

X_Creation_Date => SYSDATE,

X_Last_Update_Login => FND_GLOBAL.USER_ID,

X_Status_Lookup_Code => l_check_rec.status_lookup_code,

X_Vendor_Name => l_vendor_rec.vendor_name,

X_Vendor_Site_Code => l_apvs_rec.vendor_site_code,

X_External_Bank_Account_Id => l_inv_rec.external_bank_account_id,

X_Zip => l_apvs_rec.Zip,

X_Bank_Account_Num => NULL, -- For Electronic Pmts

X_Bank_Account_Type => NULL, -- For Electronic Pmts

X_Bank_Num => NULL, -- For Electronic Pmts

X_Check_Voucher_Num => NULL,

X_Cleared_Amount => NULL,

X_Cleared_Date => NULL,

X_Doc_Category_Code => l_Doc_Category_Code,

X_Doc_Sequence_Id => l_dbseqid, -- Seq Num

X_Doc_Sequence_Value => l_seqval, -- Seq Num

X_Province => l_apvs_rec.Province, -- PO_VENDORS

X_Released_Date => NULL,

X_Released_By => NULL,

X_State => l_apvs_rec.State,

X_Stopped_Date => NULL,

X_Stopped_By => NULL,

X_Void_Date => NULL,

X_Attribute1 => NULL,

X_Attribute10 => NULL,

X_Attribute11 => NULL,

X_Attribute12 => NULL,

X_Attribute13 => NULL,

X_Attribute14 => NULL,

X_Attribute15 => NULL,

X_Attribute2 => NULL,

X_Attribute3 => NULL,

X_Attribute4 => NULL,

X_Attribute5 => NULL,

X_Attribute6 => NULL,

X_Attribute7 => NULL,

X_Attribute8 => NULL,

X_Attribute9 => NULL,

X_Attribute_Category => NULL,

X_Future_Pay_Due_Date => l_maturity_date,

X_Treasury_Pay_Date => NULL,

X_Treasury_Pay_Number => NULL,

X_Withholding_Status_Lkup_Code => NULL,

X_Reconciliation_Batch_Id => NULL,

X_Cleared_Base_Amount => NULL,

X_Cleared_Exchange_Rate => NULL,

X_Cleared_Exchange_Date => NULL,

X_Cleared_Exchange_Rate_Type => NULL,

X_Address_Line4 => l_apvs_rec.Address_Line4,

X_County => l_apvs_rec.County,

X_Address_Style => l_apvs_rec.Address_Style,

X_Org_id => l_inv_rec.org_id,

X_Vendor_Id => l_inv_rec.vendor_id,

X_Vendor_Site_Id => l_apvs_rec.Vendor_Site_Id,

X_Exchange_Rate => l_check_rec.xrate,

X_Exchange_Date => l_check_rec.xrate_date,

X_Exchange_Rate_Type => l_check_rec.xrate_type,

X_Base_Amount => l_base_amount,

X_Checkrun_Id => NULL,

X_Calling_Sequence => 'APAYFULB.PLS',

X_Global_Attribute_Category => NULL,

X_Global_Attribute1 => NULL,

X_Global_Attribute2 => NULL,

X_Global_Attribute3 => NULL,

X_Global_Attribute4 => NULL,

X_Global_Attribute5 => NULL,

X_Global_Attribute6 => NULL,

X_Global_Attribute7 => NULL,

X_Global_Attribute8 => NULL,

X_Global_Attribute9 => NULL,

X_Global_Attribute10 => NULL,

X_Global_Attribute11 => NULL,

X_Global_Attribute12 => NULL,

X_Global_Attribute13 => NULL,

X_Global_Attribute14 => NULL,

X_Global_Attribute15 => NULL,

X_Global_Attribute16 => NULL,

X_Global_Attribute17 => NULL,

X_Global_Attribute18 => NULL,

X_Global_Attribute19 => NULL,

X_Global_Attribute20 => NULL,

X_transfer_priority => NULL,

X_maturity_exchange_rate_type => NULL,

X_maturity_exchange_date => NULL,

X_maturity_exchange_rate => NULL,

X_description => NULL,

X_anticipated_value_date => NULL,

X_actual_value_date => NULL,

X_payment_profile_id => p_payment_profile_id,

X_bank_charge_bearer => NULL,

X_settlement_priority => NULL,

X_payment_method_code => l_inv_rec.payment_method,

X_payment_document_id => p_payment_document_id,

X_party_id => l_inv_rec.party_id,

X_party_site_id => l_inv_rec.party_site_id,

X_legal_entity_id => l_check_rec.legal_entity_id,

X_payment_id => NULL);

l_debug_info := 'Calling Ap_Reconciliation_Pkg.Insert_Payment_History';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

IF ( p_payment_type_flag = 'R' ) THEN

l_transaction_type := 'REFUND RECORDED';

ELSE

l_transaction_type := 'PAYMENT CREATED';

END IF;

l_debug_info := 'transaction type = ' || l_transaction_type;

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

-- Bug3343314

AP_RECONCILIATION_PKG.insert_payment_history

(

x_check_id => l_check_rec.check_id,

x_transaction_type => l_transaction_type,

x_accounting_date => nvl(trunc(p_check_date),

trunc(sysdate)),

x_trx_bank_amount => NULL,

x_errors_bank_amount => NULL,

x_charges_bank_amount => NULL,

x_bank_currency_code => NULL,

x_bank_to_base_xrate_type => NULL,

x_bank_to_base_xrate_date => NULL,

x_bank_to_base_xrate => NULL,

x_trx_pmt_amount => l_amount,

x_errors_pmt_amount => NULL,

x_charges_pmt_amount => NULL,

x_pmt_currency_code => l_inv_rec.pmt_currency_code,

x_pmt_to_base_xrate_type => l_check_rec.xrate_type,

x_pmt_to_base_xrate_date => l_check_rec.xrate_date,

x_pmt_to_base_xrate => l_check_rec.xrate,

x_trx_base_amount => l_base_amount,

x_errors_base_amount => NULL,

x_charges_base_amount => NULL,

x_matched_flag => NULL,

x_rev_pmt_hist_id => NULL,

x_org_id => l_inv_rec.org_id,

x_creation_date => SYSDATE,

x_created_by => FND_GLOBAL.User_Id,

x_last_update_date => SYSDATE,

x_last_updated_by => FND_GLOBAL.User_Id,

x_last_update_login => FND_GLOBAL.User_Id,

x_program_update_date => NULL,

x_program_application_id => NULL,

x_program_id => NULL,

x_request_id => NULL,

x_calling_sequence => l_curr_calling_sequence,

x_accounting_event_id => l_accounting_event_id

);

-- l_accounting_event_id is IN parameter and is populated with

-- NULL in AIP. to Avoid this getting accounting_event_id from APH

-- Bug 19047932

l_debug_info := 'Getting accounting_event_id value for AIP';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

select accounting_event_id into l_accounting_event_id

from ap_payment_history_all

where check_id = l_check_rec.check_id

and transaction_type = l_transaction_type;

-- Any witholding distributions or Interest Invoices are created

-- in the call to Create_Single_Payments.

l_debug_info := 'Calling Ap_Pay_In_Full_Pkg.Create_Payments';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

AP_PAY_IN_FULL_PKG.AP_Create_Payments(

to_char(p_invoice_id),

NULL,

l_check_rec.check_id,

p_payment_type_flag,

l_inv_rec.Payment_Method,

p_internal_bank_acct_id,

null, -- bank_account_num

null, -- bank_account_type,

null, -- bank_num,

nvl(trunc(p_check_date), trunc(sysdate)),

l_period_name,

l_inv_rec.Pmt_currency_code,

l_asp_rec.base_currency_code,

l_check_rec.Checkrun_Name,

l_seqval,

l_dbseqid,

l_check_rec.xrate,

l_check_rec.xrate_type,

l_check_rec.xrate_date,

p_take_discount, -- Pay In Full Take Discount

l_asp_rec.auto_calc_int_flag,

l_vendor_rec.auto_calc_int_flag,

l_asp_rec.set_of_books_id,

null, -- future_pay_code_combination_id

FND_GLOBAL.USER_ID,

'APXPAWKB',

l_seq_num_profile,

l_accounting_Event_id,

l_inv_rec.org_id);

l_debug_info := 'Calling IBY Single Payment API';

print_debuginfo(l_module_name, l_debug_info,

G_LEVEL_PROCEDURE);

IF l_temp_status <> 'E' THEN

IF p_payment_type_flag IN ('Q', 'M') THEN

IF p_payment_type_flag = 'Q' THEN

l_manual_payment_flag := 'N';

--bug 5982788

select checkrun_name into l_check_rec.Checkrun_name

from ap_checks_all

where check_id = l_check_rec.check_id ;

ELSIF p_payment_type_flag = 'M' THEN

l_manual_payment_flag := 'Y';

--bug 5982788

update ap_checks_all

set checkrun_name = l_check_rec.check_id

where check_id = l_check_rec.check_id ;

select checkrun_name into l_check_rec.Checkrun_name

from ap_checks_all

where check_id = l_check_rec.check_id ;

END IF;

--Bug 5982788

IBY_DISBURSE_SINGLE_PMT_PKG.submit_single_payment(

p_api_version => 1.0,

p_init_msg_list => p_init_msg_list,

p_calling_app_id => 200,

p_calling_app_payreq_cd => l_check_rec.Checkrun_Name,

p_is_manual_payment_flag => l_manual_payment_flag,

p_payment_function => l_inv_rec.payment_function,

p_internal_bank_account_id => p_internal_bank_acct_id,

p_pay_process_profile_id => p_payment_profile_id,

p_payment_method_cd => l_inv_rec.payment_method,

p_legal_entity_id => l_check_rec.legal_entity_id,

p_organization_id => l_inv_rec.org_id,

p_organization_type => 'OPERATING_UNIT',

p_payment_date => nvl(trunc(p_check_date),

trunc(sysdate)),

p_payment_amount => l_amount,

p_payment_currency => l_inv_rec.pmt_currency_code,

p_payee_party_id => l_inv_rec.party_id,

p_payee_party_site_id => l_inv_rec.party_site_id,

p_supplier_site_id => l_inv_rec.vendor_site_id,

p_payee_bank_account_id => l_inv_rec.external_bank_account_id,

p_override_pmt_complete_pt => 'Y',

p_bill_payable_flag => NVL(l_bills_payable, 'N'),

p_anticipated_value_date => NULL,

p_maturity_date => l_maturity_date,

p_payment_document_id => p_payment_document_id,

p_paper_document_number => l_next_check_number,

p_printer_name => l_default_printer,

p_print_immediate_flag => l_print_instr_immed_flag,

p_transmit_immediate_flag => Null,

p_payee_address_line1 => l_apvs_rec.address_line1,

p_payee_address_line2 => l_apvs_rec.address_line2,

p_payee_address_line3 => l_apvs_rec.address_line3,

p_payee_address_line4 => l_apvs_rec.address_line4,

p_payee_address_city => l_apvs_rec.city,

p_payee_address_county => l_apvs_rec.county,

p_payee_address_state => l_apvs_rec.state,

p_payee_address_zip => l_apvs_rec.zip,

p_payee_address_country => l_apvs_rec.country,

p_attribute_category => Null,

p_attribute1 => Null,

p_attribute2 => Null,

p_attribute3 => Null,

p_attribute4 => Null,

p_attribute5 => Null,

p_attribute6 => Null,

p_attribute7 => Null,

p_attribute8 => Null,

p_attribute9 => Null,

p_attribute10 => Null,

p_attribute11 => Null,

p_attribute12 => Null,

p_attribute13 => Null,

p_attribute14 => Null,

p_attribute15 => Null,

x_num_printed_docs => l_num_printed_docs,

x_payment_id => l_payment_id,

x_paper_doc_num => l_paper_doc_num,

x_pmt_ref_num => l_pmt_ref_num,

x_return_status => l_return_status,

x_error_ids_tab => x_errorIds,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN

UPDATE AP_CHECKS_ALL

SET payment_id = l_payment_id

WHERE check_id = l_check_rec.check_id;

IF p_payment_type_flag <> 'M' THEN

IF l_paper_doc_num IS NOT NULL THEN

l_check_number := l_paper_doc_num;

ELSE

l_check_number := l_pmt_ref_num;

END IF;

IF l_next_check_number <> l_check_number THEN

UPDATE AP_CHECKS_ALL

SET check_number = l_check_number

WHERE check_id = l_check_rec.check_id;

END IF;

x_return_status := l_return_status;

ELSE

x_return_status := l_return_status;

END IF;

END IF; -- p_payment_type_flag

ELSE

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

END IF; -- l_temp_status

EXCEPTION

WHEN OTHERS THEN

print_debuginfo(l_module_name, 'Exception occured when '

|| 'Payable single payment API fialed. Single payment creation will '

|| 'be aborted and no records will be committed for '

|| 'the invoice_id: '

|| p_invoice_id, G_LEVEL_PROCEDURE

);

print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE,

G_LEVEL_PROCEDURE);

print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM,

G_LEVEL_PROCEDURE);

x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

print_debuginfo(l_module_name, 'EXIT',

G_LEVEL_PROCEDURE);

RETURN;

END AP_Pay_Invoice_In_Full;

END XXAP_PAY_SINGLE_INVOICE_PKG;

Search This Blog

Sunday, March 10, 2024

Query GL Apps R12 Difference in two Years group by Months

Wednesday, February 28, 2024

Apps R12 API to Create Supplier, Sites with interface Table

Apps R12 to create Code Combination from Back End.

Tuesday, December 19, 2023

Sales Order API Apps R12 using interface Tables

Sunday, December 10, 2023

Apps R12 AP Payments for Pre-payments