AMRESH KUMARAN ORACLE APPLICATIONS: July 2015

Tuesday, 21 July 2015

PO without PR not allowed

Trigger Event : WHEN-VALIDATE-RECORD
Triggr object : PO_HEADERS
Condition :
(:PO_HEADERS.DOC_TYPE_NAME ='Standard Purchase Order'
and ${item.po_headers.po_header_id.value} is null
and ((:system.block_status = 'NEW' AND :system.record_status = 'NEW')
OR (:system.block_status<> 'QUERY' AND :system.record_status <>'INSERT')
OR (:system.block_status<> 'QUERY' AND :system.record_status <> 'CHANGED'))
)

Actions:
Seq : 10
Type = Message
Message Type = Error
Message Text = 'PO without PR not allowed'

Seq : 20
Type = BuiltIn
Builtin Type = RAISE FORM_TRIGGER_FAILURE

Thursday, 9 July 2015

Intransit Query

SELECT
ABC.SEGMENT1,
ABC.FROM_ORG FROM_ORG,
ABC.TO_ORG TO_ORG,
BCD.INVOICE_NO INVOICE_NO,
BCD.INVOICE_DATE INVOICE_DATE,
BCD.ORDERED_DATE,
ABC.ITEM_CODE ITEM_CODE,
ABC.ITEM_NAME ITEM_NAME,
ABC.ITEM_CAT ITEM_CAT,
ABC.SEGMENT1 REQ_NO,
ABC.QTY_RECEIVED QTY,
BCD.UNIT_PRICE UNIT_PRICE,
ABC.QTY_RECEIVED*BCD.UNIT_PRICE BASIC,
BCD.TAX TAX,
(ABC.QTY_RECEIVED*BCD.UNIT_PRICE)+BCD.TAX TOTAL,
BCD.ORDER_NUMBER,
BCD.LINE_ID,
ABC.OU_NAME,
abc.prod_cat,
abc.org_id,
abc.FROM_ORG_ID,
abc.TO_ORG_ID,
abc.item_id,
abc.category_id,
abc.need_by_date,
abc.shipment_header_id,
abc.shipment_line_id
FROM
(SELECT
PRHA.SEGMENT1 SEGMENT1,
RSL.ITEM_ID ITEM_ID,
RSL.FROM_ORGANIZATION_ID FROM_ORG_ID,
RSL.TO_ORGANIZATION_ID TO_ORG_ID,
HLA1.LOCATION_CODE FROM_ORG,
HLA2.LOCATION_CODE TO_ORG,
RTRIM(LTRIM(msi.segment1||'-'||msi.segment2||'-'||msi.segment3||'-'||msi.segment4||'-'||msi.segment5||'-'||msi.segment6||'-'||msi.segment7||'-'||msi.segment8||'-'||msi.segment9)) ITEM_CODE,
MSI.DESCRIPTION ITEM_NAME,
(SELECT X.DESCRIPTION
FROM MTL_ITEM_CATEGORIES Z,
MTL_CATEGORIES_tl X
WHERE Z.CATEGORY_ID=X.CATEGORY_ID
AND Z.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND Z.ORGANIZATION_ID=(SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS Y
WHERE Y.ORGANIZATION_CODE='001')) ITEM_CAT,
SUM(RSL.QUANTITY_SHIPPED - RSL.QUANTITY_RECEIVED) QTY_RECEIVED,
(SELECT name FROM hr_operating_units hou WHERE hou.organization_id = prla.org_id) ou_name,
(SELECT ffv.description
FROM FND_FLEX_VALUES_VL FFV
WHERE flex_value_set_id = 1014898
AND flex_value = msi.SEGMENT1) prod_cat,
prla.org_id,
rsl.category_id ,
prla.need_by_date,
rsl.shipment_header_id,
rsl.shipment_line_id
FROM
RCV_SHIPMENT_LINES RSL,
MTL_SYSTEM_ITEMS_B MSI,
HR_LOCATIONS_ALL HLA1,
HR_LOCATIONS_ALL HLA2,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQUISITION_HEADERS_ALL PRHA
WHERE
NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED')!= 'FULLY RECEIVED'
AND RSL.SOURCE_DOCUMENT_CODE = 'REQ'
AND RSL.ITEM_ID = MSI.INVENTORY_ITEM_ID AND
RSL.FROM_ORGANIZATION_ID = MSI.ORGANIZATION_ID AND
RSL.FROM_ORGANIZATION_ID = HLA1.INVENTORY_ORGANIZATION_ID AND
RSL.TO_ORGANIZATION_ID = HLA2.INVENTORY_ORGANIZATION_ID AND
RSL.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID AND
PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
AND RSL.SHIP_TO_LOCATION_ID = HLA2.LOCATION_ID
AND HLA1.INACTIVE_DATE IS NULL ----------------------Added by Raju(HCL) on 25-OCT-2013
AND HLA2.INACTIVE_DATE IS NULL ----------------------Added by Raju(HCL) on 25-OCT-2013
and HLA1.ship_to_site_flag = 'Y'
and HLA2.ship_to_site_flag = 'Y'
and hla1.OBJECT_VERSION_NUMBER not like '3'
GROUP BY
RSL.ITEM_ID,
PRHA.SEGMENT1,
RSL.FROM_ORGANIZATION_ID,
RSL.TO_ORGANIZATION_ID,
HLA1.LOCATION_CODE,
HLA2.LOCATION_CODE,
msi.segment1||'-'||msi.segment2||'-'||msi.segment3||'-'||msi.segment4||'-'||msi.segment5||'-'||msi.segment6||'-'||msi.segment7||'-'||msi.segment8||'-'||msi.segment9,
MSI.DESCRIPTION,
rsl.category_id,
MSI.INVENTORY_ITEM_ID,
prla.org_id,
msi.SEGMENT1,
prla.need_by_date,
rsl.shipment_header_id,
rsl.shipment_line_id
ORDER BY 2,3,4,5) ABC,
(SELECT OOHA.ORIG_SYS_DOCUMENT_REF SEGMENT1,
OOHA.ORDER_NUMBER,
OOHA.ORDERED_DATE,
OOLA.LINE_ID,
JISPL.INVENTORY_ITEM_ID ITEM_ID,
JISPL.VAT_INVOICE_NO INVOICE_NO,
TO_CHAR(JISPL.VAT_INVOICE_DATE,'DD-MON-RRRR') INVOICE_DATE,
JISPL.SELLING_PRICE UNIT_PRICE,
SUM(NVL(JISPL.TAX_AMOUNT, 0)) TAX
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
JAI_OM_WSH_LINES_ALL JISPL
WHERE
OOHA.HEADER_ID = JISPL.ORDER_HEADER_ID
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND OOLA.LINE_ID = JISPL.ORDER_LINE_ID and JISPL.SPLIT_FROM_DELIVERY_DETAIL_ID is null
GROUP BY
OOHA.ORIG_SYS_DOCUMENT_REF,
OOHA.ORDER_NUMBER,
OOHA.ORDERED_DATE,
OOLA.LINE_ID,
JISPL.INVENTORY_ITEM_ID,
JISPL.VAT_INVOICE_NO,
JISPL.VAT_INVOICE_DATE,
JISPL.SELLING_PRICE) BCD
WHERE
ABC.SEGMENT1 = BCD.SEGMENT1 AND
ABC.ITEM_ID = BCD.ITEM_ID AND
-- BCD.INVOICE_NO IS NOT NULL AND
-- BCD.INVOICE_DATE <= :P_ST_DATE AND
BCD.ORDERED_DATE <= :P_ST_DATE AND
abc.org_id = nvl(:p_org_id,abc.org_id) AND
abc.FROM_ORG_ID = nvl(:P_FROM_ORG_ID,abc.FROM_ORG_ID) AND
abc.TO_ORG_ID = nvl(:P_TO_ORG_ID,abc.TO_ORG_ID) AND
abc.item_id = nvl(:P_item_id,abc.item_id) AND
abc.category_id = nvl(:P_category_id,abc.category_id)
-- and BCD.INVOICE_NO NOT IN ( SELECT attribute1 FROM Intransit_Temp)
ORDER BY 2,3,4;

Wednesday, 8 July 2015

AR TO XLA

SCRIPTS IS TO DELETE DATA FROM AR TO XLA

/* Formatted on 2015/07/08 15:58 (Formatter Plus v4.8.8) */
/*============================================================================+
$Header: fix_del_trx_r12.sql 120.0 2013/04/05 11:12:11 kyennawa noship $
============================================================================+*/
REM +================================================================================+
REM | |
REM | # Bug No : 9692558 |
REM | |
REM | # RCA Bug : xxxxxxx |
REM | |
REM | # Issue : gsi: fr (org id -38). provide script for deleting a invoice |
REM | |
REM | |
REM | # Symptoms : Need to delete invalid trx. |
REM | Trx is unposted and applications against are also unposted |
REM | |
REM | # Fix Approach : Delete trx related tables. |
REM | |
REM | # Usage : Supply the following when prompted: |
REM | 1) Bug_Number : Bug_Number |
REM | 2) org_id : Organization Id |
REM | 3) Read_Only_Mode : Y - TO show the corruption. |
REM | N - TO show and fix the corruption. |
REM | 4) Cust_Trx_Id : 0 - For all receipts |
REM | or a specific receipt_id. |
REM | |
REM *================================================================================*/
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus &phase=sql \
REM dbdrv: checkfile:~PROD:~PATH:~FILE \
REM dbdrv: &un_ar

SPOOL fix_del_trx_r12.lst
SET serveroutput on size 1000000;
SET linesize 400
SET echo off

DECLARE
l_bug_number NUMBER := &bug_number;
l_org_id NUMBER := ('&org_id');
l_read_only_mode VARCHAR2 (1) := UPPER ('&read_only_mode');
l_trx_id NUMBER := ('&cust_trx_id');
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER;
l_min_ra_id NUMBER;
l_posted_ra_id ar_receivable_applications_all.receivable_application_id%TYPE;

CURSOR cur_trx
IS
SELECT DISTINCT ct.customer_trx_id, ps.CLASS
FROM ra_customer_trx ct, ar_payment_schedules ps
WHERE ct.customer_trx_id = ps.customer_trx_id
AND ps.customer_trx_id = l_trx_id
AND ps.CLASS IN ('INV', 'CM', 'DM')
AND NOT EXISTS (
SELECT 1
FROM ra_cust_trx_line_gl_dist gld
WHERE gld.customer_trx_id = ct.customer_trx_id
AND gld.posting_control_id <> -3
AND gld.account_set_flag = 'N')
AND NOT EXISTS (
SELECT 1
FROM ar_receivable_applications ra
WHERE ra.customer_trx_id = ct.customer_trx_id
AND ra.posting_control_id <> -3)
AND NOT EXISTS (
SELECT 1
FROM ar_receivable_applications ra
WHERE ra.customer_trx_id = ct.customer_trx_id
AND ra.posting_control_id <> -3)
ORDER BY ct.customer_trx_id;

CURSOR ard_cur (p_trx_id IN NUMBER)
IS
SELECT line_id, source_id, source_table
FROM ar_distributions
WHERE source_table = 'RA'
AND source_id IN (
SELECT receivable_application_id
FROM ar_receivable_applications
WHERE customer_trx_id = p_trx_id
UNION
SELECT receivable_application_id
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_trx_id)
UNION
SELECT line_id, source_id, source_table
FROM ar_distributions
WHERE source_table = 'RA'
AND source_table_secondary = 'RA'
AND source_id_secondary IN (SELECT receivable_application_id
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_trx_id);

CURSOR ra_cur (p_trx_id NUMBER)
IS
SELECT *
FROM ar_receivable_applications
WHERE customer_trx_id = p_trx_id AND application_type = 'CM'
UNION
SELECT *
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_trx_id AND application_type = 'CM';

/* Print message on screen */
PROCEDURE DEBUG (s VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line (s);
END DEBUG;

/* Print spaces */
FUNCTION print_spaces (n IN NUMBER)
RETURN VARCHAR2
IS
l_return_string VARCHAR2 (100);
BEGIN
SELECT SUBSTR (' ',
1,
n
)
INTO l_return_string
FROM DUAL;

RETURN (l_return_string);
END print_spaces;

/* PS */
/* Backup table ps*/
PROCEDURE bkp_ps
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table ps_bkp_'
|| l_bug_number
|| ' as
select * from ar_payment_schedules_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_ps;

PROCEDURE insert_into_bkp_ps (p_cm_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into ps_bkp_'
|| l_bug_number
|| '( select * from ar_payment_schedules_all
where customer_trx_id = '
|| p_cm_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* ARD */

/* Backup table ard*/
PROCEDURE bkp_ard
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table ard_bkp_'
|| l_bug_number
|| ' as
select * from ar_distributions_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_ard;

PROCEDURE insert_into_bkp_ard (p_line_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into ard_bkp_'
|| l_bug_number
|| '( select * from ar_distributions_all
where line_id = '
|| p_line_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* RA */
/* Backup table ra*/
PROCEDURE bkp_ra
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table ra_bkp_'
|| l_bug_number
|| ' as
select * from ar_receivable_applications_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_ra;

/* Insert into backup ra*/
PROCEDURE insert_into_bkp_ra (p_ra_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into ra_bkp_'
|| l_bug_number
|| '( select * from ar_receivable_applications_all
where receivable_application_id = '
|| p_ra_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* GLD */
/* Backup table gld*/
PROCEDURE bkp_gld
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table gld_bkp_'
|| l_bug_number
|| ' as
select * from ra_cust_trx_line_gl_dist_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_gld;

PROCEDURE insert_into_bkp_gld (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into gld_bkp_'
|| l_bug_number
|| '( select * from ra_cust_trx_line_gl_dist_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* SALESREP */
/* Backup table salesrep*/
PROCEDURE bkp_salesreps
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table salesrep_bkp_'
|| l_bug_number
|| ' as
select * from ra_cust_trx_line_salesreps_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_salesreps;

PROCEDURE insert_into_bkp_salesrep (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into salesrep_bkp_'
|| l_bug_number
|| '( select * from ra_cust_trx_line_salesreps_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* LINES */
/* Backup table lines*/
PROCEDURE bkp_lines
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table lines_bkp_'
|| l_bug_number
|| ' as
select * from ra_customer_trx_lines_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_lines;

PROCEDURE insert_into_bkp_lines (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into lines_bkp_'
|| l_bug_number
|| '( select * from ra_customer_trx_lines_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* TRX */
/* Backup table cm trx*/
PROCEDURE bkp_trx
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table trx_bkp_'
|| l_bug_number
|| ' as
select * from ra_customer_trx_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_trx;

PROCEDURE insert_into_bkp_trx (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into trx_bkp_'
|| l_bug_number
|| '( select * from ra_customer_trx_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

----------------------------
----------------------------
/* XAH */
/* Backup table xah*/
PROCEDURE bkp_xah
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table xah_bkp_'
|| l_bug_number
|| ' as
select * from xla_ae_headers
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_xah;

PROCEDURE insert_into_bkp_xah (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into xah_bkp_'
|| l_bug_number
|| '( select * from xla_ae_headers xe
WHERE xe.application_id = 222
AND xe.entity_id IN ( SELECT xte.entity_id
FROM XLA.XLA_TRANSACTION_ENTITIES xte
WHERE xte.application_id = 222 AND xte.entity_code = ''TRANSACTIONS''
AND xte.source_id_int_1 = '
|| p_key_id
|| '))';

--debug(l_insert);
EXECUTE IMMEDIATE l_insert;
END;

/* XE */
/* Backup table xe*/
PROCEDURE bkp_xe
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table xe_bkp_'
|| l_bug_number
|| ' as
select * from xla_events
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_xe;

PROCEDURE insert_into_bkp_xe (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into xe_bkp_'
|| l_bug_number
|| '( select * from xla_events xe
WHERE xe.application_id = 222
AND xe.entity_id IN ( SELECT xte.entity_id
FROM XLA.XLA_TRANSACTION_ENTITIES xte
WHERE xte.application_id = 222 AND xte.entity_code = ''TRANSACTIONS''
AND xte.source_id_int_1 = '
|| p_key_id
|| '))';

--debug(l_insert);
EXECUTE IMMEDIATE l_insert;
END;

/* XTE */
/* Backup table cr*/
PROCEDURE bkp_xte
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table xte_bkp_'
|| l_bug_number
|| ' as
select * from XLA_TRANSACTION_ENTITIES
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_xte;

PROCEDURE insert_into_bkp_xte (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into xte_bkp_'
|| l_bug_number
|| '( select * from XLA_TRANSACTION_ENTITIES xte
WHERE xte.application_id = 222
AND xte.entity_code = ''TRANSACTIONS''
AND xte.source_id_int_1 = '
|| p_key_id
|| ')';

--debug(l_insert);
EXECUTE IMMEDIATE l_insert;
END;

-----------------Tax-------------------------

/* ZL */
PROCEDURE bkp_zl
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table zl_bkp_'
|| l_bug_number
|| ' as
select * from zx_lines
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_zl;

PROCEDURE insert_into_bkp_zl (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into zl_bkp_'
|| l_bug_number
|| '( select * from zx_lines zl
where zl.application_id = 222
AND zl.entity_code = ''TRANSACTIONS''
AND zl.event_class_code in (''INVOICE'',''DEBIT_MEMO'',''CREDIT_MEMO'')
AND zl.trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* ZLD */
PROCEDURE bkp_zld
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table zld_bkp_'
|| l_bug_number
|| ' as
select * from ZX_LINES_DET_FACTORS
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_zld;

PROCEDURE insert_into_bkp_zld (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into zld_bkp_'
|| l_bug_number
|| '( select * from ZX_LINES_DET_FACTORS zld
where zld.application_id = 222
AND zld.entity_code = ''TRANSACTIONS''
AND zld.event_class_code in (''INVOICE'',''DEBIT_MEMO'',''CREDIT_MEMO'')
AND zld.trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;
BEGIN
mo_global.init ('AR');
mo_global.set_policy_context ('S', l_org_id);

IF NVL (UPPER (l_read_only_mode), 'Y') = 'N'
THEN
bkp_trx;
bkp_lines;
bkp_salesreps;
bkp_gld;
bkp_ra;
bkp_ard;
bkp_ps;
bkp_xte;
bkp_xe;
bkp_xah;
bkp_zl;
bkp_zld;
END IF;

DEBUG ('CUST_TRX_ID TRX_TYPE ');
DEBUG ('=============== =============== ');

FOR c2 IN cur_trx
LOOP
--debug('11');
DEBUG ( c2.customer_trx_id
|| print_spaces (16 - LENGTH (c2.customer_trx_id))
|| c2.CLASS
);

IF NVL (UPPER (l_read_only_mode), 'Y') = 'N'
THEN
/* PS */
insert_into_bkp_ps (c2.customer_trx_id);

DELETE FROM ar_payment_schedules
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('');

/* ARD */
FOR ard IN ard_cur (c2.customer_trx_id)
LOOP
insert_into_bkp_ard (ard.line_id);

DELETE FROM ar_distributions
WHERE line_id = ard.line_id;

DEBUG ('');
END LOOP;

/* RA */
FOR ra IN ra_cur (c2.customer_trx_id)
LOOP
insert_into_bkp_ra (ra.receivable_application_id);

DELETE FROM ar_receivable_applications
WHERE receivable_application_id =
ra.receivable_application_id;

DEBUG ('');
END LOOP;

/* GLD */
insert_into_bkp_gld (c2.customer_trx_id);
arp_global.g_allow_datafix := TRUE;

DELETE FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('');
arp_global.g_allow_datafix := FALSE;
/* SALESREP */
insert_into_bkp_salesrep (c2.customer_trx_id);

DELETE FROM ra_cust_trx_line_salesreps
WHERE customer_trx_id = c2.customer_trx_id;

/* Tax */
insert_into_bkp_zl (c2.customer_trx_id);

DELETE FROM zx_lines zl
WHERE zl.application_id = 222
AND zl.entity_code = 'TRANSACTIONS'
AND zl.event_class_code IN
('INVOICE', 'DEBIT_MEMO', 'CREDIT_MEMO')
AND zl.trx_id = c2.customer_trx_id;

insert_into_bkp_zld (c2.customer_trx_id);

DELETE FROM zx_lines_det_factors zld
WHERE zld.application_id = 222
AND zld.entity_code = 'TRANSACTIONS'
AND zld.event_class_code IN
('INVOICE', 'DEBIT_MEMO', 'CREDIT_MEMO')
AND zld.trx_id = c2.customer_trx_id;

DEBUG ('Deleted Tax info ...');
/* LINES */
insert_into_bkp_lines (c2.customer_trx_id);

DELETE FROM ra_customer_trx_lines
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('');
/* TRX */
insert_into_bkp_trx (c2.customer_trx_id);

DELETE FROM ra_customer_trx
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('Deleted data for TRX ' || c2.customer_trx_id);
insert_into_bkp_xah (c2.customer_trx_id);

DELETE FROM xla_ae_headers xe
WHERE xe.application_id = 222
AND xe.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = c2.customer_trx_id);

insert_into_bkp_xe (c2.customer_trx_id);

DELETE FROM xla_events xe
WHERE xe.application_id = 222
AND xe.event_status_code <> 'P'
AND xe.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = c2.customer_trx_id);

insert_into_bkp_xte (c2.customer_trx_id);

DELETE FROM xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = c2.customer_trx_id;

DEBUG ('Deleted xla and event info ...');
END IF;

DEBUG ('.............................. ');
END LOOP;

--debug('============================== ');
IF NVL (UPPER (l_read_only_mode), 'Y') = 'N'
THEN
DEBUG ('============================== ');
DEBUG
('Please, review the data and issue ''commit'' to make the changes permanent.'
);
--debug('Run ''invoice_interface_line_bulk.sql'' for reinserting data into interface tables.');
--debug('Then run Autoinvoice Import Program to reimport the CMs in AR.');
END IF;
EXCEPTION
WHEN OTHERS
THEN
arp_global.g_allow_datafix := FALSE;
ROLLBACK;
RAISE;

END;
/

SPOOL off;

Thursday, 2 July 2015

Links PO To GL in R12

select * from po_requisition_headers_all prha -- prha.REQUISITION_HEADER_ID

select * from po_requisition_lines_all prla -- prha.REQUISITION_HEADER_ID , prla.REQUISITION_LINE_ID

select * from po_req_distributions_all prda -- prha.REQUISITION_LINE_ID , prla.REQUISITION_HEADER_ID , prda.DISTRIBUTION_ID`

select * from po_headers_all pha -- pha.header_id

select * from po_lines_all pla -- pha.header_id , pla.line_id

select * from po_distributions_all pda -- pah.header_id , pla.line_id , (pda.po_distribution_id links prda.DISTRIBUTION_ID)

select * from rcv_shipment_headers rsh -- rsh.SHIPMENT_HEADER_ID

select * from rcv_shipment_lines rsl -- rsl.SHIPMENT_line_ID , rsl.SHIPMENT_HEADER_ID , rsl.PO_HEADER_ID , rsl.PO_LINE_ID , rsl.REQUISITION_LINE_ID

select * from rcv_transactions rt -- rt.SHIPMENT_line_ID , rt.SHIPMENT_HEADER_ID links (xte.SOURCE_ID_INT_1 = rt.transaction_id)

select * from ap_invoices_all aia -- aia.invoice_id , aia.ACCTS_PAY_CODE_COMBINATION_ID , aia.SOURCE_ID_INT_1

select * from ap_invoice_lines_all aila -- aila.invoice_id

select * from ap_invoice_distributions_all aida -- aida.invoice_id , aida.DIST_CODE_COMBINATION_ID , aida.distribution_id links (aida.RCV_TRANSACTION_ID = rt.transaction_id)

select * from ap_invoice_payments_all aipa -- aipa.invoice_id , aipa.check_id , aipa.INVOICE_PAYMENT_ID

select * from ap_payment_schedules_all apsa -- apsa.invoice_id

select * from ap_checks_all aca -- aca.check_id , aca.SOURCE_ID_INT_1 links (xte.SOURCE_ID_INT_1)

select * from ap_suppliers as -- xal.PARTY_ID = as.VENDOR_ID , aia.vendor_id

select * from xla.xla_transaction_entities xte -- xte.SOURCE_ID_INT_1 link (aia.nvoice_id , aca.check_id, rt.transaction_id)

select * from xla_events xe -- xe.EVENT_ID , xe.ENTITY_ID

select * from xla_ae_headers xah -- xah.ENTITY_ID , xah.EVENT_ID , xah.AE_HEADER_ID

select * from xla_ae_lines xal -- xal.AE_HEADER_ID , xal.GL_SL_LINK_TABLE , xal.GL_SL_LINK_ID

select from xla_distribution_links xdl -- xdl.AE_HEADER_ID , xdl.EVENT_ID , xdl.source_distribution_id_num_1

select * from gl_je_headers gjh -- gjh.JE_HEADER_ID , gjh.je_batch_id , gjh.je_source , gjh.je_category_name

select * from gl_je_lines gjl -- gjl.JE_HEADER_ID , gjl.CODE_COMBINATION_ID , jgj.le_line_num

select * from gl_code_combinations gcc -- gcc.CODE_COMBINATION_ID (aida.DIST_CODE_COMBINATION_ID ,aia.ACCTS_PAY_CODE_COMBINATION_ID)

select * from gl_je_batches gjb -- gjb.je_batch_id links (gjh.je_batch_id )

select * from gl_je_sources gjs -- je_source_name links (gjh.je_source)

select * from gl_je_categories gjc -- je_category_name links (gjh.je_category_name)

select * from gl_import_references gir -- je_header_id , je_line_num , GL_SL_LINK_ID , GL_SL_LINK_TABLE links (xal.GL_SL_LINK_TABLE , xal.GL_SL_LINK_ID)

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