AMRESH KUMARAN ORACLE APPLICATIONS

Wednesday, 1 July 2015

AR Data Loading in the Receivables

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

Purchase Requisition Data Loading

CREATE TABLE APPS.XXSKM_PR_UPLOAD
(
ITEM_CODE VARCHAR2(50 BYTE),
NET_REQ NUMBER(10),
LIST_PRICE_PER_UNIT NUMBER(10),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(50 BYTE),
PREPARER VARCHAR2(50 BYTE),
REQUESTOR VARCHAR2(50 BYTE),
DESTINATION_ORG VARCHAR2(50 BYTE),
DELIVER_TO_LOCATION VARCHAR2(50 BYTE),
NEED_BY_DATE DATE,
CHARGE_ACCOUNTS_ID VARCHAR2(50 BYTE),
PROCESS_FLAG VARCHAR2(2 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATED_BY NUMBER,
CREATION_DATE DATE DEFAULT SYSDATE,
REQUEST_ID NUMBER
) -- this custom table

CREATE OR REPLACE PACKAGE APPS.xxskm_pr_upload_pkg
AS
--
--------------------------------------------------------------------------------

-- Program Type : Package Body
-- Conc Prog Short Name : xxskm_pr_upload_pkg
--
-- Description:
--
-- Modification History:
-- --------- ----------- ----------------------------------------------------
-- Date Author Comments
-- --------- ----------- ----------------------------------------------------
-- 21-Jul-2014 Amresh Initial Creation
--
--------------------------------------------------------------------------------
--
--
PROCEDURE main ( p_errbuf_out OUT VARCHAR2
, p_retcod_out OUT VARCHAR2
);
--
END xxskm_pr_upload_pkg;

execution file name in xxskm_pr_upload_pkg.main

================================================================
================================================================

CREATE OR REPLACE PACKAGE BODY APPS.xxskm_pr_upload_pkg
AS
--
--------------------------------------------------------------------------------
-- Owner : HCL Infosystems Ltd
-- Project : Su-Kam
-- Program Type : Package Body
-- Conc Prog Short Name : xxskm_pr_upload_pkg
--
-- Description:
--
-- Modification History:
-- --------- ----------- ----------------------------------------------------
-- Date Author Comments
-- --------- ----------- ----------------------------------------------------
-- 21-Jul-2014 Subir Initial Creation
--
--------------------------------------------------------------------------------
--
pv_conc_req_id NUMBER;
pv_request_id NUMBER := fnd_global.conc_request_id;
pv_user_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('USER_ID'), '1');
--pv_resp_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('RESP_ID'), '1');
--pv_resp_appl_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('RESP_APPL_ID'), '1');
pv_org_id NUMBER := 101;
--
PROCEDURE xxfhl_print_audit_details (p_req_id NUMBER);
--
PROCEDURE main ( p_errbuf_out OUT VARCHAR2
, p_retcod_out OUT VARCHAR2
)
IS
--
v_item_id number;
v_uom varchar2(10);
v_net_req number;
v_list_price number;
v_date date;
v_phase VARCHAR2(200) :='';
v_status VARCHAR2(200) :='';
v_dev_phase VARCHAR2(200) :='';
v_dev_status VARCHAR2(200) :='';
v_message VARCHAR2(200) :='';
v_return BOOLEAN;
--
cursor cur_data is
select a.rowid
, a.item_code
, a.net_req
, a.list_price_per_unit
, a.primary_unit_of_measure
, a.preparer
, a.requestor
, a.destination_org
, a.deliver_to_location
, a.need_by_date
, a.charge_accounts_id
, a.process_flag
, a.last_update_date
, a.last_updated_by
, a.created_by
, a.creation_date
from xxskm_pr_upload a
where a.item_code not like 'W%'
and nvl(a.process_flag,'N') = 'N'
and request_id = pv_request_id
;
--
begin
--
fnd_file.put_line (fnd_file.log,'118');
fnd_file.put_line (fnd_file.log,'delete old records from po_requisitions_interface_all');
--
delete from po_requisitions_interface_all
where created_by = pv_user_id;
--
fnd_file.put_line (fnd_file.log,'Update need_by_date of custom table');
--
update xxskm_pr_upload a
set a.need_by_date = trunc(sysdate) + 3
, a.created_by = pv_user_id
, a.creation_date= SYSDATE
, request_id = pv_request_id
where nvl(a.process_flag,'N') = 'N' ;
--
for c1 in cur_data loop
--
fnd_file.put_line (fnd_file.log,'119');
v_net_req := c1.net_req;
--
fnd_file.put_line (fnd_file.log,'v_net_req'||'--'||v_net_req);
fnd_file.put_line (fnd_file.log,'v_list_price'||'--'||v_list_price);
--
v_date := c1.need_by_date;
--
begin
--
SELECT msi.inventory_item_id
, msi.primary_unit_of_measure
, nvl(msi.list_price_per_unit,1)
INTO v_item_id
, v_uom
, v_list_price
FROM mtl_system_items_kfv msi
WHERE msi.concatenated_segments = c1.item_code
AND organization_id = 106 -- 001
;
--
exception
when others then
--
fnd_file.put_line (fnd_file.log,'Select Statement'||' '||c1.item_code);
--
end;
--
fnd_file.put_line (fnd_file.log,'122');
--
INSERT INTO po_requisitions_interface_all
(interface_source_code
,batch_id
,transaction_id
,org_id
,destination_type_code
,authorization_status
,preparer_id
,source_type_code
,unit_of_measure
,line_type_id
,item_id
,unit_price
,quantity
,destination_organization_id
,deliver_to_location_id
,deliver_to_requestor_id
,multi_distributions
,req_dist_sequence_id
,interface_source_line_id
,charge_account_id
,need_by_date
,destination_subinventory
,creation_date
,created_by
)
values ( 'EXTERNAL' -- interface_source_code
, NULL -- batch_id
, NULL -- transaction_id
, 101 -- org_id
, 'INVENTORY' -- destination_type_code
, 'INCOMPLETE' -- authorization_status
, 7329 -- preparer_id
, 'VENDOR' -- source_type_code
, v_uom -- unit_of_measure
, 1020 -- line_type_id
, v_item_id -- item_id
, v_list_price -- unit_price
, v_net_req -- quantity
, 108 -- destination_organization_id
, 166 -- deliver_to_location_id
, 7329 -- deliver_to_requestor_id
, null -- multi_distributions
, null -- req_dist_sequence_id
, null -- interface_source_line_id
, 1551 -- charge_account_id
, v_date -- need_by_date
, 'RMS' -- destination_subinventory
, sysdate -- creation_date
, pv_user_id -- created_by
);
commit;
--
update xxskm_pr_upload a
set process_flag = 'P'
, last_update_date = SYSDATE
, last_updated_by = pv_user_id
where rowid = c1.rowid
;
--
EXIT WHEN cur_data%NOTFOUND;
fnd_file.put_line (fnd_file.log,'123');
--
end loop;
--
--------------------------
-- Run Requisition Import
--------------------------
fnd_file.put_line (fnd_file.log,' Submiting the request for Requisition Import ');
--
fnd_request.set_org_id(pv_org_id);
--
pv_conc_req_id := fnd_request.submit_request(Application => 'PO'
,Program => 'REQIMPORT'
,Description => NULL
,Start_Time => NULL
,Sub_Request => FALSE
,Argument1 => NULL
,Argument2 => NULL
,Argument3 => 'VENDOR'
,Argument4 => NULL
,Argument5 => 'N'
,Argument6 => 'N' );
COMMIT;
--
fnd_file.put_line (fnd_file.log,' Waiting for completion of Requisition Import ');
--
-- Calling function fnd_concurrent.wait_for_request so that interface gets completed
-- when the submitted request is completed.
v_return := fnd_concurrent.wait_for_request(pv_conc_req_id
,10
,0
,v_phase
,v_status
,v_dev_phase
,v_dev_status
,v_message
);
--
---------------------------
-- Output detail procedure
---------------------------
fnd_file.put_line (fnd_file.log,'Print Audit Details Start Date Time :- '|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
fnd_file.put_line (fnd_file.log, '');
--
xxfhl_print_audit_details (p_req_id => pv_conc_req_id);
--
fnd_file.put_line (fnd_file.log,'Print Audit Details End Date Time :- '|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
fnd_file.put_line (fnd_file.log, '');
--

exception
when others then
--
fnd_file.put_line (fnd_file.log,'Records are not uploaded');
--
end main;
--
--
--------------------------------------------------------------------------------
-- Name : xxfhl_print_audit_details
-- Description:
-- This procedure prints audit details report
--
--
-- Parameters:
-- 1. p_req_id
--
-- Pseudo logic:
-- None
--------------------------------------------------------------------------------
--
PROCEDURE xxfhl_print_audit_details (p_req_id NUMBER)
IS
--
--
CURSOR cur_error_details
IS
SELECT NVL(TRIM(prh.segment1), ' ') pr_num
, prh.creation_date
, NVL(TRIM(msi.concatenated_segments), ' ') item_code
, NVL(TRIM(prl.unit_price), ' ') unit_price
, NVL(TRIM(prl.quantity), ' ') quantity
, NVL(TRIM(fu.user_name), ' ') user_name
, NULL error_message
, 'P' process_flag
FROM po_requisition_headers_all prh
, po_requisition_lines_all prl
, mtl_system_items_kfv msi
, fnd_user fu
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.item_id = msi.inventory_item_id
AND prl.destination_organization_id = msi.organization_id
AND trunc(prh.creation_date) = trunc(sysdate)
AND fu.user_id = prh.created_by
AND prh.request_id = p_req_id
--
UNION
--
SELECT null pr_num
, pri.creation_date
, NVL(TRIM(msi.concatenated_segments), ' ') item_code
, NVL(TRIM(pri.unit_price), ' ') unit_price
, NVL(TRIM(pri.quantity), ' ') quantity
, null user_name
, NVL(TRIM(pie.error_message), ' ') error_message
, 'E' process_flag
FROM po_requisitions_interface_all pri
, po_interface_errors pie
, mtl_system_items_kfv msi
, fnd_user fu
WHERE trunc(pri.need_by_date) = trunc(sysdate)
AND pie.interface_transaction_id = pri.transaction_id
AND pri.item_id = msi.inventory_item_id
AND pri.destination_organization_id = msi.organization_id
AND pri.request_id = p_req_id
ORDER BY 1
;
--
BEGIN
--
fnd_file.put_line(fnd_file.log,'xpad-001');
fnd_file.put_line(fnd_file.log,'xxfhl_print_audit_details' );
--
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
--
fnd_file.put_line(fnd_file.log, 'xpad-050');
fnd_file.put_line (fnd_file.output, '--------------Detail------------');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, 'Pr Number Item Code Unit Price Quantity Created By Creation Dt Error Desc');
fnd_file.put_line (fnd_file.output, '------------- ------------------------------ ---------------------- ---------------------- ---------------------- ----------- ----------------------');
--
FOR crec IN cur_error_details
LOOP
--
fnd_file.put_line (fnd_file.output
, RPAD (crec.pr_num, 13) ||' '||
RPAD (crec.item_code, 31) ||' '||
RPAD (crec.unit_price, 22) ||' '||
RPAD (crec.quantity, 22) ||' '||
RPAD (crec.user_name, 22) ||' '||
RPAD (crec.creation_date, 22) ||' '||
crec.error_message
);
--
IF crec.process_flag = 'E'
THEN
--
update xxskm_pr_upload a
set process_flag = 'E'
where item_code = crec.item_code
and net_req = crec.quantity
;
--
END IF;
--
END LOOP;
--
COMMIT;
--
fnd_file.put_line(fnd_file.log, 'xpad-110');
fnd_file.put_line(fnd_file.output, ' ' );
fnd_file.put_line(fnd_file.log, 'xpad-999');
--
EXCEPTION
--
WHEN OTHERS THEN
--
fnd_file.put_line(fnd_file.log, 'Error in := xxfhl_print_audit_details, '||sqlerrm );
--
END xxfhl_print_audit_details;
--
END xxskm_pr_upload_pkg;
/

Invoice TO Payments Based On Gl Code

/* Formatted on 2015/06/29 16:14 (Formatter Plus v4.8.8) */

(SELECT (CASE

WHEN acl.currency_code = 'USD'

THEN aia.exchange_rate * aipl.amount

WHEN acl.currency_code = 'EUR'

THEN aia.exchange_rate * aipl.amount

WHEN acl.currency_code = 'HKD'

THEN aia.exchange_rate * aipl.amount

WHEN acl.currency_code = 'AED'

THEN aia.exchange_rate * aipl.amount

ELSE aipl.amount

END

) payment_amount,aia.gl_date gl_date_invoice ,

aia.invoice_num, aia.invoice_amount , aia.invoice_id ,aia.invoice_date , aia.voucher_num, acl.check_number, acl.check_id,

asp.vendor_name supplier_name, asp.segment1 supplier_number,

aia.accts_pay_code_combination_id account_id, gk.segment3 segment3,aipl.accounting_date gl_date_payment,

acl.status_lookup_code

FROM ap_checks_all acl,

ap_invoice_payments_all aipl,

ap_invoices_all aia,

ap_suppliers asp,

gl_code_combinations gk

WHERE aipl.check_id = acl.check_id

AND aipl.invoice_id = aia.invoice_id

AND aia.vendor_id = asp.vendor_id(+)

AND aia.accts_pay_code_combination_id = gk.code_combination_id

AND aipl.accounting_date BETWEEN :p_from_date AND :p_to_date

AND gk.segment7 = 321002

AND gk.segment1 = 10

AND aipl.check_id IN ('243643'))

Thursday, 7 May 2015

Account Analysis Report Internal

SELECT Distinct JH.DESCRIPTION,
jl.description Line_Desc,
SUBSTR (jc.user_je_category_name, 1, 26) CATEGORY,
SUBSTR (jh.je_source, 1, 15) SOURCE,
TO_CHAR (jl.effective_date, 'DD-MON-YY') Gl_Date,
jh.NAME Journal_Name,
-- jh.je_batch_id batch,
SUBSTR (ds.NAME, 1, 13) lsequence,
jh.doc_sequence_value hnumber,
decode(xal.ACCOUNTED_DR,null,jl.accounted_dr,xal.ACCOUNTED_DR ) Acc_Dr ,
decode(xal.ACCOUNTED_CR,null,jl.accounted_Cr,xal.ACCOUNTED_CR ) Acc_Cr,
decode(xal.ENTERED_DR,null,jl.ENTERED_dr,xal.ENTERED_DR ) Ent_Dr,
decode(xal.ENTERED_CR,null,jl.ENTERED_Cr,xal.ENTERED_CR ) Ent_Cr,
jh.external_reference REFERENCE,
jl.period_name Period_name,
ir.subledger_doc_sequence_id seq_id,
ds.NAME Voucher_Category,
DECODE (ir.subledger_doc_sequence_value ,NULL,JH.DOC_SEQUENCE_VALUE,ir.subledger_doc_sequence_value ) Subled_Seq_No,
-- jh.doc_sequence_id h_seq_id,
DECODE (xah.DOC_SEQUENCE_VALUE,NULL,JH.DOC_SEQUENCE_VALUE,xah.DOC_SEQUENCE_VALUE) DOC_SEQUENCE_VALUE,
xal.PARTY_ID,
xal.code_combination_id,
gk.concatenated_segments Acct_Code,
gk.segment6 Natural_Acct,
gk.segment1,
gk.segment3,
gk.segment5,
gk.segment7,
decode(fu.DESCRIPTION,null ,fu.USER_NAME,fu.DESCRIPTION) user_name ,
apsu.VENDOR_NAME,
apsu.SEGMENT1 Vendor_code,
xal.CURRENCY_CODE Currency,
xal.CURRENCY_CONVERSION_RATE Rate,
arc.CUSTOMER_NAME,
arc.CUSTOMER_NUMBER,
xlate.SOURCE_ID_INT_1 Invoice_ID,
xlate.ENTITY_CODE,
xlate.TRANSACTION_NUMBER--, b.daily_balance
, ir.gl_sl_link_id
FROM gl_je_lines jl,
gl_je_headers jh,
gl_je_batches jb,
gl_je_sources js,
gl_je_categories jc,
gl_import_references ir,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
ap_suppliers apsu,
ar_customers arc,
fnd_document_sequences ds,
xla_events xlae,
xla_transaction_entities xlate,
fnd_user fu,
gl_code_combinations_kfv gk
WHERE jl.status = 'P'
-- AND jl.code_combination_id = :P_ACCT
and xal.code_combination_id= gk.code_combination_id
and jh.created_by=fu.user_id
AND jl.ledger_id = :p_set_of_bks_id
AND jh.status = 'P'
AND jh.actual_flag = 'A'
AND jh.je_header_id = jl.je_header_id
-- &C_WHERE_CURR
AND jb.je_batch_id = jh.je_batch_id
AND jb.average_journal_flag = 'N'
AND js.je_source_name = jh.je_source
AND jc.je_category_name = jh.je_category
AND ir.je_header_id(+) = jl.je_header_id
AND ir.je_line_num(+) = jl.je_line_num
AND ds.doc_sequence_id(+) = ir.subledger_doc_sequence_id
and XAH.AE_HEADER_ID(+)=XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID(+)=IR.GL_SL_LINK_ID
and arc.CUSTOMER_ID(+)=xal.PARTY_ID
AND XAL.GL_SL_LINK_TABLE(+)=IR.GL_SL_LINK_TABLE
and xah.EVENT_ID=xlae.EVENT_ID(+)
and xlae.ENTITY_ID=xlate.ENTITY_ID(+)
and apsu.VENDOR_ID(+)=xal.PARTY_ID
-- AND jl.period_name in(:P_START_PERIOD,:P_END_PERIOD)
and jh.DEFAULT_EFFECTIVE_DATE between :p_from_date and :p_to_date
and gk.segment1 = nvl(:P_Comp,gk.segment1)
and gk.segment3 = nvl(:P_Branch,gk.segment3)
and gk.segment5 = nvl(:P_Prod,gk.segment5)
and gk.segment7=nvl(:P_Acct,gk.segment7)
and gk.segment6= nvl(:P_Dept,gk.segment6)
--and jh.je_header_id=116193
-- &WHERE_FLEX
UNION ALL
select JH.DESCRIPTION,
jl.description Line_Desc,
SUBSTR (jc.user_je_category_name, 1, 26) CATEGORY,
SUBSTR (jh.je_source, 1, 15) SOURCE,
TO_CHAR (jl.effective_date, 'DD-MON-YY') Gl_Date,
jh.NAME Journal_Name,
-- jh.je_batch_id batch,
Null lsequence,
jh.doc_sequence_value hnumber,
jl.accounted_dr Acc_Dr ,
jl.accounted_Cr Acc_Cr,
jl.ENTERED_dr Ent_Dr,
jl.ENTERED_Cr Ent_Cr,
jh.external_reference REFERENCE,
jl.period_name Period_name,
jr.subledger_doc_sequence_id seq_id,
null Voucher_Category,
DECODE (jr.subledger_doc_sequence_value ,NULL,JH.DOC_SEQUENCE_VALUE,jr.subledger_doc_sequence_value ) Subled_Seq_No,
-- jh.doc_sequence_id h_seq_id,
JH.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
Null PARTY_ID,
jl.code_combination_id,
gk.concatenated_segments Acct_Code,
gk.segment6 Natural_Acct,
gk.segment1,
gk.segment3,
gk.segment5,
gk.segment7,
fu.description,
Null VENDOR_NAME,
null vendor_code,
jh.CURRENCY_CODE Currency,
jh.CURRENCY_CONVERSION_RATE Rate,
Null CUSTOMER_NAME,
null customer_number,
Null Invoice_ID,
Null ENTITY_CODE,
Null TRANSACTION_NUMBER,
jr.gl_sl_link_id
from gl_je_headers jh,
gl_je_lines jl,
gl_je_batches jb,
gl_je_sources js ,
gl_je_categories jc,
gl_import_references jr,
gl_code_combinations_kfv gk,
fnd_user fu
WHERE js.JE_SOURCE_NAME IN('Manual','Spreadsheet','Purchasing India')
and jl.JE_HEADER_ID=jh.JE_HEADER_ID
and jh.JE_BATCH_ID=jb.JE_BATCH_ID(+)
and jh.JE_SOURCE(+)=js.JE_SOURCE_NAME
and jh.JE_CATEGORY=jc.JE_CATEGORY_NAME(+)
and jr.JE_HEADER_ID(+)=jl.JE_HEADER_ID
and jr.JE_LINE_NUM(+)=jl.JE_LINE_NUM
and jl.CODE_COMBINATION_ID=gk.CODE_COMBINATION_ID
and jh.CREATED_BY=fu.USER_ID
--and jh.PERIOD_NAME in(:P_START_PERIOD,:P_END_PERIOD)
and jh.DEFAULT_EFFECTIVE_DATE between :p_from_date and :p_to_date
and jh.STATUS='P'
and jl.STATUS='P'
--and jh.ACTUAL_FLAG='Y'
AND jl.ledger_id = :p_set_of_bks_id
AND jb.average_journal_flag = 'N'
and gk.segment1 = nvl(:P_Comp,gk.segment1)
and gk.segment3 = nvl(:P_Branch,gk.segment3)
and gk.segment5 = nvl(:P_Prod,gk.segment5)
and gk.segment7=nvl(:P_Acct,gk.segment7)
and gk.segment6= nvl(:P_Dept,gk.segment6)
--and jh.je_header_id=116193
-- &WHERE_FLEX
ORDER BY 5

Saturday, 21 March 2015

Document Sequences Inventory Query

/* Formatted on 2015/03/21 15:01 (Formatter Plus v4.8.8) */
SELECT jrd.registration_num, jrd.effective_from, jrd.effective_to,
hao.NAME organization_name, hl.description location_name, jl.meaning organization_type,
(CASE
WHEN jrds.document_class = 'UD'
THEN 'Unregistered - Default'
WHEN jrds.document_class = 'D'
THEN 'Default'
WHEN jrds.document_class = 'O'
THEN 'Order'
END
) document_class, (case when ott.name is null THEN 'Default' else ott.name end) Document_type_name ,
jrds.start_number, jrds.current_number
FROM jai_rgm_doc_seq_hdrs jrd,
jai_rgm_doc_seq_dtls jrds,
hr_all_organization_units hao,
hr_locations hl ,
oe_transaction_types_tl ott ,
ja_lookups jl
WHERE jrd.registration_num = nvl(:P_registration_num ,jrd.registration_num ) --'06291922190'
AND TRUNC (jrd.effective_from) BETWEEN :p_from_date AND :p_to_date
AND jrds.rgm_document_seq_id = jrd.rgm_document_seq_id
AND hao.organization_id = jrd.party_id
AND hl.location_id = jrd.party_site_id
and ott.transaction_type_id(+) = jrds.DOCUMENT_CLASS_TYPE_ID
and jrd.ORGANIZATION_TYPE = jl.lookup_code