Supplier and Supplier Sites Interfaces
RDBMS : 9.2.0.8.0
Oracle Applications : 11.5.10.2
Interface Tables :-
a) ap_suppliers_int
b) ap_supplier_sites_int
Import Program :-
1) Supplier Open Interface Import
2) Supplier Sites Open Interface Import
Steps
1) Preapare the data template :-
LEGACY_SUPP_CODE, VENDOR_NAME,VENDOR_TYPE,
PAYMENT_TERMS,INVOICE_CURRENCY,PAYMENT_CURRENCY,
ACCTS_PAY_CODE_COMBINATION,PREPAY_CODE_COMBINATION,
VENDOR_SITE_CODE,ADDRESS1,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,
CITY,STATE ,COUNTRY,ZIP,CONTACTS_NAME,PHONE ,EMAIL ,
SHIP_TO_LOC,BILL_TO_LOC
Here all are not mandatory fields.
Pls customize the template according to your requriements.
2) Now create the staging table :-
CREATE TABLE XXX_AP_SUPPLIERS_STG
(LEGACY_SUPP_CODE NUMBER(10),
VENDOR_NAME VARCHAR2(100),
VENDOR_TYPE VARCHAR2(30),
PAYMENT_TERMS VARCHAR2(100),
INVOICE_CURRENCY VARCHAR2(10),
PAYMENT_CURRENCY VARCHAR2(10),
ACCTS_PAY_CODE_COMBINATION VARCHAR2(50),
PREPAY_CODE_COMBINATION VARCHAR2(50),
VENDOR_SITE_CODE VARCHAR2(50),
ADDRESS1 VARCHAR2(100),
ADDRESS2 VARCHAR2(100),
ADDRESS3 VARCHAR2(100),
ADDRESS4 VARCHAR2(100),
CITY VARCHAR2(50),
STATE VARCHAR2(50),
COUNTRY VARCHAR2(30),
ZIP VARCHAR2(10),
CONTACTS_NAME VARCHAR2(30),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(100),
SHIP_TO_LOC VARCHAR2(100),
BILL_TO_LOC VARCHAR2(100),
PAN_NO VARCHAR2(30),
VERIFY_FLAG CHAR(1),
L_VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(2500))
3) Import data from data template ie. excel file to staging table
You can import data through different ways.
a) By using control file and sqlloader
b) Through Toad.(Version 8.5 and above)
4) Execute the following Procedure in Toad
->Open the procedure editior
-> Paste the following procedure
->Make changes according to your requirement
-> Press Ctl+Enter.
Now the procdure created.
-> Go to schema browser
-> Procedure
-> XXX_create_supplier_api
->right button
-> execute procedure.
The below procedure will do the basic validation required
and upload data into interface tables. The importance of this script
is you will get your customized error message in stagingtable and
you can easily identify and rectify the errors.
CREATE OR REPLACE PROCEDURE xxx_create_supplier_api
AS
l_vendor_type varchar2(30);
l_verify_flag varchar(1);
l_error_message varchar2(2500);
l_invoice_currency varchar2(10);
l_payment_currency varchar2(10);
l_term_id number(10);
l_pay_code_combination_id number(10);
l_prepay_code_combination_id number(10);
l_org_id number(10);
l_territory_code varchar2(10);
l_cnt number(3);
l_location_id number(10);
l_vendor_name varchar2(150);l
_vendor_site_code varchar2(100);
CURSOR c_supp IS
SELECT distinct vendor_type
,payment_terms
,vendor_name
FROM xxx_ap_suppliers_stg
where nvl(verify_flag,'N') = 'N';
CURSOR c_supp_site (p_supp_name varchar2) IS
SELECT *
FROM xxx_ap_suppliers_stg
WHERE vendor_name = p_supp_name;
BEGIN
FOR H1 IN c_supp
LOOP
l_verify_flag := 'Y' ;
l_error_message := NULL ;
l_cnt := 0 ;
l_vendor_name := NULL;
BEGIN
select count(*)
into l_cnt
from po_vendors
where trim(upper(vendor_name)) = trim(upper(H1.vendor_name));
IF l_cnt > 0 then
l_verify_flag := 'N';
l_error_message:= 'Vendor is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
select organization_id
into l_org_id
from hr_operating_units
where name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Operating Unit is Invalid';
END;
BEGIN
select location_id
into l_location_id
from hr_locations
where location_code = 'xxx Main Store Location';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Location is Not Valid';
END;
BEGIN
SELECT lookup_code
INTO l_vendor_type
FROM po_lookup_codes
WHERE lookup_type(+) = 'VENDOR TYPE'
AND UPPER(lookup_code) = UPPER(TRIM(H1.vendor_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Vendor Type Lookup Code not existing';
END;
BEGIN
select currency_code
into l_invoice_currency
from fnd_currencies
where currency_code = trim(H1.invoice_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Invoice Currency Code is not Valid';
END;
BEGIN
select currency_codeinto
l_payment_currency
from fnd_currencies
where currency_code = trim(H1.payment_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:=l_error_message'Payament Currency Cocde is not valid';
END;
BEGIN
select term_id
into l_term_id
from ap_terms
where upper(name) = upper(trim(H1.payment_terms)) ;
EXCEPTION
when others then
l_verify_flag := 'N';
l_error_message := l_error_message'Payment Term is not valid';
END;
BEGIN
select code_combination_id
into l_pay_code_combination_id
from gl_code_combinations
where segment1'.'segment2'.'
egment3'.'segment4'.'
segment5'.'segment6= H1.accts_pay_code_combination ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message
'Accounts Pay CodeCombination is Not Valid';
END;
BEGIN
select code_combination_id
into l_prepay_code_combination_id
from gl_code_combinations
where segment1'.'segment2'.'segment3'.'
segment4'.'segment5'.' segment6=
H1.prepay_code_combination;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Pre-Pay Code Combination is Not Valid';
END;
IF H1.vendor_name is null then
l_verify_flag := 'N';
l_error_message := l_error_message'Vendor Name is not existing';
end if;
savepoint A;
IF l_verify_flag <> 'N' THEN
BEGIN
INSERT INTO
ap.ap_suppliers_int
(
vendor_interface_id,
vendor_name,
vendor_name_alt,
vendor_type_lookup_code,
invoice_currency_code,
payment_currency_code,
terms_id,
accts_pay_code_combination_id,
prepay_code_combination_id,
bill_to_location_id,
ship_to_location_id,
receiving_routing_id,
inspection_required_flag,
receipt_required_flag
)
VALUES
(
ap_suppliers_int_s.nextval,
trim(H1.vendor_name),
trim(H1.vendor_name),
l_vendor_type,
l_invoice_currency,
l_payment_currency,
l_term_id,
l_pay_code_combination_id,
l_prepay_code_combination_id,
l_location_id,
l_location_id,1,'N','Y'
) ;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'Y'
WHERE vendor_name = H1.vendor_name;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = H1.vendor_name;
GOTO next_supp;
END;
FOR L1 IN c_supp_site(H1.vendor_name)
LOOP
l_vendor_site_code := NULL;
BEGIN
select territory_code
into l_territory_code
from fnd_territories
where nls_territory = trim(L1.country) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message 'Invalid Country';
NULL;
END;
BEGIN
select vendor_site_code
into l_vendor_site_code
from po_vendor_sites_all a,
po_vendors b
where org_id = l_org_id
and upper(vendor_site_code) = trim(upper(L1.vendor_site_code))
and a.vendor_id = b.vendor_id
and upper(b.vendor_name) = trim(upper(L1.vendor_name)) ;
IF l_vendor_site_code is not null then
l_verify_flag := 'N';
l_error_message := l_error_message 'Vendor Site is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
IF l_verify_flag <> 'N' then
BEGIN
INSERT INTO
ap.ap_supplier_sites_int
(
vendor_interface_id,
vendor_site_code,
vendor_site_code_alt,
address_line1,
address_line2,
address_line3,
city,
state,
country,
zip,
phone,
accts_pay_code_combination_id ,
prepay_code_combination_id ,
org_id,
attribute_category,
attribute10,
ship_to_location_id,
bill_to_location_id,
hold_unmatched_invoices_flag
)
VALUES
(
ap_suppliers_int_s.CURRVAL,
trim(L1.vendor_site_code),
trim(L1.vendor_site_code),
trim(L1.address1),
trim(L1.address2),
trim(L1.address3),
trim(L1.city),
trim(L1.state),
l_territory_code,
trim(L1.zip),
trim(L1.phone),
l_pay_code_combination_id,
l_prepay_code_combination_id ,
l_org_id,
Legacy Supplier Code',
trim(L1.Baan_Supp_code),
l_location_id,
l_location_id,
'N'
) ;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'Y'
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
rollback to savepoint A;
l_error_message := SQLERRM;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name = L1.vendor_name;
GOTO next_supp;
END;
ELSE
rollback to savepoint A;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name = L1.vendor_name;
GOTO next_supp;
END IF;
END LOOP ;
ELSE
UPDATE xxx_ap_suppliers_stg
SET error_message = l_error_message,
verify_flag = 'N'
WHERE vendor_name = H1.vendor_name;
END IF;
<<>>
Commit;
END LOOP ;
END xxx_create_supplier_api;
Go to payable responsibility and run the following concurrent programs
1) Supplier Open Interface Import
2) Sites Open Interface Import
After completing the first concurrent pgm, run the second one.
And you can see the imported no of records and rejected no of records in View output.
Oracle Applications : 11.5.10.2
Interface Tables :-
a) ap_suppliers_int
b) ap_supplier_sites_int
Import Program :-
1) Supplier Open Interface Import
2) Supplier Sites Open Interface Import
Steps
1) Preapare the data template :-
LEGACY_SUPP_CODE, VENDOR_NAME,VENDOR_TYPE,
PAYMENT_TERMS,INVOICE_CURRENCY,PAYMENT_CURRENCY,
ACCTS_PAY_CODE_COMBINATION,PREPAY_CODE_COMBINATION,
VENDOR_SITE_CODE,ADDRESS1,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,
CITY,STATE ,COUNTRY,ZIP,CONTACTS_NAME,PHONE ,EMAIL ,
SHIP_TO_LOC,BILL_TO_LOC
Here all are not mandatory fields.
Pls customize the template according to your requriements.
2) Now create the staging table :-
CREATE TABLE XXX_AP_SUPPLIERS_STG
(LEGACY_SUPP_CODE NUMBER(10),
VENDOR_NAME VARCHAR2(100),
VENDOR_TYPE VARCHAR2(30),
PAYMENT_TERMS VARCHAR2(100),
INVOICE_CURRENCY VARCHAR2(10),
PAYMENT_CURRENCY VARCHAR2(10),
ACCTS_PAY_CODE_COMBINATION VARCHAR2(50),
PREPAY_CODE_COMBINATION VARCHAR2(50),
VENDOR_SITE_CODE VARCHAR2(50),
ADDRESS1 VARCHAR2(100),
ADDRESS2 VARCHAR2(100),
ADDRESS3 VARCHAR2(100),
ADDRESS4 VARCHAR2(100),
CITY VARCHAR2(50),
STATE VARCHAR2(50),
COUNTRY VARCHAR2(30),
ZIP VARCHAR2(10),
CONTACTS_NAME VARCHAR2(30),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(100),
SHIP_TO_LOC VARCHAR2(100),
BILL_TO_LOC VARCHAR2(100),
PAN_NO VARCHAR2(30),
VERIFY_FLAG CHAR(1),
L_VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(2500))
3) Import data from data template ie. excel file to staging table
You can import data through different ways.
a) By using control file and sqlloader
b) Through Toad.(Version 8.5 and above)
4) Execute the following Procedure in Toad
->Open the procedure editior
-> Paste the following procedure
->Make changes according to your requirement
-> Press Ctl+Enter.
Now the procdure created.
-> Go to schema browser
-> Procedure
-> XXX_create_supplier_api
->right button
-> execute procedure.
The below procedure will do the basic validation required
and upload data into interface tables. The importance of this script
is you will get your customized error message in stagingtable and
you can easily identify and rectify the errors.
CREATE OR REPLACE PROCEDURE xxx_create_supplier_api
AS
l_vendor_type varchar2(30);
l_verify_flag varchar(1);
l_error_message varchar2(2500);
l_invoice_currency varchar2(10);
l_payment_currency varchar2(10);
l_term_id number(10);
l_pay_code_combination_id number(10);
l_prepay_code_combination_id number(10);
l_org_id number(10);
l_territory_code varchar2(10);
l_cnt number(3);
l_location_id number(10);
l_vendor_name varchar2(150);l
_vendor_site_code varchar2(100);
CURSOR c_supp IS
SELECT distinct vendor_type
,payment_terms
,vendor_name
FROM xxx_ap_suppliers_stg
where nvl(verify_flag,'N') = 'N';
CURSOR c_supp_site (p_supp_name varchar2) IS
SELECT *
FROM xxx_ap_suppliers_stg
WHERE vendor_name = p_supp_name;
BEGIN
FOR H1 IN c_supp
LOOP
l_verify_flag := 'Y' ;
l_error_message := NULL ;
l_cnt := 0 ;
l_vendor_name := NULL;
BEGIN
select count(*)
into l_cnt
from po_vendors
where trim(upper(vendor_name)) = trim(upper(H1.vendor_name));
IF l_cnt > 0 then
l_verify_flag := 'N';
l_error_message:= 'Vendor is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
select organization_id
into l_org_id
from hr_operating_units
where name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Operating Unit is Invalid';
END;
BEGIN
select location_id
into l_location_id
from hr_locations
where location_code = 'xxx Main Store Location';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Location is Not Valid';
END;
BEGIN
SELECT lookup_code
INTO l_vendor_type
FROM po_lookup_codes
WHERE lookup_type(+) = 'VENDOR TYPE'
AND UPPER(lookup_code) = UPPER(TRIM(H1.vendor_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Vendor Type Lookup Code not existing';
END;
BEGIN
select currency_code
into l_invoice_currency
from fnd_currencies
where currency_code = trim(H1.invoice_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Invoice Currency Code is not Valid';
END;
BEGIN
select currency_codeinto
l_payment_currency
from fnd_currencies
where currency_code = trim(H1.payment_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:=l_error_message'Payament Currency Cocde is not valid';
END;
BEGIN
select term_id
into l_term_id
from ap_terms
where upper(name) = upper(trim(H1.payment_terms)) ;
EXCEPTION
when others then
l_verify_flag := 'N';
l_error_message := l_error_message'Payment Term is not valid';
END;
BEGIN
select code_combination_id
into l_pay_code_combination_id
from gl_code_combinations
where segment1'.'segment2'.'
egment3'.'segment4'.'
segment5'.'segment6= H1.accts_pay_code_combination ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message
'Accounts Pay CodeCombination is Not Valid';
END;
BEGIN
select code_combination_id
into l_prepay_code_combination_id
from gl_code_combinations
where segment1'.'segment2'.'segment3'.'
segment4'.'segment5'.' segment6=
H1.prepay_code_combination;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Pre-Pay Code Combination is Not Valid';
END;
IF H1.vendor_name is null then
l_verify_flag := 'N';
l_error_message := l_error_message'Vendor Name is not existing';
end if;
savepoint A;
IF l_verify_flag <> 'N' THEN
BEGIN
INSERT INTO
ap.ap_suppliers_int
(
vendor_interface_id,
vendor_name,
vendor_name_alt,
vendor_type_lookup_code,
invoice_currency_code,
payment_currency_code,
terms_id,
accts_pay_code_combination_id,
prepay_code_combination_id,
bill_to_location_id,
ship_to_location_id,
receiving_routing_id,
inspection_required_flag,
receipt_required_flag
)
VALUES
(
ap_suppliers_int_s.nextval,
trim(H1.vendor_name),
trim(H1.vendor_name),
l_vendor_type,
l_invoice_currency,
l_payment_currency,
l_term_id,
l_pay_code_combination_id,
l_prepay_code_combination_id,
l_location_id,
l_location_id,1,'N','Y'
) ;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'Y'
WHERE vendor_name = H1.vendor_name;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = H1.vendor_name;
GOTO next_supp;
END;
FOR L1 IN c_supp_site(H1.vendor_name)
LOOP
l_vendor_site_code := NULL;
BEGIN
select territory_code
into l_territory_code
from fnd_territories
where nls_territory = trim(L1.country) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message 'Invalid Country';
NULL;
END;
BEGIN
select vendor_site_code
into l_vendor_site_code
from po_vendor_sites_all a,
po_vendors b
where org_id = l_org_id
and upper(vendor_site_code) = trim(upper(L1.vendor_site_code))
and a.vendor_id = b.vendor_id
and upper(b.vendor_name) = trim(upper(L1.vendor_name)) ;
IF l_vendor_site_code is not null then
l_verify_flag := 'N';
l_error_message := l_error_message 'Vendor Site is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
IF l_verify_flag <> 'N' then
BEGIN
INSERT INTO
ap.ap_supplier_sites_int
(
vendor_interface_id,
vendor_site_code,
vendor_site_code_alt,
address_line1,
address_line2,
address_line3,
city,
state,
country,
zip,
phone,
accts_pay_code_combination_id ,
prepay_code_combination_id ,
org_id,
attribute_category,
attribute10,
ship_to_location_id,
bill_to_location_id,
hold_unmatched_invoices_flag
)
VALUES
(
ap_suppliers_int_s.CURRVAL,
trim(L1.vendor_site_code),
trim(L1.vendor_site_code),
trim(L1.address1),
trim(L1.address2),
trim(L1.address3),
trim(L1.city),
trim(L1.state),
l_territory_code,
trim(L1.zip),
trim(L1.phone),
l_pay_code_combination_id,
l_prepay_code_combination_id ,
l_org_id,
Legacy Supplier Code',
trim(L1.Baan_Supp_code),
l_location_id,
l_location_id,
'N'
) ;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'Y'
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
rollback to savepoint A;
l_error_message := SQLERRM;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name = L1.vendor_name;
GOTO next_supp;
END;
ELSE
rollback to savepoint A;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name = L1.vendor_name;
GOTO next_supp;
END IF;
END LOOP ;
ELSE
UPDATE xxx_ap_suppliers_stg
SET error_message = l_error_message,
verify_flag = 'N'
WHERE vendor_name = H1.vendor_name;
END IF;
<<>>
Commit;
END LOOP ;
END xxx_create_supplier_api;
Go to payable responsibility and run the following concurrent programs
1) Supplier Open Interface Import
2) Sites Open Interface Import
After completing the first concurrent pgm, run the second one.
And you can see the imported no of records and rejected no of records in View output.
Sales Order Interface
Interface Tables are :-
-----------------------
a) oe_headers_iface_all
b) oe_lines_iface_all
Import Order Program
-----------------------
Order Management
-> Oracle Order Management
-> Orders, Returns
-> Import Orders
-> Order Import Request
To upload the price even if list price existing :-
---------------------------------------------
calculate_price_flag = 'N', then populate unit_selling_price and unit_list_price through line interface table
To re-run error records :-
-----------------------------
Once the error rectified, error_flag and request_id should be null in header as well as line tables.
Then again submit the import concurrent program.
Staging tables
-------------
:- For Sales Order header
-------------------------
create table xxx_so_header_stg
(
legacy_so_num varchar2(15 byte),
customer_name varchar2(150 byte),
customer_no varchar2(10 byte),
bill_to varchar2(5 byte),
ship_to varchar2(5 byte),
transaction_type varchar2(30 byte),
customer_po_no varchar2(100 byte),
order_date date,
price_list varchar2(20 byte),
payment_terms varchar2(50 byte),
warehouse varchar2(5 byte),
verify_flag char(1 byte),
error_message varchar2(3000 byte),
no_lines varchar2(1 byte)
)
:- For Sales Order Lines
------------------------
create table xxx_so_lines_stg
(
legacy_so_num varchar2(15 byte),
line_num number,
item_code varchar2(50 byte),
uom varchar2(10 byte),
line_type varchar2(30 byte),
qty number(10),
price_unit number(10,2),
request_date date,
scheduled_ship_date date,
verify_flag char(1 byte),
error_message varchar2(3000 byte),
no_header varchar2(1 byte)
)
PL/SQL Script to migrate data from legacy to interface
-------------------------------------------------------
CREATE OR REPLACE PROCEDURE xxx_so_api
IS
l_price_list qp_list_headers_tl.list_header_id%type;
l_verify_flag char(1);
l_error_message varchar2(3000);
l_term_id ra_terms_tl.term_id%type;
l_order_type oe_transaction_types_tl.transaction_type_id%type;
l_order_source_id oe_order_sources.order_source_id%type;
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_org_id hr_operating_units.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);
cursor c_header
is
select *
from xxx_so_header_stg
where nvl(verify_flag,'N') = 'N' ;
cursor c_lines(p_legacy_so_num varchar2)
is select *
from xxx_so_lines_stg
where legacy_so_num = p_legacy_so_num
and nvl(verify_flag,'N') = 'N';
BEGIN
for h1 in c_header
loop
l_error_message := null;
l_verify_flag := 'Y';
BEGIN
select organization_id
into l_org_id
from hr_operating_units
where name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Operating Unit is Invalid';
END;
BEGIN
select list_header_id
into l_price_list
from qp_list_headers_tl
where upper(name) = upper('xxx SPARES 08-09');
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Price List Is not valid';
END;
BEGIN
select term_id
into l_term_id
from ra_terms_tl
where upper(name) = upper(trim(h1.payment_terms));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Payment Term ID is not Valid';
END;
BEGIN /* Order Type */
select transaction_type_id
into l_order_type
from oe_transaction_types_tl
where upper(name) = trim(upper(h1.transaction_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Order Transaction Type is not Valid';
END;
BEGIN
select order_source_id
into l_order_source_id
from OE_ORDER_SOURCES
where NAME = 'Online' ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Order Source is not Valid';
END;
BEGIN
select organization_id
into l_organization_id
from org_organization_definitions
where organization_code = trim(h1.warehouse);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Ware House is not Existing';
END;
BEGIN
select customer_id
into l_customer_id --Sold To OrgID
from ra_customers
where upper(customer_name) = trim(upper(h1.customer_name))
and status = 'A';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Customer Name not Valid';
END;
BEGIN
select user_id
into l_user_id
from fnd_user
where user_name = 'KPMG' ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'User Name is Invalid';
END;
begin
select ship_su.site_use_id
into l_ship_to_org_id
from hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps
where ship_su.org_id = l_org_id
and ship_su.org_id = ship_cas.org_id
and ship_su.location = trim(h1.ship_to)
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
AND ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_ps.party_site_id in (select party_site_id
from hz_party_sites
where party_id = (select party_id
from ra_customers
where customer_id = l_customer_id
and status = 'A'));
exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message||'Invalid Ship To';
end;
begin
select ship_su.site_use_id
into l_bill_to_org_id
from hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps
where ship_su.org_id = l_org_id
and ship_su.org_id = ship_cas.org_id
and ship_su.location = trim(h1.bill_to)
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
AND ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_ps.party_site_id in (select party_site_id
from hz_party_sites
where party_id = (select party_id
from ra_customers
where customer_id = l_customer_id
and status = 'A'));
exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message||'Invalid Bill To';
end;
if l_verify_flag <> 'N' then
begin
savepoint a;
insert into oe_headers_iface_all
(
order_source_id
,orig_sys_document_ref
,org_id
,sold_from_org_id
,ship_from_org_id
,ordered_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
)
values
(
l_order_source_id --order_source_id
,oe_order_headers_s.nextval --orig_sys_document_ref
,l_org_id --org_id
,l_org_id --sold_from_org_id
,l_organization_id --ship_from_org_id
,trim(h1.order_date) --ordered_date
,l_order_type --order_type_id
,l_customer_id --sold_to_org_id
,l_term_id --payment_term_id
,'CREATE' --operation_code
,l_user_id --created_by
,sysdate --creation_date
,l_user_id --last_updated_by
,sysdate --last_update_date
,null --customer_po_number
,l_price_list --price_list_id
,'xxx Legacy SO Number'
,trim(h1.legacy_so_num)
,l_ship_to_org_id
,l_bill_to_org_id
);
update xxx_so_header_stg
set verify_flag = 'Y'
where customer_name = h1.customer_name
and legacy_so_num = h1.legacy_so_num;
exception
when others then
l_error_message := sqlerrm;
l_verify_flag := 'N';
update xxx_so_header_stg
set verify_flag = 'N',
error_message = 'Header error'||l_error_message
where customer_name = h1.customer_name
and legacy_so_num = h1.legacy_so_num;
goto next_so;
end;
------------------Line Details ---------------------
for l1 in c_lines (h1.legacy_so_num)
loop
BEGIN
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where upper(segment1||'.'||segment2||'.'||segment3||'.'||segment4) = trim(upper(l1.item_code))
and organization_id = l_organization_id ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Item Name is not existing';
END;
BEGIN
select uom_code
into l_uom_code
from mtl_units_of_measure_vl
where uom_code = trim(upper(l1.uom));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'UOM is Not Valid';
END;
BEGIN
select transaction_type_id
into l_line_type_id
from oe_transaction_types_tl
where name = trim(upper(l1.line_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Line Type is Not Valid';
END;
if l_verify_flag <> 'N' then
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
,request_date
,created_by
,creation_date
,last_updated_by
,last_update_date
,line_type_id
,calculate_price_flag
)
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(l1.line_num) --line_number
,l_inventory_item_id --inventory_item_id
,trim(l1.qty) --ordered_quantity
,l_organization_id --ship_from_org_id
,l_org_id --ship_from_org_id
,trim(l1.qty) --pricing_quantity
,trim(l1.price_unit) --unit_selling_price
,trim(l1.price_unit) --unit_list_price
,l_price_list --price_list_id
,l_term_id --payment_term_id
,to_date(l1.scheduled_ship_date,'DD-MON-RRRR') --schedule_ship_date
,to_date(l1.request_date,'DD-MON-RRRR') --request_date
,l_user_id --created_by
,sysdate --creation_date
,l_user_id --last_updated_by
,sysdate --last_update_date
,l_line_type_id --line_type_id
,'N'
);
update xxx_so_lines_stg
set verify_flag = 'Y'
where legacy_so_num = l1.legacy_so_num
and item_code = l1.item_code;
exception
when others then
rollback to a;
l_error_message := sqlerrm;
update xxx_so_lines_stg
set verify_flag = 'N',
error_message = 'Line error'||l_error_message
where legacy_so_num = l1.legacy_so_num
and item_code = l1.item_code;
update xxx_so_header_stg
set verify_flag = 'N'
where legacy_so_num = l1.legacy_so_num;
goto next_so;
end;
else
rollback to a;
update xxx_so_lines_stg
set verify_flag = 'N',
error_message = l_error_message
where legacy_so_num = l1.legacy_so_num
and item_code = l1.item_code;
update xxx_so_header_stg
set verify_flag = 'N'
where legacy_so_num = l1.legacy_so_num;
goto next_so;
end if;
end loop ;
else
update xxx_so_header_stg
set verify_flag = 'N',
error_message = l_error_message
where legacy_so_num = h1.legacy_so_num;
end if;
next_so --(This code will be inside << >>)
commit;
end loop;
END xxx_so_api;
/
Purchase Order Interface
Interface Tables :-
-----------------
po_headers_interface
po_lines_interface
Import Concurrent Program :- Import Standard Purchase Orders
Staging Table :-
--------------
CREATE TABLE XXX_PURCHASE_ORDER_STG
(
LEGACY_PONUM NUMBER(20),
CURRENCY_CODE VARCHAR2(15 BYTE),
VENDOR_NAME VARCHAR2(240 BYTE),
VENDOR_SITE_CODE VARCHAR2(15 BYTE),
BILL_TO VARCHAR2(60 BYTE),
SHIP_TO VARCHAR2(60 BYTE),
STATUS VARCHAR2(40 BYTE),
AGENT_NAME VARCHAR2(100 BYTE),
ITEM VARCHAR2(100 BYTE),
LINE_NUM NUMBER(3),
UNIT_PRICE NUMBER(10,2),
QUANTITY NUMBER(10),
NEED_BY_DATE DATE,
H_VERIFY_FLAG CHAR(1 BYTE),
L_VERIFY_FLAG CHAR(1 BYTE),
H_ERROR_MESSAGE VARCHAR2(2500 BYTE),
L_ERROR_MESSAGE VARCHAR2(2500 BYTE)
)
PL/SQL Script to upload data from staging table to interface table
------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE xxx_po_api
AS
---To Import data from Interface to Base Tables - Import Standard Purchase Orders
-- please do the following: to see the errors
-- Run the program - Purchasing Interface Errors Report
-- choose parameter : PO_DOCS_OPEN_INTERFACE
-- po_interface_errors
l_currency_code fnd_currencies_vl.currency_code%type;
l_verify_flag Char(1);
l_error_message varchar2(5000);
l_vendor_id po_vendors.vendor_id%type;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_ship_to hr_locations.location_id%type;
l_bill_to hr_locations.location_id%type;
l_inventory_item_id mtl_system_items_b.inventory_item_id%type;
l_legacy_ponum number(20):=0;
l_batch_id number(3);
CURSOR C_PO_HEADER IS
select distinct legacy_ponum,
currency_code,
vendor_name,
vendor_site_code,
ship_to,
bill_to,
status
from xxx_purchase_order_stg;
CURSOR C_PO_LINES(l_legacy_ponum NUMBER) IS
select *
from xxx_purchase_order_stg
where trim(legacy_ponum) = trim(l_legacy_ponum);
BEGIN
FOR H1 IN C_PO_HEADER
LOOP
l_verify_flag := 'Y' ;
l_error_message := NULL;
BEGIN
select currency_code
into l_currency_code
from fnd_currencies_vl
where enabled_flag = 'Y'
and currency_flag = 'Y'
and upper(currency_code) = upper(trim(H1.currency_code));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Currency Code is not Valid...';
END;
BEGIN
select vendor_id
into l_vendor_id
from po_vendors
where upper(vendor_name) = upper(trim(H1.vendor_name)) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Vendor is not Existing...';
END;
BEGIN
select vendor_site_id
into l_vendor_site_id
from po_vendor_sites_all
where vendor_id = l_vendor_id
and vendor_site_code = upper(trim(H1.vendor_site_code)) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Vendor Site is not Existing...';
END;
BEGIN
select location_id
into l_ship_to
from hr_locations
where location_code = upper(trim(H1.ship_to));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Ship To Location is not Existing...';
END;
BEGIN
select location_id
into l_bill_to
from hr_locations
where location_code = upper(trim(H1.bill_to));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Bill To Location is not Existing...';
END;
If H1.status = 'Approved' then
l_batch_id := 100 ;
elsif H1.status = 'Incomplete' then
l_batch_id := 101 ;
else
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Status is not valid...';
end if;
l_legacy_ponum := trim(H1.legacy_ponum) ;
IF l_verify_flag <> 'N' THEN
insert into po_headers_interface
(interface_header_id,
batch_id,
action,
document_type_code,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location_id,
bill_to_location_id
)
values
(po_headers_interface_s.nextval,
l_batch_id,
'ORIGINAL',
'STANDARD',
l_currency_code,
5479,
l_vendor_id,
l_vendor_site_id,
l_ship_to,
l_bill_to );
update xxx_purchase_order_stg
set h_verify_flag = 'Y'
where legacy_ponum = l_legacy_ponum;
COMMIT;
FOR L1 IN C_PO_LINES(l_legacy_ponum)
LOOP
BEGIN
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where segment1||'.'||segment2||'.'||segment3||'.'||segment4=L1.item
and organization_id = (select inventory_organization_id
from hr_locations
where location_id = l_ship_to ) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Inventory Item is not Existing...';
END;
IF L1.unit_price IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Unit Price is not Existing...';
ELSIF L1.quantity IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Quantity is not Existing...';
ELSIF L1.need_by_date IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Need By Date is not Existing...';
END IF;
IF l_verify_flag <> 'N' THEN
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
line_num,
item_id,
unit_price,
quantity,
Need_By_Date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
L1.line_num,
l_inventory_item_id,
L1.unit_price,
L1.quantity,
L1.need_by_date);
update xxx_purchase_order_stg
set l_verify_flag = 'Y'
where legacy_ponum = L1.legacy_ponum
and line_num = L1.line_num;
ELSE
update xxx_purchase_order_stg
set l_error_message = l_error_message,
l_verify_flag = 'N'
where legacy_ponum = L1.legacy_ponum
and line_num = L1.line_num;
END IF;
COMMIT;
END LOOP;
ELSE
update xxx_purchase_order_stg
set h_error_message = l_error_message,
h_verify_flag = 'N'
where legacy_ponum = H1.legacy_ponum;
END IF;
COMMIT;
END LOOP;
end xxx_po_api;
/
Receipt API calling
When working on 11.5.8 for one of my previous clients in Year 2002, I was tasked to build a lockbox interface.
Client had over 400 bank accounts into which cash could be received.Some of these cash transactions were to be reconciled & accounted from Oracle Receivables. For this purpose "Receipt Handling Process" needed a mechanism to load bank receipts into Oracle AR in realtime. For this requirement, I developed an API based process which made possible the integration of "Receipt Handling Process" with Oracle Receivables. This technique was preferred over lockbox approach. The requirement was quite simple, receipts from a third party system were being dumped into a couple of tables. Those receipts were to be validated and transferred into Oracle Receivables. Doing a bit of research on Metalink I found that AR now has a receipt creation API. Please find the steps below for implementing a Receipt creation API in Oracle AR.
Step 1. Validate the following:-
a. Ensure that exchange Rate exists in the system if the receipt being interfaced is a foreign currency receipt.
b. Validate that the receipt belongs to an Open or Future enterable period in GL.
This check can be done by using below function within a package
FUNCTION validate_gl_accounting_date (
p_accounting_date IN DATE
,p_sob_id IN NUMBER
)
RETURN BOOLEAN
IS
v_count NUMBER := 0;
BEGIN
SELECT COUNT ( * )
INTO v_count
FROM gl_period_statuses gps
WHERE gps.application_id = g_gl_application_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ( 'O', 'F' )
AND p_accounting_date BETWEEN NVL ( gps.start_date, p_accounting_date )
AND NVL ( gps.end_date, p_accounting_date );
IF v_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END validate_gl_accounting_date ;
Step 2. After successful validation, call the AR Receipt API ar_receipt_api_pub.create_cash. If the receipt is a foreign currency receipt, then parameters p_exchange_rate_type & p_exchange_rate_date must be supplied with a value. You may pass a value of fnd_api.g_false to parameter p_commit , to facilitate rollback if an errored is encountered in any other part of the functionality. If OUT parameter x_return_status is returned with a value of E, it implies error.
IF p_foriegn_currency THEN
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
,p_exchange_rate_type => xxdhi_util_pkg.g_conversion_type_code
,p_exchange_rate_date => p_rhp_receipt.receipt_date
);
ELSE
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
);
END IF ;Step 3. If the requirement is To reverse an existing receipt, then use API ar_receipt_api_pub.REVERSE. Once again, prefer setting the validation level to fnd_api.g_valid_level_full
.
Following validation must be done at the time of reversal. This is required because Oracle wouldn't let you Reverse an applied receipt straightaway.
FUNCTION validate_reversal_flag_valid(p_cash_receipt_id OUT INTEGER)
RETURN BOOLEAN IS
CURSOR c_check IS
SELECT cash_receipt_id
,status
FROM ar_cash_receipts
WHERE receipt_number =
g_process_name || '-' || p_rhp_receipt.receipt_id || '-' ||
p_rhp_receipt_routing.receipt_routing_id;
p_check c_check%ROWTYPE;
no_existing_receipt EXCEPTION;
receipt_already_applied EXCEPTION;
receipt_already_reversed EXCEPTION;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
IF p_check.cash_receipt_id IS NULL
THEN
RAISE no_existing_receipt;
END IF;
IF p_check.status = 'APP'
THEN
RAISE receipt_already_applied;
END IF;
IF p_check.status = 'REV'
THEN
RAISE receipt_already_reversed;
END IF;
p_cash_receipt_id := p_check.cash_receipt_id;
RETURN TRUE;
EXCEPTION
WHEN no_existing_receipt THEN
fnd_message.set_name('AR', 'AR_RAPI_CASH_RCPT_ID_INVALID');
RETURN FALSE;
WHEN receipt_already_applied THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has been applied to transaction in Delphi Oracle Receivables.' ||
chr(10) ||
'Please unapply the receipt before reversing');
RETURN FALSE;
WHEN receipt_already_reversed THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has already been reversed in Delphi Oracle Receivables.');
RETURN FALSE;
END validate_reversal_flag_valid;
Step 1. Validate the following:-
a. Ensure that exchange Rate exists in the system if the receipt being interfaced is a foreign currency receipt.
b. Validate that the receipt belongs to an Open or Future enterable period in GL.
This check can be done by using below function within a package
FUNCTION validate_gl_accounting_date (
p_accounting_date IN DATE
,p_sob_id IN NUMBER
)
RETURN BOOLEAN
IS
v_count NUMBER := 0;
BEGIN
SELECT COUNT ( * )
INTO v_count
FROM gl_period_statuses gps
WHERE gps.application_id = g_gl_application_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ( 'O', 'F' )
AND p_accounting_date BETWEEN NVL ( gps.start_date, p_accounting_date )
AND NVL ( gps.end_date, p_accounting_date );
IF v_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END validate_gl_accounting_date ;
Step 2. After successful validation, call the AR Receipt API ar_receipt_api_pub.create_cash. If the receipt is a foreign currency receipt, then parameters p_exchange_rate_type & p_exchange_rate_date must be supplied with a value. You may pass a value of fnd_api.g_false to parameter p_commit , to facilitate rollback if an errored is encountered in any other part of the functionality. If OUT parameter x_return_status is returned with a value of E, it implies error.
IF p_foriegn_currency THEN
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
,p_exchange_rate_type => xxdhi_util_pkg.g_conversion_type_code
,p_exchange_rate_date => p_rhp_receipt.receipt_date
);
ELSE
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
);
END IF ;Step 3. If the requirement is To reverse an existing receipt, then use API ar_receipt_api_pub.REVERSE. Once again, prefer setting the validation level to fnd_api.g_valid_level_full
.
Following validation must be done at the time of reversal. This is required because Oracle wouldn't let you Reverse an applied receipt straightaway.
FUNCTION validate_reversal_flag_valid(p_cash_receipt_id OUT INTEGER)
RETURN BOOLEAN IS
CURSOR c_check IS
SELECT cash_receipt_id
,status
FROM ar_cash_receipts
WHERE receipt_number =
g_process_name || '-' || p_rhp_receipt.receipt_id || '-' ||
p_rhp_receipt_routing.receipt_routing_id;
p_check c_check%ROWTYPE;
no_existing_receipt EXCEPTION;
receipt_already_applied EXCEPTION;
receipt_already_reversed EXCEPTION;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
IF p_check.cash_receipt_id IS NULL
THEN
RAISE no_existing_receipt;
END IF;
IF p_check.status = 'APP'
THEN
RAISE receipt_already_applied;
END IF;
IF p_check.status = 'REV'
THEN
RAISE receipt_already_reversed;
END IF;
p_cash_receipt_id := p_check.cash_receipt_id;
RETURN TRUE;
EXCEPTION
WHEN no_existing_receipt THEN
fnd_message.set_name('AR', 'AR_RAPI_CASH_RCPT_ID_INVALID');
RETURN FALSE;
WHEN receipt_already_applied THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has been applied to transaction in Delphi Oracle Receivables.' ||
chr(10) ||
'Please unapply the receipt before reversing');
RETURN FALSE;
WHEN receipt_already_reversed THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has already been reversed in Delphi Oracle Receivables.');
RETURN FALSE;
END validate_reversal_flag_valid;
AR Invoice APIS
CREATE OR REPLACE PACKAGE BODY APPS.XXDC_NOV
IS
--
-- This Package is for Coverting data from NOV Ticket and Violation Code files to Transactions, Lines and Receipts..
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ------ ------------------------------------------
FUNCTION get_constant_val (p_constant_name IN VARCHAR2)
RETURN VARCHAR2
IS
v_value VARCHAR2 (1000);
BEGIN
EXECUTE IMMEDIATE 'BEGIN :x := '
|| gc_pkg
|| '.'
|| p_constant_name
|| '; end;'
USING OUT v_value;
RETURN v_value;
END;
PROCEDURE write_log (p_debug IN VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_debug);
END;
PROCEDURE sync_status (p_request_id IN NUMBER, p_retcode OUT VARCHAR2)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.SYNC_STATUS';
CURSOR get_err_tickets_cur (v_req_id IN NUMBER)
IS
SELECT *
FROM xxdc.xxdc_nov_ticket
WHERE request_id = NVL (v_req_id, request_id)
AND process_status IN ('RS', 'RD');
CURSOR get_err_violcode_cur (v_req_id IN NUMBER)
IS
SELECT *
FROM xxdc.xxdc_nov_violcode
WHERE request_id = NVL (v_req_id, request_id)
AND process_status IN ('RS', 'RD');
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
p_retcode := 0;
FOR get_err_tickets_rec IN get_err_tickets_cur (p_request_id)
LOOP
UPDATE xxdc_nov_violcode nv
SET nv.process_status =
DECODE (nv.process_status,
'RS', 'RS',
'RD', 'RD',
get_err_tickets_rec.process_status
),
nv.last_updated_by = fnd_global.user_id,
nv.last_update_date = SYSDATE
WHERE nv.violation_number = get_err_tickets_rec.violation_number
AND nv.request_id = NVL (p_request_id, nv.request_id);
END LOOP;
FOR get_err_violcode_rec IN get_err_violcode_cur (p_request_id)
LOOP
UPDATE xxdc_nov_ticket nt
SET nt.process_status =
DECODE (nt.process_status,
'RS', 'RS',
'RD', 'RD',
get_err_violcode_rec.process_status
),
nt.last_updated_by = fnd_global.user_id,
nt.last_update_date = SYSDATE
WHERE NVL (nt.violation_number, -1) =
get_err_violcode_rec.violation_number
AND nt.request_id = NVL (p_request_id, nt.request_id);
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
p_retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END;
PROCEDURE prevalidation (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_debug IN VARCHAR2 DEFAULT 'Y',
p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.PREVALIDATION';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
retcode := 0;
--xxdc_p2_conv_util.gen_pre_validation (gc_conv_id, p_request_id);
xxdc_p2_conv_util.prevalidation (p_conv_id => gc_conv_id,
p_request_id => p_request_id,
p_log => gv_debug_log_clob,
p_result => retcode
);
xxdc_p2_conv_util.gen_datatype_validation (gc_conv_id, p_request_id);
sync_status (p_request_id, retcode);
UPDATE xxdc_nov_ticket nt
SET nt.last_updated_by = fnd_global.user_id,
nt.last_update_date = SYSDATE,
nt.process_status =
DECODE (nt.process_status,
gc_new_status, gc_pending_status,
nt.process_status
);
UPDATE xxdc_nov_violcode nv
SET nv.last_updated_by = fnd_global.user_id,
nv.last_update_date = SYSDATE,
nv.process_status =
DECODE (nv.process_status,
gc_new_status, gc_pending_status,
nv.process_status
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
END prevalidation;
PROCEDURE convert_data (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_debug IN VARCHAR2 DEFAULT 'Y',
p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.CONVERT_DATA';
v_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
v_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
v_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
v_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
v_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
v_header_rec ar_invoice_api_pub.trx_header_rec_type;
v_line_rec ar_invoice_api_pub.trx_line_rec_type;
v_batch_source_id ra_customer_trx_all.batch_source_id%TYPE;
v_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%TYPE;
v_cust_account_id ra_customer_trx_all.bill_to_customer_id%TYPE;
v_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
v_term_id ra_customer_trx_all.term_id%TYPE;
v_violation_number ra_customer_trx_all.trx_number%TYPE;
v_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
v_memo_line_id ra_customer_trx_lines_all.memo_line_id%TYPE;
v_inventory_item_id ra_customer_trx_lines_all.inventory_item_id%TYPE;
v_receipt_method_id ar_cash_receipts_all.receipt_method_id%TYPE;
v_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
v_nov_trx_hdr_id NUMBER;
v_nov_trx_line_id NUMBER;
v_row_cnt INTEGER := 0;
v_person_id per_all_people_f.person_id%TYPE;
v_msg_index_out NUMBER;
v_issue_date DATE;
v_chr_issue_dt VARCHAR2 (25);
v_chr_dt_att3 VARCHAR2 (25);
v_party_id hz_parties.party_id%TYPE;
v_party_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
CURSOR get_ticket_data_cur
IS
SELECT *
FROM xxdc_nov_ticket
WHERE process_status IN ('P', 'PW', 'E')
-- AND ROWNUM < 2
AND request_id = NVL (p_request_id, request_id);
CURSOR get_violcode_data_cur (v_viol_number IN VARCHAR2)
IS
SELECT *
FROM xxdc_nov_violcode
WHERE violation_number = v_viol_number
AND request_id = NVL (p_request_id, request_id);
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
retcode := '0';
SELECT batch_source_id
INTO v_batch_source_id
FROM ra_batch_sources_all
WHERE UPPER (NAME) = UPPER (gc_batch_source_name);
gv_process_msg := 'Batch Source Id - ' || v_batch_source_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT cust_trx_type_id
INTO v_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE UPPER (NAME) = UPPER (gc_cust_trx_type);
gv_process_msg := 'Customer Trx Type Id - ' || v_cust_trx_type_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT term_id
INTO v_term_id
FROM ra_terms
WHERE UPPER (NAME) = UPPER (gc_term_name);
gv_process_msg := 'Payment Term Id - ' || v_term_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT memo_line_id
INTO v_memo_line_id
FROM ar_memo_lines_all_vl
WHERE UPPER (NAME) = UPPER (gc_memo_line_name);
gv_process_msg := 'v_memo_line_id - ' || v_memo_line_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT receipt_method_id
INTO v_receipt_method_id
FROM ar_receipt_methods
WHERE UPPER (NAME) = UPPER (gc_receipt_method);
gv_process_msg := 'v_receipt_method_id - ' || v_receipt_method_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
FOR get_ticket_data_rec IN get_ticket_data_cur
LOOP
/*
* Resetting the variables.
*/
v_cust_account_id := NULL;
v_site_use_id := NULL;
v_person_id := NULL;
v_header_rec := NULL;
v_line_rec := NULL;
v_batch_source_rec := NULL;
v_customer_trx_id := NULL;
v_cash_receipt_id := NULL;
v_violation_number := NULL;
v_header_tbl.DELETE;
v_lines_tbl.DELETE;
v_dist_tbl.DELETE;
v_salescredits_tbl.DELETE;
/*
* Savepoint for roll back.
*/
SAVEPOINT conv_record;
/*
* Get Customer info for the invoice.
*/
xxdc_p2_conv_util.get_bill_to_acct_site_use_id
(p_caller => v_prog_unit,
p_legacy_acct_num => get_ticket_data_rec.legacy_account_number,
p_bill_to_party_id => v_party_id,
p_cust_acct_id => v_cust_account_id,
p_bill_to_site_id => v_site_use_id,
p_bill_to_site_use_id => v_party_site_use_id
);
/*
* Get Employee number for DFF.
*/
v_person_id :=
xxdc_p2_conv_util.get_person_id
(v_prog_unit,
get_ticket_data_rec.employee_number
);
/*
* Setting warning flag to 'Y' to update the process_status as 'W"
*/
gv_warning_flag := NULL;
IF (v_person_id IS NULL)
THEN
gv_warning_flag := 'Y';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'The employee was not found for '
|| get_ticket_data_rec.violation_number
|| ' for '
|| get_ticket_data_rec.employee_number
);
END IF;
/*
* sequence for trx hdr id.
*/
SELECT xxdc_nov_trx_hdr_s.NEXTVAL
INTO v_nov_trx_hdr_id
FROM DUAL;
v_violation_number := get_ticket_data_rec.violation_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_nov_trx_hdr_id - '
|| v_nov_trx_hdr_id
|| 'v_cust_account_id - '
|| v_cust_account_id
|| 'v_site_use_id - '
|| v_site_use_id
|| 'v_person_id - '
|| v_person_id
|| 'v_violation_number - '
|| v_violation_number,
p_prog_unit => v_prog_unit
);
/*
* Loading Batch Source Info...
*/
v_batch_source_rec.batch_source_id := v_batch_source_id;
/*
* Loading Header Information for invoice..
*/
v_header_rec.trx_header_id := v_nov_trx_hdr_id;
v_header_rec.trx_number :=
'CN'
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY')
|| get_ticket_data_rec.violation_number;
v_header_rec.trx_date :=
NVL (TO_DATE (get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format
),
SYSDATE
);
v_header_rec.trx_currency := gc_currency_code;
v_header_rec.cust_trx_type_id := v_cust_trx_type_id;
v_header_rec.bill_to_customer_id := v_cust_account_id;
v_header_rec.bill_to_site_use_id := v_site_use_id;
v_header_rec.sold_to_customer_id := v_cust_account_id;
v_header_rec.term_id := v_term_id;
v_header_rec.attribute_category := gc_trx_hdr_dff_ctxt;
--Ticket Number
v_header_rec.attribute1 :=
'CN' || get_ticket_data_rec.violation_number;
-- Name of the Inspecter.
v_header_rec.attribute2 := v_person_id;
-- Ticket Date
v_chr_issue_dt := get_ticket_data_rec.ticket_issue_date;
v_issue_date := TO_DATE (v_chr_issue_dt, xxd_utils.gc_dor_dt_format);
v_chr_dt_att3 :=
TO_CHAR (v_issue_date, xxd_utils.gc_fnd_std_dt_format);
v_header_rec.attribute3 := v_chr_dt_att3;
/*v_header_rec.attribute3 :=
TO_CHAR
(TO_DATE (get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format --'MM/DD/YYYY HH24:MI:SS'
),
xxd_utils.gc_fnd_std_dt_format
);
*/
--case comments
v_header_rec.attribute5 := get_ticket_data_rec.case_comment;
v_header_tbl (1) := v_header_rec;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'After setting header table - v_header_tbl - '
|| v_header_tbl.COUNT,
p_prog_unit => v_prog_unit
);
/*
* Violation Code table loop
*/
FOR get_violcode_data_rec IN
get_violcode_data_cur (get_ticket_data_rec.violation_number)
LOOP
/*
* Loading the standard memo line...
*/
IF (v_row_cnt = 0)
THEN
/*
* sequence for trx line id.
*/
SELECT xxdc_nov_trx_line_s.NEXTVAL
INTO v_nov_trx_line_id
FROM DUAL;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'v_row_cnt - '
|| v_row_cnt
|| 'v_nov_trx_line_id - '
|| v_nov_trx_line_id
|| ' trx date - '
|| TO_DATE
(get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format
)
|| 'Date for att 3 - '
|| v_chr_dt_att3,
p_prog_unit => v_prog_unit
);
v_row_cnt := v_row_cnt + 1;
v_line_rec.trx_header_id := v_nov_trx_hdr_id;
v_line_rec.trx_line_id := v_nov_trx_line_id;
v_line_rec.line_number := v_row_cnt;
v_line_rec.quantity_invoiced :=
get_ticket_data_rec.original_fine_amount;
v_line_rec.line_type := gc_line_type;
v_line_rec.memo_line_id := v_memo_line_id;
v_line_rec.amount := get_ticket_data_rec.original_fine_amount;
v_lines_tbl (v_row_cnt) := v_line_rec;
v_line_rec := NULL;
END IF;
/*
* sequence for trx line id.
*/
SELECT xxdc_nov_trx_line_s.NEXTVAL
INTO v_nov_trx_line_id
FROM DUAL;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_row_cnt - '
|| v_row_cnt
|| 'v_nov_trx_line_id - '
|| v_nov_trx_line_id,
p_prog_unit => v_prog_unit
);
/*
* Query for item id for violation code.
*/
v_inventory_item_id :=
xxdc_p2_conv_util.get_inventory_item_id
(v_prog_unit,
get_violcode_data_rec.violation_code
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_inventory_item_id - '
|| v_inventory_item_id,
p_prog_unit => v_prog_unit
);
v_row_cnt := v_row_cnt + 1;
v_line_rec.trx_header_id := v_nov_trx_hdr_id;
v_line_rec.trx_line_id := v_nov_trx_line_id;
v_line_rec.line_number := v_row_cnt;
v_line_rec.inventory_item_id := v_inventory_item_id;
v_line_rec.quantity_invoiced := 1;
v_line_rec.unit_selling_price := 0;
v_line_rec.line_type := gc_line_type;
v_line_rec.interface_line_context := gc_trx_line_dff_ctxt;
v_line_rec.interface_line_attribute1 :=
'CN' || get_violcode_data_rec.legacy_id;
-- As amount is captured under standard memo line.
v_line_rec.amount := 0;
v_lines_tbl (v_row_cnt) := v_line_rec;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'After setting line table '
|| v_lines_tbl.COUNT (),
p_prog_unit => v_prog_unit
);
v_row_cnt := 0;
gv_return_status := NULL;
gv_return_msg := NULL;
create_invoice (p_batch_source_rec => v_batch_source_rec,
p_header_tbl => v_header_tbl,
p_lines_tbl => v_lines_tbl,
p_dist_tbl => v_dist_tbl,
p_salescredits_tbl => v_salescredits_tbl,
x_customer_trx_id => v_customer_trx_id,
x_return_status => gv_return_status,
x_return_msg => gv_return_msg
);
/*
* Continue creating the receipt only if the invoice is created successfully and
* trx_id is successfully created.
*/
IF (gv_return_status = fnd_api.g_ret_sts_success)
AND (v_customer_trx_id IS NOT NULL)
THEN
gv_process_msg :=
'Invoice created for NOV violation number: '
|| get_ticket_data_rec.violation_number
|| ' customer_trx_id : '
|| v_customer_trx_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
/*
* Checking if difference between original fine amount and total collected <> 0 and
* Total collected is 0 then adjustment has to be created not the receipt and is manual
* process.
*
* Checking if difference between original fine amount and total collected <> 0 and
* Total collected is not 0 then need a receipt equal to the collected amount and
* credit memo for the difference manually.
*
* So we are not creating receipts for these two conditions.
*/
IF NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
AND NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
THEN
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
create_receipt
(p_payment_amount => get_ticket_data_rec.original_fine_amount,
p_customer_id => v_cust_account_id,
p_site_use_id => v_site_use_id,
p_receipt_number => 'CN'
|| TO_CHAR (SYSDATE,
'DD-MON-YYYY'
)
|| get_ticket_data_rec.violation_number,
p_receipt_date => SYSDATE,
p_receipt_method_id => v_receipt_method_id,
p_receipt_currency_code => gc_currency_code,
x_cash_receipt_id => v_cash_receipt_id,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
/*
* Continue applying the receipt on invoice if the receipt is created successfully and
* cash_receipt_id is successfully created.
*/
IF (gv_return_status = fnd_api.g_ret_sts_success)
AND (v_cash_receipt_id IS NOT NULL)
THEN
gv_process_msg :=
'Receipt created for NOV violation number: '
|| get_ticket_data_rec.violation_number
|| ' cash_receipt_id : '
|| v_cash_receipt_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
apply_receipt
(p_customer_id => v_cust_account_id,
p_site_use_id => v_site_use_id,
p_cash_receipt_id => v_cash_receipt_id,
p_cust_trx_id => v_customer_trx_id,
p_trx_amt => get_ticket_data_rec.original_fine_amount,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
IF (gv_return_status <> fnd_api.g_ret_sts_success)
THEN
ROLLBACK TO conv_record;
retcode := '1';
IF (NVL (gv_msg_count, 0) > 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count,
gv_concat_msg
);
END IF;
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when applying receipt for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_concat_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
ELSE
/*
* Else loop for failure of apply_receipt procedure..
*/
gv_process_msg :=
'Receipt was successfully applied on invoice for NOV violation number: '
|| get_ticket_data_rec.violation_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
/*
* Invoice was Created, Receipt was created and it was applied successfully. So updating the record in staging tables to 'S'
*/
gv_process_status := NULL;
IF (gv_warning_flag = 'Y')
THEN
gv_process_status := gc_warning_status;
ELSE
gv_process_status := gc_success_status;
END IF;
UPDATE xxdc_nov_ticket
SET process_status = gv_process_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number =
get_ticket_data_rec.violation_number;
UPDATE xxdc_nov_violcode
SET process_status = gv_process_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number =
get_ticket_data_rec.violation_number;
END IF;
ELSE
/*
* Else loop for failure of create_receipt procedure..
*/
ROLLBACK TO conv_record;
retcode := '1';
IF (NVL (gv_msg_count, 0) > 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count,
gv_concat_msg
);
END IF;
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when creating receipt for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_concat_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
END IF;
ELSE
/*
* Else Loop for
IF NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
AND NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
*/
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'Created Invoice but user has to manually create adjustment',
p_prog_unit => v_prog_unit
);
IF ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0) <> 0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
THEN
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'Need an adjustment/Credit Memo created to reflect the adjustment in the amount of '
|| ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0)
)
);
ELSIF ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0) <> 0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
THEN
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'Need an receipt equal to the collected amount and credit memo created for the amount of '
|| ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0)
)
);
END IF;
/*
* Invoice was Created successfully but receipt was not created and applied.
* This is because the amounts are not same and it is a manual process.
* So updating the record in staging tables to 'W'
*/
UPDATE xxdc_nov_ticket
SET process_status = gc_warning_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number = get_ticket_data_rec.violation_number;
UPDATE xxdc_nov_violcode
SET process_status = gc_warning_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number = get_ticket_data_rec.violation_number;
END IF;
ELSE
/*
* Else loop for failure of create_invoice procedure..
*/
ROLLBACK TO conv_record;
retcode := '1';
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when creating invoice for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_return_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
--commit;
END IF;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO conv_record;
retcode := '2';
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_exp_rule_id,
p_request_id,
v_violation_number,
SQLCODE || ' - ' || SQLERRM
);
END convert_data;
PROCEDURE create_invoice (
p_batch_source_rec IN ar_invoice_api_pub.batch_source_rec_type
DEFAULT NULL,
p_header_tbl IN ar_invoice_api_pub.trx_header_tbl_type,
p_lines_tbl IN ar_invoice_api_pub.trx_line_tbl_type,
p_dist_tbl IN ar_invoice_api_pub.trx_dist_tbl_type,
p_salescredits_tbl IN ar_invoice_api_pub.trx_salescredits_tbl_type,
x_customer_trx_id OUT NUMBER,
x_return_status OUT VARCHAR2,
x_return_msg OUT VARCHAR2
)
IS
v_cnt INTEGER := 0;
v_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE := NULL;
v_trx_number ra_customer_trx_all.trx_number%TYPE := NULL;
v_api_ret_msg VARCHAR2 (2000) := NULL;
v_concat_msg VARCHAR2 (2000) := NULL;
v_table_err_msg VARCHAR2 (2000) := NULL;
v_err_msg VARCHAR2 (2000);
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_INVOICE';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
--fnd_msg_pub.initialize;
--fnd_msg_pub.delete_msg;
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
ar_invoice_api_pub.create_single_invoice
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_batch_source_rec => p_batch_source_rec,
p_trx_header_tbl => p_header_tbl,
p_trx_lines_tbl => p_lines_tbl,
p_trx_dist_tbl => p_dist_tbl,
p_trx_salescredits_tbl => p_salescredits_tbl,
--,p_trx_contingencies_tbl IN trx_contingencies_tbl_type,
x_customer_trx_id => v_customer_trx_id,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'After calling create_single_invoice api'
|| ' x_return_status - '
|| gv_return_status
|| ' x_msg_count - '
|| gv_msg_count
|| ' x_msg_data - '
|| gv_msg_data,
p_prog_unit => v_prog_unit
);
v_api_ret_msg :=
'API Return Status: ['
|| gv_return_status
|| ']'
|| ' Msg Count: ['
|| gv_msg_count
|| ']'
|| ' Msg Data: ['
|| gv_msg_data
|| ']'
|| ' CustTrxId: ['
|| v_customer_trx_id
|| ']';
IF (NVL (gv_return_status, fnd_api.g_ret_sts_error) !=
fnd_api.g_ret_sts_success
)
OR (NVL (gv_msg_count, 0) > 0)
OR (NVL (v_customer_trx_id, 0) = 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count, gv_concat_msg);
END IF;
FOR err IN (SELECT *
FROM ar_trx_errors_gt
WHERE trx_header_id = p_header_tbl (1).trx_header_id)
LOOP
v_cnt := v_cnt + 1;
IF err.trx_line_id IS NOT NULL
THEN
v_table_err_msg :=
NVL (v_table_err_msg, ' ')
|| 'trx_line_id=['
|| err.trx_line_id
|| ']';
END IF;
v_table_err_msg :=
NVL (v_table_err_msg, ' ')
|| 'errmsg=['
|| SUBSTR (err.error_message, 1, 200)
|| ']invval=['
|| err.invalid_value
|| ']';
END LOOP;
IF NVL (gv_return_status, fnd_api.g_ret_sts_error) =
fnd_api.g_ret_sts_success
AND v_cnt = 0
AND NVL (v_customer_trx_id, 0) > 0
THEN
BEGIN
SELECT trx_number
INTO v_trx_number
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = v_customer_trx_id;
EXCEPTION
WHEN OTHERS
THEN
v_err_msg :=
'Fatal error - Get trx_number from ra_customer_trx_all failed - Msg:'
|| SUBSTR (SQLERRM, 1, 100);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END;
x_customer_trx_id := v_customer_trx_id;
x_return_status := fnd_api.g_ret_sts_success;
ELSE
v_err_msg :=
SUBSTR ( 'Invoice API Error - <'
|| NVL (v_api_ret_msg, ' ')
|| '>'
|| ':Concat Msg: <'
|| NVL (v_concat_msg, ' ')
|| '>'
|| ':Ar_trx_errors_gt Msg:<'
|| NVL (v_table_err_msg, ' ')
|| '>',
1,
2000
);
v_customer_trx_id := NULL;
x_return_msg := v_err_msg;
x_return_status := gv_return_status;
END IF;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
v_customer_trx_id := NULL;
x_return_msg :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END create_invoice;
PROCEDURE create_receipt (
p_payment_amount IN NUMBER,
p_customer_id IN NUMBER,
p_site_use_id IN NUMBER,
p_receipt_number IN VARCHAR2,
p_receipt_date IN DATE DEFAULT TRUNC (SYSDATE),
p_receipt_method_id IN NUMBER,
p_receipt_currency_code IN VARCHAR2,
x_cash_receipt_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_RECEIPT';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_receipt_number => p_receipt_number,
p_amount => p_payment_amount,
p_receipt_method_id => p_receipt_method_id,
p_customer_id => p_customer_id,
p_customer_site_use_id => p_site_use_id,
p_currency_code => p_receipt_currency_code,
p_receipt_date => TRUNC
(p_receipt_date),
p_gl_date => TRUNC
(p_receipt_date),
p_cr_id => x_cash_receipt_id,
p_called_from => 'IREC'
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_cash_receipt_id := NULL;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END create_receipt;
PROCEDURE apply_receipt (
p_customer_id IN NUMBER,
p_site_use_id IN NUMBER DEFAULT NULL,
p_cash_receipt_id IN NUMBER,
p_cust_trx_id IN NUMBER,
p_trx_amt IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.APPLY_RECEIPT';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
--Assign default values
ar_receipt_api_pub.APPLY
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cash_receipt_id => p_cash_receipt_id,
p_customer_trx_id => p_cust_trx_id,
--p_applied_payment_schedule_id => ,
--p_amount_applied => ,
p_amount_applied => p_trx_amt,
--p_discount => ,
p_apply_date => SYSDATE,
p_called_from => 'ARI',
p_show_closed_invoices => 'Y'
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END apply_receipt;
END;
/
IS
--
-- This Package is for Coverting data from NOV Ticket and Violation Code files to Transactions, Lines and Receipts..
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ------ ------------------------------------------
FUNCTION get_constant_val (p_constant_name IN VARCHAR2)
RETURN VARCHAR2
IS
v_value VARCHAR2 (1000);
BEGIN
EXECUTE IMMEDIATE 'BEGIN :x := '
|| gc_pkg
|| '.'
|| p_constant_name
|| '; end;'
USING OUT v_value;
RETURN v_value;
END;
PROCEDURE write_log (p_debug IN VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_debug);
END;
PROCEDURE sync_status (p_request_id IN NUMBER, p_retcode OUT VARCHAR2)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.SYNC_STATUS';
CURSOR get_err_tickets_cur (v_req_id IN NUMBER)
IS
SELECT *
FROM xxdc.xxdc_nov_ticket
WHERE request_id = NVL (v_req_id, request_id)
AND process_status IN ('RS', 'RD');
CURSOR get_err_violcode_cur (v_req_id IN NUMBER)
IS
SELECT *
FROM xxdc.xxdc_nov_violcode
WHERE request_id = NVL (v_req_id, request_id)
AND process_status IN ('RS', 'RD');
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
p_retcode := 0;
FOR get_err_tickets_rec IN get_err_tickets_cur (p_request_id)
LOOP
UPDATE xxdc_nov_violcode nv
SET nv.process_status =
DECODE (nv.process_status,
'RS', 'RS',
'RD', 'RD',
get_err_tickets_rec.process_status
),
nv.last_updated_by = fnd_global.user_id,
nv.last_update_date = SYSDATE
WHERE nv.violation_number = get_err_tickets_rec.violation_number
AND nv.request_id = NVL (p_request_id, nv.request_id);
END LOOP;
FOR get_err_violcode_rec IN get_err_violcode_cur (p_request_id)
LOOP
UPDATE xxdc_nov_ticket nt
SET nt.process_status =
DECODE (nt.process_status,
'RS', 'RS',
'RD', 'RD',
get_err_violcode_rec.process_status
),
nt.last_updated_by = fnd_global.user_id,
nt.last_update_date = SYSDATE
WHERE NVL (nt.violation_number, -1) =
get_err_violcode_rec.violation_number
AND nt.request_id = NVL (p_request_id, nt.request_id);
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
p_retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END;
PROCEDURE prevalidation (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_debug IN VARCHAR2 DEFAULT 'Y',
p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.PREVALIDATION';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
retcode := 0;
--xxdc_p2_conv_util.gen_pre_validation (gc_conv_id, p_request_id);
xxdc_p2_conv_util.prevalidation (p_conv_id => gc_conv_id,
p_request_id => p_request_id,
p_log => gv_debug_log_clob,
p_result => retcode
);
xxdc_p2_conv_util.gen_datatype_validation (gc_conv_id, p_request_id);
sync_status (p_request_id, retcode);
UPDATE xxdc_nov_ticket nt
SET nt.last_updated_by = fnd_global.user_id,
nt.last_update_date = SYSDATE,
nt.process_status =
DECODE (nt.process_status,
gc_new_status, gc_pending_status,
nt.process_status
);
UPDATE xxdc_nov_violcode nv
SET nv.last_updated_by = fnd_global.user_id,
nv.last_update_date = SYSDATE,
nv.process_status =
DECODE (nv.process_status,
gc_new_status, gc_pending_status,
nv.process_status
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
END prevalidation;
PROCEDURE convert_data (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_debug IN VARCHAR2 DEFAULT 'Y',
p_request_id IN NUMBER
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.CONVERT_DATA';
v_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
v_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
v_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
v_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
v_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
v_header_rec ar_invoice_api_pub.trx_header_rec_type;
v_line_rec ar_invoice_api_pub.trx_line_rec_type;
v_batch_source_id ra_customer_trx_all.batch_source_id%TYPE;
v_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%TYPE;
v_cust_account_id ra_customer_trx_all.bill_to_customer_id%TYPE;
v_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
v_term_id ra_customer_trx_all.term_id%TYPE;
v_violation_number ra_customer_trx_all.trx_number%TYPE;
v_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
v_memo_line_id ra_customer_trx_lines_all.memo_line_id%TYPE;
v_inventory_item_id ra_customer_trx_lines_all.inventory_item_id%TYPE;
v_receipt_method_id ar_cash_receipts_all.receipt_method_id%TYPE;
v_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
v_nov_trx_hdr_id NUMBER;
v_nov_trx_line_id NUMBER;
v_row_cnt INTEGER := 0;
v_person_id per_all_people_f.person_id%TYPE;
v_msg_index_out NUMBER;
v_issue_date DATE;
v_chr_issue_dt VARCHAR2 (25);
v_chr_dt_att3 VARCHAR2 (25);
v_party_id hz_parties.party_id%TYPE;
v_party_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
CURSOR get_ticket_data_cur
IS
SELECT *
FROM xxdc_nov_ticket
WHERE process_status IN ('P', 'PW', 'E')
-- AND ROWNUM < 2
AND request_id = NVL (p_request_id, request_id);
CURSOR get_violcode_data_cur (v_viol_number IN VARCHAR2)
IS
SELECT *
FROM xxdc_nov_violcode
WHERE violation_number = v_viol_number
AND request_id = NVL (p_request_id, request_id);
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
retcode := '0';
SELECT batch_source_id
INTO v_batch_source_id
FROM ra_batch_sources_all
WHERE UPPER (NAME) = UPPER (gc_batch_source_name);
gv_process_msg := 'Batch Source Id - ' || v_batch_source_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT cust_trx_type_id
INTO v_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE UPPER (NAME) = UPPER (gc_cust_trx_type);
gv_process_msg := 'Customer Trx Type Id - ' || v_cust_trx_type_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT term_id
INTO v_term_id
FROM ra_terms
WHERE UPPER (NAME) = UPPER (gc_term_name);
gv_process_msg := 'Payment Term Id - ' || v_term_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT memo_line_id
INTO v_memo_line_id
FROM ar_memo_lines_all_vl
WHERE UPPER (NAME) = UPPER (gc_memo_line_name);
gv_process_msg := 'v_memo_line_id - ' || v_memo_line_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
SELECT receipt_method_id
INTO v_receipt_method_id
FROM ar_receipt_methods
WHERE UPPER (NAME) = UPPER (gc_receipt_method);
gv_process_msg := 'v_receipt_method_id - ' || v_receipt_method_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
FOR get_ticket_data_rec IN get_ticket_data_cur
LOOP
/*
* Resetting the variables.
*/
v_cust_account_id := NULL;
v_site_use_id := NULL;
v_person_id := NULL;
v_header_rec := NULL;
v_line_rec := NULL;
v_batch_source_rec := NULL;
v_customer_trx_id := NULL;
v_cash_receipt_id := NULL;
v_violation_number := NULL;
v_header_tbl.DELETE;
v_lines_tbl.DELETE;
v_dist_tbl.DELETE;
v_salescredits_tbl.DELETE;
/*
* Savepoint for roll back.
*/
SAVEPOINT conv_record;
/*
* Get Customer info for the invoice.
*/
xxdc_p2_conv_util.get_bill_to_acct_site_use_id
(p_caller => v_prog_unit,
p_legacy_acct_num => get_ticket_data_rec.legacy_account_number,
p_bill_to_party_id => v_party_id,
p_cust_acct_id => v_cust_account_id,
p_bill_to_site_id => v_site_use_id,
p_bill_to_site_use_id => v_party_site_use_id
);
/*
* Get Employee number for DFF.
*/
v_person_id :=
xxdc_p2_conv_util.get_person_id
(v_prog_unit,
get_ticket_data_rec.employee_number
);
/*
* Setting warning flag to 'Y' to update the process_status as 'W"
*/
gv_warning_flag := NULL;
IF (v_person_id IS NULL)
THEN
gv_warning_flag := 'Y';
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'The employee was not found for '
|| get_ticket_data_rec.violation_number
|| ' for '
|| get_ticket_data_rec.employee_number
);
END IF;
/*
* sequence for trx hdr id.
*/
SELECT xxdc_nov_trx_hdr_s.NEXTVAL
INTO v_nov_trx_hdr_id
FROM DUAL;
v_violation_number := get_ticket_data_rec.violation_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_nov_trx_hdr_id - '
|| v_nov_trx_hdr_id
|| 'v_cust_account_id - '
|| v_cust_account_id
|| 'v_site_use_id - '
|| v_site_use_id
|| 'v_person_id - '
|| v_person_id
|| 'v_violation_number - '
|| v_violation_number,
p_prog_unit => v_prog_unit
);
/*
* Loading Batch Source Info...
*/
v_batch_source_rec.batch_source_id := v_batch_source_id;
/*
* Loading Header Information for invoice..
*/
v_header_rec.trx_header_id := v_nov_trx_hdr_id;
v_header_rec.trx_number :=
'CN'
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY')
|| get_ticket_data_rec.violation_number;
v_header_rec.trx_date :=
NVL (TO_DATE (get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format
),
SYSDATE
);
v_header_rec.trx_currency := gc_currency_code;
v_header_rec.cust_trx_type_id := v_cust_trx_type_id;
v_header_rec.bill_to_customer_id := v_cust_account_id;
v_header_rec.bill_to_site_use_id := v_site_use_id;
v_header_rec.sold_to_customer_id := v_cust_account_id;
v_header_rec.term_id := v_term_id;
v_header_rec.attribute_category := gc_trx_hdr_dff_ctxt;
--Ticket Number
v_header_rec.attribute1 :=
'CN' || get_ticket_data_rec.violation_number;
-- Name of the Inspecter.
v_header_rec.attribute2 := v_person_id;
-- Ticket Date
v_chr_issue_dt := get_ticket_data_rec.ticket_issue_date;
v_issue_date := TO_DATE (v_chr_issue_dt, xxd_utils.gc_dor_dt_format);
v_chr_dt_att3 :=
TO_CHAR (v_issue_date, xxd_utils.gc_fnd_std_dt_format);
v_header_rec.attribute3 := v_chr_dt_att3;
/*v_header_rec.attribute3 :=
TO_CHAR
(TO_DATE (get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format --'MM/DD/YYYY HH24:MI:SS'
),
xxd_utils.gc_fnd_std_dt_format
);
*/
--case comments
v_header_rec.attribute5 := get_ticket_data_rec.case_comment;
v_header_tbl (1) := v_header_rec;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'After setting header table - v_header_tbl - '
|| v_header_tbl.COUNT,
p_prog_unit => v_prog_unit
);
/*
* Violation Code table loop
*/
FOR get_violcode_data_rec IN
get_violcode_data_cur (get_ticket_data_rec.violation_number)
LOOP
/*
* Loading the standard memo line...
*/
IF (v_row_cnt = 0)
THEN
/*
* sequence for trx line id.
*/
SELECT xxdc_nov_trx_line_s.NEXTVAL
INTO v_nov_trx_line_id
FROM DUAL;
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'v_row_cnt - '
|| v_row_cnt
|| 'v_nov_trx_line_id - '
|| v_nov_trx_line_id
|| ' trx date - '
|| TO_DATE
(get_ticket_data_rec.ticket_issue_date,
xxd_utils.gc_dor_dt_format
)
|| 'Date for att 3 - '
|| v_chr_dt_att3,
p_prog_unit => v_prog_unit
);
v_row_cnt := v_row_cnt + 1;
v_line_rec.trx_header_id := v_nov_trx_hdr_id;
v_line_rec.trx_line_id := v_nov_trx_line_id;
v_line_rec.line_number := v_row_cnt;
v_line_rec.quantity_invoiced :=
get_ticket_data_rec.original_fine_amount;
v_line_rec.line_type := gc_line_type;
v_line_rec.memo_line_id := v_memo_line_id;
v_line_rec.amount := get_ticket_data_rec.original_fine_amount;
v_lines_tbl (v_row_cnt) := v_line_rec;
v_line_rec := NULL;
END IF;
/*
* sequence for trx line id.
*/
SELECT xxdc_nov_trx_line_s.NEXTVAL
INTO v_nov_trx_line_id
FROM DUAL;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_row_cnt - '
|| v_row_cnt
|| 'v_nov_trx_line_id - '
|| v_nov_trx_line_id,
p_prog_unit => v_prog_unit
);
/*
* Query for item id for violation code.
*/
v_inventory_item_id :=
xxdc_p2_conv_util.get_inventory_item_id
(v_prog_unit,
get_violcode_data_rec.violation_code
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'v_inventory_item_id - '
|| v_inventory_item_id,
p_prog_unit => v_prog_unit
);
v_row_cnt := v_row_cnt + 1;
v_line_rec.trx_header_id := v_nov_trx_hdr_id;
v_line_rec.trx_line_id := v_nov_trx_line_id;
v_line_rec.line_number := v_row_cnt;
v_line_rec.inventory_item_id := v_inventory_item_id;
v_line_rec.quantity_invoiced := 1;
v_line_rec.unit_selling_price := 0;
v_line_rec.line_type := gc_line_type;
v_line_rec.interface_line_context := gc_trx_line_dff_ctxt;
v_line_rec.interface_line_attribute1 :=
'CN' || get_violcode_data_rec.legacy_id;
-- As amount is captured under standard memo line.
v_line_rec.amount := 0;
v_lines_tbl (v_row_cnt) := v_line_rec;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => 'After setting line table '
|| v_lines_tbl.COUNT (),
p_prog_unit => v_prog_unit
);
v_row_cnt := 0;
gv_return_status := NULL;
gv_return_msg := NULL;
create_invoice (p_batch_source_rec => v_batch_source_rec,
p_header_tbl => v_header_tbl,
p_lines_tbl => v_lines_tbl,
p_dist_tbl => v_dist_tbl,
p_salescredits_tbl => v_salescredits_tbl,
x_customer_trx_id => v_customer_trx_id,
x_return_status => gv_return_status,
x_return_msg => gv_return_msg
);
/*
* Continue creating the receipt only if the invoice is created successfully and
* trx_id is successfully created.
*/
IF (gv_return_status = fnd_api.g_ret_sts_success)
AND (v_customer_trx_id IS NOT NULL)
THEN
gv_process_msg :=
'Invoice created for NOV violation number: '
|| get_ticket_data_rec.violation_number
|| ' customer_trx_id : '
|| v_customer_trx_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
/*
* Checking if difference between original fine amount and total collected <> 0 and
* Total collected is 0 then adjustment has to be created not the receipt and is manual
* process.
*
* Checking if difference between original fine amount and total collected <> 0 and
* Total collected is not 0 then need a receipt equal to the collected amount and
* credit memo for the difference manually.
*
* So we are not creating receipts for these two conditions.
*/
IF NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
AND NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
THEN
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
create_receipt
(p_payment_amount => get_ticket_data_rec.original_fine_amount,
p_customer_id => v_cust_account_id,
p_site_use_id => v_site_use_id,
p_receipt_number => 'CN'
|| TO_CHAR (SYSDATE,
'DD-MON-YYYY'
)
|| get_ticket_data_rec.violation_number,
p_receipt_date => SYSDATE,
p_receipt_method_id => v_receipt_method_id,
p_receipt_currency_code => gc_currency_code,
x_cash_receipt_id => v_cash_receipt_id,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
/*
* Continue applying the receipt on invoice if the receipt is created successfully and
* cash_receipt_id is successfully created.
*/
IF (gv_return_status = fnd_api.g_ret_sts_success)
AND (v_cash_receipt_id IS NOT NULL)
THEN
gv_process_msg :=
'Receipt created for NOV violation number: '
|| get_ticket_data_rec.violation_number
|| ' cash_receipt_id : '
|| v_cash_receipt_id;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
apply_receipt
(p_customer_id => v_cust_account_id,
p_site_use_id => v_site_use_id,
p_cash_receipt_id => v_cash_receipt_id,
p_cust_trx_id => v_customer_trx_id,
p_trx_amt => get_ticket_data_rec.original_fine_amount,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
IF (gv_return_status <> fnd_api.g_ret_sts_success)
THEN
ROLLBACK TO conv_record;
retcode := '1';
IF (NVL (gv_msg_count, 0) > 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count,
gv_concat_msg
);
END IF;
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when applying receipt for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_concat_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
ELSE
/*
* Else loop for failure of apply_receipt procedure..
*/
gv_process_msg :=
'Receipt was successfully applied on invoice for NOV violation number: '
|| get_ticket_data_rec.violation_number;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
/*
* Invoice was Created, Receipt was created and it was applied successfully. So updating the record in staging tables to 'S'
*/
gv_process_status := NULL;
IF (gv_warning_flag = 'Y')
THEN
gv_process_status := gc_warning_status;
ELSE
gv_process_status := gc_success_status;
END IF;
UPDATE xxdc_nov_ticket
SET process_status = gv_process_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number =
get_ticket_data_rec.violation_number;
UPDATE xxdc_nov_violcode
SET process_status = gv_process_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number =
get_ticket_data_rec.violation_number;
END IF;
ELSE
/*
* Else loop for failure of create_receipt procedure..
*/
ROLLBACK TO conv_record;
retcode := '1';
IF (NVL (gv_msg_count, 0) > 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count,
gv_concat_msg
);
END IF;
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when creating receipt for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_concat_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
END IF;
ELSE
/*
* Else Loop for
IF NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
AND NOT ( NVL (get_ticket_data_rec.original_fine_amount,
0)
- NVL (get_ticket_data_rec.total_collected, 0) <>
0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
*/
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'Created Invoice but user has to manually create adjustment',
p_prog_unit => v_prog_unit
);
IF ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0) <> 0
AND NVL (get_ticket_data_rec.total_collected, 0) = 0
)
THEN
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'Need an adjustment/Credit Memo created to reflect the adjustment in the amount of '
|| ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0)
)
);
ELSIF ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0) <> 0
AND NVL (get_ticket_data_rec.total_collected, 0) <> 0
)
THEN
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_warning_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
'Need an receipt equal to the collected amount and credit memo created for the amount of '
|| ( NVL (get_ticket_data_rec.original_fine_amount, 0)
- NVL (get_ticket_data_rec.total_collected, 0)
)
);
END IF;
/*
* Invoice was Created successfully but receipt was not created and applied.
* This is because the amounts are not same and it is a manual process.
* So updating the record in staging tables to 'W'
*/
UPDATE xxdc_nov_ticket
SET process_status = gc_warning_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number = get_ticket_data_rec.violation_number;
UPDATE xxdc_nov_violcode
SET process_status = gc_warning_status,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE violation_number = get_ticket_data_rec.violation_number;
END IF;
ELSE
/*
* Else loop for failure of create_invoice procedure..
*/
ROLLBACK TO conv_record;
retcode := '1';
gv_process_msg :=
'API return status code: '
|| gv_return_status
|| ' when creating invoice for NOV violation Number: '
|| get_ticket_data_rec.violation_number
|| '. Error message: '
|| gv_return_msg;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => gv_process_msg,
p_prog_unit => v_prog_unit
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_api_error_rule_id,
p_request_id,
get_ticket_data_rec.violation_number,
gv_process_msg
);
--commit;
END IF;
END LOOP;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO conv_record;
retcode := '2';
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
xxd_utils.write_stuff (p_type => fnd_file.LOG,
p_retcode => retcode,
p_debug => p_debug,
p_stuff => gv_debug_log_clob,
p_result => gv_result
);
xxdc_p2_conv_util.gen_ins_upd_error
(gc_conv_id,
xxdc_p2_conv_util.gc_exp_rule_id,
p_request_id,
v_violation_number,
SQLCODE || ' - ' || SQLERRM
);
END convert_data;
PROCEDURE create_invoice (
p_batch_source_rec IN ar_invoice_api_pub.batch_source_rec_type
DEFAULT NULL,
p_header_tbl IN ar_invoice_api_pub.trx_header_tbl_type,
p_lines_tbl IN ar_invoice_api_pub.trx_line_tbl_type,
p_dist_tbl IN ar_invoice_api_pub.trx_dist_tbl_type,
p_salescredits_tbl IN ar_invoice_api_pub.trx_salescredits_tbl_type,
x_customer_trx_id OUT NUMBER,
x_return_status OUT VARCHAR2,
x_return_msg OUT VARCHAR2
)
IS
v_cnt INTEGER := 0;
v_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE := NULL;
v_trx_number ra_customer_trx_all.trx_number%TYPE := NULL;
v_api_ret_msg VARCHAR2 (2000) := NULL;
v_concat_msg VARCHAR2 (2000) := NULL;
v_table_err_msg VARCHAR2 (2000) := NULL;
v_err_msg VARCHAR2 (2000);
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_INVOICE';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
--fnd_msg_pub.initialize;
--fnd_msg_pub.delete_msg;
gv_return_status := NULL;
gv_msg_count := NULL;
gv_msg_data := NULL;
ar_invoice_api_pub.create_single_invoice
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_batch_source_rec => p_batch_source_rec,
p_trx_header_tbl => p_header_tbl,
p_trx_lines_tbl => p_lines_tbl,
p_trx_dist_tbl => p_dist_tbl,
p_trx_salescredits_tbl => p_salescredits_tbl,
--,p_trx_contingencies_tbl IN trx_contingencies_tbl_type,
x_customer_trx_id => v_customer_trx_id,
x_return_status => gv_return_status,
x_msg_count => gv_msg_count,
x_msg_data => gv_msg_data
);
xxd_utils.LOG
(p_log_text => gv_debug_log_clob,
p_new_text => 'After calling create_single_invoice api'
|| ' x_return_status - '
|| gv_return_status
|| ' x_msg_count - '
|| gv_msg_count
|| ' x_msg_data - '
|| gv_msg_data,
p_prog_unit => v_prog_unit
);
v_api_ret_msg :=
'API Return Status: ['
|| gv_return_status
|| ']'
|| ' Msg Count: ['
|| gv_msg_count
|| ']'
|| ' Msg Data: ['
|| gv_msg_data
|| ']'
|| ' CustTrxId: ['
|| v_customer_trx_id
|| ']';
IF (NVL (gv_return_status, fnd_api.g_ret_sts_error) !=
fnd_api.g_ret_sts_success
)
OR (NVL (gv_msg_count, 0) > 0)
OR (NVL (v_customer_trx_id, 0) = 0)
THEN
gv_concat_msg := NULL;
xxd_util_pkg.combine_message (gv_msg_count, gv_concat_msg);
END IF;
FOR err IN (SELECT *
FROM ar_trx_errors_gt
WHERE trx_header_id = p_header_tbl (1).trx_header_id)
LOOP
v_cnt := v_cnt + 1;
IF err.trx_line_id IS NOT NULL
THEN
v_table_err_msg :=
NVL (v_table_err_msg, ' ')
|| 'trx_line_id=['
|| err.trx_line_id
|| ']';
END IF;
v_table_err_msg :=
NVL (v_table_err_msg, ' ')
|| 'errmsg=['
|| SUBSTR (err.error_message, 1, 200)
|| ']invval=['
|| err.invalid_value
|| ']';
END LOOP;
IF NVL (gv_return_status, fnd_api.g_ret_sts_error) =
fnd_api.g_ret_sts_success
AND v_cnt = 0
AND NVL (v_customer_trx_id, 0) > 0
THEN
BEGIN
SELECT trx_number
INTO v_trx_number
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = v_customer_trx_id;
EXCEPTION
WHEN OTHERS
THEN
v_err_msg :=
'Fatal error - Get trx_number from ra_customer_trx_all failed - Msg:'
|| SUBSTR (SQLERRM, 1, 100);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END;
x_customer_trx_id := v_customer_trx_id;
x_return_status := fnd_api.g_ret_sts_success;
ELSE
v_err_msg :=
SUBSTR ( 'Invoice API Error - <'
|| NVL (v_api_ret_msg, ' ')
|| '>'
|| ':Concat Msg: <'
|| NVL (v_concat_msg, ' ')
|| '>'
|| ':Ar_trx_errors_gt Msg:<'
|| NVL (v_table_err_msg, ' ')
|| '>',
1,
2000
);
v_customer_trx_id := NULL;
x_return_msg := v_err_msg;
x_return_status := gv_return_status;
END IF;
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
v_customer_trx_id := NULL;
x_return_msg :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END create_invoice;
PROCEDURE create_receipt (
p_payment_amount IN NUMBER,
p_customer_id IN NUMBER,
p_site_use_id IN NUMBER,
p_receipt_number IN VARCHAR2,
p_receipt_date IN DATE DEFAULT TRUNC (SYSDATE),
p_receipt_method_id IN NUMBER,
p_receipt_currency_code IN VARCHAR2,
x_cash_receipt_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
v_prog_unit VARCHAR (100) := gc_pkg || '.CREATE_RECEIPT';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_receipt_number => p_receipt_number,
p_amount => p_payment_amount,
p_receipt_method_id => p_receipt_method_id,
p_customer_id => p_customer_id,
p_customer_site_use_id => p_site_use_id,
p_currency_code => p_receipt_currency_code,
p_receipt_date => TRUNC
(p_receipt_date),
p_gl_date => TRUNC
(p_receipt_date),
p_cr_id => x_cash_receipt_id,
p_called_from => 'IREC'
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_cash_receipt_id := NULL;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END create_receipt;
PROCEDURE apply_receipt (
p_customer_id IN NUMBER,
p_site_use_id IN NUMBER DEFAULT NULL,
p_cash_receipt_id IN NUMBER,
p_cust_trx_id IN NUMBER,
p_trx_amt IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
v_prog_unit VARCHAR (100) := gc_pkg || '.APPLY_RECEIPT';
BEGIN
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_start,
p_prog_unit => v_prog_unit
);
--Assign default values
ar_receipt_api_pub.APPLY
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cash_receipt_id => p_cash_receipt_id,
p_customer_trx_id => p_cust_trx_id,
--p_applied_payment_schedule_id => ,
--p_amount_applied => ,
p_amount_applied => p_trx_amt,
--p_discount => ,
p_apply_date => SYSDATE,
p_called_from => 'ARI',
p_show_closed_invoices => 'Y'
);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_complete,
p_prog_unit => v_prog_unit
);
EXCEPTION
WHEN OTHERS
THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data :=
'In exception Error - ' || SQLCODE || SUBSTR (SQLERRM, 1, 300);
xxd_utils.LOG (p_log_text => gv_debug_log_clob,
p_new_text => xxd_utils.gc_exception
|| v_prog_unit
|| ', error: '
|| SQLCODE
|| ' - '
|| SQLERRM,
p_prog_unit => v_prog_unit
);
END apply_receipt;
END;
/
