CREATE TABLE XXSUKAM.XXSKM_CRM_REVENUE
(
DOCUMENT_NUMBER VARCHAR2(50 BYTE),
STATUS VARCHAR2(50 BYTE),
END_CUSTOMER_NAME VARCHAR2(240 BYTE),
SALE_LOCATION_TYPE VARCHAR2(30 BYTE),
BRANCH_LOCATION VARCHAR2(30 BYTE),
CUSTOMER_ASSIGNED VARCHAR2(240 BYTE),
CUSTOMER_ID VARCHAR2(150 BYTE),
SFID VARCHAR2(150 BYTE),
CONTRACT_START_DATE DATE,
CONTRACT_END_DATE DATE,
CONTRACT_DURATION NUMBER,
PAYMENT_OPTION VARCHAR2(15 BYTE),
SERVICE_TYPE VARCHAR2(20 BYTE),
PRODUCT_QUANTITY NUMBER,
AMC_AMOUNT NUMBER,
AMC_BILL_NUMBER VARCHAR2(150 BYTE),
CURRENCY_CODE VARCHAR2(15 BYTE),
OPERATING_UNIT VARCHAR2(240 BYTE),
BILL_DATE DATE,
COMMENTS VARCHAR2(240 BYTE),
COMPONENT_AMOUNT NUMBER,
TAX_AMOUNT NUMBER,
VAT_AMOUNT NUMBER,
SERVICE_AMOUNT NUMBER,
SALE_AMOUNT NUMBER,
UNIT_SELLING_PRICE NUMBER,
GL_DATE DATE,
TAX_PERCENTAGE NUMBER,
VAT_PERCENTAGE NUMBER,
INTFC_STATUS VARCHAR2(20 BYTE),
ERROR_MESSAGE VARCHAR2(1000 BYTE),
SFFLAG VARCHAR2(100 BYTE),
UOM_CODE VARCHAR2(3 BYTE),
COMPONENT_BILL_NUMBER VARCHAR2(60 BYTE),
SERVICE_BILL_NUMBER VARCHAR2(60 BYTE)
)
CREATE TABLE XXSUKAM.XXSKM_CRM_REVENUE_ARCHIVE
(
DOCUMENT_NUMBER VARCHAR2(50 BYTE),
STATUS VARCHAR2(50 BYTE),
END_CUSTOMER_NAME VARCHAR2(240 BYTE),
SALE_LOCATION_TYPE VARCHAR2(30 BYTE),
BRANCH_LOCATION VARCHAR2(30 BYTE),
CUSTOMER_ASSIGNED VARCHAR2(240 BYTE),
CUSTOMER_ID VARCHAR2(150 BYTE),
SFID VARCHAR2(150 BYTE),
CONTRACT_START_DATE DATE,
CONTRACT_END_DATE DATE,
CONTRACT_DURATION NUMBER,
PAYMENT_OPTION VARCHAR2(15 BYTE),
SERVICE_TYPE VARCHAR2(20 BYTE),
PRODUCT_QUANTITY NUMBER,
AMC_AMOUNT NUMBER,
AMC_BILL_NUMBER VARCHAR2(150 BYTE),
CURRENCY_CODE VARCHAR2(15 BYTE),
OPERATING_UNIT VARCHAR2(240 BYTE),
BILL_DATE DATE,
COMMENTS VARCHAR2(240 BYTE),
COMPONENT_AMOUNT NUMBER,
TAX_AMOUNT NUMBER,
VAT_AMOUNT NUMBER,
SERVICE_AMOUNT NUMBER,
SALE_AMOUNT NUMBER,
UNIT_SELLING_PRICE NUMBER,
GL_DATE DATE,
TAX_PERCENTAGE NUMBER,
VAT_PERCENTAGE NUMBER,
INTFC_STATUS VARCHAR2(20 BYTE),
ERROR_MESSAGE VARCHAR2(1000 BYTE),
SFFLAG VARCHAR2(100 BYTE),
UOM_CODE VARCHAR2(3 BYTE),
COMPONENT_BILL_NUMBER VARCHAR2(60 BYTE),
SERVICE_BILL_NUMBER VARCHAR2(60 BYTE)
)
CREATE OR REPLACE PACKAGE APPS.XXSKM_CRM_REV_INTFC
AS
/*- - ==============================================================
| Module :- CRM AND AR INVOICE INTERFACE
| Description :- This Package will validate the AR invoice data coming from CRM legacy
| and dump into interface tables as per CRM Interface document .
|
| Parameter :- 1.) retCode, ErrBuff are default parameters and used in concurrent program.
- -================================================================*/
PROCEDURE MAIN_PROC(errBuff OUT CHAR
, retCode OUT NUMBER
,P_INPUT_STATUS IN VARCHAR2
,P_MODE IN VARCHAR2 );
END XXSKM_CRM_REV_INTFC;
/
PROCEDURE MAIN_PROC(errBuff OUT CHAR
,retCode OUT NUMBER
,P_INPUT_STATUS IN VARCHAR2
,P_MODE IN VARCHAR2) AS --
CURSOR CRM_INTFC IS
SELECT *
FROM XXSKM_CRM_REVENUE where NVL(INTFC_STATUS,'NEW') = DECODE(P_INPUT_STATUS,'ERROR','ERROR','NEW');
CURSOR CRM_INTFC_ERR_NEW IS
SELECT *
FROM XXSKM_CRM_REVENUE where INTFC_STATUS = 'ERROR_NEW';
--- APPS.XXSKMREV_INTFC_BCKUP ----
l_line_type VARCHAR2(50);
l_sale_loc VARCHAR2(20);
l_sale_loc_r VARCHAR2(200);
l_unit_price NUMBER;
l_attr_cat VARCHAR2(30);
l_acct_rule VARCHAR2(20);
l_inv_rule VARCHAR2(20);
l_amount NUMBER;
l_cust_id NUMBER;
l_primary_bill_to NUMBER := NULL;
l_error_msg VARCHAR2(1000);
l_status VARCHAR2(20) := NULL;
l_ledger_id NUMBER;
l_rule_start_date DATE;
l_rule_end_date DATE;
l_contract_duration NUMBER;
l_org_id NUMBER;
l_latest_period VARCHAR2(30);
l_sfid VARCHAR2(20);
l_bill_number VARCHAR2(150);
l_total_records NUMBER;
l_error_records NUMBER;
l_interfaced_records NUMBER;
l_error_count NUMBER;
l_memo_line VARCHAR2(50);
v_request_id NUMBER;
l_batch_source_id NUMBER;
l_service_center_check NUMBER;
l_branch_location_check NUMBER;
l_term_name VARCHAR2(15);
l_line_inserted NUMBER;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering CRM Revenue Interface Program');
select count(*)
INTO l_total_records
FROM XXSKM_CRM_REVENUE where NVL(INTFC_STATUS,'NEW') = DECODE(P_INPUT_STATUS,'ERROR','ERROR','NEW');
IF l_total_records = 0 THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'No Records to Process/Show.');
ELSE
l_error_records:= 0;
l_interfaced_records:=0;
l_line_inserted:= 0;
--------BEGIN PREVIEW MODE-------
IF UPPER(P_MODE) = 'PREVIEW' THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DOCUMENT_NUMBER~STATUS~END_CUSTOMER_NAME~SALE_LOCATION_TYPE~BRANCH_LOCATION~CUSTOMER_ASSIGNED~CUSTOMER_ID~SFID~CONTRACT_START_DATE~CONTRACT_END_DATE~CONTRACT_DURATION~PAYMENT_OPTION~SERVICE_TYPE~PRODUCT_QUANTITY~AMC_AMOUNT~AMC_BILL_NUMBER~CURRENCY_CODE~OPERATING_UNIT~BILL_DATE~COMMENTS~COMPONENT_AMOUNT~TAX_AMOUNT~VAT_AMOUNT~SERVICE_AMOUNT~SALE_AMOUNT~UNIT_SELLING_PRICE~GL_DATE~TAX_PERCENTAGE~VAT_PERCENTAGE~INTFC_STATUS~ERROR_MESSAGE~SFFLAG~UOM_CODE~COMPONENT_BILL_NUMBER~SERVICE_BILL_NUMBER
');
FOR rc_crm_intfc IN CRM_INTFC
LOOP
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rc_crm_intfc.DOCUMENT_NUMBER||'~'||rc_crm_intfc.STATUS||'~'||rc_crm_intfc.END_CUSTOMER_NAME||'~'||rc_crm_intfc.SALE_LOCATION_TYPE||'~'||rc_crm_intfc.BRANCH_LOCATION||'~'||rc_crm_intfc.CUSTOMER_ASSIGNED||'~'||rc_crm_intfc.CUSTOMER_ID||'~'||rc_crm_intfc.SFID||'~'||rc_crm_intfc.CONTRACT_START_DATE||'~'||rc_crm_intfc.CONTRACT_END_DATE||'~'||rc_crm_intfc.CONTRACT_DURATION||'~'||rc_crm_intfc.PAYMENT_OPTION||'~'||rc_crm_intfc.SERVICE_TYPE||'~'||rc_crm_intfc.PRODUCT_QUANTITY||'~'||rc_crm_intfc.AMC_AMOUNT||'~'||rc_crm_intfc.AMC_BILL_NUMBER||'~'||rc_crm_intfc.CURRENCY_CODE||'~'||rc_crm_intfc.OPERATING_UNIT||'~'||rc_crm_intfc.BILL_DATE||'~'||rc_crm_intfc.COMMENTS||'~'||rc_crm_intfc.COMPONENT_AMOUNT||'~'||rc_crm_intfc.TAX_AMOUNT||'~'||rc_crm_intfc.VAT_AMOUNT||'~'||rc_crm_intfc.SERVICE_AMOUNT||'~'||rc_crm_intfc.SALE_AMOUNT||'~'||rc_crm_intfc.UNIT_SELLING_PRICE||'~'||rc_crm_intfc.GL_DATE||'~'||rc_crm_intfc.TAX_PERCENTAGE||'~'||rc_crm_intfc.VAT_PERCENTAGE||'~'||rc_crm_intfc.INTFC_STATUS||'~'||rc_crm_intfc.ERROR_MESSAGE||'~'||rc_crm_intfc.SFFLAG||'~'||rc_crm_intfc.UOM_CODE||'~'||rc_crm_intfc.COMPONENT_BILL_NUMBER||'~'||rc_crm_intfc.SERVICE_BILL_NUMBER
);
END;
END LOOP;
ELSE
/*BEGIN
SELECT ledger_id,latest_opened_period_name
INTO l_ledger_id,l_latest_period
FROM gl_ledgers
WHERE name = 'Su-Kam India Primary Ledger';
EXCEPTION
WHEN OTHERS THEN
l_ledger_id := 0;
l_latest_period := 0;
END*/
FOR rc_crm_intfc IN CRM_INTFC
LOOP
l_cust_id := NULL;
l_error_msg := NULL;
l_ledger_id := Null;
l_service_center_check:= 0;
l_branch_location_check:=0;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Document_Number: '||rc_crm_intfc.document_number);
BEGIN
select set_of_books_id, organization_id
into l_ledger_id, l_org_id
from HR_OPERATING_UNITS
where upper(name) = upper(rc_crm_intfc.OPERATING_UNIT);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Operating Unit does not exist for the document number:'||rc_crm_intfc.document_number);
l_ledger_id:= -1;
l_error_records:= l_error_records + 1;
END;
IF l_ledger_id <> -1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Operating Unit Id: '||l_org_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Ledger Id: '||l_ledger_id);
--------Checking the Service Center Value-----
select count(FFV.FLEX_VALUE)
INTO l_service_center_check
from
FND_FLEX_VALUE_SETS FFVS, FND_FLEX_VALUES FFV
WHERE
FFVS.flex_value_set_id = FFV.flex_value_set_id
and FFVS.FLEX_VALUE_SET_NAME = 'SKM_SC / WH'
and FFV.ENABLED_FLAG = 'Y'
and NVL(FFV.END_DATE_ACTIVE, sysdate) >= sysdate
and FFV.FLEX_VALUE = substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
IF l_service_center_check <> 1 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Service Center Code: '||substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3)||' does not exist or inactive');
--l_ledger_id:= -1;
--l_error_records:= l_error_records + 1;
l_error_msg := l_error_msg ||'The Service Center Code '||substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3)||' does not exist or inactive.';
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW', ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
END IF;
--------Checking the Service Center Value-----
--------Checking the Branch Location Value-----
select count(FFV.FLEX_VALUE)
INTO l_branch_location_check
from
FND_FLEX_VALUE_SETS FFVS, FND_FLEX_VALUES FFV
WHERE
FFVS.flex_value_set_id = FFV.flex_value_set_id
and FFVS.FLEX_VALUE_SET_NAME = 'SKM_BRANCH / PLANT'
and FFV.ENABLED_FLAG = 'Y'
and NVL(FFV.END_DATE_ACTIVE, sysdate) >= sysdate
and FFV.FLEX_VALUE = substr(rc_crm_intfc.branch_location,1,4);
IF l_branch_location_check <> 1 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Branch Location Code:'||substr(rc_crm_intfc.branch_location,1,4)||' does not exist or inactive');
--l_ledger_id:= -1;
--l_error_records:= l_error_records + 1;
l_error_msg := l_error_msg ||'The Branch Location Code '||substr(rc_crm_intfc.branch_location,1,4)||' does not exist or inactive.';
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW', ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
END IF;
--------Checking the Branch Location Value-----*/
--------Customer id Check--------------
IF rc_crm_intfc.customer_id IS NOT NULL THEN
BEGIN
SELECT cust_account_id
INTO l_cust_id
FROM apps.hz_cust_accounts_all
WHERE orig_system_reference = rc_crm_intfc.customer_id; --For existing customers
FND_FILE.PUT_LINE(FND_FILE.LOG,'Pre-R12-GOLIVE Customer Id: '||l_cust_id);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'This may be a Post-R12-GOLIVE Customer Id');
l_cust_id := 0;
END;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Customer id: '||l_cust_id);
IF l_cust_id = 0 THEN
BEGIN
SELECT cust_account_id
INTO l_cust_id
FROM apps.hz_cust_accounts_all
WHERE account_number = rc_crm_intfc.customer_id; --For new customers after R12 implementation
FND_FILE.PUT_LINE(FND_FILE.LOG,'Post-R12-GOLIVE Customer Id: '||l_cust_id);
EXCEPTION
WHEN OTHERS THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG,'This Customer does not exist');
l_cust_id := 0;
END;
END IF;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'New Customer id: '||l_cust_id); --After R12 implmentation new customer
IF l_cust_id = 0 THEN
l_error_msg := 'Customer id: '||rc_crm_intfc.customer_id||' does not exist in Oracle ERP';
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: '||l_error_msg);
--l_error_records:= l_error_records + 1;
BEGIN
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
ELSE
BEGIN
select hcs.cust_acct_site_id
into l_primary_bill_to
from
hz_cust_acct_sites_all hcs,
HZ_CUST_SITE_USES_ALL hcsu
where
hcsu.site_use_code = 'BILL_TO'
and hcsu.primary_flag = 'Y'
and hcsu.status = 'A'
and hcs.cust_acct_site_id = hcsu.cust_acct_site_id
and hcs.org_id = l_org_id
and hcs.cust_account_id = l_cust_id;
/*ELECT cust_acct_site_id
INTO l_primary_bill_to
FROM hz_cust_acct_sites_all
WHERE cust_account_id = l_cust_id
AND org_id = rc_crm_intfc.operating_unit;*/
EXCEPTION
WHEN OTHERS THEN
l_primary_bill_to := 0;
l_error_msg := l_error_msg ||'Primary Bill to Address does not exist.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR '||l_error_msg);
END;
IF l_primary_bill_to = 0 THEN
BEGIN
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
END IF;
END IF;
IF l_cust_id > 0 AND l_primary_bill_to > 0 and l_branch_location_check = 1 and l_service_center_check = 1 THEN --Validation if customer exists or not
--IF (rc_crm_intfc.amc_amount IS NOT NULL) OR (rc_crm_intfc.component_amount IS NOT NULL) OR (rc_crm_intfc.service_amount IS NOT NULL) OR (rc_crm_intfc.sale_amount IS NOT NULL) THEN --For AMC Base amount
l_line_type := 'LINE';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
--l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Line Type Customer ID: '||rc_crm_intfc.customer_id);
IF UPPER(rc_crm_intfc.SERVICE_TYPE) IN ('COMPREHENSIVE','NON-COMPREHENSIVE') THEN
l_amount := rc_crm_intfc.amc_amount;
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
l_memo_line:= 'CRM_AMC';
l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= rc_crm_intfc.contract_start_date;
l_rule_end_date:= rc_crm_intfc.contract_end_date;
l_contract_duration:= rc_crm_intfc.contract_duration;
l_bill_number:= rc_crm_intfc.amc_bill_number;
--ELSIF rc_crm_intfc.sale_amount IS NOT NULL THEN
ELSIF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_amount := rc_crm_intfc.sale_amount;
l_attr_cat := 'CRM_SCRAP';
l_memo_line:= 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_unit_price := (rc_crm_intfc.sale_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= Null;
l_rule_end_date:= Null;
l_contract_duration:= Null;
l_bill_number:= rc_crm_intfc.component_bill_number;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_amount := rc_crm_intfc.component_amount;
l_attr_cat := 'CRM_OTHER';
l_memo_line:= 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_unit_price := (rc_crm_intfc.sale_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= Null;
l_rule_end_date:= Null;
l_contract_duration:= Null;
l_bill_number:= rc_crm_intfc.component_bill_number;
--END IF;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_amount := rc_crm_intfc.component_amount;
l_attr_cat := 'CRM_SERVICE';
l_memo_line:= 'CRM_COMPONENT';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_unit_price := (rc_crm_intfc.component_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= Null;
l_rule_end_date:= Null;
l_contract_duration:= Null;
l_bill_number:= rc_crm_intfc.component_bill_number;
END IF;
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
HEADER_ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
HEADER_ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
RULE_START_DATE,
RULE_END_DATE,
ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
ATTRIBUTE13,
HEADER_ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
HEADER_ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
ACCOUNTING_RULE_NAME,
INVOICING_RULE_NAME ,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
INTERFACE_LINE_ATTRIBUTE4,
ATTRIBUTE14,
HEADER_ATTRIBUTE14,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
rc_crm_intfc.sfid,
l_rule_start_date,
l_rule_end_date,
l_contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_memo_line,
rc_crm_intfc.product_quantity,
l_amount,
l_bill_number, --rc_crm_intfc.amc_bill_number,
l_bill_number,
NULL,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_unit_price,
l_attr_cat,
l_attr_cat,
l_attr_cat,
l_acct_rule,
l_inv_rule,
Null,
Null,
Null,
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
--l_attr_cat,
'Corporate',
decode(upper(rc_crm_intfc.service_type),'SERVICE','C',Null),
rc_crm_intfc.service_bill_number,
rc_crm_intfc.service_bill_number,
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'First Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
--END IF; --For AMC Base amount
--
IF upper(rc_crm_intfc.service_type) = 'SERVICE' and rc_crm_intfc.service_amount IS NOT NULL THEN --For Service amount
l_line_type := 'LINE';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
l_amount := rc_crm_intfc.service_amount;
l_unit_price := (rc_crm_intfc.service_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_SERVICE';
l_memo_line:='CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_sfid := rc_crm_intfc.sfid;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Service Type Customer ID: '||rc_crm_intfc.customer_id);
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
HEADER_ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
HEADER_ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
RULE_START_DATE,
RULE_END_DATE,
ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
ATTRIBUTE13,
HEADER_ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
HEADER_ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
ACCOUNTING_RULE_NAME,
INVOICING_RULE_NAME ,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
INTERFACE_LINE_ATTRIBUTE4,
ATTRIBUTE14,
HEADER_ATTRIBUTE14,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
l_sfid,
l_rule_start_date,
l_rule_end_date,
l_contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_memo_line,
rc_crm_intfc.product_quantity,
rc_crm_intfc.service_amount,
rc_crm_intfc.component_bill_number,
rc_crm_intfc.component_bill_number,
NULL,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_unit_price,
l_attr_cat,
l_attr_cat,
l_attr_cat,
l_acct_rule,
l_inv_rule,
Null,
Null,
Null,
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
--l_attr_cat,
'Corporate',
decode(upper(rc_crm_intfc.service_type),'SERVICE','S',Null),
rc_crm_intfc.service_bill_number,
rc_crm_intfc.service_bill_number,
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Second Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
END IF; --For Service amount
----Upal -- Checked till this point'-----------18-Mar-2013
IF rc_crm_intfc.tax_amount IS NOT NULL THEN --For Tax Amount
l_line_type := 'TAX';
l_sfid := rc_crm_intfc.sfid||'_TAX';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
IF UPPER(rc_crm_intfc.SERVICE_TYPE) IN ('COMPREHENSIVE','NON-COMPREHENSIVE') THEN
--l_line_type := 'TAX';
--l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
l_memo_line:= 'CRM_AMC';
ELSIF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_attr_cat := 'CRM_SCRAP';
l_memo_line:= 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_attr_cat := 'CRM_OTHER';
l_memo_line:= 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_attr_cat := 'CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on customer_id : '||rc_crm_intfc.customer_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_attr_cat : '||l_attr_cat);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_acct_rule : '||l_acct_rule);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_inv_rule : '||l_inv_rule);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_sale_loc : '||l_sale_loc);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_sale_loc_r : '||l_sale_loc_r);
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
--RULE_START_DATE,
--RULE_END_DATE,
--ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
--ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
--ACCOUNTING_RULE_NAME,
--INVOICING_RULE_NAME ,
LINK_TO_LINE_CONTEXT,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
LINK_TO_LINE_ATTRIBUTE4,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
TAX_RATE_CODE,
INTERFACE_LINE_ATTRIBUTE4,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
l_sfid,
--rc_crm_intfc.contract_start_date,
--rc_crm_intfc.contract_end_date,
--rc_crm_intfc.contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_attr_cat,
1,
rc_crm_intfc.tax_amount,
--rc_crm_intfc.amc_bill_number,
rc_crm_intfc.tax_percentage,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id, --rc_crm_intfc.operating_unit,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_amount,
l_attr_cat,
l_attr_cat,
--l_acct_rule,
--l_inv_rule,
l_attr_cat,
rc_crm_intfc.sfid,
l_sale_loc,
substr(rc_crm_intfc.branch_location,1,4),
decode(upper(rc_crm_intfc.service_type),'SERVICE','S',Null),
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
'Corporate',
'CRM_SERVICE_TAX',
decode(upper(rc_crm_intfc.service_type),'SERVICE','S',Null),
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Service Tax Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
END IF; --For Tax on AMC Amount
IF rc_crm_intfc.vat_amount IS NOT NULL THEN --For VAT on AMC Amount
/*l_line_type := 'VAT';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,4);
l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
l_sfid := rc_crm_intfc.sfid||'_VAT';
IF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_attr_cat := 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_attr_cat := 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_attr_cat := 'CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
END IF;*/
l_line_type := 'TAX';
l_sfid := rc_crm_intfc.sfid||'_VAT';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
IF UPPER(rc_crm_intfc.SERVICE_TYPE) IN ('COMPREHENSIVE','NON-COMPREHENSIVE') THEN
--l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
ELSIF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_attr_cat := 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_attr_cat := 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_attr_cat := 'CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'VAT Type Customer ID: '||rc_crm_intfc.customer_id);
/*BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
RULE_START_DATE,
RULE_END_DATE,
ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
ACCOUNTING_RULE_NAME,
INVOICING_RULE_NAME ,
LINK_TO_LINE_CONTEXT,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
TAX_RATE_CODE
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
rc_crm_intfc.branch_location,
l_cust_id,
l_primary_bill_to,
l_sfid,
rc_crm_intfc.contract_start_date,
rc_crm_intfc.contract_end_date,
rc_crm_intfc.contract_duration,
rc_crm_intfc.payment_option,
rc_crm_intfc.service_type,
l_attr_cat,
1,
rc_crm_intfc.vat_amount,
rc_crm_intfc.amc_bill_number,
rc_crm_intfc.vat_percentage,
rc_crm_intfc.currency_code,
rc_crm_intfc.operating_unit,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_unit_price,
l_attr_cat,
l_attr_cat,
l_acct_rule,
l_inv_rule,
l_attr_cat,
rc_crm_intfc.sfid,
l_sale_loc,
rc_crm_intfc.branch_location,
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
'Corporate',
'SKM_VAT'
);*/
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
--RULE_START_DATE,
--RULE_END_DATE,
--ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
--ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
--ACCOUNTING_RULE_NAME,
--INVOICING_RULE_NAME ,
LINK_TO_LINE_CONTEXT,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
LINK_TO_LINE_ATTRIBUTE4,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
TAX_RATE_CODE,
INTERFACE_LINE_ATTRIBUTE4,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
l_sfid,
--rc_crm_intfc.contract_start_date,
--rc_crm_intfc.contract_end_date,
--rc_crm_intfc.contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_attr_cat,
1,
rc_crm_intfc.vat_amount,
--rc_crm_intfc.amc_bill_number,
rc_crm_intfc.vat_percentage,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id, --rc_crm_intfc.operating_unit,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_amount,
l_attr_cat,
l_attr_cat,
--l_acct_rule,
--l_inv_rule,
l_attr_cat,
rc_crm_intfc.sfid,
l_sale_loc,
substr(rc_crm_intfc.branch_location,1,4),
decode(upper(rc_crm_intfc.service_type),'SERVICE','C',Null),
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
'Corporate',
'CRM_VALUE_ADDED_TAX',
decode(upper(rc_crm_intfc.service_type),'SERVICE','C',Null),
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'VAT Tax Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
END IF; --For VAT on AMC Amount
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'INTERFACED',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
l_interfaced_records:=l_interfaced_records +1 ;
------Begin Archive to Archive Table-----
INSERT INTO XXSKM_CRM_REVENUE_ARCHIVE
(DOCUMENT_NUMBER,
STATUS,
END_CUSTOMER_NAME,
SALE_LOCATION_TYPE,
BRANCH_LOCATION,
CUSTOMER_ASSIGNED,
CUSTOMER_ID,
SFID,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_DURATION,
PAYMENT_OPTION,
SERVICE_TYPE,
PRODUCT_QUANTITY,
AMC_AMOUNT,
AMC_BILL_NUMBER,
CURRENCY_CODE,
OPERATING_UNIT,
BILL_DATE,
COMMENTS,
COMPONENT_AMOUNT,
TAX_AMOUNT,
VAT_AMOUNT,
SERVICE_AMOUNT,
SALE_AMOUNT,
UNIT_SELLING_PRICE,
GL_DATE,
TAX_PERCENTAGE,
VAT_PERCENTAGE,
INTFC_STATUS,
ERROR_MESSAGE,
SFFLAG,
UOM_CODE,
COMPONENT_BILL_NUMBER,
SERVICE_BILL_NUMBER
)
VALUES
(rc_crm_intfc.DOCUMENT_NUMBER,
rc_crm_intfc.STATUS,
rc_crm_intfc.END_CUSTOMER_NAME,
rc_crm_intfc.SALE_LOCATION_TYPE,
rc_crm_intfc.BRANCH_LOCATION,
rc_crm_intfc.CUSTOMER_ASSIGNED,
rc_crm_intfc.CUSTOMER_ID,
rc_crm_intfc.SFID,
rc_crm_intfc.CONTRACT_START_DATE,
rc_crm_intfc.CONTRACT_END_DATE,
rc_crm_intfc.CONTRACT_DURATION,
rc_crm_intfc.PAYMENT_OPTION,
rc_crm_intfc.SERVICE_TYPE,
rc_crm_intfc.PRODUCT_QUANTITY,
rc_crm_intfc.AMC_AMOUNT,
rc_crm_intfc.AMC_BILL_NUMBER,
rc_crm_intfc.CURRENCY_CODE,
rc_crm_intfc.OPERATING_UNIT,
rc_crm_intfc.BILL_DATE,
rc_crm_intfc.COMMENTS,
rc_crm_intfc.COMPONENT_AMOUNT,
rc_crm_intfc.TAX_AMOUNT,
rc_crm_intfc.VAT_AMOUNT,
rc_crm_intfc.SERVICE_AMOUNT,
rc_crm_intfc.SALE_AMOUNT,
rc_crm_intfc.UNIT_SELLING_PRICE,
rc_crm_intfc.GL_DATE,
rc_crm_intfc.TAX_PERCENTAGE,
rc_crm_intfc.VAT_PERCENTAGE,
'INTERFACED',
Null,
rc_crm_intfc.SFFLAG,
rc_crm_intfc.UOM_CODE,
rc_crm_intfc.COMPONENT_BILL_NUMBER,
rc_crm_intfc.SERVICE_BILL_NUMBER);
DELETE from XXSKM_CRM_REVENUE
--SET INTFC_STATUS = 'INTERFACED'--,ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
------End Archive to Archive Table-----*/
END IF; --l_cust_id >0 and l_primary_bill_to > 0
--END IF; --Validation if customer exists or not
ELSE IF l_ledger_id = -1 then --Validation for non-existant Operating Unit
l_error_msg := 'The Operating Unit does not exist for the document number.'||rc_crm_intfc.document_number;
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
END IF;
END IF; ---Validation where Organization Id doesn't exist
END LOOP;
SELECT count(*) into l_error_records from XXSKM_CRM_REVENUE
WHERE INTFC_STATUS = 'ERROR_NEW';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Records Processed: '||l_total_records);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Succesful Records: '||l_interfaced_records);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Lines Inserted into AR Interface: '||l_line_inserted);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Error Records: '||l_error_records);
IF l_error_records <> 0 then
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------------------Details of Error Records-----------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'SL NO~DOCUMENT_NUMBER~CUSTOMER ASSIGNED~CUSTOMER_ID~SERVICE TYPE~ERROR_MESSAGE');
l_error_count:=0;
FOR rc_crm_intfc in CRM_INTFC_ERR_NEW LOOP
l_error_count:=l_error_count + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_error_count||'~'||rc_crm_intfc.DOCUMENT_NUMBER||'~'||rc_crm_intfc.CUSTOMER_ASSIGNED||'~'||rc_crm_intfc.CUSTOMER_ID||'~'||rc_crm_intfc.SERVICE_TYPE||'~'||rc_crm_intfc.ERROR_MESSAGE);
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR'
WHERE document_number = rc_crm_intfc.document_number;
END LOOP;
END IF;
COMMIT;
v_request_id :=
fnd_request.submit_request
('AR','RAXTRX',NULL,NULL,FALSE,'MAIN','T',l_batch_source_id,
'CRM',SYSDATE,'','','','','','','','','','','','',
'','','','','','','','','N','Y','','101');
END IF;
/*select BATCH_SOURCE_ID
INTO l_batch_source_id
from RA_BATCH_SOURCES_ALL
where name = 'CRM';*/
END IF;
END MAIN_PROC;
END XXSKM_CRM_REV_INTFC;
/
(
DOCUMENT_NUMBER VARCHAR2(50 BYTE),
STATUS VARCHAR2(50 BYTE),
END_CUSTOMER_NAME VARCHAR2(240 BYTE),
SALE_LOCATION_TYPE VARCHAR2(30 BYTE),
BRANCH_LOCATION VARCHAR2(30 BYTE),
CUSTOMER_ASSIGNED VARCHAR2(240 BYTE),
CUSTOMER_ID VARCHAR2(150 BYTE),
SFID VARCHAR2(150 BYTE),
CONTRACT_START_DATE DATE,
CONTRACT_END_DATE DATE,
CONTRACT_DURATION NUMBER,
PAYMENT_OPTION VARCHAR2(15 BYTE),
SERVICE_TYPE VARCHAR2(20 BYTE),
PRODUCT_QUANTITY NUMBER,
AMC_AMOUNT NUMBER,
AMC_BILL_NUMBER VARCHAR2(150 BYTE),
CURRENCY_CODE VARCHAR2(15 BYTE),
OPERATING_UNIT VARCHAR2(240 BYTE),
BILL_DATE DATE,
COMMENTS VARCHAR2(240 BYTE),
COMPONENT_AMOUNT NUMBER,
TAX_AMOUNT NUMBER,
VAT_AMOUNT NUMBER,
SERVICE_AMOUNT NUMBER,
SALE_AMOUNT NUMBER,
UNIT_SELLING_PRICE NUMBER,
GL_DATE DATE,
TAX_PERCENTAGE NUMBER,
VAT_PERCENTAGE NUMBER,
INTFC_STATUS VARCHAR2(20 BYTE),
ERROR_MESSAGE VARCHAR2(1000 BYTE),
SFFLAG VARCHAR2(100 BYTE),
UOM_CODE VARCHAR2(3 BYTE),
COMPONENT_BILL_NUMBER VARCHAR2(60 BYTE),
SERVICE_BILL_NUMBER VARCHAR2(60 BYTE)
)
CREATE TABLE XXSUKAM.XXSKM_CRM_REVENUE_ARCHIVE
(
DOCUMENT_NUMBER VARCHAR2(50 BYTE),
STATUS VARCHAR2(50 BYTE),
END_CUSTOMER_NAME VARCHAR2(240 BYTE),
SALE_LOCATION_TYPE VARCHAR2(30 BYTE),
BRANCH_LOCATION VARCHAR2(30 BYTE),
CUSTOMER_ASSIGNED VARCHAR2(240 BYTE),
CUSTOMER_ID VARCHAR2(150 BYTE),
SFID VARCHAR2(150 BYTE),
CONTRACT_START_DATE DATE,
CONTRACT_END_DATE DATE,
CONTRACT_DURATION NUMBER,
PAYMENT_OPTION VARCHAR2(15 BYTE),
SERVICE_TYPE VARCHAR2(20 BYTE),
PRODUCT_QUANTITY NUMBER,
AMC_AMOUNT NUMBER,
AMC_BILL_NUMBER VARCHAR2(150 BYTE),
CURRENCY_CODE VARCHAR2(15 BYTE),
OPERATING_UNIT VARCHAR2(240 BYTE),
BILL_DATE DATE,
COMMENTS VARCHAR2(240 BYTE),
COMPONENT_AMOUNT NUMBER,
TAX_AMOUNT NUMBER,
VAT_AMOUNT NUMBER,
SERVICE_AMOUNT NUMBER,
SALE_AMOUNT NUMBER,
UNIT_SELLING_PRICE NUMBER,
GL_DATE DATE,
TAX_PERCENTAGE NUMBER,
VAT_PERCENTAGE NUMBER,
INTFC_STATUS VARCHAR2(20 BYTE),
ERROR_MESSAGE VARCHAR2(1000 BYTE),
SFFLAG VARCHAR2(100 BYTE),
UOM_CODE VARCHAR2(3 BYTE),
COMPONENT_BILL_NUMBER VARCHAR2(60 BYTE),
SERVICE_BILL_NUMBER VARCHAR2(60 BYTE)
)
CREATE OR REPLACE PACKAGE APPS.XXSKM_CRM_REV_INTFC
AS
/*- - ==============================================================
| Module :- CRM AND AR INVOICE INTERFACE
| Description :- This Package will validate the AR invoice data coming from CRM legacy
| and dump into interface tables as per CRM Interface document .
|
| Parameter :- 1.) retCode, ErrBuff are default parameters and used in concurrent program.
- -================================================================*/
PROCEDURE MAIN_PROC(errBuff OUT CHAR
, retCode OUT NUMBER
,P_INPUT_STATUS IN VARCHAR2
,P_MODE IN VARCHAR2 );
END XXSKM_CRM_REV_INTFC;
/
PROCEDURE MAIN_PROC(errBuff OUT CHAR
,retCode OUT NUMBER
,P_INPUT_STATUS IN VARCHAR2
,P_MODE IN VARCHAR2) AS --
CURSOR CRM_INTFC IS
SELECT *
FROM XXSKM_CRM_REVENUE where NVL(INTFC_STATUS,'NEW') = DECODE(P_INPUT_STATUS,'ERROR','ERROR','NEW');
CURSOR CRM_INTFC_ERR_NEW IS
SELECT *
FROM XXSKM_CRM_REVENUE where INTFC_STATUS = 'ERROR_NEW';
--- APPS.XXSKMREV_INTFC_BCKUP ----
l_line_type VARCHAR2(50);
l_sale_loc VARCHAR2(20);
l_sale_loc_r VARCHAR2(200);
l_unit_price NUMBER;
l_attr_cat VARCHAR2(30);
l_acct_rule VARCHAR2(20);
l_inv_rule VARCHAR2(20);
l_amount NUMBER;
l_cust_id NUMBER;
l_primary_bill_to NUMBER := NULL;
l_error_msg VARCHAR2(1000);
l_status VARCHAR2(20) := NULL;
l_ledger_id NUMBER;
l_rule_start_date DATE;
l_rule_end_date DATE;
l_contract_duration NUMBER;
l_org_id NUMBER;
l_latest_period VARCHAR2(30);
l_sfid VARCHAR2(20);
l_bill_number VARCHAR2(150);
l_total_records NUMBER;
l_error_records NUMBER;
l_interfaced_records NUMBER;
l_error_count NUMBER;
l_memo_line VARCHAR2(50);
v_request_id NUMBER;
l_batch_source_id NUMBER;
l_service_center_check NUMBER;
l_branch_location_check NUMBER;
l_term_name VARCHAR2(15);
l_line_inserted NUMBER;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Entering CRM Revenue Interface Program');
select count(*)
INTO l_total_records
FROM XXSKM_CRM_REVENUE where NVL(INTFC_STATUS,'NEW') = DECODE(P_INPUT_STATUS,'ERROR','ERROR','NEW');
IF l_total_records = 0 THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'No Records to Process/Show.');
ELSE
l_error_records:= 0;
l_interfaced_records:=0;
l_line_inserted:= 0;
--------BEGIN PREVIEW MODE-------
IF UPPER(P_MODE) = 'PREVIEW' THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'DOCUMENT_NUMBER~STATUS~END_CUSTOMER_NAME~SALE_LOCATION_TYPE~BRANCH_LOCATION~CUSTOMER_ASSIGNED~CUSTOMER_ID~SFID~CONTRACT_START_DATE~CONTRACT_END_DATE~CONTRACT_DURATION~PAYMENT_OPTION~SERVICE_TYPE~PRODUCT_QUANTITY~AMC_AMOUNT~AMC_BILL_NUMBER~CURRENCY_CODE~OPERATING_UNIT~BILL_DATE~COMMENTS~COMPONENT_AMOUNT~TAX_AMOUNT~VAT_AMOUNT~SERVICE_AMOUNT~SALE_AMOUNT~UNIT_SELLING_PRICE~GL_DATE~TAX_PERCENTAGE~VAT_PERCENTAGE~INTFC_STATUS~ERROR_MESSAGE~SFFLAG~UOM_CODE~COMPONENT_BILL_NUMBER~SERVICE_BILL_NUMBER
');
FOR rc_crm_intfc IN CRM_INTFC
LOOP
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rc_crm_intfc.DOCUMENT_NUMBER||'~'||rc_crm_intfc.STATUS||'~'||rc_crm_intfc.END_CUSTOMER_NAME||'~'||rc_crm_intfc.SALE_LOCATION_TYPE||'~'||rc_crm_intfc.BRANCH_LOCATION||'~'||rc_crm_intfc.CUSTOMER_ASSIGNED||'~'||rc_crm_intfc.CUSTOMER_ID||'~'||rc_crm_intfc.SFID||'~'||rc_crm_intfc.CONTRACT_START_DATE||'~'||rc_crm_intfc.CONTRACT_END_DATE||'~'||rc_crm_intfc.CONTRACT_DURATION||'~'||rc_crm_intfc.PAYMENT_OPTION||'~'||rc_crm_intfc.SERVICE_TYPE||'~'||rc_crm_intfc.PRODUCT_QUANTITY||'~'||rc_crm_intfc.AMC_AMOUNT||'~'||rc_crm_intfc.AMC_BILL_NUMBER||'~'||rc_crm_intfc.CURRENCY_CODE||'~'||rc_crm_intfc.OPERATING_UNIT||'~'||rc_crm_intfc.BILL_DATE||'~'||rc_crm_intfc.COMMENTS||'~'||rc_crm_intfc.COMPONENT_AMOUNT||'~'||rc_crm_intfc.TAX_AMOUNT||'~'||rc_crm_intfc.VAT_AMOUNT||'~'||rc_crm_intfc.SERVICE_AMOUNT||'~'||rc_crm_intfc.SALE_AMOUNT||'~'||rc_crm_intfc.UNIT_SELLING_PRICE||'~'||rc_crm_intfc.GL_DATE||'~'||rc_crm_intfc.TAX_PERCENTAGE||'~'||rc_crm_intfc.VAT_PERCENTAGE||'~'||rc_crm_intfc.INTFC_STATUS||'~'||rc_crm_intfc.ERROR_MESSAGE||'~'||rc_crm_intfc.SFFLAG||'~'||rc_crm_intfc.UOM_CODE||'~'||rc_crm_intfc.COMPONENT_BILL_NUMBER||'~'||rc_crm_intfc.SERVICE_BILL_NUMBER
);
END;
END LOOP;
ELSE
/*BEGIN
SELECT ledger_id,latest_opened_period_name
INTO l_ledger_id,l_latest_period
FROM gl_ledgers
WHERE name = 'Su-Kam India Primary Ledger';
EXCEPTION
WHEN OTHERS THEN
l_ledger_id := 0;
l_latest_period := 0;
END*/
FOR rc_crm_intfc IN CRM_INTFC
LOOP
l_cust_id := NULL;
l_error_msg := NULL;
l_ledger_id := Null;
l_service_center_check:= 0;
l_branch_location_check:=0;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Document_Number: '||rc_crm_intfc.document_number);
BEGIN
select set_of_books_id, organization_id
into l_ledger_id, l_org_id
from HR_OPERATING_UNITS
where upper(name) = upper(rc_crm_intfc.OPERATING_UNIT);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Operating Unit does not exist for the document number:'||rc_crm_intfc.document_number);
l_ledger_id:= -1;
l_error_records:= l_error_records + 1;
END;
IF l_ledger_id <> -1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Operating Unit Id: '||l_org_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Ledger Id: '||l_ledger_id);
--------Checking the Service Center Value-----
select count(FFV.FLEX_VALUE)
INTO l_service_center_check
from
FND_FLEX_VALUE_SETS FFVS, FND_FLEX_VALUES FFV
WHERE
FFVS.flex_value_set_id = FFV.flex_value_set_id
and FFVS.FLEX_VALUE_SET_NAME = 'SKM_SC / WH'
and FFV.ENABLED_FLAG = 'Y'
and NVL(FFV.END_DATE_ACTIVE, sysdate) >= sysdate
and FFV.FLEX_VALUE = substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
IF l_service_center_check <> 1 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Service Center Code: '||substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3)||' does not exist or inactive');
--l_ledger_id:= -1;
--l_error_records:= l_error_records + 1;
l_error_msg := l_error_msg ||'The Service Center Code '||substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3)||' does not exist or inactive.';
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW', ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
END IF;
--------Checking the Service Center Value-----
--------Checking the Branch Location Value-----
select count(FFV.FLEX_VALUE)
INTO l_branch_location_check
from
FND_FLEX_VALUE_SETS FFVS, FND_FLEX_VALUES FFV
WHERE
FFVS.flex_value_set_id = FFV.flex_value_set_id
and FFVS.FLEX_VALUE_SET_NAME = 'SKM_BRANCH / PLANT'
and FFV.ENABLED_FLAG = 'Y'
and NVL(FFV.END_DATE_ACTIVE, sysdate) >= sysdate
and FFV.FLEX_VALUE = substr(rc_crm_intfc.branch_location,1,4);
IF l_branch_location_check <> 1 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Branch Location Code:'||substr(rc_crm_intfc.branch_location,1,4)||' does not exist or inactive');
--l_ledger_id:= -1;
--l_error_records:= l_error_records + 1;
l_error_msg := l_error_msg ||'The Branch Location Code '||substr(rc_crm_intfc.branch_location,1,4)||' does not exist or inactive.';
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW', ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
END IF;
--------Checking the Branch Location Value-----*/
--------Customer id Check--------------
IF rc_crm_intfc.customer_id IS NOT NULL THEN
BEGIN
SELECT cust_account_id
INTO l_cust_id
FROM apps.hz_cust_accounts_all
WHERE orig_system_reference = rc_crm_intfc.customer_id; --For existing customers
FND_FILE.PUT_LINE(FND_FILE.LOG,'Pre-R12-GOLIVE Customer Id: '||l_cust_id);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'This may be a Post-R12-GOLIVE Customer Id');
l_cust_id := 0;
END;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Customer id: '||l_cust_id);
IF l_cust_id = 0 THEN
BEGIN
SELECT cust_account_id
INTO l_cust_id
FROM apps.hz_cust_accounts_all
WHERE account_number = rc_crm_intfc.customer_id; --For new customers after R12 implementation
FND_FILE.PUT_LINE(FND_FILE.LOG,'Post-R12-GOLIVE Customer Id: '||l_cust_id);
EXCEPTION
WHEN OTHERS THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG,'This Customer does not exist');
l_cust_id := 0;
END;
END IF;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'New Customer id: '||l_cust_id); --After R12 implmentation new customer
IF l_cust_id = 0 THEN
l_error_msg := 'Customer id: '||rc_crm_intfc.customer_id||' does not exist in Oracle ERP';
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: '||l_error_msg);
--l_error_records:= l_error_records + 1;
BEGIN
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
ELSE
BEGIN
select hcs.cust_acct_site_id
into l_primary_bill_to
from
hz_cust_acct_sites_all hcs,
HZ_CUST_SITE_USES_ALL hcsu
where
hcsu.site_use_code = 'BILL_TO'
and hcsu.primary_flag = 'Y'
and hcsu.status = 'A'
and hcs.cust_acct_site_id = hcsu.cust_acct_site_id
and hcs.org_id = l_org_id
and hcs.cust_account_id = l_cust_id;
/*ELECT cust_acct_site_id
INTO l_primary_bill_to
FROM hz_cust_acct_sites_all
WHERE cust_account_id = l_cust_id
AND org_id = rc_crm_intfc.operating_unit;*/
EXCEPTION
WHEN OTHERS THEN
l_primary_bill_to := 0;
l_error_msg := l_error_msg ||'Primary Bill to Address does not exist.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR '||l_error_msg);
END;
IF l_primary_bill_to = 0 THEN
BEGIN
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
END IF;
END IF;
IF l_cust_id > 0 AND l_primary_bill_to > 0 and l_branch_location_check = 1 and l_service_center_check = 1 THEN --Validation if customer exists or not
--IF (rc_crm_intfc.amc_amount IS NOT NULL) OR (rc_crm_intfc.component_amount IS NOT NULL) OR (rc_crm_intfc.service_amount IS NOT NULL) OR (rc_crm_intfc.sale_amount IS NOT NULL) THEN --For AMC Base amount
l_line_type := 'LINE';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
--l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Line Type Customer ID: '||rc_crm_intfc.customer_id);
IF UPPER(rc_crm_intfc.SERVICE_TYPE) IN ('COMPREHENSIVE','NON-COMPREHENSIVE') THEN
l_amount := rc_crm_intfc.amc_amount;
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
l_memo_line:= 'CRM_AMC';
l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= rc_crm_intfc.contract_start_date;
l_rule_end_date:= rc_crm_intfc.contract_end_date;
l_contract_duration:= rc_crm_intfc.contract_duration;
l_bill_number:= rc_crm_intfc.amc_bill_number;
--ELSIF rc_crm_intfc.sale_amount IS NOT NULL THEN
ELSIF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_amount := rc_crm_intfc.sale_amount;
l_attr_cat := 'CRM_SCRAP';
l_memo_line:= 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_unit_price := (rc_crm_intfc.sale_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= Null;
l_rule_end_date:= Null;
l_contract_duration:= Null;
l_bill_number:= rc_crm_intfc.component_bill_number;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_amount := rc_crm_intfc.component_amount;
l_attr_cat := 'CRM_OTHER';
l_memo_line:= 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_unit_price := (rc_crm_intfc.sale_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= Null;
l_rule_end_date:= Null;
l_contract_duration:= Null;
l_bill_number:= rc_crm_intfc.component_bill_number;
--END IF;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_amount := rc_crm_intfc.component_amount;
l_attr_cat := 'CRM_SERVICE';
l_memo_line:= 'CRM_COMPONENT';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_unit_price := (rc_crm_intfc.component_amount/rc_crm_intfc.product_quantity);
l_rule_start_date:= Null;
l_rule_end_date:= Null;
l_contract_duration:= Null;
l_bill_number:= rc_crm_intfc.component_bill_number;
END IF;
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
HEADER_ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
HEADER_ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
RULE_START_DATE,
RULE_END_DATE,
ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
ATTRIBUTE13,
HEADER_ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
HEADER_ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
ACCOUNTING_RULE_NAME,
INVOICING_RULE_NAME ,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
INTERFACE_LINE_ATTRIBUTE4,
ATTRIBUTE14,
HEADER_ATTRIBUTE14,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
rc_crm_intfc.sfid,
l_rule_start_date,
l_rule_end_date,
l_contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_memo_line,
rc_crm_intfc.product_quantity,
l_amount,
l_bill_number, --rc_crm_intfc.amc_bill_number,
l_bill_number,
NULL,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_unit_price,
l_attr_cat,
l_attr_cat,
l_attr_cat,
l_acct_rule,
l_inv_rule,
Null,
Null,
Null,
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
--l_attr_cat,
'Corporate',
decode(upper(rc_crm_intfc.service_type),'SERVICE','C',Null),
rc_crm_intfc.service_bill_number,
rc_crm_intfc.service_bill_number,
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'First Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
--END IF; --For AMC Base amount
--
IF upper(rc_crm_intfc.service_type) = 'SERVICE' and rc_crm_intfc.service_amount IS NOT NULL THEN --For Service amount
l_line_type := 'LINE';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
l_amount := rc_crm_intfc.service_amount;
l_unit_price := (rc_crm_intfc.service_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_SERVICE';
l_memo_line:='CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
l_sfid := rc_crm_intfc.sfid;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Service Type Customer ID: '||rc_crm_intfc.customer_id);
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
HEADER_ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
HEADER_ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
RULE_START_DATE,
RULE_END_DATE,
ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
ATTRIBUTE13,
HEADER_ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
HEADER_ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
ACCOUNTING_RULE_NAME,
INVOICING_RULE_NAME ,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
INTERFACE_LINE_ATTRIBUTE4,
ATTRIBUTE14,
HEADER_ATTRIBUTE14,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
l_sfid,
l_rule_start_date,
l_rule_end_date,
l_contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_memo_line,
rc_crm_intfc.product_quantity,
rc_crm_intfc.service_amount,
rc_crm_intfc.component_bill_number,
rc_crm_intfc.component_bill_number,
NULL,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_unit_price,
l_attr_cat,
l_attr_cat,
l_attr_cat,
l_acct_rule,
l_inv_rule,
Null,
Null,
Null,
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
--l_attr_cat,
'Corporate',
decode(upper(rc_crm_intfc.service_type),'SERVICE','S',Null),
rc_crm_intfc.service_bill_number,
rc_crm_intfc.service_bill_number,
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Second Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
END IF; --For Service amount
----Upal -- Checked till this point'-----------18-Mar-2013
IF rc_crm_intfc.tax_amount IS NOT NULL THEN --For Tax Amount
l_line_type := 'TAX';
l_sfid := rc_crm_intfc.sfid||'_TAX';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
IF UPPER(rc_crm_intfc.SERVICE_TYPE) IN ('COMPREHENSIVE','NON-COMPREHENSIVE') THEN
--l_line_type := 'TAX';
--l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
l_memo_line:= 'CRM_AMC';
ELSIF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_attr_cat := 'CRM_SCRAP';
l_memo_line:= 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_attr_cat := 'CRM_OTHER';
l_memo_line:= 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_attr_cat := 'CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on customer_id : '||rc_crm_intfc.customer_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_attr_cat : '||l_attr_cat);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_acct_rule : '||l_acct_rule);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_inv_rule : '||l_inv_rule);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_sale_loc : '||l_sale_loc);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Tax on l_sale_loc_r : '||l_sale_loc_r);
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
--RULE_START_DATE,
--RULE_END_DATE,
--ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
--ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
--ACCOUNTING_RULE_NAME,
--INVOICING_RULE_NAME ,
LINK_TO_LINE_CONTEXT,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
LINK_TO_LINE_ATTRIBUTE4,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
TAX_RATE_CODE,
INTERFACE_LINE_ATTRIBUTE4,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
l_sfid,
--rc_crm_intfc.contract_start_date,
--rc_crm_intfc.contract_end_date,
--rc_crm_intfc.contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_attr_cat,
1,
rc_crm_intfc.tax_amount,
--rc_crm_intfc.amc_bill_number,
rc_crm_intfc.tax_percentage,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id, --rc_crm_intfc.operating_unit,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_amount,
l_attr_cat,
l_attr_cat,
--l_acct_rule,
--l_inv_rule,
l_attr_cat,
rc_crm_intfc.sfid,
l_sale_loc,
substr(rc_crm_intfc.branch_location,1,4),
decode(upper(rc_crm_intfc.service_type),'SERVICE','S',Null),
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
'Corporate',
'CRM_SERVICE_TAX',
decode(upper(rc_crm_intfc.service_type),'SERVICE','S',Null),
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Service Tax Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
END IF; --For Tax on AMC Amount
IF rc_crm_intfc.vat_amount IS NOT NULL THEN --For VAT on AMC Amount
/*l_line_type := 'VAT';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,4);
l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
l_sfid := rc_crm_intfc.sfid||'_VAT';
IF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_attr_cat := 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_attr_cat := 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_attr_cat := 'CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
END IF;*/
l_line_type := 'TAX';
l_sfid := rc_crm_intfc.sfid||'_VAT';
l_sale_loc := substr(rc_crm_intfc.SALE_LOCATION_TYPE,1,3);
l_sale_loc_r := substr(rc_crm_intfc.SALE_LOCATION_TYPE,5);
IF UPPER(rc_crm_intfc.SERVICE_TYPE) IN ('COMPREHENSIVE','NON-COMPREHENSIVE') THEN
--l_unit_price := (rc_crm_intfc.amc_amount/rc_crm_intfc.product_quantity);
l_attr_cat := 'CRM_AMC';
l_acct_rule := 'CRM_AMC';
l_inv_rule := 'Advance Invoice';
ELSIF upper(rc_crm_intfc.service_type) = 'SCRAP' THEN
l_attr_cat := 'CRM_SCRAP';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'OTHER SALES' THEN
l_attr_cat := 'CRM_OTHER';
l_acct_rule := NULL;
l_inv_rule := NULL;
ELSIF upper(rc_crm_intfc.service_type) = 'SERVICE' THEN
l_attr_cat := 'CRM_SERVICE';
l_acct_rule := NULL;
l_inv_rule := NULL;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'VAT Type Customer ID: '||rc_crm_intfc.customer_id);
/*BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
RULE_START_DATE,
RULE_END_DATE,
ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
ACCOUNTING_RULE_NAME,
INVOICING_RULE_NAME ,
LINK_TO_LINE_CONTEXT,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
TAX_RATE_CODE
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
rc_crm_intfc.branch_location,
l_cust_id,
l_primary_bill_to,
l_sfid,
rc_crm_intfc.contract_start_date,
rc_crm_intfc.contract_end_date,
rc_crm_intfc.contract_duration,
rc_crm_intfc.payment_option,
rc_crm_intfc.service_type,
l_attr_cat,
1,
rc_crm_intfc.vat_amount,
rc_crm_intfc.amc_bill_number,
rc_crm_intfc.vat_percentage,
rc_crm_intfc.currency_code,
rc_crm_intfc.operating_unit,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_unit_price,
l_attr_cat,
l_attr_cat,
l_acct_rule,
l_inv_rule,
l_attr_cat,
rc_crm_intfc.sfid,
l_sale_loc,
rc_crm_intfc.branch_location,
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
'Corporate',
'SKM_VAT'
);*/
BEGIN
INSERT INTO RA_INTERFACE_LINES_ALL
(TRX_NUMBER,
ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE2,
ATTRIBUTE12,
INTERFACE_LINE_ATTRIBUTE3,
ORIG_SYSTEM_BILL_CUSTOMER_ID, --ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID, --ORIG_SYSTEM_BILL_ADDRESS_REF,
INTERFACE_LINE_ATTRIBUTE1,
--RULE_START_DATE,
--RULE_END_DATE,
--ACCOUNTING_RULE_DURATION,
TERM_NAME,
CUST_TRX_TYPE_NAME,
MEMO_LINE_NAME,
QUANTITY,
AMOUNT,
--ATTRIBUTE13,
TAX_RATE,
CURRENCY_CODE,
ORG_ID,
UOM_CODE,
TRX_DATE,
GL_DATE,
COMMENTS,
UNIT_SELLING_PRICE,
ATTRIBUTE_CATEGORY,
INTERFACE_LINE_CONTEXT,
--ACCOUNTING_RULE_NAME,
--INVOICING_RULE_NAME ,
LINK_TO_LINE_CONTEXT,
LINK_TO_LINE_ATTRIBUTE1,
LINK_TO_LINE_ATTRIBUTE2,
LINK_TO_LINE_ATTRIBUTE3,
LINK_TO_LINE_ATTRIBUTE4,
SET_OF_BOOKS_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
DESCRIPTION,
CONVERSION_TYPE,
TAX_RATE_CODE,
INTERFACE_LINE_ATTRIBUTE4,
PRIMARY_SALESREP_NUMBER
)
VALUES
(rc_crm_intfc.document_number,
rc_crm_intfc.end_customer_name,
l_sale_loc,
l_sale_loc_r,
substr(rc_crm_intfc.branch_location,1,4),
l_cust_id,
l_primary_bill_to,
l_sfid,
--rc_crm_intfc.contract_start_date,
--rc_crm_intfc.contract_end_date,
--rc_crm_intfc.contract_duration,
nvl(rc_crm_intfc.payment_option,'Single Payment'),
initcap(rc_crm_intfc.service_type),
l_attr_cat,
1,
rc_crm_intfc.vat_amount,
--rc_crm_intfc.amc_bill_number,
rc_crm_intfc.vat_percentage,
nvl(rc_crm_intfc.currency_code,'INR'),
l_org_id, --rc_crm_intfc.operating_unit,
'Ea',
rc_crm_intfc.bill_date,
rc_crm_intfc.bill_date,
rc_crm_intfc.comments,
l_amount,
l_attr_cat,
l_attr_cat,
--l_acct_rule,
--l_inv_rule,
l_attr_cat,
rc_crm_intfc.sfid,
l_sale_loc,
substr(rc_crm_intfc.branch_location,1,4),
decode(upper(rc_crm_intfc.service_type),'SERVICE','C',Null),
l_ledger_id,
'CRM',
l_line_type,
l_attr_cat,
'Corporate',
'CRM_VALUE_ADDED_TAX',
decode(upper(rc_crm_intfc.service_type),'SERVICE','C',Null),
'-3'
);
l_line_inserted:= l_line_inserted + 1;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'VAT Tax Line is not inserted for Document Number: '||rc_crm_intfc.document_number);
--NULL;
END;
END IF; --For VAT on AMC Amount
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'INTERFACED',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
l_interfaced_records:=l_interfaced_records +1 ;
------Begin Archive to Archive Table-----
INSERT INTO XXSKM_CRM_REVENUE_ARCHIVE
(DOCUMENT_NUMBER,
STATUS,
END_CUSTOMER_NAME,
SALE_LOCATION_TYPE,
BRANCH_LOCATION,
CUSTOMER_ASSIGNED,
CUSTOMER_ID,
SFID,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_DURATION,
PAYMENT_OPTION,
SERVICE_TYPE,
PRODUCT_QUANTITY,
AMC_AMOUNT,
AMC_BILL_NUMBER,
CURRENCY_CODE,
OPERATING_UNIT,
BILL_DATE,
COMMENTS,
COMPONENT_AMOUNT,
TAX_AMOUNT,
VAT_AMOUNT,
SERVICE_AMOUNT,
SALE_AMOUNT,
UNIT_SELLING_PRICE,
GL_DATE,
TAX_PERCENTAGE,
VAT_PERCENTAGE,
INTFC_STATUS,
ERROR_MESSAGE,
SFFLAG,
UOM_CODE,
COMPONENT_BILL_NUMBER,
SERVICE_BILL_NUMBER
)
VALUES
(rc_crm_intfc.DOCUMENT_NUMBER,
rc_crm_intfc.STATUS,
rc_crm_intfc.END_CUSTOMER_NAME,
rc_crm_intfc.SALE_LOCATION_TYPE,
rc_crm_intfc.BRANCH_LOCATION,
rc_crm_intfc.CUSTOMER_ASSIGNED,
rc_crm_intfc.CUSTOMER_ID,
rc_crm_intfc.SFID,
rc_crm_intfc.CONTRACT_START_DATE,
rc_crm_intfc.CONTRACT_END_DATE,
rc_crm_intfc.CONTRACT_DURATION,
rc_crm_intfc.PAYMENT_OPTION,
rc_crm_intfc.SERVICE_TYPE,
rc_crm_intfc.PRODUCT_QUANTITY,
rc_crm_intfc.AMC_AMOUNT,
rc_crm_intfc.AMC_BILL_NUMBER,
rc_crm_intfc.CURRENCY_CODE,
rc_crm_intfc.OPERATING_UNIT,
rc_crm_intfc.BILL_DATE,
rc_crm_intfc.COMMENTS,
rc_crm_intfc.COMPONENT_AMOUNT,
rc_crm_intfc.TAX_AMOUNT,
rc_crm_intfc.VAT_AMOUNT,
rc_crm_intfc.SERVICE_AMOUNT,
rc_crm_intfc.SALE_AMOUNT,
rc_crm_intfc.UNIT_SELLING_PRICE,
rc_crm_intfc.GL_DATE,
rc_crm_intfc.TAX_PERCENTAGE,
rc_crm_intfc.VAT_PERCENTAGE,
'INTERFACED',
Null,
rc_crm_intfc.SFFLAG,
rc_crm_intfc.UOM_CODE,
rc_crm_intfc.COMPONENT_BILL_NUMBER,
rc_crm_intfc.SERVICE_BILL_NUMBER);
DELETE from XXSKM_CRM_REVENUE
--SET INTFC_STATUS = 'INTERFACED'--,ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
------End Archive to Archive Table-----*/
END IF; --l_cust_id >0 and l_primary_bill_to > 0
--END IF; --Validation if customer exists or not
ELSE IF l_ledger_id = -1 then --Validation for non-existant Operating Unit
l_error_msg := 'The Operating Unit does not exist for the document number.'||rc_crm_intfc.document_number;
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR_NEW',ERROR_MESSAGE = l_error_msg
WHERE document_number = rc_crm_intfc.document_number;
END IF;
END IF; ---Validation where Organization Id doesn't exist
END LOOP;
SELECT count(*) into l_error_records from XXSKM_CRM_REVENUE
WHERE INTFC_STATUS = 'ERROR_NEW';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Records Processed: '||l_total_records);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Succesful Records: '||l_interfaced_records);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Lines Inserted into AR Interface: '||l_line_inserted);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Error Records: '||l_error_records);
IF l_error_records <> 0 then
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------------------Details of Error Records-----------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'SL NO~DOCUMENT_NUMBER~CUSTOMER ASSIGNED~CUSTOMER_ID~SERVICE TYPE~ERROR_MESSAGE');
l_error_count:=0;
FOR rc_crm_intfc in CRM_INTFC_ERR_NEW LOOP
l_error_count:=l_error_count + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_error_count||'~'||rc_crm_intfc.DOCUMENT_NUMBER||'~'||rc_crm_intfc.CUSTOMER_ASSIGNED||'~'||rc_crm_intfc.CUSTOMER_ID||'~'||rc_crm_intfc.SERVICE_TYPE||'~'||rc_crm_intfc.ERROR_MESSAGE);
UPDATE XXSKM_CRM_REVENUE
SET INTFC_STATUS = 'ERROR'
WHERE document_number = rc_crm_intfc.document_number;
END LOOP;
END IF;
COMMIT;
v_request_id :=
fnd_request.submit_request
('AR','RAXTRX',NULL,NULL,FALSE,'MAIN','T',l_batch_source_id,
'CRM',SYSDATE,'','','','','','','','','','','','',
'','','','','','','','','N','Y','','101');
END IF;
/*select BATCH_SOURCE_ID
INTO l_batch_source_id
from RA_BATCH_SOURCES_ALL
where name = 'CRM';*/
END IF;
END MAIN_PROC;
END XXSKM_CRM_REV_INTFC;
/
No comments:
Post a Comment