AR INVOICE INTERFACE

CREATE OR REPLACE PROCEDURE APPS.XX_AR_INV_INTERFACE_INSERTS
IS
    CURSOR CUR_INV_HEADER
    IS        
    SELECT *
    FROM xx_ar_inv_head_stg
    WHERE RECORD_TYPE='HEAD'
    AND process_flag='Y'
    --AND TRANSACTION_NO='XXDAKTRNNO1'
    ORDER BY TRANSACTION_NO;
   
    CURSOR CUR_INV_LINES(P_TRNS_NO varchar2)
    IS
    SELECT *
    FROM xx_ar_inv_line_stg
    WHERE RECORD_TYPE='LINE'
    AND TRANSACTION_NO=P_TRNS_NO;
   
    CURSOR CUR_INV_DIST(P_TRNS_NO varchar2,P_TRNS_LINE_NO NUMBER)
    IS
    SELECT *
    FROM xx_ar_inv_dist_stg1
    WHERE RECORD_TYPE='DIST'
    AND TRANSACTION_NO=P_TRNS_NO
    AND TRANSACTION_LINE_NO = P_TRNS_LINE_NO
    ORDER BY RATE_CENTER;  
   
    V_CUST_TRX_TYPE_ID  NUMBER;
    V_CUST_ACCT_SITE_ID NUMBER;
    V_CUST_ACCT_ID      NUMBER;
    V_PAYMENT_TERM      NUMBER;
    V_CCID              NUMBER;
    V_REQUEST_ID        NUMBER;
   
BEGIN
    FOR R1 IN CUR_INV_HEADER LOOP
   
        --DBMS_OUTPUT.PUT_LINE('INSERT LOOP');
   
        BEGIN
            SELECT CUST_TRX_TYPE_ID
            INTO V_CUST_TRX_TYPE_ID
            FROM RA_CUST_TRX_TYPES_ALL
            WHERE ORG_ID=204
            AND NAME =R1.TRANSACTION_TYPE;
           
            --DBMS_OUTPUT.PUT_LINE('TRX TYPE ID = '||V_CUST_TRX_TYPE_ID);
           
        END;
   
        BEGIN
            SELECT  HCS.CUST_ACCT_SITE_ID,HC.CUST_ACCOUNT_ID
            INTO    V_CUST_ACCT_SITE_ID,V_CUST_ACCT_ID
            FROM    HZ_CUST_SITE_USES_ALL HCS,
                    HZ_CUST_ACCT_SITES_ALL HCA,
                    HZ_CUST_ACCOUNTS HC
            WHERE   HCA.CUST_ACCT_SITE_ID=HCS.CUST_ACCT_SITE_ID
            AND HCS.SITE_USE_CODE='BILL_TO'
            AND hcs.primary_flag='Y'
            AND HC.CUST_ACCOUNT_ID=HCA.CUST_ACCOUNT_ID
            AND HC.account_number=R1.CUSTOMER_NUMBER
            AND HCA.ORG_ID=204
            AND HCS.ORG_ID=204;
           
            --DBMS_OUTPUT.PUT_LINE(V_CUST_ACCT_SITE_ID||V_CUST_ACCT_ID);    
        END;
   
        BEGIN
            SELECT TERM_ID
            INTO V_PAYMENT_TERM
            FROM RA_TERMS_TL
            WHERE NAME=R1.PAYMENT_TERMS;
           
            --DBMS_OUTPUT.PUT_LINE(V_PAYMENT_TERM);
        END;
   
        FOR R2 IN CUR_INV_LINES(R1.TRANSACTION_NO) LOOP       
               
                --DBMS_OUTPUT.PUT_LINE('INSERT HEADER AND LINE DETAILS INTO RA_INTERFACE_LINES_ALL TABLE');
               
                INSERT INTO ra_interface_lines_all
                    (interface_line_context,
                    trx_number,
                    interface_line_attribute1,
                    interface_line_attribute2,
                    --interface_line_attribute2,-
                    batch_source_name,
                    set_of_books_id,
                    currency_code, --
                    cust_trx_type_id,
                    term_id, --4
                    orig_system_sold_customer_id, --1005 --hzca.cust_account_id
                    orig_system_bill_customer_id,--1005 -- hzca.cust_account_id
                    orig_system_bill_address_id, --1033 hz_cust_site_uses_all.cust_acct_site_id where site_use_code=bill_to
                    trx_date,-- sysdate
                    gl_date,-- sysdate
                    primary_salesrep_id, -- 1412 hz_cust_site_uses_all.primary_salesrep_id where site_use_code=bill_to
                    org_id, --204
                   line_number,
                    conversion_type, -- user
                    conversion_rate,--1
                    line_type,--line
                    memo_line_id, --1000
                    description, --
                    uom_code, --ea
                    quantity,--10
                    amount, --1000
                    created_by, --1009635 
                    creation_date, -- sysdate
                    last_updated_by, -- 1009635 
                    last_update_date
                    ) -- sysdate
                VALUES
                    ('LEGACY1', --INTERFACE_LINE_CONTEXT
                    R1.TRANSACTION_NO,--'XXAR'||'HEAD'||'-'||'1',
                    R1.TRANSACTION_NO,--'XXAR'||'HEAD'||'-'||'1', -- INTERFACE_LINE_ATTRIBUTE1 (invoice number)
                    R2.TRANSACTION_LINE_NO,
                    --'1',
                    'LEGACY1', --BATCH_SOURCE_NAME
                    1,--v_set_of_books_id, --SET_OF_BOOKS_ID
                    'INR', -- Currency Code
                    V_CUST_TRX_TYPE_ID,--1,--v_cust_trx_type_id,--CUST_TRX_TYPE_ID
                    V_PAYMENT_TERM,--5,-- term id
                    1001,--V_CUSTOMER_ID,--ORIG_SYSTEM_SOLD_CUSTOMER_ID, --1005 --hzca.cust_account_id
                    1001,--V_CUSTOMER_ID,--ORIG_SYSTEM_SOLD_CUSTOMER_ID, --1005 --hzca.cust_account_id
                    1024,--V_CUST_ACCT_SITE_ID,--hz_cust_site_uses_all.cust_acct_site_id where site_use_code=BILL_TO
                    R1.TRANSACTION_DATE,--'15-MAR-2011',--R1.TRX_DATE,--TRX DATE
                    R1.TRANSACTION_DATE,--'15-MAR-2011',--R1.GL_DATE, ---GL DATE
                    1005,--hz_cust_site_uses_all.primary_salesrep_id where site_use_code=BILL_TO
                    204,-- org_id for sales
                    R2.TRANSACTION_LINE_NO,
                    'User',
                    1, --CONVERSION TYPE
                    'LINE',-- line type
                    1,  --MEMO LINE ID
                    'Testing AR Invoice',-- Opening Balance line description
                    R2.UOM_CODE,--1,-- quantity
                    R2.QUANTITY,--1,-- QTY
                    R2.QUANTITY*R2.UNIT_PRICE,--1000,-- outstanding
                    FND_GLOBAL.USER_ID,--1318,-- created by
                    sysdate,
                    FND_GLOBAL.USER_ID,--1318,
                    sysdate
                    );
                   
            FOR R3 IN CUR_INV_DIST(R2.TRANSACTION_NO,R2.TRANSACTION_LINE_NO) LOOP
           
                BEGIN
               
                    SELECT CODE_COMBINATION_ID
                    INTO V_CCID
                    FROM GL_CODE_COMBINATIONS
                    WHERE CHART_OF_ACCOUNTS_ID=101
                    AND   ENABLED_FLAG='Y'
                    AND   SEGMENT1 = R3.COMPANY
                    AND   SEGMENT2 = R3.DEPARTMENT
                    AND   SEGMENT3 = R3.ACCOUNT
                    AND   SEGMENT4 = R3.SERVICE_TYPE
                    AND   SEGMENT5 = R3.FRANCHISE
                    AND   NVL(SEGMENT6,0) = NVL(R3.INTERCOMPANY,0)
                    AND   NVL(SEGMENT7,0) = NVL(R3.FUTURE,0);
                   
                    --DBMS_OUTPUT.PUT_LINE('CCID = '||V_CCID);
                   
                END;
           
                IF R3.TYPE='Receivable' THEN
                           
                       --DBMS_OUTPUT.PUT_LINE(' INSERT FOR INVOICE DISTIBUTION RECEIVABLE ACCT    TO OVERRIDE THE TRX TYPE RECVEIBALE ACCT');
           
                        INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL  
                            (account_class,
                            amount,
                            percent,
                            code_combination_id,          
                            comments,
                            interface_line_attribute1,
                            INTERFACE_LINE_ATTRIBUTE2,                      
                            interface_line_context,
                            org_id)
                        VALUES
                            ('REC',
                            R3.AMOUNT,--1000 ,--FROM STG
                            R3.PER_AMOUNT,--100, --PERCENT
                            V_CCID,--88919,  ---FROM STG DERIVE  CCID   
                            'RECEIVABLE',           
                            R3.TRANSACTION_NO,--'XXAR'||'HEAD'||'-'||'1', --FROM STG  
                            R3.TRANSACTION_LINE_NO,        
                            'LEGACY1',
                            204);             
    
                ELSIF R3.TYPE='Revenue' THEN
                        --DBMS_OUTPUT.PUT_LINE('INSERT FOR INVOICE DISTIBUTION REVENUE ACCT  TO OVERRIDE THE TRX TYPE REVENUE ACCT');             

     
                        INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL  
                            (account_class,
                            amount,
                            percent,
                            code_combination_id,            
                            comments,
                            interface_line_attribute1,
                            INTERFACE_LINE_ATTRIBUTE2,                       
                            interface_line_context,
                            org_id)
                        VALUES
                            ('REV',
                            R3.AMOUNT,--600, ---FROM STG
                            R3.PER_AMOUNT,--60, --PERCBET
                            V_CCID,--16297, --FROM STG DERIVE CCID
                            'REVENUE',           
                            R3.TRANSACTION_NO,--'XXAR'||'HEAD'||'-'||'1', --FROM STG  
                            R3.TRANSACTION_LINE_NO,        
                            'LEGACY1',
                            204);
       
                ELSE       
                    NULL;       
                END IF;                                  
                    
            END LOOP;
        END LOOP;
    END LOOP;
    COMMIT;
   
    V_REQUEST_ID :=FND_REQUEST.SUBMIT_REQUEST ('AR',
                                                'RAXMTR',
                                                'XX AR Interface ',
                                                '',
                                                FALSE,
                                                1,
                                                204,
                                                3028,
                                                'LEGACY1',
                                                TO_CHAR(trunc(SYSDATE),'YYYY/MM/DD HH24:mi:ss'),
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                NULL,
                                                'Y',
                                                NULL
                                               );


END;
/

CREATE OR REPLACE PROCEDURE APPS.XX_AR_INV_UPDT_SPA(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER,P_THRESHOLD NUMBER)
IS
   CURSOR c
   IS                                                           
      SELECT A.TRANSACTION_NO           
            ,B.UNIT_PRICE
            ,C.TYPE
            ,C.RATE_CENTER       
            ,C.FRANCHISE           
            ,C.AMOUNT       
      FROM  xx_ar_inv_HEAD_stg A
            ,xx_ar_inv_line_stg B
            ,xx_ar_inv_dist_stg1 C
      WHERE A.TRANSACTION_NO=B.TRANSACTION_NO
      AND   B.TRANSACTION_NO=C.TRANSACTION_NO
      --AND A.TRANSACTION_NO='XXDAKTRNNO1'
      --AND C.RATE_CENTER = 'LUMBERCITY, GA'--'PELHAM, GA'
      AND C.TYPE='Revenue'
      AND C.RATE_CENTER IS NOT NULL
      --AND C.FRANCHISE = 0                 
      ORDER BY A.TRANSACTION_NO,C.RATE_CENTER,C.AMOUNT;

   CURSOR c2 (p_ratecen VARCHAR2)
   IS  
      SELECT spa, value_per
        FROM xx_rc_spa_stg
       WHERE ratecenter = p_ratecen;
  
   V_TOTAL_VALUE_PER NUMBER;
   V_MAX_VALUE   VARCHAR2 (100);
   V_SPA         VARCHAR2 (100);
   V_LINES      NUMBER;
BEGIN
   fnd_file.put_line (fnd_file.output,'********START OF PROGRAM***********');
   fnd_file.put_line (fnd_file.output,'');           
  
   FOR r IN c
   LOOP
    --fnd_file.put_line (fnd_file.output,'**** START OF PROCESSING ONE LINE ****');
    --fnd_file.put_line (fnd_file.output,'');  
    fnd_file.put_line (fnd_file.output,' TRANSACTION_NO = '||r.TRANSACTION_NO||' RATECENTER = '||R.RATE_CENTER||' AMOUNT = '||R.AMOUNT);
   
    BEGIN
        SELECT  SUM(VALUE_PER)
        INTO    V_TOTAL_VALUE_PER
        FROM    XX_RC_SPA_STG
        WHERE   ratecenter = r.RATE_CENTER
        AND     spa IS NOT NULL;               
    END;
   
    IF V_TOTAL_VALUE_PER != 100 THEN
        fnd_file.put_line (fnd_file.output,'ERRORED--CHECK LOG FILE');
        fnd_file.put_line (fnd_file.log,' TRANSACTION_NO = '||r.TRANSACTION_NO||' RATECENTER = '||R.RATE_CENTER);
        fnd_file.put_line (fnd_file.output,'');
        fnd_file.put_line (fnd_file.log,'% SUM IS NOT 100 FOR THIS RATE CENTER : '||R.RATE_CENTER);
        fnd_file.put_line (fnd_file.log,'SKIPPING THIS LINE FOR UPDATING OR INSERTING SPA VALUES');
        --DBMS_OUTPUT.PUT_LINE('THE TOTAL PERCENTAGE VALUE IS NOT 100 FOR THIS RATECENTER : '||R.RATE_CENTER);
    ELSE
        NULL;
        --fnd_file.put_line (fnd_file.output,'% SUM FOR THIS RATE CENTER IS 100');  
        --fnd_file.put_line (fnd_file.output,'PROCEEDING FOR UPDATING OR INSERTING SPA DETAILS');
      IF r.AMOUNT < P_THRESHOLD--100
      THEN     
         --fnd_file.put_line (fnd_file.output,'DIST AMOUNT IS LESS THAN THRESHOLD VALUE');
         BEGIN
            SELECT MAX (VALUE_PER)
              INTO v_max_value
              FROM xx_rc_spa_stg
             WHERE ratecenter = r.RATE_CENTER
               AND spa IS NOT NULL;
            --fnd_file.put_line (fnd_file.output,'MAX % VALUE FOR THIS RATE CENTER IS = ' || v_max_value);
            --DBMS_OUTPUT.put_line ('max value =  ' || v_max_value);

            BEGIN
               SELECT spa
                 INTO v_spa
                 FROM xx_rc_spa_stg
                WHERE value_per = v_max_value
                  AND ratecenter = r.RATE_CENTER
                  AND spa IS NOT NULL;
                --fnd_file.put_line (fnd_file.output,'CORRESPONDING SPA FOR THE ABOVE MAX VALUE IS =  ' || v_spa);
               --DBMS_OUTPUT.put_line ('spa =  ' || v_spa);

               UPDATE xx_ar_inv_dist_stg1
                  SET FRANCHISE = v_spa
                WHERE TRANSACTION_NO = r.TRANSACTION_NO
                AND RATE_CENTER = r.RATE_CENTER
                AND AMOUNT = r.AMOUNT;
               
                fnd_file.put_line (fnd_file.output,v_spa||' - UPDATED THIS SPA FOR THE FRANCHISE SEGMENT VALUE');
               
            EXCEPTION
               WHEN OTHERS
               THEN
                  fnd_file.put_line (fnd_file.output,'ERRORED--CHECK LOG FILE'); 
                  fnd_file.put_line (fnd_file.log,' TRANSACTION_NO = '||r.TRANSACTION_NO||' RATECENTER = '||R.RATE_CENTER);
                  fnd_file.put_line (fnd_file.log,''); 
                  fnd_file.put_line (fnd_file.log,'ERROR AT SPA RETRIEVAL FOR THE ABOVE RATE CENTER');
                  --DBMS_OUTPUT.put_line ('error at spa retrival'||r.RATE_CENTER||v_max_value);
                  fnd_file.put_line (fnd_file.output,SQLERRM);
                  --DBMS_OUTPUT.put_line (SQLERRM);
            END;
         EXCEPTION
            WHEN OTHERS
            THEN
                fnd_file.put_line (fnd_file.output,'ERRORED--CHECK LOG FILE');
                fnd_file.put_line (fnd_file.log,' TRANSACTION_NO = '||r.TRANSACTION_NO||' RATECENTER = '||R.RATE_CENTER);
                fnd_file.put_line (fnd_file.log,''); 
                fnd_file.put_line (fnd_file.log,'ERROR AT MAX % VALUE RETRIEVAL');
               --DBMS_OUTPUT.put_line ('error at max value retrival');
         END;
      ELSE
        --fnd_file.put_line (fnd_file.output,'DIST AMOUNT IS GREATER THAN THRESHOLD VALUE');
       
        BEGIN
            SELECT COUNT(*)
            INTO   V_LINES
            FROM XX_RC_SPA_STG
            WHERE RATECENTER = r.RATE_CENTER;
        END;
               
        fnd_file.put_line (fnd_file.output,'INSERTING '||V_LINES||' INTO DIST TABLE AND UPDATING SPA DETAILS');
        
         --DBMS_OUTPUT.put_line (   'max value is greater than 100'|| r.RATE_CENTER|| r.AMOUNT);

         FOR r2 IN c2 (r.RATE_CENTER)
         LOOP
            --DBMS_OUTPUT.put_line ('insert this spa into dist table' || r2.spa);

            INSERT INTO xx_ar_inv_dist_stg1
               SELECT DISTINCT *
                          FROM xx_ar_inv_dist_stg1
                         WHERE TRANSACTION_NO = r.TRANSACTION_NO
                           AND RATE_CENTER = r.RATE_CENTER
                           AND AMOUNT = r.AMOUNT
                           AND FRANCHISE = 0;

            UPDATE xx_ar_inv_dist_stg1
               SET FRANCHISE = r2.spa,
                   AMOUNT = round((r2.value_per * (r.AMOUNT / 100)),2)
             WHERE TRANSACTION_NO = r.TRANSACTION_NO
               AND RATE_CENTER = r.RATE_CENTER
               AND AMOUNT = r.AMOUNT
               AND FRANCHISE = 0
               AND ROWNUM < 2;

            COMMIT;
           
            --fnd_file.put_line (fnd_file.output,'LINE IS INSERTING AND THIS SPA '|| r2.spa||' IS UPDATED' );
           
           
         END LOOP;

         DELETE FROM xx_ar_inv_dist_stg1
               WHERE TRANSACTION_NO = r.TRANSACTION_NO
               AND RATE_CENTER = r.RATE_CENTER
               AND AMOUNT = r.AMOUNT
               AND FRANCHISE = 0;
      END IF;
    END IF;
    fnd_file.put_line (fnd_file.output,'');    
    --fnd_file.put_line (fnd_file.output,'**** END OF PROCESSING ONE LINE ****');
    fnd_file.put_line (fnd_file.output,'');
   END LOOP;
         
   COMMIT;

    fnd_file.put_line (fnd_file.output,'****** END OF PROGRAM ******');
   
END;
/