AMRESH KUMARAN ORACLE APPLICATIONS

Thursday, 28 January 2016

Autonomous Transactions

CREATE TABLE at_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.

SQL>

Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.

SQL>

As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

SQL>

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.

Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.
CREATE TABLE error_logs (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;

We define a procedure to log error messages as an autonomous transaction.
CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/

The following code forces an error, which is trapped and logged.
BEGIN
  INSERT INTO at_test (id, description)
  VALUES (998, 'Description for 998');

  -- Force invalid insert.
  INSERT INTO at_test (id, description)
  VALUES (999, NULL);
EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test WHERE id >= 998;

no rows selected

SELECT * FROM error_logs;

        ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
         1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")


1 row selected.

PL/SQL Table Using Bulk Collect

/* Formatted on 2016/01/28 14:22 (Formatter Plus v4.8.8) */
DECLARE
TYPE amresh IS TABLE OF emp_test%ROWTYPE;

objecttable amresh;

CURSOR cur
IS
SELECT *
FROM emp_test;
BEGIN
OPEN cur;

LOOP
FETCH cur
BULK COLLECT INTO objecttable;

FORALL i IN 1 .. objecttable.COUNT
INSERT INTO emp_test_t
VALUES objecttable (i);
EXIT WHEN cur%NOTFOUND;
END LOOP;

CLOSE cur;
END;

CREATE TABLE EMP_TEST
(
EMP_ID NUMBER,
ORG_CODE VARCHAR2(50 BYTE),
ITEM_CODE VARCHAR2(250 BYTE),
E_NUMBER VARCHAR2(100 BYTE)
)

CREATE TABLE EMP_TEST_T
(
EMP_ID NUMBER,
ORG_CODE VARCHAR2(50 BYTE),
ITEM_CODE VARCHAR2(250 BYTE),
E_NUMBER VARCHAR2(100 BYTE)
)

/* Formatted on 2016/01/28 14:22 (Formatter Plus v4.8.8) */
DECLARE
TYPE amresh IS TABLE OF emp_test%ROWTYPE; -- THIS IS CALLED PL/SQL TABLE - OBJECT OF TYPE IS KNOW AS PL/SQL TABLE

L_DATA amresh; ---- FROM TABLE IM GETTING DATA THROUGH AMRESH , FOR AMRESH IM DECLARING THE VARIABLE

CURSOR cur
IS
SELECT *
FROM emp_test;
BEGIN
OPEN cur;

LOOP
FETCH cur
BULK COLLECT INTO L_DATA;

FORALL i IN 1 .. L_DATA.COUNT
INSERT INTO emp_test_t
VALUES L_DATA (i);
EXIT WHEN cur%NOTFOUND;
END LOOP;

CLOSE cur;
END;

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